Tom,
It does not make sense that the smaller set of values in the IN clause would work then does it?
We took a look at the code we compiled from and indeed the patch you provided was not not applied, out fault. The patch is in the 7.4.2 code base. We upgraded today and I will be running the query again to see what happens.
You were right on the analyze, we do that in frequently as it takes a whole bunch of time over this much data. Something to cron in the middle of the night I think.
Thanks.
--sean
Tom Lane wrote:
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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend