From: jackrg <jack@groundbreakingsoftware.com>
To: pgsql-performance@postgresql.org
Subject: Avoiding Recheck Cond when using Select Distinct
Date: Fri, 22 Feb 2013 08:36:16 -0800 (PST)
Message-ID: <1361550976611-5746290.post@n5.nabble.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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.h...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jack@groundbreakingsoftware.com
Subject: Re: Avoiding Recheck Cond when using Select Distinct
In-Reply-To: <1361550976611-5746290.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox