Re: Does btrfs on Linux have a negative performance impact for PostgreSQL 13?

PostgreSQL 13. Does anyone know if it would have a major performance impact? This is a few years old, but Tomas Vondra did a presentation comparing major Linux file systems for PostgreSQL: https://www.slideshare.net/fuzzycz/postgresql-on-ext4-xfs-btrfs-and-zfs That talk was ages ago

Re: Does btrfs on Linux have a negative performance impact for PostgreSQL 13?

oes anyone know if > it would have a major performance impact? This is a few years old, but Tomas Vondra did a presentation comparing major Linux file systems for PostgreSQL: https://www.slideshare.net/fuzzycz/postgresql-on-ext4-xfs-btrfs-and-zfs

Re: Does btrfs on Linux have a negative performance impact for PostgreSQL 13?

ed to use btrfs > > on the server, but it got me thinking about PostgreSQL 13. Does anyone know > > if it would have a major performance impact? > > Is there some reason the question is specific to postgres13 , or did you just > say that because it's your development target for

Re: Does btrfs on Linux have a negative performance impact for PostgreSQL 13?

yone know if > it would have a major performance impact? Is there some reason the question is specific to postgres13 , or did you just say that because it's your development target for your project. I think it almost certainly depends more on your project than on postgres 13.

Does btrfs on Linux have a negative performance impact for PostgreSQL 13?

I'm curious, really. I use btrfs as my filesystem on my home systems and am setting up a server as I near releasing my project. I planned to use btrfs on the server, but it got me thinking about PostgreSQL 13. Does anyone know if it would have a major performance impact? Simon

Re: Planning performance problem (67626.278ms)

David Rowley writes: > FWIW, here's a simple test case that shows the problem in current master. This isn't telling the whole story. That first EXPLAIN did set the killed bits in the index, so that subsequent ones are fairly fast, even without VACUUM: regression=# explain select * from a where

Re: Planning performance problem (67626.278ms)

On Thu, 22 Apr 2021 at 00:03, Jeremy Schneider wrote: > > Two years later, I still remember this. And today I just confirmed > someone hitting this on open source PG13. The only thing that changed about get_actual_variable_range() is that it now uses a SnapshotNonVacuumable snapshot. Previously

Re: SQL performance issue (postgresql chooses a bad plan when a better one is available)

When I do serious database development I try to use database functions as much as possible. You can attach any flag value to a function in which case it gets set when the function is running, In your case you could probably wrap your query into an set-returning `LANGUAGE SQL` function [1] and

Re: SQL performance issue (postgresql chooses a bad plan when a better one is available)

"set enable_material=false;" produces an efficient plan. good to know there are *some* knobs to turn when the optimizer comes up with a bad plan. would be awesome if you could lock that plan into place w/out altering the variable. thanks for the help Hannu! On Mon, Mar 22, 2021 at 4:39 PM Hannu

Re: SQL performance issue (postgresql chooses a bad plan when a better one is available)

you can play around various `enable_*` flags to see if disabling any of these will *maybe* yield the plan you were expecting, and then check the costs in EXPLAIN to see if the optimiser also thinks this plan is cheaper. On Mon, Mar 22, 2021 at 6:29 PM Chris Stephens wrote: > > we are but i was

Re: SQL performance issue (postgresql chooses a bad plan when a better one is available)

we are but i was hoping to get a better understanding of where the optimizer is going wrong and what i can do about it. chris On Mon, Mar 22, 2021 at 9:54 AM Laurenz Albe wrote: > On Mon, 2021-03-22 at 08:10 -0500, Chris Stephens wrote: > > The following SQL takes ~25 seconds to run. I'm

Re: SQL performance issue (postgresql chooses a bad plan when a better one is available)

On Mon, 2021-03-22 at 08:10 -0500, Chris Stephens wrote: > The following SQL takes ~25 seconds to run. I'm relatively new to postgres > but the execution plan (https://explain.depesz.com/s/N4oR) looks like it's > materializing the entire EXISTS subquery for each row returned by the rest > of

SQL performance issue (postgresql chooses a bad plan when a better one is available)

a better plan. any and all help/suggestions/explanations would be greatly appreciated. the rewritten SQL performs sufficiently well but i'd like to understand why postgres is doing this and what to do about it so i can't tackle the next SQL performance issue with a little more knowledge. SELECT

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 >

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.

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

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

Re: Potential performance issues related to group by and covering index

at covers only a > prefix of the GROUP BY clause may still help. > > If no indexes exist then you might get better performance by putting > the most distinct column first. That's because sorts don't need to > compare the remaining columns once it receives two different values > for

Re: Potential performance issues related to group by and covering index

y still help. If no indexes exist then you might get better performance by putting the most distinct column first. That's because sorts don't need to compare the remaining columns once it receives two different values for one column. That gets more complex when the most distinct column i

