On Tue, 19 Feb 2019 at 11:11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> What I'd like to understand about this patch is how it relates
> to Amit L.'s work on making the planner faster for partitioned
> UPDATE/DELETE cases (https://commitfest.postgresql.org/22/1778/).
It's close to being unrelated and completely unrelated for the INSERT case.
It's true that currently we obtain locks on all partitions during the
planning of an UPDATE to a partitioned table. During execution, we
attempt to obtain all those locks again in
ExecSetupPartitionTupleRouting(), looking up the local lock table
isn't free, so it's beneficial to not wastefully do this again. In
the case of PREPAREd UPDATE statements, delaying obtaining any
partition lock until we first route a tuple to the partition can save
big time. Patches further on in Amit's patch series will reduce the
locking taken in the planner to just non-pruned partitions, these
partitions might not be the same ones that tuples get routed to.
Remember that tuples can go to any partition, not just the ones listed
in the ModifyTable node's subnodes. So in that case, this patch just
complements Amit's patch series.
> I think that that might render this moot? And if it doesn't,
> how much does it really matter?
Quite a bit. Here's an INSERT benchmark from the first email in this
thread. The test was done with 10k partitions.
I wrote:
> Patched:
> tps = 27811.427620 (excluding connections establishing)
> tps = 28617.417308 (excluding connections establishing)
>
> Unpatched:
> tps = 130.446706 (excluding connections establishing)
> tps = 119.726641 (excluding connections establishing)
Remember that planner will obtain no locks on any partitions during
INSERT. In the above case, the executor in the unpatched version
obtains 10k locks when only 1 is needed. In the patched version the
executor just takes a lock on the single partition that the tuple is
routed to.
> You can't really postpone taking
> a lock on a relation that the planner is going to do anything
> nontrivial with.
Not sure what you mean here. This is an executor change. What does
the planner care about what the executor does?
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services