Slow query using simple equality operators

To follow up on my own email, by disabling BitmapScan in my
postgresql.conf the performance seems to be better. Is something
wrong with the query analyzer?

v=# explain select s_content,textdir from text_search where
path_id='1' AND tb_id='P2_TB00001';
QUERY PLAN
------------------------------------------------------------------------
------------------------
Index Scan using idx_search_path_id on text_search
(cost=0.00..4081857.23 rows=5083 width=36)
Index Cond: (path_id = 1)
Filter: (tb_id = 'P2_TB00001'::text)
(3 rows)

Benjamin

On Apr 23, 2007, at 3:38 PM, Benjamin Arai wrote:

Benjamin Arai <benjamin@araisoft.com> writes:

To follow up on my own email, by disabling BitmapScan in my
postgresql.conf the performance seems to be better. Is something
wrong with the query analyzer?

I just rewrote choose_bitmap_and() to fix some issues that might be
related to this; please try your query with 8.2.4 or 8.1.9 as the
case may be (and next time, mention what version you're using
right off the bat).

regards, tom lane

Hi,

I upgraded to 8.2.4 but there was no significant change in
performance. I did notice that hte query appears to be executed
incorrectly. Specifically, it appears to perform each equality
operation then perform a bitwise AND. I think it should instead be
performing one of the equalities then use the results to perform the
other. This would create a vastly smaller dataset for the second to
work with.

I have pasted the EXPLAIN ANALYZE below to illustrate:

=# explain analyze select s_content,textdir from (SELECT * from
text_search WHERE tb_id='P2_TB00001') AS a where path_id='4';

QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
-----
Bitmap Heap Scan on text_search (cost=39864.98..59746.59 rows=5083
width=36) (actual time=7418.651..7418.863 rows=52 loops=1)
Recheck Cond: ((path_id = 4) AND (tb_id = 'P2_TB00001'::text))
-> BitmapAnd (cost=39864.98..39864.98 rows=5083 width=0)
(actual time=6706.928..6706.928 rows=0 loops=1)
-> Bitmap Index Scan on idx_search_path_id
(cost=0.00..16546.34 rows=1016571 width=0) (actual
time=6609.458..6609.458 rows=52777 loops=1)
Index Cond: (path_id = 4)
-> Bitmap Index Scan on idx_search_tb_id
(cost=0.00..23315.85 rows=1016571 width=0) (actual
time=96.903..96.903 rows=411341 loops=1)
Index Cond: (tb_id = 'P2_TB00001'::text)
Total runtime: 7419.128 ms
(8 rows)

Is there are way to force the "Bitmap Index Scan on idx_search_tb_id"
to perform first then let "Bitmap Index Scan on idx_search_path_id"
use the results?

Benjamin

On Apr 23, 2007, at 5:12 PM, Tom Lane wrote:

Benjamin Arai wrote:

Hi,

I upgraded to 8.2.4 but there was no significant change in performance.
I did notice that hte query appears to be executed incorrectly.

I have pasted the EXPLAIN ANALYZE below to illustrate:

=# explain analyze select s_content,textdir from (SELECT * from
text_search WHERE tb_id='P2_TB00001') AS a where path_id='4';

What's wrong with a plain select * from text_search where
tb_id='P2_TB00001' and path_id=4; ?

You posted the explain output of something like that earlier, but that
was on an older pg 8 and without analyze.

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------

Bitmap Heap Scan on text_search (cost=39864.98..59746.59 rows=5083
width=36) (actual time=7418.651..7418.863 rows=52 loops=1)
Recheck Cond: ((path_id = 4) AND (tb_id = 'P2_TB00001'::text))
-> BitmapAnd (cost=39864.98..39864.98 rows=5083 width=0) (actual
time=6706.928..6706.928 rows=0 loops=1)
-> Bitmap Index Scan on idx_search_path_id
(cost=0.00..16546.34 rows=1016571 width=0) (actual
time=6609.458..6609.458 rows=52777 loops=1)

The row estimates are off by a factor 20 (~1M rows estimated, 50k rows
actual). Are you sure you analyze'd recently?

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //

Hi,

Apparently, the amount of free space on the partition makes a big
difference in performance. I went from about 30% free space to about
5% and this triggered the performance issues. As soon as freed up
the drive to about 30% free space again the performance issues went
away.

Benjamin

On Apr 24, 2007, at 1:12 AM, Alban Hertroys wrote: