On 27/04/12 09:33, Tom Lane wrote:
Toby Corkindale<toby.corkindale@xxxxxxxxxxxxxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
