CODEWITHSUNDEEP
sqlruby-on-railspostgresql
Jack
Jack

Reputation: 5434

Slow wildcard search LIKE across multiple columns PostgreSQL/Rails

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

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:

I'm using PostgreSQL 13 and Ruby on Rails.

Upvotes: 0

Views: 1468

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656596

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 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:

Upvotes: 4

Related Questions