Re: Query performance issue

> > Additionally in the plan which mysql makes and showing the highest > response time, is it suffering because of differences of the speed of the > underlying IO/storage or is it just because of the optimization features > which are available in postgres and not there in mysql ? Trying to > unders

Re: Query performance issue

On Wed, Oct 23, 2024 at 2:06 AM yudhi s wrote: > > > On Wed, Oct 23, 2024 at 12:32 AM Greg Sabino Mullane > wrote: > >> To be frank, there is so much wrong with this query that it is hard to >> know where to start. But a few top items: >> >> * Make sure all of the tables involved have been analy

Re: Query performance issue

On Tue, 2024-10-22 at 13:06 -0700, David G. Johnston wrote: > On Tue, Oct 22, 2024 at 1:01 PM Ron Johnson wrote: > > On Tue, Oct 22, 2024 at 3:02 PM Greg Sabino Mullane > > wrote: > > > * Don't use the "FROM table1, table2, table3" syntax but use "FROM table1 > > > JOIN table2 ON (...) JOIN tab

Re: Query performance issue

On Wed, Oct 23, 2024 at 12:32 AM Greg Sabino Mullane wrote: > To be frank, there is so much wrong with this query that it is hard to > know where to start. But a few top items: > > * Make sure all of the tables involved have been analyzed. You might want > to bump default_statistics_target up and

Re: Query performance issue

On Tue, Oct 22, 2024 at 1:01 PM Ron Johnson wrote: > On Tue, Oct 22, 2024 at 3:02 PM Greg Sabino Mullane > wrote: > [snip] > >> * Don't use the "FROM table1, table2, table3" syntax but use "FROM table1 >> JOIN table2 ON (...) JOIN table3 ON (...) >> > > Why? > > Readability is improved when done

Re: Query performance issue

On Tue, Oct 22, 2024 at 3:02 PM Greg Sabino Mullane wrote: [snip] > * Don't use the "FROM table1, table2, table3" syntax but use "FROM table1 > JOIN table2 ON (...) JOIN table3 ON (...) > Why? -- Death to , and butter sauce. Don't boil me, I'm still alive. crustacean!

Re: Query performance issue

To be frank, there is so much wrong with this query that it is hard to know where to start. But a few top items: * Make sure all of the tables involved have been analyzed. You might want to bump default_statistics_target up and see if that helps. * As mentioned already, increase work_mem, as you

Re: Query performance issue

On 10/21/24 23:31, yudhi s wrote: On Thu, Oct 17, 2024 at 3:06 AM Peter J. Holzer wrote: The execution plan looks like a postgresql execution plan, not a mysql execution plan. Did you run this query on postgresql? That may be interesting for comparison purposese, but ultimately i

Re: Query performance issue

On Thu, Oct 17, 2024 at 3:06 AM Peter J. Holzer wrote: > > The execution plan looks like a postgresql execution plan, not a mysql > execution plan. Did you run this query on postgresql? That may be > interesting for comparison purposese, but ultimately it is useless: You > won't get mysql to work

Re: Query performance issue

On Thu, Oct 17, 2024 at 3:06 AM Peter J. Holzer wrote: > On 2024-10-16 23:20:36 +0530, yudhi s wrote: > > Below is a query which is running for ~40 seconds. > [...] > > In the execution path below , the line number marked in bold are the top > lines > > for the IN and NOT IN subquery evaluation a

Re: Query performance issue

On 2024-10-16 23:20:36 +0530, yudhi s wrote: > Below is a query which is running for ~40 seconds. [...] > In the execution path below , the line number marked in bold are the top lines > for the IN and NOT IN subquery evaluation and they are showing "Actual time" > as >  Approx ~9 seconds and ~8 s

Re: Query performance issue

On 10/16/24 10:50 AM, yudhi s wrote: Hi, Below question got in one discussion.Appreciate any guidance on this. Below is a query which is running for ~40 seconds. As it's a query which is executed from UI , we were expecting it to finish in <~5 seconds. It has a "IN" and a "NOT IN" subquery

Query performance issue

