Slow wildcard search LIKE across multiple columns PostgreSQL/Rails

Question

I'm trying to optimize these slow queries (excuse the SQL mixed with Ruby on Rails):

WHERE name ILIKE %<the user's search text>%
WHERE lower(NAME) LIKE :search OR lower(BARCODE) LIKE :search OR lower(SKU) like :search, search: "%<the user's search text>%"

As you can see, these are wildcard queries with the % at the beginning and end, which means normal indexing is useless. The table consists of items, and the queries are fine when there are not a lot of items for the user, but when the user has a lot of items (tens of thousands), this takes a long time (like tens of seconds). How can I improve the performance? The search text is a portion of a barcode or name of the product, so it's different than if I was just trying to search for text (I'd use full text search in that case so that searches for 'dog' would yield results containing 'dogs' or 'doggy', etc.). In one of the use cases, I'm also searching across multiple columns on the same table.

Some initial approaches I considered but not sure if these will work:

full text search (add a column that is the to_tsvector of the multiple columns to search on then add a gin index for the new column)
trigram index (more suitable?)
other suggestions I've not thought of

I'm using PostgreSQL 13 and Ruby on Rails.

AFAIK trigram indexes are what you want. A quick way to check is to get the SQL you're going to use (`Model.where(...).to_sql`), have a look at `explain the_sql` in `psql` (you'll probably see a table scan), then add the index and look at the EXPLAIN output again (and you should see it looking at the new index). — mu is too short, Oct 18 '21 at 21:09

Erwin Brandstetter · Accepted Answer · 2023-04-09T20:16:07.270

Like mu already hinted: create a trigram index on the target column(s). For the first example:

CREATE INDEX tbl_name_gin_trgm_idx  ON tbl USING gin (name gin_trgm_ops);

A trigram index also supports case insensitive matching, but to cooperate with a plain trigram index, rewrite the second example with ILIKE:

WHERE name ILIKE :search OR barcode ILIKE :search OR sku ILIKE :search

You can support this with three indices like the example above. That's most versatile. Like, when various columns might be combined in the same search.

Or one index with three index columns. Less versatile, but faster and smaller.

Or one trigram index with one concatenated expression. Least versatile, but fastest. Using the custom function immutable_concat_ws() as defined and explained here:

Create an immutable clone of concat_ws

CREATE INDEX tbl_special_gin_trgm_idx ON tbl USING gin (immutable_concat_ws('|', name, barcode, sku) gin_trgm_ops);

Combined with this WHERE clause in queries:

WHERE immutable_concat_ws('|', name, barcode, sku) ILIKE :search
AND  (name ILIKE :search OR barcode ILIKE :search OR sku ILIKE :search)

The first line of the WHERE clause pulls in the fast index.
The second line excludes possible (rare) false positives if :search matches across the separator.
If :search never contains the chosen separator | nor nested wildcards, we can drop the second line.

See:

Slow wildcard search LIKE across multiple columns PostgreSQL/Rails

1 Answers1