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:
Show quoted text
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.
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:
Show quoted text
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:
Show quoted text
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:
Show quoted text
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.