Обсуждение: Query performance in 9.6.24 vs 14.10
(I don't know how this will look in text mode. Hopefully it will be comprehensible in the archives.)
This is the summary of EXPLAIN (ANALYZE) on eight frequently-run complex queries from our application, extracted from the Postgresql log because either the BIND or SELECT takes longer than 3000 ms. I ran them each 13 times in succession, on otherwise-idle VMs with exact specifications. The 9.6 server runs RHEL6, and the 14 server runs RHEL8.
The Planning costs are so high, I think, because the tables are partitioned using Inheritance. (This will NOT be changed, yet.)
Naturally, Your Mileage Will Vary.
Qry Num | PG Version | FIRST | LAST | MIN | MAX | MEDIAN | Speed-up Pct |
Planning | |||||||
01 | 9.6.24 | 17,922.488 | 18,160.742 | 16,695.140 | 18,580.261 | 17,741.328 | |
14.10 | 13,176.262 | 13,483.961 | 12,934.866 | 13,689.540 | 13,483.961 | 24.00% | |
02 | 9.6.24 | 3,673.805 | 4,141.206 | 3,470.742 | 4,141.206 | 3,673.805 | |
14.10 | 1,777.958 | 1,700.264 | 1,675.137 | 1,777.958 | 1,718.571 | 53.22% | |
03 | 9.6.24 | 239.675 | 268.971 | 225.361 | 348.565 | 271.909 | |
14.10 | 218.283 | 220.856 | 215.588 | 225.895 | 221.485 | 18.54% | |
04 | 9.6.24 | 4,394.479 | 4,111.673 | 3,632.297 | 4,394.479 | 4,006.170 | |
14.1 | 1,960.575 | 1,937.504 | 1,928.816 | 2,094.365 | 1,997.432 | 50.14% | |
05 | 9.6.24 | 3,674.314 | 3,876.537 | 3,192.784 | 4,105.541 | 3,789.318 | |
14.10 | 1,790.975 | 1,753.901 | 1,677.151 | 1,846.911 | 1,760.372 | 53.54% | |
06 | 9.6.24 | 3,431.269 | 4,026.270 | 3,431.269 | 4,026.270 | 3,702.654 | |
14.10 | 1,801.959 | 1,876.325 | 1,653.144 | 1,876.325 | 1,725.933 | 53.39% | |
07 | 9.6.24 | 4,273.917 | 4,268.979 | 3,888.995 | 4,553.267 | 4,157.208 | |
14.10 | 1,914.381 | 2,006.053 | 1,914.381 | 2,042.719 | 1,980.043 | 52.37% | |
08 | 9.6.24 | 4,403.466 | 4,338.042 | 4,062.099 | 4,699.233 | 4,360.336 | |
14.10 | 1,961.035 | 1,866.127 | 1,866.127 | 2,037.441 | 1,956.783 | 55.12% | |
Execution | |||||||
01 | 9.6.24 | 2,245.996 | 2,071.423 | 1,774.261 | 2,245.996 | 1,984.238 | |
14.10 | 540.961 | 469.541 | 462.658 | 540.961 | 469.660 | 76.33% | |
02 | 9.6.24 | 36.875 | 37.047 | 33.718 | 40.299 | 37.513 | |
14.10 | 31.910 | 29.618 | 28.387 | 31.910 | 30.284 | 19.27% | |
03 | 9.6.24 | 587.939 | 553.042 | 538.726 | 594.222 | 556.793 | |
14.10 | 172.890 | 159.617 | 154.603 | 172.890 | 162.924 | 70.74% | |
04 | 9.6.24 | 1,068.230 | 1,081.044 | 1,022.087 | 1,100.884 | 1,068.230 | |
14.10 | 157.058 | 161.618 | 157.058 | 167.006 | 160.243 | 85.00% | |
05 | 9.6.24 | 15.608 | 17.949 | 15.171 | 19.329 | 17.211 | |
14.10 | 9.538 | 9.837 | 8.920 | 10.917 | 9.572 | 44.38% | |
06 | 9.6.24 | 54.796 | 53.216 | 53.216 | 67.378 | 57.401 | |
14.10 | 60.135 | 50.899 | 47.108 | 60.135 | 48.267 | 15.91% | |
07 | 9.6.24 | 1,041.981 | 1,104.688 | 1,031.757 | 1,134.455 | 1,090.329 | |
14.10 | 158.666 | 159.348 | 155.695 | 162.539 | 159.972 | 85.33% | |
08 | 9.6.24 | 1,142.164 | 1,160.801 | 1,103.716 | 1,249.852 | 1,191.081 | |
14.10 | 159.354 | 155.111 | 155.111 | 162.797 | 158.157 | 86.72% |
On Mon, 29 Jan 2024 at 07:37, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
08 9.6.24 1,142.164 1,160.801 1,103.716 1,249.852 1,191.081 14.10 159.354 155.111 155.111 162.797 158.157 86.72%
Your speedup per cent calculation undersells PG14 by quite a bit. I'd call that an increase of ~639% rather than 86.72%.
I think you've done "1 - sum( <14.10 numbers> ) / sum( <9.6.24 numbers>)" whereas I think you should have done "sum( <9.6.24 numbers>) / sum( <14.10 numbers> ) - 1"
Nonetheless, thanks for testing this out. I assume this is just a report giving good feedback about progress in this area...?
David
On Sun, Jan 28, 2024 at 10:44 PM David Rowley <dgrowleyml@gmail.com> wrote:
On Mon, 29 Jan 2024 at 07:37, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
08 9.6.24 1,142.164 1,160.801 1,103.716 1,249.852 1,191.081 14.10 159.354 155.111 155.111 162.797 158.157 86.72% Your speedup per cent calculation undersells PG14 by quite a bit. I'd call that an increase of ~639% rather than 86.72%.I think you've done "1 - sum( <14.10 numbers> ) / sum( <9.6.24 numbers>)" whereas I think you should have done "sum( <9.6.24 numbers>) / sum( <14.10 numbers> ) - 1"Nonetheless, thanks for testing this out. I assume this is just a report giving good feedback about progress in this area...?
The spreadsheet function, using the Median cells, is (PG9.6 - PG14) / PG9.6). That's essentially the same as what you wrote.
158.157 / 1191.081 = 0.13278
1191.081 / 158.157 = 7.53, so 9.6.24 on that query is 7.53x slower.
Out of curiosity, is the pg14 running with the default jit=on setting?
This is obviously entirely due to the nature of the particular queries themselves, but we found that for our workloads that pg versions greater than 11 were exacting a huge cost due to the jit compiler. Once we explicitly turned jit=off we started to see improvements.
On Mon, 29 Jan 2024 at 07:55, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Sun, Jan 28, 2024 at 10:44 PM David Rowley <dgrowleyml@gmail.com> wrote:On Mon, 29 Jan 2024 at 07:37, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
08 9.6.24 1,142.164 1,160.801 1,103.716 1,249.852 1,191.081 14.10 159.354 155.111 155.111 162.797 158.157 86.72% Your speedup per cent calculation undersells PG14 by quite a bit. I'd call that an increase of ~639% rather than 86.72%.I think you've done "1 - sum( <14.10 numbers> ) / sum( <9.6.24 numbers>)" whereas I think you should have done "sum( <9.6.24 numbers>) / sum( <14.10 numbers> ) - 1"Nonetheless, thanks for testing this out. I assume this is just a report giving good feedback about progress in this area...?The spreadsheet function, using the Median cells, is (PG9.6 - PG14) / PG9.6). That's essentially the same as what you wrote.158.157 / 1191.081 = 0.132781191.081 / 158.157 = 7.53, so 9.6.24 on that query is 7.53x slower.
Yes, jit=on.
I'll test them with jit=off, to see the difference. (The application is 3rd party, so will change it at the system level.)
On Mon, Jan 29, 2024 at 7:09 AM Bob Jolliffe <bobjolliffe@gmail.com> wrote:
Out of curiosity, is the pg14 running with the default jit=on setting?This is obviously entirely due to the nature of the particular queries themselves, but we found that for our workloads that pg versions greater than 11 were exacting a huge cost due to the jit compiler. Once we explicitly turned jit=off we started to see improvements.
According to my tests, sometimes JIT is a little faster, and sometimes it's a little slower. Mostly within the realm of statistical noise (especially with each query having a sample size of only 13, on a VM that lives on a probably-busy host).
On Mon, Jan 29, 2024 at 9:18 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
Yes, jit=on.I'll test them with jit=off, to see the difference. (The application is 3rd party, so will change it at the system level.)On Mon, Jan 29, 2024 at 7:09 AM Bob Jolliffe <bobjolliffe@gmail.com> wrote:Out of curiosity, is the pg14 running with the default jit=on setting?This is obviously entirely due to the nature of the particular queries themselves, but we found that for our workloads that pg versions greater than 11 were exacting a huge cost due to the jit compiler. Once we explicitly turned jit=off we started to see improvements.
Thanks for the update.
On Mon, Jan 29, 2024, 16:53 Ron Johnson <ronljohnsonjr@gmail.com> wrote:
According to my tests, sometimes JIT is a little faster, and sometimes it's a little slower. Mostly within the realm of statistical noise (especially with each query having a sample size of only 13, on a VM that lives on a probably-busy host).On Mon, Jan 29, 2024 at 9:18 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:Yes, jit=on.I'll test them with jit=off, to see the difference. (The application is 3rd party, so will change it at the system level.)On Mon, Jan 29, 2024 at 7:09 AM Bob Jolliffe <bobjolliffe@gmail.com> wrote:Out of curiosity, is the pg14 running with the default jit=on setting?This is obviously entirely due to the nature of the particular queries themselves, but we found that for our workloads that pg versions greater than 11 were exacting a huge cost due to the jit compiler. Once we explicitly turned jit=off we started to see improvements.