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