Re: Bug? Query plans / EXPLAIN using gigabytes of memory

On 04/29/2012 07:19 PM, Toby Corkindale wrote:
> On 27/04/12 09:33, Tom Lane wrote:
>> Toby Corkindale<toby.corkindale@strategicdata.com.au>  writes:
>>> I've created a bit of a test case now.
>>> There's a Perl script here:
>>> http://dryft.net/postgres/
>>
>> AFAICT, what is happening is that we're repeating the planning of that
>> messy nest of views for each child table of foo.  For most of the
>> children the planner eventually decides that the join degenerates to
>> nothing because of constraint exclusion, but not until it's expended a
>> fair amount of time and memory space per child.
>>
>> I looked at whether we could improve that by having inheritance_planner
>> use a temporary memory context per child, but that doesn't look very
>> practical: it would add a good deal of extra data-copying overhead,
>> and some of the data structures involved are not easily copiable.
>>
>> The general scheme of replanning per child might be questioned as well,
>> but IMO it's fairly important given the looseness of inheritance
>> restrictions --- it's not unlikely that you *need* different plans for
>> different children.  We might be able to reconsider that approach
>> whenever we invent an explicit concept of partitioned tables, since
>> presumably the partitions would all be essentially alike.
>>
>> In the meantime, the best advice I can come up with is to reconsider
>> whether you need so many partitions.  That mechanism is really designed
>> for only a dozen or two partitions at most.
>
>
> Hi Tom,
> Thanks for looking into this, I appreciate you spending the time.
>
> The system I've come up with for partitioning this data requires quite
> a lot of partitions - say thirty to seventy - but I didn't realise it
> would cause trouble down the line, so I'll see if it can be reworked
> to reduce the number.
>
> For what it's worth, the actual query that was blowing out to
> gigabytes was only hitting a couple of dozen partitions per table it
> was touching - but it was hitting three such tables, about sixteen
> times (!) each.
>
> I'm still curious about why I can do a SELECT * FROM complexview
> without using much memory, but an UPDATE foo FROM complexview causes
> all the memory to get exhausted?
>
> Thanks,
> Toby
>
Does

UPDATE foo set <values>
where foo.id in (select id from complexview...)

also swallow the memory?