Re: A problem with the IN clause - Mailing list pgsql-general

Sean Shanny <shannyconsulting@earthlink.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