Re: Query performance in 9.6.24 vs 14.10

2024-01-28 Thread Ron Johnson 
On Sun, Jan 28, 2024 at 10:44 PM David Rowley  wrote:

> On Mon, 29 Jan 2024 at 07:37, Ron Johnson  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.

Re: Query performance in 9.6.24 vs 14.10

2024-01-28 Thread David Rowley 
On Mon, 29 Jan 2024 at 07:37, Ron Johnson  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

Query performance in 9.6.24 vs 14.10

2024-01-28 Thread Ron Johnson 
(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%

Re: permission denied on socket

2024-01-28 Thread pf 
On Fri, 26 Jan 2024 01:52:58 +0530 Atul Kumar wrote:

>Is the server running locally and accepting connections on that
>socket?

Maybe this will help:

# ss -l | grep 5432