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.
