Re: A problem with the IN clause

Sean Shanny <shannyconsulting(at)earthlink(dot)net> writes:
> When I run this against our warehouse instance I get an out of memory
> error. If I remove the
> AND t1.newsletterid_key IN (SELECT newsletterid FROM t_newscontentstatic)
> portion if runs fine.

I think the problem is not there at all, but with drastic
underestimation of the number of rows coming from f_pageviews:

> -> Seq Scan on f_pageviews t1
> (cost=0.00..585486.72 rows=1 width=24) (actual
> time=60502.415..-463715.543 rows=24422838 loops=1)
> Filter: ((date_key >= 496) AND
> (date_key <= 502))

The plan you say is failing is trying to load this result into a
hashtable ... and since it's only expecting 1 row, it's not going
to try to partition the hashtable or anything like that.

Are your ANALYZE stats for f_pageviews up to date? Perhaps you need to
increase the stats target for date_key to get more resolution in the
stats.

regards, tom lane