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

Condor <condor(at)stz-bg(dot)com> writes:
> 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

You sure that server is 9.2? Because that looks like a planner bug we
squelched some time ago, wherein it was way too enthusiastic about
adding more indexes to a BitmapAnd.

If it is 9.2, please send a self-contained test case, that is some test
data (and settings, if you're using nondefault ones) that makes it do
this.

regards, tom lane