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

On 2012-10-12 01:14, Sergey Konoplev wrote:
> On Thu, Oct 11, 2012 at 2:23 AM, Condor <condor(at)stz-bg(dot)com> 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.