Hello,
I have a problem with query and index scan based on pg_trgm module.
Here is few examples:
First example is with equal:
explain analyze SELECT * FROM table WHERE firstname = 'OLEG' AND
middlename || lastname LIKE '%KUZNICOV%IGORU%';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on abonats_tbl (cost=34.42..6043.65 rows=1
width=601) (actual time=2.885..14.062 rows=1 loops=1)
Recheck Cond: (firstname = 'OLEG'::text)
Filter: ((middlename || lastname) ~~ '%KUZNICOV%IGORU%'::text)
Rows Removed by Filter: 1731
-> Bitmap Index Scan on table_firstname_idx (cost=0.00..34.42
rows=1690 width=0) (actual time=0.699..0.699 rows=1732 loops=1)
Index Cond: (firstname = 'OLEG'::text)
Total runtime: 14.126 ms
(7 rows)
But if I add one like with gist index result is not filtered here is
example:
explain analyze SELECT * FROM table WHERE phone LIKE '12%' AND
firstname = 'OLEG' AND middlename || lastname LIKE '%KUZNICOV%IGORU%';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on abonats_tbl (cost=1638.89..1816.65 rows=1
width=601) (actual time=219.793..219.793 rows=0 loops=1)
Recheck Cond: ((firstname = 'OLEG'::text) AND (phone ~~
'12%'::text))
Filter: ((middlename || lastname) ~~ '%KUZNICOV%IGORU%'::text)
Rows Removed by Filter: 65
-> BitmapAnd (cost=1638.89..1638.89 rows=45 width=0) (actual
time=219.197..219.197 rows=0 loops=1)
-> Bitmap Index Scan on table_firstname_idx
(cost=0.00..34.42 rows=1690 width=0) (actual time=0.867..0.867 rows=1732
loops=1)
Index Cond: (firstname = 'OLEG'::text)
-> Bitmap Index Scan on table_phonegist_idx
(cost=0.00..1604.22 rows=33995 width=0) (actual time=217.639..217.639
rows=33256 loops=1)
Index Cond: (phone ~~ '12%'::text)
Total runtime: 220.426 ms
My question is: Is there any way how to make postgresql first to search
from field that is with equal I have index there and then to filter
result based to other conditions first gist and then other.
I think may be I should play with index points.
Im using postgresql 9.2.1 x86_64
Regards,
C.
