Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT

John Papandriopoulos <dr(dot)jpap(at)gmail(dot)com> writes:
> I set up my schema using a machine generated SQL file [1] that simply
> creates a table
> create table ptest ( id integer );
> and N = 0..4095 inherited children
> create table ptest_N (
> check ( (id >= N_min) and (id <= N_max) )
> ) inherits (ptest);

> that split the desired id::integer range into N buckets, one for each of
> the N partitions.

> I then immediately run a query-plan using EXPLAIN that exhibits the
> described behavior: super-fast plan for a SELECT statement, without
> swapping, and memory intensive (swapping) plans for DELETE and UPDATE.

[ pokes at that for a bit ... ] Ah, I had forgotten that UPDATE/DELETE
go through inheritance_planner() while SELECT doesn't. And
inheritance_planner() makes a copy of the querytree, including the
already-expanded range table, for each target relation. So the memory
usage is O(N^2) in the number of child tables.

It's difficult to do much better than that in the general case where the
children might have different rowtypes from the parent: you need a
distinct targetlist for each target relation. I expect that we can be a
lot smarter when we have true partitioning support (which among other
things is going to have to enforce that all the children have identical
column sets). But the inheritance mechanism was never intended to scale
to anything like this number of children.

I remain of the opinion that you're using far too many child tables.
Please note the statement at the bottom of
http://www.postgresql.org/docs/9.0/static/ddl-partitioning.html:

Partitioning using these techniques will work well with up to
perhaps a hundred partitions; don't try to use many thousands of
partitions.

regards, tom lane