Postgres Pro
Downloads
Home   >   mailing lists

Re: Query performance in 9.6.24 vs 14.10 - Mailing list pgsql-general

From Bob Jolliffe
Subject Re: Query performance in 9.6.24 vs 14.10
Date
Msg-id CACd=f9fEXsSgwipMfdvhhLhuYbAKK3p7vKU75UoAVYQ8E57sQQ@mail.gmail.com
Whole thread Raw
In response to Re: Query performance in 9.6.24 vs 14.10  (Ron Johnson <ronljohnsonjr@gmail.com>)
Responses Re: Query performance in 9.6.24 vs 14.10
List pgsql-general
Tree view
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:
089.6.241,142.1641,160.8011,103.7161,249.8521,191.081
14.10159.354155.111155.111162.797158.15786.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.

pgsql-general by date:

Previous
From: Ron Johnson
Date:
Subject: Re: Scriptable way to validate a pg_dump restore ?
Next
From: Ron Johnson
Date:
Subject: Re: Query performance in 9.6.24 vs 14.10