On 2012-10-12 01:14, Sergey Konoplev wrote:
On Thu, Oct 11, 2012 at 2:23 AM, Condor <condor@xxxxxxxxxx> wrote:
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.
What about dropping table_phonegist_idx index? Is it used somewhere
else?
Ill try this night, no isn't used elsewhere.
ps. BTW how do you cope with the pg_trgm ASCII alphanumeric
restriction? Transliteration?
The tel field has alphanumeric values and md5 hash values sometimes of
some phone.
Server is setup and started in CP1251 encoding.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
