From: | Condor <condor(at)stz-bg(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | How to raise index points when equal and like is used with gist ? |
Date: | 2012-10-11 09:23:40 |
Message-ID: | bc563b5da91c856e80e7ca72e7836ca3@stz-bg.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | JC de Villa | 2012-10-11 09:25:04 | Re: auto-increment field : in a simple way |
Previous Message | Vineet Deodhar | 2012-10-11 09:11:43 | Re: auto-increment field : in a simple way |