The following query produces a Recheck Cond and a costly Bitmap Heap Scan
even though I have a composite index that covers both columns being filtered
and selected. I believe this is because the initial bitmap scan produces
2912 rows, which is too many for the available bitmap space. I've tried
rewriting the command as "Select ... group by" but it still uses the BHS. Is
there a way to rewrite this command that would improve performance by
avoiding the costly Bitmap Heap Scan?
SELECT distinct store_id, book_id FROM "sales_points" WHERE
"sales_points"."store_id" IN (1, 2, 3, 4, 5, 6, 199, 201, 202) AND
"sales_points"."book_id" IN (421, 422, 419, 420)
Here is the explain/analyze output:
"HashAggregate (cost=5938.72..5939.01 rows=97 width=8) (actual
time=10.837..10.854 rows=32 loops=1)"
" -> Bitmap Heap Scan on sales_points (cost=47.03..5936.53 rows=2191
width=8) (actual time=0.547..5.296 rows=4233 loops=1)"
" Recheck Cond: (book_id = ANY ('{421,422,419,420}'::integer[]))"
" Filter: (store_id = ANY ('{1,2,3,4,5,6,199,201,202}'::integer[]))"
" -> Bitmap Index Scan on index_sales_points_on_book_id
(cost=0.00..46.92 rows=4430 width=0) (actual time=0.469..0.469 rows=4233
loops=1)"
" Index Cond: (book_id = ANY ('{421,422,419,420}'::integer[]))"
"Total runtime: 10.935 ms"
Actual runtime is more like 15ms when tested against a development database
(which gave est. total runtime of 6ms). Under load in production, the
command takes 10,158 ms. Tuning Postgre is not an option, as the instance
is provided by Heroku and as far as I know cannot be tuned by me.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Avoiding-Recheck-Cond-when-using-Select-Distinct-tp5746290.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
