Re: slow bitmap heap scans on pg 9.2

On Wed, Apr 10, 2013 at 6:49 AM, Steve Singer <ssinger(at)ca(dot)afilias(dot)info>wrote:

> I'm encountering an issue where PG 9.2.4 (we also see this with 9.2.3) is
> picking a plan involving a bitmap heap scan that turns out to be much
> slower than a nested-loop plan using indexes.
>
> The planner picks the hashjoin plan by default (see attached files)
>
> Bitmap Heap Scan on public.table_b_2 b (cost=172635.99..9800225.75
> rows=8435754 width=10) (actual t
> ime=9132.194..1785196.352 rows=9749680 loops=1)
> Recheck Cond: ((b.organization_id = 3) AND
> (b.year = 2013) AND (b.month = 3))
> Rows Removed by Index Recheck: 313195667
> Filter: (b.product_id = 2)
>

I think the index recheck means your bitmap is overflowing (i.e. needing
more space than work_mem) and so keeping only the pages which have at least
one match, which means all rows in those pages need to be rechecked. How
many rows does the table have? You might be essentially doing a seq scan,
but with the additional overhead of the bitmap machinery. Could you do
"explain (analyze,buffers)", preferably with track_io_timing set to on?

Cheers,

Jeff