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

On 2012-10-12 03:27, Tom Lane wrote:
> 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.

Yes, Im sure:
  PostgreSQL 9.2.1 on x86_64-slackware-linux-gnu, compiled by
x86_64-slackware-linux-gcc (GCC) 4.7.1, 64-bit

>
> 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.
>

Hm ... strange problem I catch. When I try to reproduce the problem,
with test table, I made a very little table

http://pastebin.com/nEK3cRr2

When I run the same type of query results is different:
  Seq Scan on users  (cost=0.00..1.12 rows=1 width=26) (actual
time=0.014..0.016 rows=1 loops=1)
    Filter: ((tel ~~ '09%'::text) AND (firstname = 'GREG'::text) AND
((middlename || lastname) ~~ '%%'::text))
    Rows Removed by Filter: 5
  Total runtime: 0.042 ms
(4 rows)


Okay, may be the problem is because I use cp1251 encoding .. lets
change the data values, drop table, insert cp1251 values,
start vacuum and result was the same speed  Total runtime: 0.052 ms the
same type of scan was used:

  Seq Scan on users  (cost=0.00..1.14 rows=1 width=132) (actual
time=0.019..0.021 rows=1 loops=1)
    Filter: ((tel ~~ '09%'::text) AND (firstname = 'CP1251 CHARS
HERE'::text) AND ((middlename || lastname) ~~ '%%'::text))
    Rows Removed by Filter: 6
  Total runtime: 0.052 ms

Even without tel filed result and type of scan is the same (Seq Scan).

Now first name is write in cyrillic and mean "GREG" (I replace it with
CP1251 CHARS HERE, because some ppl did not have cyrillic encoding).
When I run the same query on the same database but different table that
give strange result Bitmap Heap Scan. Index field is the same like test
table from pastebin, no difference.


And here I must say the history of the table. That table was made on
psql 7.3 version and migrate on every major upgrade of the server that
require dump/restore of database if that information is valuable.

Any one has ideas what is going wrong on that table ? Why the same
query on two different table with the same data gives me different scan
results ?

Regards,
C