Re: Trigram (pg_trgm) GIN index not used

On 21/02/2013 12:52, Ivan Voras wrote:

> I'd like to use pg_trgm for matching substrings case-insensitively, but=

> it doesn't seem to use the index:


As a sort-of followup, the '%' operator kind of works but takes
incredibly long time, and the selectivity estimates are completely wrong:=


nn=3D> vacuum analyze documents;
VACUUM
nn=3D> explain select id,title from documents where raw_data % 'zagreb';
                                       QUERY PLAN

-------------------------------------------------------------------------=
----------------
 Bitmap Heap Scan on documents  (cost=3D128.42..330.87 rows=3D54 width=3D=
108)
   Recheck Cond: ((raw_data)::text % 'zagreb'::text)
   ->  Bitmap Index Scan on documents_raw_data_trgm  (cost=3D0.00..128.40=

rows=3D54 width=3D0)
         Index Cond: ((raw_data)::text % 'zagreb'::text)
(4 rows)

nn=3D> explain analyze select id,title from documents where raw_data %
'zagreb';
                                                               QUERY
PLAN
-------------------------------------------------------------------------=
----------------------------------------------------------------
 Bitmap Heap Scan on documents  (cost=3D128.42..330.87 rows=3D54 width=3D=
108)
(actual time=3D98750.283..98750.283 rows=3D0 loops=3D1)
   Recheck Cond: ((raw_data)::text % 'zagreb'::text)
   ->  Bitmap Index Scan on documents_raw_data_trgm  (cost=3D0.00..128.40=

rows=3D54 width=3D0) (actual time=3D26.748..26.748 rows=3D51874 loops=3D1=
)
         Index Cond: ((raw_data)::text % 'zagreb'::text)
 Total runtime: 98750.623 ms
(5 rows)


There is no IO load during this query.