Re: Potential performance issues related to group by and covering index

> > If we want to do anything much smarter than that like trying every > combination of the GROUP BY clause, then plan times are likely going > to explode. The join order search is done based on the chosen query > pathkeys, which in many queries is the pathkeys for the GROUP BY > clause (see

Re: Performance issues related to left join and order by

On Tue, 2 Mar 2021 at 21:53, Liu, Xinyu wrote: > *Expected Behavior > > Since these two queries are semantically equivalent, we were hoping that > PostgreSQL would evaluate them in roughly the same amount of time. > It looks to me that there is a missing optimization rule related to pushing >

Re: Potential performance issues related to group by and covering index

scan will not be triggered. > Given that the user might not pay close attention to this subtle difference, > I was wondering if it is worth making these two queries have the same and > predictable performance on Postgresql. Unfortunately, it would take a pretty major overhaul of the

Re: Potential performance issues related to group by and covering index

út 2. 3. 2021 v 9:53 odesílatel Liu, Xinyu napsal: > > > > > > > > > > > * Hello, We have 2 TPC-H queries which fetch the same tuples but have > significant query execution time differences (4.3 times). We are sharing a > pair of TPC-H queries that exh

Performance issues related to left join and order by

Hello, We have 2 TPC-H queries which fetch the same tuples but have significant query execution time differences (22.0 times). We are sharing a pair of TPC-H queries that exhibit this performance difference: First query: SELECT"orders3"."o_comment", &quo

Potential performance issues related to group by and covering index

Hello, We have 2 TPC-H queries which fetch the same tuples but have significant query execution time differences (4.3 times). We are sharing a pair of TPC-H queries that exhibit this performance difference: First query: SELECT "ps_comment", "ps_suppkey",

Re: Potential performance issues

parts etc. Cheers Hannu On Mon, Mar 1, 2021 at 4:07 PM Pavel Stehule wrote: > > Hi > > po 1. 3. 2021 v 15:59 odesílatel Jung, Jinho napsal: >> >> Andrew, Bob, Michael >> >> Thanks for the valuable feedback! Even with the default setting, PostgreSQL >

Re: Postgres performance comparing GCP and AWS

Have you tried to set the instance running on GCP to have similar shared_buffers as the AWS database ? What you described has a much lower cache hit rate on GCS and 2X the shared buffers on AWS which could well explain much of the difference in execution times. DETAILS: Query explain for

Re: Potential performance issues

Hi po 1. 3. 2021 v 15:59 odesílatel Jung, Jinho napsal: > Andrew, Bob, Michael > > Thanks for the valuable feedback! Even with the default setting, > PostgreSQL mostly showed good performance than other DBMSs. The reported > queries are a very tiny portion among all execut

Re: Potential performance issues

Andrew, Bob, Michael Thanks for the valuable feedback! Even with the default setting, PostgreSQL mostly showed good performance than other DBMSs. The reported queries are a very tiny portion among all executed queries (e.g., <0.001%). As you guided, we will make the follow-up report after

Re: Potential performance issues

wever it is something that is adaptable for those times when you bump up the server size, but don't want to have to revisit and update every parameter to support the change. I've been thinking a lot about running PG in containers for dev environments lately, and trying to tune to get reasonable de

Re: Potential performance issues

Jung, Jinho schrieb am 28.02.2021 um 16:04: > # Performance issues discovered from differential test > > For example, the below query runs x1000 slower than other DBMSs from > PostgreSQL. > >     select ref_0.ol_amount as c0 >     from order_line as ref_0 >        

Re: Potential performance issues

ocal/pgsql/bin/initdb -D /usr/local/pgsql/data > >> /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start > >> /usr/local/pgsql/bin/createdb jjung > > > > Using an untuned Postgres is fairly useless for a performance test. Out > > of the box, share

Re: Potential performance issues

/pgsql/bin/initdb -D /usr/local/pgsql/data /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start /usr/local/pgsql/bin/createdb jjung Using an untuned Postgres is fairly useless for a performance test. Out of the box, shared_buffers and work_mem are too low for almost all situations

Re: Potential performance issues

Hi, It is worthy work trying to compare performance across multiple database vendors, but unfortunately, it does not really come across as comparing apples to apples. For instance, configuration parameters:  I do not see where you are doing any modification of configuration at all.  Since

Re: Potential performance issues

sql/data > /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start > /usr/local/pgsql/bin/createdb jjung Using an untuned Postgres is fairly useless for a performance test. Out of the box, shared_buffers and work_mem are too low for almost all situations, and many other sett

Potential performance issues

# Performance issues discovered from differential test Hello. We are studying DBMS from GeorgiaTech and reporting interesting queries that potentially show performance problems. To discover such cases, we used the following procedures: * Install four DBMSs with the latest version (PostgreSQL

Re: Postgres performance comparing GCP and AWS

Since this is a comparison to RDS, and the goal presumably is to make the test as even as possible, you will want to pay attention to the network IO capacity for the client and the server in both tests. For RDS, you will be unable to run the client software locally on the server hardware, so you

Re: Postgres performance comparing GCP and AWS

Philip, The results in first email in this thread were using explain analyze. I thought that you asked to run using only 'explain'. My bad. The point is, the execution time with explain analyze is less the 1 second. But the actual execution time (calculated from the python client) is 24 seconds

Re: Postgres performance comparing GCP and AWS

Hi, Philip We ran: EXPLAIN (FORMAT JSON) SELECT "Id", "DateTime", "SignalRegisterId", "Raw" FROM "SignalRecordsBlobs" WHERE "SignalSettingId" = 103 AND "DateTime" BETWEEN '2019-11-28T14:00:12.54020' AND '2020-07-23T21:12:32.24900'; but it was really fast. I think the results were

Re: Postgres performance comparing GCP and AWS

> On Feb 25, 2021, at 4:04 PM, Igor Gois wrote: > > Philip, > > The results in first email in this thread were using explain analyze. > > I thought that you asked to run using only 'explain'. My bad. > > The point is, the execution time with explain analyze is less the 1 second. > But the

Re: Postgres performance comparing GCP and AWS

> On Feb 25, 2021, at 3:46 PM, Igor Gois wrote: > > Hi, Philip > > We ran: EXPLAIN (FORMAT JSON) SELECT "Id", "DateTime", "SignalRegisterId", > "Raw" FROM "SignalRecordsBlobs" WHERE "SignalSettingId" = 103 AND "DateTime" > BETWEEN '2019-11-28T14:00:12.54020' AND

Re: Postgres performance comparing GCP and AWS

> On Feb 24, 2021, at 10:11 AM, Igor Gois wrote: > > Hi, Julien > > Your hypothesis about network transfer makes sense. The query returns a big > size byte array blobs. > > Is there a way to test the network speed against the instances? I have access > to the network speed in gcp (5

Re: Postgres performance comparing GCP and AWS

Hi, Julien Your hypothesis about network transfer makes sense. The query returns a big size byte array blobs. Is there a way to test the network speed against the instances? I have access to the network speed in gcp (5 Mb/s), but don't have access in aws rds. [image: image.png] Thanks in

Re: Postgres performance comparing GCP and AWS

ns has unique price and performance characteristics:* *- Zonal persistent disk: Efficient, reliable block storage.* *- Regional persistent disk: Regional block storage replicated in two zones.* *- Local SSD: High performance, transient, local block storage.* *- Cloud Storage buckets: Affordable o

Re: Postgres performance comparing GCP and AWS

Hi, On Wed, Feb 24, 2021 at 6:14 AM Maurici Meneghetti wrote: > > I have 2 postgres instances created from the same dump (backup), one on a GCP > VM and the other on AWS RDS. The first instance takes 18 minutes and the > second one takes less than 20s to run this simples query: > SELECT "Id",

Re: Postgres performance comparing GCP and AWS

es made to the settings in the postgresql.conf file:*Here are some > postgres parameters that might be useful: > *Instance on VM on GCP (2 vCPUs, 2 GB memory, 800 GB disk):* > • effective_cache_size: 1496MB > • maintenance_work_mem: 255462kB (close to 249MB) > • max_wal_size: 1GB

Re: Postgres performance comparing GCP and AWS

uffers: 15320kB (close to 15MB) • work_mem: 2554kB • effective_io_concurrency: 200 • dynamic_shared_memory_type: posix On this instance we installed a postgres extension called timescaledb to gain performance on other tables. Some of these parameters were set using recommendation

Postgres performance comparing GCP and AWS

l_size: 512MB • shared_buffers: 510920kB (close to 499MB) • max_locks_per_transaction 1000 • wal_buffers: 15320kB (close to 15MB) • work_mem: 2554kB • effective_io_concurrency: 200 • dynamic_shared_memory_type: posix On this instance we installed a postgres extension called timescaledb to gain performance on other ta

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 >

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

Performance issues with composite types (partitioned table)

Hello, We are having performance issues with a table partitioned by date, using composite type columns. I have attached the table definition, full reproducible of the issue and execution plans to this email. Ultimately, we want to sum certain fields contained in those composite types, using

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

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

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: 

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

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

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

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

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

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

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 >

Re: Query Performance / Planner estimate off

wap_v2."Pair_evt_Swap": 12M rows, not partitioned, 12M rows after WHERE The query plans I submitted was querying the table `uniswap_v2."Pair_evt_Mint"`which has 560k rows before and after WHERE. Also not partitioned. Apologies for the inconsistency, but as I mentioned the same perf

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

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

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

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

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

ú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

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

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 >

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

Re: Poor Performance running Django unit tests after upgrading from 10.6

Roger Hunwicks writes: > ... > However, even though we have similar performance for 12.4 for most > test runs, it remains very variable. > ... > I think we have narrowed down the problem to a single, very complex, > materialized view using CTEs; the unit tests cr

Re: Poor Performance running Django unit tests after upgrading from 10.6

t; When we first started running the same test suite against Postgresql > 12.4 we got: > > Ran 1166 tests in 8502.030s > > I think that this reduction in performance is caused by the lack of > accurate statistics [...] > > We have since managed to get the performance of

Poor Performance running Django unit tests after upgrading from 10.6

tently get: Ran 1166 tests in 1291.855s When we first started running the same test suite against Postgresql 12.4 we got: Ran 1166 tests in 8502.030s I think that this reduction in performance is caused by the lack of accurate statistics because we had a similar problem (a large reduction in p

Performance issue when we use policies for Row Level Security along with functions

On Sunday, October 11, 2020, Gopisetty, Ramesh wrote: > > to sch USING ( key = > f_sel_1(key) > ); > As Tom said it doesn’t matter what you classify the function as (stable, etc) if your function call accepts a column reference as an input and compares its output to another

Re: Performance issue when we use policies for Row Level Security along with functions

INT test1_pkey PRIMARY KEY (vpd_key, id, begin_date) ); Thank you. Regards, Ramesh G From: Tom Lane Sent: Wednesday, September 16, 2020 10:17 AM To: Gopisetty, Ramesh Cc: pgsql-performance@lists.postgresql.org Subject: Re: Performance issue when we use polic

Re: Performance issue when we use policies for Row Level Security along with functions

"Gopisetty, Ramesh" writes: > Policy > create policy policy_sel on test FOR SELECT to ram1 USING ( testkey in > (f_sel_policy_test(testkey)) ); > Going to a Sequential scan instead of index scan. Hence, performance issue. > If i replace the policy with stright

Re: Performance issue when we use policies for Row Level Security along with functions

De: "Gopisetty, Ramesh" Para: "pgsql-performance" Enviadas: Quarta-feira, 16 de setembro de 2020 0:39:08 Assunto: Performance issue when we use policies for Row Level Security along with functions BQ_BEGIN Hi, I'm seeing a strange behavior when we impleme

Performance issue when we use policies for Row Level Security along with functions

)) ); Going to a Sequential scan instead of index scan. Hence, performance issue. pgwfc01q=> explain analyze select * from test; QUERY P

