From: John Papandriopoulos <dr.jpap@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT
Date: Sun, 05 Dec 2010 21:00:13 -0800
Message-ID: <idhqku$2ujm$1@news.hub.org> (raw)
In-Reply-To: <29662.1291580078@sss.pgh.pa.us>
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>
On 12/5/10 12:14 PM, Tom Lane wrote:
> I wrote:
>> 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 ...)
>
> That worked better than I thought it would --- see
> http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=d1001a78ce612a16ea622b558f5fc2b68c4...
> I'm not intending to back-patch this, but it ought to apply cleanly to
> 9.0.x if you want it badly enough to carry a local patch.
Fantastic, Tom! Thank you kindly for taking the time to create the patch.
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? (I'm not yet sufficiently familiar with the source to determine on my own.) If that's the case, is there a simple test (like cardinality of columns) that can be used to differentiate partitioning from general inheritance cases?
Thanks again!
John
Simple partitioning test timing with 4096 children:
> $ echo "explain select * from ptest where id = 34324234; \q" | time -p psql ptest
> QUERY PLAN
> ----------------------------------------------------------------------------
> Result (cost=0.00..80.00 rows=24 width=4)
> -> Append (cost=0.00..80.00 rows=24 width=4)
> -> Seq Scan on ptest (cost=0.00..40.00 rows=12 width=4)
> Filter: (id = 34324234)
> -> Seq Scan on ptest_65 ptest (cost=0.00..40.00 rows=12 width=4)
> Filter: (id = 34324234)
> (6 rows)
>
> real 0.55
> user 0.00
> sys 0.00
> $ echo "explain delete from ptest where id = 34324234; \q" | time -p psql ptest
> QUERY PLAN
> ----------------------------------------------------------------------
> Delete (cost=0.00..80.00 rows=24 width=6)
> -> Seq Scan on ptest (cost=0.00..40.00 rows=12 width=6)
> Filter: (id = 34324234)
> -> Seq Scan on ptest_65 ptest (cost=0.00..40.00 rows=12 width=6)
> Filter: (id = 34324234)
> (5 rows)
>
> real 10.47
> user 0.00
> sys 0.00
> $ echo "explain update ptest set id = 34324235 where id = 34324234; \q" | time -p psql ptest
> QUERY PLAN
> ----------------------------------------------------------------------
> Update (cost=0.00..80.00 rows=24 width=6)
> -> Seq Scan on ptest (cost=0.00..40.00 rows=12 width=6)
> Filter: (id = 34324234)
> -> Seq Scan on ptest_65 ptest (cost=0.00..40.00 rows=12 width=6)
> Filter: (id = 34324234)
> (5 rows)
>
> real 9.53
> user 0.00
> sys 0.00
> $
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: dr.jpap@gmail.com
Subject: Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT
In-Reply-To: <idhqku$2ujm$1@news.hub.org>
* 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