Sean Shanny <shannyconsulting@earthlink.net> writes:
>> I think the problem is not there at all, but with drastic
>> underestimation of the number of rows coming from f_pageviews:
> It does not make sense that the smaller set of values in the IN clause
> would work then does it?
Look at your two plans. In one, the set of rows extracted from
f_pageviews is loaded into a hashtable, in the other, it's not. This is
exactly the sort of plan changeover that I'd expect to happen given a
change in the selectivity of a WHERE clause. The fact that the problem
appears or disappears depending on how you change the IN clause doesn't
mean that the IN clause itself is where the problem is. And certainly
an 11-row IN clause subselect isn't going to run anything out of memory,
so it's pretty implausible that this failure is coming from right there.
You should probably check with plain EXPLAIN that the production
database is generating these same plans, but based on the dev machine's
EXPLAIN ANALYZE results I don't see where else the out-of-memory could
be coming from than the hashtable for f_pageviews.
regards, tom lane