On Wed, Aug 29, 2018 at 5:36 PM, Amit Langote <langote_amit...@lab.ntt.co.jp> wrote: > It is more or less well known that the planner doesn't perform well with > more than a few hundred partitions even when only a handful of partitions > are ultimately included in the plan. Situation has improved a bit in PG > 11 where we replaced the older method of pruning partitions one-by-one > using constraint exclusion with a much faster method that finds relevant > partitions by using partitioning metadata. However, we could only use it > for SELECT queries, because UPDATE/DELETE are handled by a completely > different code path, whose structure doesn't allow it to call the new > pruning module's functionality. Actually, not being able to use the new > pruning is not the only problem for UPDATE/DELETE, more on which further > below. > > > pgbench -n -T 60 -f update.sql > > nparts master 0001 0002 0003 > ====== ====== ==== ==== ==== > 0 2856 2893 2862 2816 > 8 507 1115 1447 1872 > 16 260 765 1173 1892 > 32 119 483 922 1884 > 64 59 282 615 1881 > 128 29 153 378 1835 > 256 14 79 210 1803 > 512 5 40 113 1728 > 1024 2 17 57 1616 > 2048 0* 9 30 1471 > 4096 0+ 4 15 1236 > 8192 0= 2 7 975 > > * 0.46 > + 0.0064 > = 0 (OOM on a virtual machine with 4GB RAM) >
The idea looks interesting while going through the patch I observed this comment. /* * inheritance_planner * Generate Paths in the case where the result relation is an * inheritance set. * * We have to handle this case differently from cases where a source relation * is an inheritance set. Source inheritance is expanded at the bottom of the * plan tree (see allpaths.c), but target inheritance has to be expanded at * the top. I think with your patch these comments needs to be change? if (parse->resultRelation && - rt_fetch(parse->resultRelation, parse->rtable)->inh) + rt_fetch(parse->resultRelation, parse->rtable)->inh && + rt_fetch(parse->resultRelation, parse->rtable)->relkind != + RELKIND_PARTITIONED_TABLE) inheritance_planner(root); else grouping_planner(root, false, tuple_fraction); I think we can add some comments to explain if the target rel itself is partitioned rel then why we can directly go to the grouping planner. -- Regards, Dilip Kumar EnterpriseDB: http://www.enterprisedb.com
