On 17 March 2018 at 01:55, Amit Langote <langote_amit...@lab.ntt.co.jp> wrote: > Hope the attached version is easier to understand.
Hi Amit, Thanks for making the updates. I'll look at them soon. I've been thinking about how we're making these improvements for SELECT only. If planning for an UPDATE or DELETE of a partitioned table then since the inheritance planner is planning each partition individually we gain next to nothing from this patch. Generally, it seems the aim of this patch is to improve the usability of partitions in an OLTP type workload, most likely OLAP does not matter as much since planner overhead, in that case, is generally less of a concern. I experimented with the attached small patch to see if the situation could be improved if we first plan the entire query with all partitions then ignore dummy rels when planning for each individual partition. I used something along the lines of: # create table listp (a int, b int) partition by list(a); # select 'create table listp'||x||' partition of listp for values in('||x||');' from generate_series(1, <number of tables>)x; $ echo explain update listp set b = 1 where a = 1; > bench.sql $ pgbench -f bench.sql -n -T 30 postgres where <number of tables> started at 1 and went up in powers of 2 until 1024. Unpatched = your v35 patch Patched = your v35 + the attached. The TPS result from a 30-second pgbench run of the above query showed: Partitions = 1 Unpatched: 7323.3 Patched: 6573.2 (-10.24%) Partitions = 2 Unpatched: 6784.8 Patched: 6377.1 (-6.01%) Partitions = 4 Unpatched: 5903.0 Patched: 6106.8 (3.45%) Partitions = 8 Unpatched: 4582.0 Patched: 5579.9 (21.78%) Partitions = 16 Unpatched: 3131.5 Patched: 4521.2 (44.38%) Partitions = 32 Unpatched: 1779.8 Patched: 3387.8 (90.35%) Partitions = 64 Unpatched: 821.9 Patched: 2245.4 (173.18%) Partitions = 128 Unpatched: 322.2 Patched: 1319.6 (309.56%) Partitions = 256 Unpatched: 84.3 Patched: 731.7 (768.27%) Partitions = 512 Unpatched: 22.5 Patched: 382.8 (1597.74%) Partitions = 1024 Unpatched: 5.5 Patched: 150.1 (2607.83%) Which puts the crossover point at just 4 partitions, and just a small overhead for 1, 2 and probably 3 partitions. The planner generated a plan 26 times faster (!) with 1024 partitions. Likely there's more than could be squeezed out of this if we could get the grouping_planner() to somehow skip creating paths and performing the join search. But that patch is not nearly as simple as the attached. Probably grouping_planner could also be called with inheritance_update = false, for this one case too, which might save a small amount of effort. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
improve_performance_of_inheritance_planner.patch
Description: Binary data