On 2012-10-12 03:27, Tom Lane wrote:
Condor <condor@xxxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
