Re: Slow query using simple equality operators

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 <benjamin(at)araisoft(dot)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
>