Re: slow bitmap heap scans on pg 9.2

On Thursday, April 11, 2013, Steve Singer wrote:

>
> I think the reason why it is picking the hash join based plans is because
> of
>
> Index Scan using table_b_1_ptid_orgid_ym_unq on table_b_1 b
> (cost=0.00..503.86 rows=1 width=10) (actual time=0.016..0.017 rows=1
> loops=414249)
> Index Cond: ((a.id = a_id) AND (organization_id = 2)
> AND (year = 2013) AND (month = 3))
> Filter: (product_id = 1)
>

Trying to reason about how the planner estimates costs for the inner side
of nested loops makes my head hurt.
So before doing that, could you run explain (analyze,buffers) on both of
these much simpler (but hopefully morally equivalent to this planner node)
sql:

select * from table_b_1_b where a_id = <some plausible value> and
organization_id=2 and year=2013 and month=3

select * from table_b_1_b where a_id = <some plausible value> and
organization_id=2 and year=2013 and month=3 and product_id=1

Of particular interest here is whether the estimate of 1 row is due to the
specificity of the filter, or if the index clauses alone are specific
enough to drive that estimate. (If you get many rows without the
product_id filter, that would explain the high estimate.).

Please run with the default cost parameters, or if you can't get the right
plan with the defaults, specify what the used parameters were.

Cheers,

Jeff