How to raise index points when equal and like is used with gist ?

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.

Responses

Browse pgsql-general by date

  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