Re: A problem with the IN clause

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(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
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>
>