On Tue, 19 Feb 2019 at 11:11, Tom Lane <t...@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
