PostgreSQL trigram Indexes behaviour in characters < 3

Question

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.

I asked a similar question here dba.stackexchange.com/questions/331929/…. Laurenz is spot on, but the link has more context. There is an ugly workaround for it but may not be worth it for latin characters. It helps with short input string but makes things worse for long ones. — Pawel Zieminski
– Pawel Zieminski, Commented Mar 7, 2024 at 19:32

Laurenz Albe · Accepted Answer · 2017-06-27 07:51:14Z

4

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.

answered Jun 27, 2017 at 7:51

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL trigram Indexes behaviour in characters < 3

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related