CODEWITHSUNDEEP
sqlpostgresql
Mateusz Urbański
Mateusz Urbański

Reputation: 7862

PostgreSQL trigram Indexes behaviour in characters < 3

In my PostgreSQL database I have slides table which has name column. I want to implement search. I tried trigram indexes in PostgreSQL. I've created following index:

CREATE INDEX index_slides_on_name_trigram ON slides USING gin (name gin_trgm_ops);

When I searching for at least 3 characters index is working fine:

explain analyze SELECT name FROM slides WHERE name ILIKE '%hur%';

QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on slides  (cost=18.97..1809.80 rows=900 width=25) (actual time=0.810..6.316 rows=906 loops=1)
   Recheck Cond: ((name)::text ~~* '%hur%'::text)
   Heap Blocks: exact=583
   ->  Bitmap Index Scan on index_slides_on_name_trigram  (cost=0.00..18.75 rows=900 width=0) (actual time=0.552..0.552 rows=906 loops=1)
         Index Cond: ((name)::text ~~* '%hur%'::text)
 Planning time: 0.973 ms
 Execution time: 6.506 ms
(7 rows)

but when my search phrase is shorter than 3 characters index is not being used:

explain analyze SELECT name FROM slides WHERE name ILIKE '%hu%';

QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 Seq Scan on slides  (cost=0.00..2803.86 rows=932 width=25) (actual time=0.053..31.075 rows=910 loops=1)
   Filter: ((name)::text ~~* '%hu%'::text)
   Rows Removed by Filter: 25399
 Planning time: 0.954 ms
 Execution time: 31.220 ms
(5 rows)

Is this the way the trigram indexes works? I'm wondering is there any better way to implement search.

Upvotes: 2

Views: 1235

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246463

PostgreSQL thinks that it will be more efficient to use a sequential scan than the trigram index if the query string is too short.

This is because short search strings will probably find a lot of results, correct or not, and sequential scans are usually faster if you need to examine a bigger part of the table.

You can test for yourself by first running

SET enable_seqscan=off;

Then PostgreSQL will try to avoid sequential scans if possible.

If you are nore sure if PostgreSQL is right, you can execute the query with sequential scans turned on or off and measure how long it takes in each case.

Upvotes: 4

Related Questions