Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT

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=d1001a78ce612a16ea622b558f5fc2b68c45ab4c
> 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
> $