From: Tom Lane <tgl@sss.pgh.pa.us>
To: John Papandriopoulos <dr.jpap@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT
Date: Mon, 06 Dec 2010 13:03:44 -0500
Message-ID: <28785.1291658624@sss.pgh.pa.us> (raw)
In-Reply-To: <idhqku$2ujm$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>
<idfrnv$2bc$1@news.hub.org>
<26275.1291568163@sss.pgh.pa.us>
<29662.1291580078@sss.pgh.pa.us>
<idhqku$2ujm$1@news.hub.org>
John Papandriopoulos <dr.jpap@gmail.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, dr.jpap@gmail.com
Subject: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT
In-Reply-To: <28785.1291658624@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox