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

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?

ps. BTW how do you cope with the pg_trgm ASCII alphanumeric
restriction? Transliteration?

--
Sergey Konoplev

a database and software architect
http://www.linkedin.com/in/grayhemp

Jabber: gray(dot)ru(at)gmail(dot)com Skype: gray-hemp Phone: +14158679984