Fwd: Very slow query performance when using CTE

unfortunately that increased the query execution time by a factor of 8: https://explain.dalibo.com/plan/a6d2443d87fea1ee On Tue, 1 Apr 2025 at 16:28, Tom Lane wrote: > Chris Joysn writes: > > I have an issue when using CTEs. A query, which consists of multiple > CTEs, > > runs usually rather f

Re: Very slow query performance when using CTE

> CREATE STATISTICS st_simrun_component_metadata (dependencies) ON >> sim_run_id, key FROM sim_run_component_metadata; >> ANALYZE sim_run_component_metadata; >> >> When I run this query, no statistics are returned: >> >> SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = >> stxoi

Re: Very slow query performance when using CTE

> > CREATE STATISTICS st_simrun_component_metadata (dependencies) ON > sim_run_id, key FROM sim_run_component_metadata; > ANALYZE sim_run_component_metadata; > > When I run this query, no statistics are returned: > > SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = > stxoid), >

Re: Very slow query performance when using CTE

Chris Joysn writes: > Hello, > I have an issue when using CTEs. A query, which consists of multiple CTEs, > runs usually rather fast (~5s on my > environment). But it turned out that using one CTE can lead to execution > times of up to one minute. > That CTE is used two times within the query.

Re: Very slow query performance when using CTE

Chris Joysn writes: > I have an issue when using CTEs. A query, which consists of multiple CTEs, > runs usually rather fast (~5s on my environment). But it turned out that > using one CTE can lead to execution times of up to one minute. > That CTE is used two times within the query. Try labeling

Very slow query performance when using CTE

Hello, I have an issue when using CTEs. A query, which consists of multiple CTEs, runs usually rather fast (~5s on my environment). But it turned out that using one CTE can lead to execution times of up to one minute. That CTE is used two times within the query. In the CTE there are 2600 rows, comp

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

Query performance issue

Hello all, 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.pg_namespace n WHERE p.p

Re: Inconsistent query performance based on relation hit frequency

Στις 27/6/24 17:58, ο/η Laura Hausmann έγραψε: Heya & thank you for the response! That makes a lot of sense. I'm glad to hear it's on the radar of the team, but I understand that this is a complex task and won't happen anytime soon. For the meantime, I've tried a couple ways of rewriting the

Re: Inconsistent query performance based on relation hit frequency

Heya & thank you for the response! That makes a lot of sense. I'm glad to hear it's on the radar of the team, but I understand that this is a complex task and won't happen anytime soon. For the meantime, I've tried a couple ways of rewriting the query, sadly none of which seem to translate to the

Re: Inconsistent query performance based on relation hit frequency

On 6/27/24 03:50, Laura Hausmann wrote: Heya, I hope the title is somewhat descriptive. I'm working on a decentralized social media platform and have encountered the following performance issue/quirk, and would like to ask for input, since I'm not sure I missed anything. I'm running PostgreS

Re: Inconsistent query performance based on relation hit frequency

On 6/27/24 07:50, Laura Hausmann wrote: I'd appreciate any and all input on the situation. If I've left out any information that would be useful in figuring this out, please tell me. Thanks for this curious case, I like it! At first, you can try to avoid "OR" expressions - PostgreSQL has quite

Inconsistent query performance based on relation hit frequency

Heya, I hope the title is somewhat descriptive. I'm working on a decentralized social media platform and have encountered the following performance issue/quirk, and would like to ask for input, since I'm not sure I missed anything. I'm running PostgreSQL 16.2 on x86_64-pc-linux-gnu, compiled by gc

Re: Range partitioning query performance with date_trunc (vs timescaledb)

