On Fri, Feb 22, 2013 at 8:36 AM, jackrg
<jack@groundbreakingsoftware.com> wrote:
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.
Can you show us the definition of that index?
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?
How do you know that the bitmap heap scan is costly, since you haven't gotten it to use an alternative to compare it to? As a temporary experimental measure, you could at least set enable_bitmapscan TO off, to see what happens.
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).
I don't understand the parenthetical. In the explain plan you show, where is 6ms coming from?
Under load in production, the
command takes 10,158 ms.
Do you mean 10.158 ms rather than 10,158 ms? If the production environment really takes 1000 times longer than the environment in which you gathered the EXPLAIN, then I would seriously doubt how useful that EXPLAIN could possibly be.
Cheers,
Jeff