(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% |