On 29/08/2023, David Rowley wrote: > On Tue, 29 Aug 2023 at 19:40, Philippe Pepiot wrote: > > I'm trying to implement some range partitioning on timeseries data. But it > > looks some queries involving date_trunc() doesn't make use of partitioning. > > > > BEGIN; > > CREATE TABLE test ( > > ti

Re: Range partitioning query performance with date_trunc (vs timescaledb)

On Tue, 29 Aug 2023 at 19:40, Philippe Pepiot wrote: > I'm trying to implement some range partitioning on timeseries data. But it > looks some queries involving date_trunc() doesn't make use of partitioning. > > BEGIN; > CREATE TABLE test ( > time TIMESTAMP WITHOUT TIME ZONE NOT NULL, > va

Range partitioning query performance with date_trunc (vs timescaledb)

Hi, I'm trying to implement some range partitioning on timeseries data. But it looks some queries involving date_trunc() doesn't make use of partitioning. BEGIN; CREATE TABLE test ( time TIMESTAMP WITHOUT TIME ZONE NOT NULL, value FLOAT NOT NULL ) PARTITION BY RANGE (time); CREATE INDEX t

Re: Why is query performance on RLS enabled Postgres worse?

Hi, Is there a way to visualize RLS policy check(s) in the query plan? Regards, Akash Anand On Mon, Jul 10, 2023 at 11:33 AM Akash Anand wrote: > Hi, > > -- > Postgres version > -- > postgres=# SELECT version(); > version >

Why is query performance on RLS enabled Postgres worse?

Hi, -- Postgres version -- postgres=# SELECT version(); version --- PostgreSQL 15.3 (Debian 15.3-1

Re: Advice needed: query performance deteriorates by 2000% within 1 minute

And I would absolutely crank up autovacuum and analyze settings. Turn up the cost limits, turn down the cost delays, decrease the scale factor. Whatever you need to do such that autovacuum runs often. No need to schedule a manual vacuum at all. Just don't wait until 20% of the table is dead before

Re: Advice needed: query performance deteriorates by 2000% within 1 minute

If you expect to have high cache hits and/or have ssd or similar fast storage, random page cost should be more like 1-2 rather than the default 4. When using jsonb, you'd normally have estimates based solely on the constants for the associated datatype (1/3 or 2/3 for a nullable boolean for instanc

Re: Advice needed: query performance deteriorates by 2000% within 1 minute

Hello, Michael, Tom: thanks for all the insights and informations in your previous mails. A quick update of the explain outputs (this time using explain (analyze, buffers, verbose)) *The good: * *LOG Time: | 2022-02-28 09:30:01.400777+01 | order rows: | 9668* Limit (cost=616.37..653.30 ro

Re: Advice needed: query performance deteriorates by 2000% within 1 minute

Peter Adlersburg writes: > Limit (cost=0.00..804.97 rows=10 width=22) (actual > time=23970.845..25588.432 rows=1 loops=1) >-> Seq Scan on "order" (cost=0.00..3863.86 rows=48 width=22) (actual > time=23970.843..25588.429 rows=1 loops=1) > Filter: (jsonb_to_tsvector('english'::regco

Re: Advice needed: query performance deteriorates by 2000% within 1 minute

You are getting row estimate 48 in both cases, so it seems perhaps tied to the free space map that will mean more heap lookups from the index, to the point where the planner thinks that doing sequential scan is less costly. What is random_page_cost set to? Do you have default autovacuum/analyze se

Advice needed: query performance deteriorates by 2000% within 1 minute

Dear fellow DBAs, I am seeking for some guidance with the following case that our developers have thrown at me and I apologize in advance for this lengthy mail ... $> postgres --version postgres (PostgreSQL) 13.6 We are dealing with the following issue: select version, content from orderstore

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

Query performance !

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? Thanks a ton in advance for your support. SELECT tab2.rulename, tab2.totalexecuted,

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

Query performance !

> > 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? > > Thanks a ton in advance for your support. >

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

Query Performance

New to Postgres, Oracle background. With Oracle the amount of work a query does is tracked via logical reads. Oracle tracks logical and physical reads differently than Postgres. With Oracle a physical read is always considered a logical read. So if a query reads 5 blocks are all 5 are read f

Re: wide table, many many partitions, poor query performance

On Mon, 2021-03-15 at 10:53 -0600, S Bob wrote: > We have a client that is running PostgreSQL 12, they have a table with > 212 columns and 723 partitions > > It seems the planning time is consumed by generating 723 sub plans > > I suspect it's due to the fact that they are using hash based > pa

Re: wide table, many many partitions, poor query performance

On Mon, Mar 15, 2021 at 10:53:06AM -0600, S Bob wrote: > We have a client that is running PostgreSQL 12, they have a table with 212 > columns and 723 partitions > > It seems the planning time is consumed by generating 723 sub plans Is plannning time the issue ? Please show diagnostic output. You

wide table, many many partitions, poor query performance

All; We have a client that is running PostgreSQL 12, they have a table with 212 columns and 723 partitions It seems the planning time is consumed by generating 723 sub plans I suspect it's due to the fact that they are using hash based partitioning, example: CREATE TABLE rental_transact

Re: Slow query performance inside a transaction on a clean database

On Fri, 2021-03-05 at 17:55 +, val.jane...@gmail.com wrote: > I have a SELECT query that uses a long chain of CTEs (6) and is executed > repeatedly as part of the transaction (with different parameters). It is > executed quickly most of the time, but sometimes becomes very slow. I > managed to

Slow query performance inside a transaction on a clean database

Hi everyone, I have a SELECT query that uses a long chain of CTEs (6) and is executed repeatedly as part of the transaction (with different parameters). It is executed quickly most of the time, but sometimes becomes very slow. I managed to consistently reproduce the issue by executing a transactio

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

Query performance issue

Hi, I have a query performance issue, it takes a long time, and not even getting explain analyze the output. this query joining on 3 tables which have around a - 176223509 b - 286887780 c - 214219514 explainselect  Count(a."individual_entity_proxy_id")from "prospe

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).  >

Query performance

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).  table1: transfer_order_header(records 2782678)table2: transfer_orde

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

Query Performance / Planner estimate off

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 batches every 15 seconds or so, and the all queryable

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

Query Performance in bundled requests

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 semicolons for bundling several statements in one request. We did some tests against a

AW: Query performance issue

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 semicolons for bundling several statements in one request. We did some tests against a postgres cluster. The results in terms of

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

Query performance issue

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? Query: EXPALIN ANALYZE select serial_no,receivingplant,sku,r3_eventtime from (select serial_no,receivingpl

Re: Suggestion to improve query performance for GIS query.

Thanks for your support David and Afsar. Hi David, Could you please suggest the resource link to "Add a trigger to the table to normalize the contents of column1 upon insert and then rewrite your query to reference the newly created normalized fields." if anything available. So that it will help

Re: Suggestion to improve query performance for GIS query.

Dear team, Kindly try to execute the vacuum analyzer on that particular table and refresh the session and execute the query. VACUUM (VERBOSE, ANALYZE) tablename; Regards, Mohammed Afsar Database engineer On Fri, May 22, 2020, 12:30 PM postgann2020 s wrote: > Hi Team, > > Thanks for your suppo

Re: Suggestion to improve query performance for GIS query.

On Thursday, May 21, 2020, postgann2020 s wrote: > > SELECT seq_no+1 INTO pair_seq_no FROM SCHEMA.TABLE WHERE (Column1 like > '%,sheath--'||cable_seq_id ||',%' or Column1 like 'sheath--'||cable_seq_id > ||',%' or Column1 like '%,sheath--'||cable_seq_id or > Column1='sheath--'||cable_seq_id) orde

Suggestion to improve query performance for GIS query.

Hi Team, Thanks for your support. Could you please suggest on below query. EnvironmentPostgreSQL: 9.5.15 Postgis: 2.2.7 The table contains GIS data which is fiber data(underground routes). We are using the below query inside the proc which is taking a long time to complete. **

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

Query Performance Issue

Hi everyone , Have this explain analyze output : *https://explain.depesz.com/s/Pra8a * Appreciated for any help . *PG version* --- PostgreSQL 9.6.11 on x8

Re: Time bucketing query performance

On Mon, May 07, 2018 at 07:33:17PM -0400, Julian Wilson wrote: > Hi, > > I'm trying to debug improve the performance of my time bucketing query. > What I'm trying to do is essentially bucket by an arbitrary interval and > then do some aggregations within that interval (min,max,sum, etc). I am > us

Time bucketing query performance

Hi, I'm trying to debug improve the performance of my time bucketing query. What I'm trying to do is essentially bucket by an arbitrary interval and then do some aggregations within that interval (min,max,sum, etc). I am using a `max` in the query I posted. For context in the data, it is 1 minute

Re: Batch insert heavily affecting query performance.

On Wed, Dec 27, 2017 at 2:10 PM, Mike Sofen wrote: > In my experience, that 77ms will stay quite constant even if your db grew > to > 1TB. Postgres IS amazing. BTW, for a db, you should always have > provisioned IOPS or else your performance can vary wildly, since the SSDs > are shared. > > > >

Re: Batch insert heavily affecting query performance.

: Wednesday, December 27, 2017 11:03 AM Subject: Re: Batch insert heavily affecting query performance. Sorry guys, The performance problem is not caused by PG.  'Index Scan using idx_user_country on public.old_card  (cost=0.57..1854.66 rows=460 width=922) (actual time=3.442..76.606 row

  1   2   >