From: John Papandriopoulos <dr.jpap@gmail.com>
Subject: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps
	vmem compared to SELECT
Date: Sun, 05 Dec 2010 03:06:39 -0800
Organization: Hub.Org Networking Services
Lines: 39
Message-ID: <idfrnv$2bc$1@news.hub.org>
References: <idbo6g$92r$1@news.hub.org> <14012.1291443654@sss.pgh.pa.us>
	<idd14l$a4u$1@news.hub.org> <23792.1291480923@sss.pgh.pa.us>
	<idec54$2hpd$1@news.hub.org> <13159.1291502400@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US;
	rv:1.9.2.8) Gecko/20100802 Thunderbird/3.1.2
In-Reply-To: <13159.1291502400@sss.pgh.pa.us>
To: pgsql-performance@postgresql.org

On 12/4/10 2:40 PM, Tom Lane wrote:
> [ 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.

Thanks for the pointer to the code and explanation.

In inheritance_planner(...) I see the memcpy of the input query tree, but for my example constraint exclusion would only result in one child being included.  Or is the O(N^2) memory usage from elsewhere?

> 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).

Is this the same as saying that the inheritance_planner(...) can be avoided if it were known that the children have the same rowtype as the parent?  Is it easy to check?

> But the inheritance mechanism was never intended to scale to anything like
> this number of children.

Unfortunately so. :(

When I push the number of child tables up to 10k, the SELECT planning starts to slow down (~1 sec), though no swapping.
 
> 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.

Thanks for the reference---I'm well aware of it, but it was not clear to me why: the reason I was structuring my partition inheritance as a tree, because I thought it was simply a case of time-to-scan the CHECK constraints at any level in the inheritance hierarchy.  You've been a great help in helping my understanding PostgreSQL inheritance.

Best,
John