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 <[EMAIL PROTECTED]> 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
