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

Toby Corkindale <toby(dot)corkindale(at)strategicdata(dot)com(dot)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.

regards, tom lane