Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 49F941337B90 for ; Mon, 6 Dec 2010 14:03:53 -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 77086-07 for ; Mon, 6 Dec 2010 18:03:46 +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 E96941337B39 for ; Mon, 6 Dec 2010 14:03:45 -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 oB6I3i72028786; Mon, 6 Dec 2010 13:03:44 -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> <26275.1291568163@sss.pgh.pa.us> <29662.1291580078@sss.pgh.pa.us> Comments: In-reply-to John Papandriopoulos message dated "Sun, 05 Dec 2010 21:00:13 -0800" Date: Mon, 06 Dec 2010 13:03:44 -0500 Message-ID: <28785.1291658624@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/63 X-Sequence-Number: 41515 John Papandriopoulos writes: > The memory issue has indeed disappeared---there was no noticeable memory increase in the three queries below, with 4096 children. Inheritance planning overhead is around 20x for UPDATE/DELETE compared to SELECT; thankfully they are required much less frequently in my case. > I am still wondering whether the inheritance_planner(...) can be avoided if the rowtypes of children are the same as the parent? Possibly, but it's far from a trivial change. The difficulty is that you'd need to generate a different plan tree structure. inheritance_planner generates a separate subtree for each target table, so that the ModifyTable node can execute each one separately and know a priori which target table the rows coming out of a particular subplan apply to. If we expand inheritance "at the bottom" like SELECT does, that table identifier would have to propagate up as part of the returned rows. It's doable but not simple. Moreover, it's far from clear this actually would save much, and it could easily slow things down at execution time. Have you done any profiling work to see where the extra time goes? I had thought that the unreferenced RTE entries would simply be ignored in each subplanning step, but maybe there's something that is examining them. regards, tom lane