Re: Performance Issue (Not using Index when joining two tables).

urpose, i have rebuilded indexes, analyzed, did vaccum on those tables). Sorry for the lengthy email and i'm trying to explain my best on this. Thank you. Regards, Ramesh G From: Michael Lewis Sent: Sunday, September 13, 2020 10:51 PM To: Tom Lane Cc: Tomas

Re: Performance Issue (Not using Index when joining two tables).

Filter: ((("current_user"())::text <> ANY ('{wfnadmin,skipvpd}'::text[])) AND f_sel_policy_all(vpd_key, 'CH R_EMP_POSITION'::character varying) AND f_sel_policy_prod_locale((cep.*)::character varying, prod_locale_code)) This looks like some stuff for row level security perhaps. My understanding

Re: Performance Issue (Not using Index when joining two tables).

Tomas Vondra writes: > Most of the time (3460ms) is spent in the sequential scan on > chr_simple_val, and the seqscan on chr_emp_position is taking ~330ms). > Combined that's 3790ms out of 3797ms, so the join is pretty much > irrelevant. > Either the seqscans are causing a lot of I/O, or maybe

Re: Performance Issue (Not using Index when joining two tables).

On Sun, Sep 13, 2020 at 02:58:15PM +, Gopisetty, Ramesh wrote: Hi, Good Morning! Postgres Version : 11.6 (AWS Native Postgres/AWS Aurora tried on both flavours). When i'm joining two tables the primary index is not being used. While is use in clause with values then the index is

Performance Issue (Not using Index when joining two tables).

Hi, Good Morning! Postgres Version : 11.6 (AWS Native Postgres/AWS Aurora tried on both flavours). When i'm joining two tables the primary index is not being used. While is use in clause with values then the index is being used. I have reindexed all the tables, run the auto vaccum as

Re: Query Performance in bundled requests

| 32.00k| 280us | I guess you're looking at the minimum of 280us. ; 1/(280e-6) * 60 ~214285.71428571428571428571 > the question remains why this is so. You can't expect it to go a billion times faster just by putting a billion queries in one request, and at

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

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 |

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,

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,

<    1   2   3   4   5   6   7   8   9   10   >