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