Hi, Below question got in one discussion.Appreciate any guidance on this. Below is a query which is running for ~40 seconds. As it's a query which is executed from UI , we were expecting it to finish in <~5 seconds. It has a "IN" and a "NOT IN" subquery , from the execution path it seems the tota

Re: Insert query performance

On Tue, 20 Aug 2024 at 19:09, sud wrote: > However, my initial understanding of "having the FK index will improve the > insert performance in the child table" is not accurate it seems. Rather as > you mentioned it may negatively impact the loading/insert performance because > it has to now upda

Re: Insert query performance

It will record all statements in logs. If you are concerned about query times then you may use pg_stat_statements. Muhammad Ikram On Tue, 20 Aug 2024 at 12:19, sud wrote: > > On Mon, Aug 19, 2024 at 1:25 PM Muhammad Ikram wrote: > >> Hi Sud, >> >> Please make following change in your postgr

Re: Insert query performance

On Mon, Aug 19, 2024 at 1:25 PM Muhammad Ikram wrote: > Hi Sud, > > Please make following change in your postgresql.conf file > > log_statement = 'all' > > Will this put all the internal sql query or the recursive query entries in the pg_stats_statement view which we can analyze then? And also to

Re: Insert query performance

On Mon, Aug 19, 2024 at 4:33 PM David Rowley wrote: > On Mon, 19 Aug 2024 at 19:48, sud wrote: > > In a version 15.4 postgres database, Is it possible that , if we have > two big range partition tables with foreign key relationships between them, > insert into the child table can cause slowness

Re: Insert query performance

On Mon, 19 Aug 2024 at 19:48, sud wrote: > In a version 15.4 postgres database, Is it possible that , if we have two big > range partition tables with foreign key relationships between them, insert > into the child table can cause slowness if we don't have foreign key index > present in the chi

Re: Insert query performance

Hi Sud, Please make following change in your postgresql.conf file log_statement = 'all' Regards, Ikram On Mon, Aug 19, 2024 at 12:48 PM sud wrote: > Hello All, > > In a version 15.4 postgres database, Is it possible that , if we have two > big range partition tables with foreign key relatio

Insert query performance

Hello All, In a version 15.4 postgres database, Is it possible that , if we have two big range partition tables with foreign key relationships between them, insert into the child table can cause slowness if we don't have foreign key index present in the child table? Basically it need to make sure

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 du

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 turn

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 bit

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 thi

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

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 t

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 predica

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 > nu

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 wh

Query performance going from Oracle to Postgres

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 why. The query is hitting a partitioned table but to simply things I

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,         b.befo

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

itute a value for a.field1, query runs faster. Any suggestion/guidance/links to improve the query performance without substituting the value ? Is there an index on table1.field1? Have you vacuumed or analyzed table1 lately? Have you decomposed the query into the smallest possible que

Re: Query Performance

gestion/guidance/links to improve the query performance without substituting the value ? Happiness Always BKR Sivaprakash

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 > confi

Re: Query performance with min and filter

Adam Sent: Monday, August 31, 2020 2:57 PM To: pgsql-general@lists.postgresql.org Cc: Cianciaruso, Lorenzo Subject: Query performance with min and filter Hi Postgres community, We seem to have found a situation where a query run using explain analyse or pgbench is incredibly fast, but ru

Query performance with min and filter

Hi Postgres community, 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 running

[HELP] General query - performance metrics

Hello, I have set up pgpool and there are 2 postgres servers behind it. I had run pgbench with and without pgpool and got the results. I would like to showcase the results for a presentation. Just thought of asking you people if there are any tools to generate nice results with charts and graphs ?

Re: Suggestion to improve query performance of data validation in proc.

On Fri, May 22, 2020 at 2:09 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Friday, May 22, 2020, postgann2020 s wrote: > > >> >> We are looking for a better query than "*SELECT 1 FROM >> schema.table_name WHERE column1=structure_id1*" this query for data >> validation. >> > If

Re: Suggestion to improve query performance of data validation in proc.

