Postgres Multiple Column Text Indexing

Your GiST index is not treating col4 the same way as the query treats it. Also, GiST indexes are sensitive to column order, and the column which can eliminate the most rows with the least work should go first. Presumably this would be account_id:

CREATE INDEX ix_name ON table
   USING gist (account_id, col1 gist_trgm_ops, col2 gist_trgm_ops, LOWER(col3) gist_trgm_ops, lower(col4) gist_trgm_ops);

The same index only in GIN rather than GiST form might be effective, you can try both and see. That one would not be sensitive to the order the columns are specified in the index.

The pg_trgm indexes operators support LIKE and ILIKE equally well. Rather than doing lower on two of the columns, you could just query with ILIKE rather than LIKE on the original columns and build the index on the original columns. This probably won’t change the performance much, but the simpler indexes will be more flexible.

But is it really necessary to having the leading ‘%’ in your queries? Do people expect autocomplete to work when they start typing from the middle?

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top