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?
