Re: Query performance in 9.6.24 vs 14.10

Thanks for the update. On Mon, Jan 29, 2024, 16:53 Ron Johnson 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

Re: Query performance in 9.6.24 vs 14.10

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 wrote:

Re: Query performance in 9.6.24 vs 14.10

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 wrote: > Out of curiosity, is the pg14 running with the default jit=on setting? > > This is obviously entirely

Re: Query performance in 9.6.24 vs 14.10

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

Re: Query performance in 9.6.24 vs 14.10

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

Re: Query performance in 9.6.24 vs 14.10

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

RE: [EXT] Re: Query performance going from Oracle to Postgres

: [EXT] Re: Query performance going from Oracle to Postgres External Email: Use caution with links and attachments. On Thu, Sep 7, 2023 at 3:48 AM David Rowley wrote: > On Thu, 7 Sept 2023 at 19:17, Peter Geoghegan wrote: > > It seems likely that the problem here is that some of the p

Re: Query performance going from Oracle to Postgres

On Thu, Sep 7, 2023 at 3:48 AM David Rowley wrote: > On Thu, 7 Sept 2023 at 19:17, Peter Geoghegan wrote: > > It seems likely that the problem here is that some of the predicates > > appear as so-called "Filter:" conditions, as opposed to true index > > quals. > > hmm, if that were true we'd see

Re: Query performance going from Oracle to Postgres

On Thu, 7 Sept 2023 at 19:17, Peter Geoghegan wrote: > It seems likely that the problem here is that some of the predicates > appear as so-called "Filter:" conditions, as opposed to true index > quals. hmm, if that were true we'd see "Rows Removed by Filter" in the explain analyze. I think all

Re: Query performance going from Oracle to Postgres

On Wed, Sep 6, 2023 at 1:07 PM Dirschel, Steve wrote: > Oracle will find the same 332 rows using the same index but in Oracle it only > does 20 logical reads. I thought maybe the index was fragmented so I > reindexed that index: It seems likely that the problem here is that some of the

Re: Query performance going from Oracle to Postgres

On Thu, 7 Sept 2023 at 11:14, Dirschel, Steve wrote: > select count(historyeve0_.HISTORY_EVENT_SID) as col_0_0_ from > hist28.history_event_display_timestamp_20230301 historyeve0_ where > historyeve0_.IS_DELETED=0 > history_event_sid | character varying(32) | | not >

Re: Query performance going from Oracle to Postgres

On Wed, 2023-09-06 at 20:06 +, Dirschel, Steve wrote: > We are in the process of converting from Oracle to Postgres and I have a > query that is using > the same index in Postgres as is used in Oracle but in Postgres the query > does 16x more > buffer/logical reads.  I’d like to understand

Re: Query Performance

I should have given you the full query.   Here it is  Select a.locationname, a.usercode, a.itemname, a.uomname, a.batchnumber, a.expirydate,         a.itemnamefk, a.itemuomfk, f.itemgroupname, a.locationpk,          Sum(a.quantity) as quantity, Sum(a.freequantity) as freequantity,        

Re: Query Performance

On 2022-09-17 05:28:25 +, sivapostg...@yahoo.com wrote: > My query is like this > > Select a.field1, a.field2, a.field3 > From (Select a.field1, b.field2, c.field3 > From table1 a > Join table2 b > on b.something = a.something > Join table3 c >

Re: Query Performance

On 9/17/22 00:28, sivapostg...@yahoo.com wrote: Hello, My query is like this Select a.field1, a.field2, a.field3 From   (Select a.field1, b.field2, c.field3         From   table1 a         Join   table2 b         on     b.something = a.something         Join   table3 c         On     

Re: Query Performance

Hello, My query is like this    Select a.field1, a.field2, a.field3From   (Select a.field1, b.field2, c.field3        From   table1 a        Join   table2 b        on     b.something = a.something        Join   table3 c        On     c.something = a.something         Where  a.field7 = 'value'  

Re: Query performance with min and filter

"Yorwerth, Adam" writes: > We seem to have found a situation where a query run using explain analyse or > pgbench is incredibly fast, but run via Java under load performs very poorly > – we’ve checked query performance metrics for our Postgres instance and can > confirm that it’s the query

Re: Query performance with min and filter

Hi Adam, What're the query times when you run the query directly on the psql prompt, but without explain/analyze? Can you check the cache hit rate vs disk read on explain analyze vs from java? Sometimes, the data's in RAM when you run a query manually, but the live Java app might be hitting

Re: query performance

On Sun, 18 Feb 2018 00:35:18 +0100 hmidi slim wrote: > Hi, > I have two tables: establishment which contains these columns: id, name, > longitude, latitude, geom (Geometric column) > Product contains: id, name, establishment_id > First of all I want to select the

Re: query performance

On 02/18/2018 06:37 AM, David Rowley wrote: > On 18 February 2018 at 12:35, hmidi slim wrote: >> Is there an other optimized solution to make a query such this: >> select * from ( >> select e.name, e1.name, e.id >> from establishment as e, establishment as e1 >> where e.id

Re: query performance

On 18 February 2018 at 12:35, hmidi slim wrote: > Is there an other optimized solution to make a query such this: > select * from ( > select e.name, e1.name, e.id > from establishment as e, establishment as e1 > where e.id <> e1.id > and e1.id = 1 > and ST_DWithin(geom,