RE: speeding up planning with partitions - Mailing list pgsql-hackers

Amit-san,

On Tue, Jan 29, 2019 at 10:11 AM, Amit Langote wrote:
> On 2019/01/28 10:44, Imai, Yoshikazu wrote:
> > On Thu, Jan 24, 2019 at 6:10 AM, Imai, Yoshikazu wrote:
> >> updating partkey case:
> >>
> >> part-num  master     0001     0002     0003     0004
> >> 1        8215.34  7924.99  7931.15  8407.40  8475.65
> >> 2        7137.49  7026.45  7128.84  7583.08  7593.73
> >> 4        5880.54  5896.47  6014.82  6405.33  6398.71
> >> 8        4222.96  4446.40  4518.54  4802.43  4785.82
> >> 16       2634.91  2891.51  2946.99  3085.81  3087.91
> >> 32        935.12  1125.28  1169.17  1199.44  1202.04
> >> 64        352.37   405.27   417.09   425.78   424.53
> >> 128       236.26   310.01   307.70   315.29   312.81
> >> 256        65.36    86.84    87.67    84.39    89.27
> >> 512        18.34    24.84    23.55    23.91    23.91
> >> 1024        4.83     6.93     6.51     6.45     6.49
> >
> > I also tested with non-partitioned table case.
> >
> > updating partkey case:
> >
> > part-num  master     0001     0002     0003     0004
> > 0        10956.7  10370.5  10472.6  10571.0  10581.5
> > 1        8215.34  7924.99  7931.15  8407.40  8475.65
> > ...
> > 1024        4.83     6.93     6.51     6.45     6.49
> >
> >
> > In my performance results, it seems update performance degrades in
> non-partitioned case with v17-patch applied.
> > But it seems this degrades did not happen at v2-patch.
> >
> > On Thu, Aug 30, 2018 at 1:45 AM, Amit, Langote wrote:
> >> UPDATE:
> >>
> >> nparts  master    0001    0002   0003
> >> ======  ======    ====    ====   ====
> >> 0         2856    2893    2862   2816
> >
> > Does this degradation only occur in my tests? Or if this result is correct,
> what may causes the degradation?
> 
> I re-ran tests with v18 using the following setup [1]:
> 
> create table ht (a int, b int) partition by hash (b); create table ht_0
> partition of ht for values with (modulus N, remainder 0); ...
> $ cat update-noprune.sql
> update ht set a = 0;
> 
> pgbench -n -T 60 -f update-noprune,sql
> 
> TPS:
> 
> nparts  master    0001    0002   0003   0004
> ======  ======    ====    ====   ====   ====
> 0       4408      4335    4423   4379   4314
> 1       3883      3873    3679   3856   4007
> 2       3495      3476    3477   3500   3627
> 
> I can see some degradation for small number of partitions, but maybe it's
> just noise?  At least, I don't yet have a systematic explanation for that
> happening.

Thanks for testing.

I also re-ran tests with v18 using settings almost same as I used before, but this time I run pgbench for 60 second
whichwas 30 second in previous test.
 

TPS:

 nparts  master    0001    0002   0003   0004
 ======  ======    ====    ====   ====   ====
 0       10794     11018   10761  10552  11066
 1       7574      7625    7558   8071   8219
 2       6745      6778    6746   7281   7344

I can see no degradation, so I also think that performance degradation in my previous test and your test was because of
justnoise.
 


Why I did these tests is that I wanted to confirm that even if we apply each patch one by one, there's no performance
problem.Because patches are quite large, I just felt it might be difficult to commit these patches all at once and I
thoughtcommitting patch one by one would be another option to commit these patches. I don't know there is the rule in
thecommunity how patches should be committed, and if there, my thoughts above may be bad.
 

Anyway, I'll restart code reviewing :)

--
Yoshikazu Imai