On Friday, May 22, 2020, postgann2020 s wrote: > > We are looking for a better query than "*SELECT 1 FROM schema.table_name > WHERE column1=structure_id1*" this query for data validation. > There is no more simple a query that involve records on a single,table. Please suggest is there any ot

Re: Suggestion to improve query performance of data validation in proc.

Hi David, Thanks for your feedback. We are using the below kind of validation throughout the proc in multiple locations and for validation we are using the below statements. --check Data available or not for structure_id1 IF EXISTS(SELECT 1 FROM schema.table_name WHERE column1=structure_

Re: Suggestion to improve query performance of data validation in proc.

You should read through the and the contained linked FAQ - note especially the concept and recommendation for “cross-posting”. https://wiki.postgresql.org/wiki/Mailing_Lists#Email_etiquette_mechanics On Thursday, May 21, 2020, postgann2020 s wrote: > > We have multiple long procs that are havin

Suggestion to improve query performance of data validation in proc.

Hi Team, Thanks for your support. Could you please suggest on below query. We have multiple long procs that are having 100s of data validations and currently we have written as below. *** if (SELECT 1 FROM SCHEMA.TABLE WHERE column=data AND column=data) then statements etc.. *

Suggestion to improve query performance.

Hi Team, Thanks for your support. We are using below environment: Application : Programming Language : JAVA Geoserver Database Stack: PostgreSQL : 9.5.15 Postgis We have 3 geoserver queries and are getting some performance issues after changing the GeoServer queries.I have posted the queries a

Re: strange slow query performance

On Thu, Jan 17, 2019 at 4:13 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thu, Jan 17, 2019 at 9:19 AM Ben Snaidero > wrote: > > Any ideas as to why this is happening? > > Not really, I would expect roughly double execution time, not an > exponential increase. Still not experie

Re: strange slow query performance

On Thu, Jan 17, 2019 at 9:19 AM Ben Snaidero wrote: > Any ideas as to why this is happening? Not really, I would expect roughly double execution time, not an exponential increase. Still not experienced enough to diagnose with what has been provided but I will suggest you provide the version that

Re: strange slow query performance

;InitPlan 1 (returns $3) > -> Nested Loop (cost=1.10..3254.75 rows=16296 width=4) (actual > time=0.403..26.147 rows=19042 loops=1) > [...] > Planning time: 5.693 ms > Execution time: 33.383 ms > (15 rows) > > But when we add a second condition to the where

strange slow query performance

h=8) (actual time=0.002..0.007 rows=9 loops=2082) Index Cond: (inodeid = n1ne.iid) Heap Fetches: 13973 Planning time: 5.693 ms Execution time: 33.383 ms (15 rows) But when we add a second condition to the where clause it causes the query performance to drop dramat

Re: Postgres 11.0 Partitioned Table Query Performance

On 8 November 2018 at 15:28, Paul Schaap wrote: > I have an issue, and a partial workaround, with a query outlined below. What > I am hoping to get to is a Parallel Index Only Scan on my partition indexes > as theoretically that should be the fastest, but can only get either a > Parallel Seq Scan

Postgres 11.0 Partitioned Table Query Performance

Hi, I have an issue, and a partial workaround, with a query outlined below. What I am hoping to get to is a Parallel Index Only Scan on my partition indexes as theoretically that should be the fastest, but can only get either a Parallel Seq Scan on each partition which is very slow, or a non pa

Query performance with cluster

Hi, I'm creating this table : *create table availability(product_id integer, product_name varchar(255), during daterange);* *create index time_idx on availability using gist(during);* *insert into availability* *select p.id , concat('prod ', p.id::text), daterange((now() + concat(r

Query performance

I want to compare the performance of some queries so I created those tables: *CREATE TABLE reservation1 (room int, name varchar(255), during daterange);CREATE TABLE reservation2 (room int, name varchar(255), start_date date, end_date date);* then: *INSERT INTO public.reservation1(name, room,

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 establishment within a radius. >

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 <> e1.id >> and e1.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, ST_MakePoint(e1.longitude,

query performance

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 establishment within a radius. I run this query: select e.name, e1.name from establishment as e, e