Re: Query performance issue

Dheeraj Sonawane writes: > While executing the join query on the postgres database we have observed > sometimes randomly below query is being fired which is affecting our response > time. > Query randomly fired in the background:- > SELECT p.proname,p.oid FROM pg_catalog.pg_proc p, pg_catalog.p

Re: Query performance !

Please don't cross post to multiple lists like this. Cc: pgsql-...@lists.postgresql.org, pgsql-performance@lists.postgresql.org, pgsql-gene...@lists.postgresql.org, pgsql-ad...@lists.postgresql.org If you're hoping for help on the -performance list, see this page and send the "exp

Re: Query performance !

On Tue, Jul 27, 2021 at 10:44:03PM +0530, kenny a wrote: > Hi Experts, > > The attached query is performing slow, this needs to be optimized to > improve the performance. > > Could you help me with query rewrite (or) on new indexes to be created to > improve the performance? >

Re: Query Performance

"Dirschel, Steve" writes: > I have a sample query that is doing more work if some of the reads are > physical reads and I'm trying to understand why. If you look at attached > QueryWithPhyReads.txt it shows the query did Buffers: shared hit=171 > read=880. So it did 171 + 880 = 1051 total blo

Re: Query performance issue

What indexes exist on those tables? How many rows do you expect to get back in total? Is the last_contacted_anychannel_dttm clause restrictive, or does that include most of the prospect table (check pg_stats for the histogram if you don't know). and (a."shared_paddr_with_customer_ind" = 'N')

Re: Query performance issue

On 1/22/21 3:35 AM, Justin Pryzby wrote: On Fri, Jan 22, 2021 at 01:53:26AM +, Nagaraj Raj wrote: Tables ddl are attached in dbfiddle -- Postgres 11 | db<>fiddle Postgres 11 | db<>fiddle Server configuration is: Version: 10.11RAM - 320GBvCPU - 32 "maintenance_work_mem" 256MB"work_mem"  

Re: Query performance issue

On Fri, Jan 22, 2021 at 01:53:26AM +, Nagaraj Raj wrote: > Tables ddl are attached in dbfiddle -- Postgres 11 | db<>fiddle > Postgres 11 | db<>fiddle > Server configuration is: Version: 10.11RAM - 320GBvCPU - 32  > "maintenance_work_mem" 256MB"work_mem"             1GB"shared_buffers" 64GB > A

Re: Query Performance / Planner estimate off

On 10/21/20 5:35 PM, Sebastian Dressler wrote: Hi Mats, Happy to help. On 21. Oct 2020, at 16:42, Mats Olsen > wrote: On 10/21/20 2:38 PM, Sebastian Dressler wrote: Hi Mats, On 20. Oct 2020, at 11:37, Mats Julian Olsen mailto:m...@duneanalytics.com>> wrote:

Re: Query Performance / Planner estimate off

Thanks for your response Justin. On 10/22/20 3:48 PM, Justin Pryzby wrote: On Thu, Oct 22, 2020 at 09:36:03AM +0200, Mats Olsen wrote: On 10/22/20 8:37 AM, Justin Pryzby wrote: These look redundant (which doesn't matter for this the query): Partition key: RANGE (block_number) Indexes: "

Re: Query Performance / Planner estimate off

On Thu, Oct 22, 2020 at 09:36:03AM +0200, Mats Olsen wrote: > On 10/22/20 8:37 AM, Justin Pryzby wrote: > > These look redundant (which doesn't matter for this the query): > > > > Partition key: RANGE (block_number) > > Indexes: > > "transactions_block_number_btree" btree (block_number DESC)

Re: Query Performance / Planner estimate off

On 10/22/20 8:37 AM, Justin Pryzby wrote: On Wed, Oct 21, 2020 at 04:42:02PM +0200, Mats Olsen wrote: On 10/21/20 2:38 PM, Sebastian Dressler wrote: On 20. Oct 2020, at 11:37, Mats Julian Olsen https://explain.depesz.com/s/NvDR 2) enable_nestloop=off (4 min):

Re: Query Performance / Planner estimate off

On Wed, Oct 21, 2020 at 04:42:02PM +0200, Mats Olsen wrote: > On 10/21/20 2:38 PM, Sebastian Dressler wrote: > > > On 20. Oct 2020, at 11:37, Mats Julian Olsen > > > > > [...] > > > > > > 1) Vanilla plan (16 min) : https://explain.depesz.com/s/NvDR > > > > > >

Re: Query Performance / Planner estimate off

On 10/21/20 5:29 PM, Michael Lewis wrote: On Wed, Oct 21, 2020, 8:42 AM Mats Olsen > wrote: On 10/21/20 2:38 PM, Sebastian Dressler wrote: Hi Mats, On 20. Oct 2020, at 11:37, Mats Julian Olsen mailto:m...@duneanalytics.com>> wrote: [...

Re: Query performance

On Wed, Oct 21, 2020 at 5:32 PM Nagaraj Raj wrote: > Hi, I have long running query which running for long time and its planner > always performing sequnce scan the table2. > FROM sor_t.transfer_order_header hed,sor_t.transfer_order_item itm > where hed.eventid=itm.eventid group by 1,2,3,4,5,6

Re: Query performance

On Thu, Oct 22, 2020 at 12:32:29AM +, Nagaraj Raj wrote: > Hi, I have long running query which running for long time and its planner > always performing sequnce scan the table2.My gole is to reduce Read IO on the > disk cause, this query runns more oftenly ( using this in funtion for ETL).  >

Re: Query Performance / Planner estimate off

Hi Mats, Happy to help. On 21. Oct 2020, at 16:42, Mats Olsen mailto:m...@duneanalytics.com>> wrote: On 10/21/20 2:38 PM, Sebastian Dressler wrote: Hi Mats, On 20. Oct 2020, at 11:37, Mats Julian Olsen mailto:m...@duneanalytics.com>> wrote: [...] 1) Vanilla plan (16 min) : https://explain.de

Re: Query Performance / Planner estimate off

On Wed, Oct 21, 2020, 8:42 AM Mats Olsen wrote: > > On 10/21/20 2:38 PM, Sebastian Dressler wrote: > > Hi Mats, > > On 20. Oct 2020, at 11:37, Mats Julian Olsen > wrote: > > [...] > > 1) Vanilla plan (16 min) : https://explain.depesz.com/s/NvDR > 2) enable_nestloop=off (4 min): https://explain.d

Re: Query Performance / Planner estimate off

On 10/21/20 2:38 PM, Sebastian Dressler wrote: Hi Mats, On 20. Oct 2020, at 11:37, Mats Julian Olsen > wrote: [...] 1) Vanilla plan (16 min) : https://explain.depesz.com/s/NvDR 2) enable_nestloop=off (4 min): https://explain

