Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 930B41337B56 for ; Sun, 5 Dec 2010 12:56:12 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 97560-06 for ; Sun, 5 Dec 2010 16:56:05 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 4453E1337B50 for ; Sun, 5 Dec 2010 12:56:05 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id oB5Gu3RU026276; Sun, 5 Dec 2010 11:56:04 -0500 (EST) To: John Papandriopoulos cc: pgsql-performance@postgresql.org Subject: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT In-reply-to: References: <14012.1291443654@sss.pgh.pa.us> <23792.1291480923@sss.pgh.pa.us> <13159.1291502400@sss.pgh.pa.us> Comments: In-reply-to John Papandriopoulos message dated "Sun, 05 Dec 2010 03:06:39 -0800" Date: Sun, 05 Dec 2010 11:56:03 -0500 Message-ID: <26275.1291568163@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201012/51 X-Sequence-Number: 41503 John Papandriopoulos writes: > 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 copying the whole range table, even though any one child query only needs one of the child table entries. There might be some way to finesse that, but it's not clear how else to end up with a final plan tree in which each child table has the correct RT index number. You could get rid of the memory growth, at the cost of a lot of tree-copying, by doing each child plan step in a discardable memory context. I'm not sure that'd be a win for normal sizes of inheritance trees though --- you'd need to copy the querytree in and then copy the resulting plantree out again, for each child. (Hm, but we're doing the front-end copy already ...) regards, tom lane