Re: How to raise index points when equal and like is used with gist? - Mailing list pgsql-general

Condor <condor@stz-bg.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