Re: Query Performance / Planner estimate off

Hi Mats, On 20. Oct 2020, at 11:37, Mats Julian Olsen mailto:m...@duneanalytics.com>> wrote: [...] 1) Vanilla plan (16 min) : https://explain.depesz.com/s/NvDR 2) enable_nestloop=off (4 min): https://explain.depesz.com/s/buKK 3) enable_nestloop=off; enable_seqscan=off (2 min): https://explain.

Re: Query Performance / Planner estimate off

On 10/20/20 6:51 PM, Victor Yegorov wrote: вт, 20 окт. 2020 г. в 16:50, Mats Olsen >: On 10/20/20 3:04 PM, Victor Yegorov wrote: вт, 20 окт. 2020 г. в 11:38, Mats Julian Olsen mailto:m...@duneanalytics.com>>: I'm looking for some help to man

Re: Query Performance / Planner estimate off

On 10/20/20 3:22 PM, Victor Yegorov wrote: вт, 20 окт. 2020 г. в 11:38, Mats Julian Olsen >: I'm looking for some help to manage queries against two large tables. Also, can you enable `track_io_timing` (no restart required) and provide output of `EXPLAIN (a

Re: Query Performance / Planner estimate off

On 10/20/20 3:40 PM, Sushant Pawar wrote: Looping in the main group ID. Regards Sushant On Tue, Oct 20, 2020 at 6:49 PM Sushant Pawar > wrote: On Tue, Oct 20, 2020 at 3:08 PM Mats Julian Olsen mailto:m...@duneanalytics.com>> wrote: Dear Postgres

Re: Query Performance / Planner estimate off

вт, 20 окт. 2020 г. в 16:50, Mats Olsen : > On 10/20/20 3:04 PM, Victor Yegorov wrote: > > вт, 20 окт. 2020 г. в 11:38, Mats Julian Olsen : > >> I'm looking for some help to manage queries against two large tables. >> > > Can you tell the version you're running currently and the output of this > q

Re: Query Performance / Planner estimate off

On 10/20/20 11:37 AM, Mats Julian Olsen wrote: Dear Postgres community, I'm looking for some help to manage queries against two large tables. Context: We run a relatively large postgresql instance (5TB, 32 vCPU, 120GB RAM) with a hybrid transactional/analytical workload. Data is written in b

Re: Query Performance / Planner estimate off

On 10/20/20 3:04 PM, Victor Yegorov wrote: вт, 20 окт. 2020 г. в 11:38, Mats Julian Olsen >: I'm looking for some help to manage queries against two large tables. Can you tell the version you're running currently and the output of this query, please?    

