RE: speeding up planning with partitions - Mailing list pgsql-hackers
From | Imai, Yoshikazu |
---|---|
Subject | RE: speeding up planning with partitions |
Date | |
Msg-id | 0F97FA9ABBDBE54F91744A9B37151A5125CE37@g01jpexmbkw24 Whole thread Raw |
In response to | Re: speeding up planning with partitions (Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>) |
Responses |
Re: speeding up planning with partitions
|
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
pgsql-hackers by date: