Re: speeding up planning with partitions

On 2018/08/29 21:06, Amit Langote wrote:
> I measured the gain in performance due to each patch on a modest virtual
> machine.  Details of the measurement and results follow.
>
> UPDATE:
>
> 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
>
> For SELECT:
>
> nparts  master    0001   0002   0003
> ======  ======    ====   ====   ====
> 0         2290    2329   2319   2268
> 8         1058    1077   1414   1788
> 16         711     729   1124   1789
> 32         450     475    879   1773
> 64         265     272    603   1765
> 128        146     149    371   1685
> 256          76      77    214   1678
> 512          39      39    112   1636
> 1024          16      17     59   1525
> 2048           8       9     29   1416
> 4096           4       4     15   1195
> 8192           2       2      7    932

Prompted by Tsunakawa-san's comment, I tried to look at the profiles when
running the benchmark with partitioning and noticed a few things that made
clear why, even with 0003 applied, tps numbers decreased as the number of
partitions increased.  Some functions that appeared high up in the
profiles were related to partitioning:

* set_relation_partition_info calling partition_bounds_copy(), which calls
  datumCopy() on N Datums, where N is the number of partitions.  The more
  the number of partitions, higher up it is in profiles.  I suspect that
  this copying might be redundant; planner can keep using the same pointer
  as relcache

There are a few existing and newly introduced sites in the planner where
the code iterates over *all* partitions of a table where processing just
the partition selected for scanning would suffice.  I observed the
following functions in profiles:

* make_partitionedrel_pruneinfo, which goes over all partitions to
  generate subplan_map and subpart_map arrays to put into the
  PartitionedRelPruneInfo data structure that it's in the charge of
  generating

* apply_scanjoin_target_to_paths, which goes over all partitions to adjust
  their Paths for applying required scanjoin target, although most of
  those are dummy ones that won't need the adjustment

* For UPDATE, a couple of functions I introduced in patch 0001 were doing
  the same thing as apply_scanjoin_target_to_paths, which is unnecessary

To fix the above three instances of redundant processing, I added a
Bitmapset 'live_parts' to the RelOptInfo which stores the set of indexes
of only the unpruned partitions (into the RelOptInfo.part_rels array) and
replaced the for (i = 0; i < rel->nparts; i++) loops in those sites with
the loop that iterates over the members of 'live_parts'.

Results looked were promising indeed, especially after applying 0003 which
gets rid of locking all partitions.

UPDATE:

nparts  master    0001    0002   0003
======  ======    ====    ====   ====
0         2856    2893    2862   2816
8          507    1115    1466   1845
16         260     765    1161   1876
32         119     483     910   1862
64          59     282     609   1895
128         29     153     376   1884
256         14      79     212   1874
512          5      40     115   1859
1024         2      17      58   1847
2048         0       9      29   1883
4096         0       4      15   1867
8192         0       2       7   1826

SELECT:

nparts  master    0001    0002   0003
======  ======    ====    ====   ====
0       2290      2329    2319   2268
8       1058      1077    1431   1800
16       711       729    1158   1781
32       450       475     908   1777
64       265       272     612   1791
128      146       149     379   1777
256       76        77     213   1785
512       39        39     114   1776
1024      16        17      59   1756
2048       8         9      30   1746
4096       4         4      15   1722
8192       2         2       7   1706

Note that with 0003, tps doesn't degrade as the number of partitions increase.

Attached updated patches, with 0002 containing the changes mentioned above.

Thanks,
Amit