Re: Query Performance / Planner estimate off

Looping in the main group ID. Regards Sushant On Tue, Oct 20, 2020 at 6:49 PM Sushant Pawar wrote: > > On Tue, Oct 20, 2020 at 3:08 PM Mats Julian Olsen > wrote: > >> Dear Postgres community, >> >> I'm looking for some help to manage queries against two large tables. >> >> Context: >> We run a

Re: Query Performance / Planner estimate off

вт, 20 окт. 2020 г. в 11:38, Mats Julian Olsen : > I'm looking for some help to manage queries against two large tables. > Also, can you enable `track_io_timing` (no restart required) and provide output of `EXPLAIN (analyze, buffers, settings)` for all 3 variants, please? (I assume you're on 12+.

Re: Query Performance / Planner estimate off

вт, 20 окт. 2020 г. в 11:38, Mats Julian Olsen : > I'm looking for some help to manage queries against two large tables. > Can you tell the version you're running currently and the output of this query, please? select name,setting,source from pg_settings where source not in ('default','overr

Re: Query Performance / Planner estimate off

On Tue, Oct 20, 2020 at 11:16 AM Pavel Stehule wrote: > > > út 20. 10. 2020 v 13:09 odesílatel Mats Julian Olsen < > m...@duneanalytics.com> napsal: > >> >> >> On Tue, Oct 20, 2020 at 10:50 AM Pavel Stehule >> wrote: >> >>> >>> >>> út 20. 10. 2020 v 11:59 odesílatel Mats Julian Olsen < >>> m...@

Re: Query Performance / Planner estimate off

út 20. 10. 2020 v 13:09 odesílatel Mats Julian Olsen napsal: > > > On Tue, Oct 20, 2020 at 10:50 AM Pavel Stehule > wrote: > >> >> >> út 20. 10. 2020 v 11:59 odesílatel Mats Julian Olsen < >> m...@duneanalytics.com> napsal: >> >>> On Tue, Oct 20, 2020 at 9:50 AM David Rowley >>> wrote: >>>

Re: Query Performance / Planner estimate off

On Tue, Oct 20, 2020 at 10:50 AM Pavel Stehule wrote: > > > út 20. 10. 2020 v 11:59 odesílatel Mats Julian Olsen < > m...@duneanalytics.com> napsal: > >> On Tue, Oct 20, 2020 at 9:50 AM David Rowley >> wrote: >> >>> On Tue, 20 Oct 2020 at 22:38, Mats Julian Olsen >>> wrote: >>> > >>> > The crux

Re: Query Performance / Planner estimate off

út 20. 10. 2020 v 11:59 odesílatel Mats Julian Olsen napsal: > On Tue, Oct 20, 2020 at 9:50 AM David Rowley wrote: > >> On Tue, 20 Oct 2020 at 22:38, Mats Julian Olsen >> wrote: >> > >> > The crux of our issue is that the query planner chooses a nested loop >> join for this query. Essentially m

Re: Query Performance / Planner estimate off

On Tue, Oct 20, 2020 at 9:50 AM David Rowley wrote: > On Tue, 20 Oct 2020 at 22:38, Mats Julian Olsen > wrote: > > > > The crux of our issue is that the query planner chooses a nested loop > join for this query. Essentially making this query (and other queries) take > a very long time to complet

Re: Query Performance / Planner estimate off

On Tue, 20 Oct 2020 at 22:38, Mats Julian Olsen wrote: > > The crux of our issue is that the query planner chooses a nested loop join > for this query. Essentially making this query (and other queries) take a very > long time to complete. In contrast, by toggling `enable_nestloop` and > `enable

Re: Query Performance in bundled requests

On Tue, Sep 08, 2020 at 10:30:50AM +, Dirk Krautschick wrote: > Update: Better title and format corrections > > Hi %, > > in order to be able to readjust the effects of the stored procedure and, if > necessary, to save turnaround times, different requests can be concatenated > using semicol

Re: Query performance issue

Hi Michael, I created an index as suggested, it improved.  I was tried with partial index but the planner not using it. also, there is no difference even with timing OFF. ktbv : Optimization for: plan #HaOx | explain.depesz.com | | | | ktbv : Optimization for: plan #HaOx | explain.depesz

Re: Query performance issue

On Fri, Sep 4, 2020, 4:20 PM Nagaraj Raj wrote: > Hi Mechel, > > I added the index as you suggested and the planner going through the > bitmap index scan,heap and the new planner is, > HaOx | explain.depesz.com > > HaOx | explain.depesz.com > >

Re: Query performance issue

On Sat, 5 Sep 2020 at 10:20, Nagaraj Raj wrote: > I added the index as you suggested and the planner going through the bitmap > index scan,heap and the new planner is, > HaOx | explain.depesz.com In addition to that index, you could consider moving away from standard SQL and use DISTINCT ON, whi

Re: Query performance issue

Hi Mechel, I added the index as you suggested and the planner going through the bitmap  index scan,heap and the new planner is,HaOx | explain.depesz.com | | | | HaOx | explain.depesz.com | | | Mem config:  Aurora PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3,

Re: Query performance issue

"Subquery Scan on rec (cost=1628601.89..1676580.92 rows=7381 width=41) (actual time=22171.986..23549.079 rows=1236042 loops=1)" " Filter: (rec.mpos = 1)" " Rows Removed by Filter: 228737" " Buffers: shared hit=45 read=1166951" " I/O Timings: read=29.530" " -> WindowAgg (cost=1628601.89..1658127.45

Re: Query performance issue

Sorry, I have attached the wrong query planner, which executed in lower environment which has fewer resources: Updated one,eVFiF | explain.depesz.com | | | | eVFiF | explain.depesz.com | | | Thanks,RjOn Friday, September 4, 2020, 02:39:57 PM PDT, Michael Lewis wrote: CR

Re: Query performance issue

Note- you may need to vacuum* the table to get full benefit of index only scan by updating the visibility map. I think index only scan is skipped in favor of just checking visibility when the visibility map is stale. *NOT full

Re: Query performance issue

CREATE INDEX receiving_item_delivered_received ON receiving_item_delivered_received USING btree ( eventtype, replenishmenttype, serial_no, eventtime DESC ); > More work_mem as Tomas suggests, but also, the above index should find the candidate rows by the first two keys, and then be able to skip t

Re: Query performance issue

On Fri, Sep 04, 2020 at 09:18:41PM +, Nagaraj Raj wrote: I have a query which will more often run on DB and very slow and it is doing 'seqscan'. I was trying to optimize it by adding indexes in different ways but nothing helps. Any suggestions? 1) It's rather difficult to read the query

Re: Query performance issue

Nagaraj Raj schrieb am 04.09.2020 um 23:18: I have a query which will more often run on DB and very slow and it is doing 'seqscan'. I was trying to optimize it by adding indexes in different ways but nothing helps. EXPALIN ANALYZE select serial_no,receivingplant,sku,r3_eventtime from (select ser

Re: Query performance issue

query planner:SPJe | explain.depesz.com | | | | SPJe | explain.depesz.com | | | On Friday, September 4, 2020, 02:19:06 PM PDT, Nagaraj Raj wrote: I have a query which will more often run on DB and very slow and it is doing 'seqscan'. I was trying to optimize it by add

Re: Query Performance Issue

On Sat, 29 Dec 2018 at 20:15, Justin Pryzby wrote: > On Sat, Dec 29, 2018 at 07:58:28PM +1300, David Rowley wrote: > > Unfortunately, I don't think that'll help this situation. Extended > > statistics are currently only handled for base quals, not join quals. > > See dependency_is_compatible_claus

Re: Query Performance Issue

On Sat, Dec 29, 2018 at 1:58 AM David Rowley wrote: > On Sat, 29 Dec 2018 at 04:32, Justin Pryzby wrote: > > I think the solution is to upgrade (at least) to PG10 and CREATE > STATISTICS > > (dependencies). > > Unfortunately, I don't think that'll help this situation. Extended > statistics are c

Re: Query Performance Issue

On Thu, Dec 27, 2018 at 10:25:47PM +0300, neslişah demirci wrote: > Have this explain analyze output : > > *https://explain.depesz.com/s/Pra8a * On Sat, Dec 29, 2018 at 07:58:28PM +1300, David Rowley wrote: > On Sat, 29 Dec 2018 at 04:32, Justin Pryzby wrote:

Re: Query Performance Issue

On Sat, 29 Dec 2018 at 04:32, Justin Pryzby wrote: > I think the solution is to upgrade (at least) to PG10 and CREATE STATISTICS > (dependencies). Unfortunately, I don't think that'll help this situation. Extended statistics are currently only handled for base quals, not join quals. See dependenc

Re: Query Performance Issue

On Thu, Dec 27, 2018 at 10:25:47PM +0300, neslişah demirci wrote: > Have this explain analyze output : > > *https://explain.depesz.com/s/Pra8a * Row counts are being badly underestimated leading to nested loop joins: |Index Scan using product_content_recommend

Re: Query Performance Issue

*https://explain.depesz.com/s/Pra8a* Could you share the query itself please? And the tables definitions including indexes. work_mem : 8MB That's not a lot. The 16-batches hash join may have worked faster if you had resources to increase work_mem.