Re: Performance implications of 8K pread()s

Exciting! Since I still have the same performance issues on compressed btrfs, I'm looking forward to testing the patches, probably when a 17 Beta is out and I can find binaries on my platform (OpenSUSE). It looks like it will make a huge difference. Thank you for persisting and getting this

Re: Performance implications of 8K pread()s

On Wed, Jul 12, 2023 at 1:11 AM Dimitrios Apostolou wrote: > So would it make sense for postgres to perform reads in bigger blocks? Is it > easy-ish to implement (where would one look for that)? Or must the I/O unit be > tied to postgres' page size? FYI as of last week we can do a little bit of

Re: Performance

Hi Mehmet, On Wed, 31 Jan 2024 at 13:33, Mehmet COKCEVIK wrote: > Hi, > We want to work with PostgreSQL in our new project. I need your opinion on > the best way to create a database. > First of all, congratulations on your decision to use PostgreSQL for your new project. :) > Description of

Re: Performance degradation with CTEs, switching from PG 11 to PG 15

Jean-Christophe Boggio writes: > I did upgrade :-) But we have many users for which we don't decide on > when they do upgrade so we have to keep compatibility with most versions > of PG and in that particular case (non-existence of the materialized > keyword for PG 11 and before) it is a real

Re: Performance degradation with CTEs, switching from PG 11 to PG 15

Andreas, Le 22/11/2023 à 15:25, Andreas Kretschmer a écrit : Am 22.11.23 um 12:38 schrieb Jean-Christophe Boggio: >> Also, adding "materialized" to both "withcwrack" and "withcwrack0" >> CTEs gets the result in acceptable timings (a few seconds). The >> problem with this is that we have some

Re: Performance degradation with CTEs, switching from PG 11 to PG 15

Am 22.11.23 um 12:38 schrieb Jean-Christophe Boggio: Also, adding "materialized" to both "withcwrack" and "withcwrack0" CTEs gets the result in acceptable timings (a few seconds). The problem with this is that we have some clients with older versions of PG and I guess blindly adding the

Re: Performance degradation with CTEs, switching from PG 11 to PG 15

John, Le 22/11/2023 à 14:30, John Naylor a écrit : Note that "vacuum full" is not recommended practice in most > situations. Among the downsides, it removes the visibility map, > which is necessary to allow index-only scans. Plain vacuum should > always be used except for certain dire

Re: Performance degradation with CTEs, switching from PG 11 to PG 15

On Wed, Nov 22, 2023 at 6:39 PM Jean-Christophe Boggio wrote: > > Hello, > > I just switched from PG11 to PG15 on our production server (Version is > 15.5). Just made a vacuum full analyze on the DB. Note that "vacuum full" is not recommended practice in most situations. Among the downsides, it

Re: Performance problems with Postgres JDBC 42.4.2

On Mon, 6 Nov 2023 at 09:59, Jose Osinde wrote: > > Dear all, > > I'm running a query from Java on a postgres database: > > Java version: 17 > JDBC version: 42.4.2 > Postgres version: 13.1 > > In parallel I'm testing the same queries from pgAdmin 4 version 6.13 > > The tables I'm using contains

Re: [EXTERNAL] Re: Performance down with JDBC 42

On Mon, 6 Nov 2023 at 08:37, Abraham, Danny wrote: > > Both plans refer to the same DB. JDBC is making use of PREPARE statements, whereas psql, unless you're using PREPARE is not. > #1 – Fast – using psql or old JDBC driver The absence of any $1 type parameters here shows that's a custom plan

RE: [EXTERNAL] Re: Performance down with JDBC 42

Thanks for the help. Both plans refer to the same DB. #1 – Fast – using psql or old JDBC driver ==> Sort (cost=13113.27..13113.33 rows=24 width=622) Output: dm.calname, dm.jobyear, dm.caltype, ((dm.daymask)::character varying(400)) Sort Key: dm.calname, dm.jobyear -> HashAggregate

Re: [EXTERNAL] Re: Performance down with JDBC 42

On Sun, Nov 5, 2023 at 11:20 AM Abraham, Danny wrote: > Thanks Laurenz, > > Traced two huge plans. They differ. > The fast one does use Materialize and Memoize (the psql). > Is there something in JDBC 42 that blocks these algoruthms? Directly blocking those is not likely. Maybe the way the

Re: [EXTERNAL] Re: Performance down with JDBC 42

blocks these algoruthms? > > Thanks again > > Danny > > -Original Message- > From: Laurenz Albe > Sent: Saturday, November 4, 2023 11:07 PM > To: Abraham, Danny ; psql-performance > > Subject: [EXTERNAL] Re: Performance down with JDBC 42 > >>

Re: [EXTERNAL] Re: Performance down with JDBC 42

name, setting from pg_settings where name ~ 'enable'; using the JDBC-connection. Regards, Andreas Thanks again Danny -Original Message- From: Laurenz Albe Sent: Saturday, November 4, 2023 11:07 PM To: Abraham, Danny ; psql-performance Subject: [EXTERNAL] Re: Performance down

RE: [EXTERNAL] Re: Performance down with JDBC 42

, Danny ; psql-performance Subject: [EXTERNAL] Re: Performance down with JDBC 42 On Sat, 2023-11-04 at 19:08 +, Abraham, Danny wrote: > Asking for help with a JDBC related issue. > Environment: Linux 7.9 PG 14.9 , very busy PG Server. > > A big query - 3 unions and about 1

Re: Performance down with JDBC 42

On Sat, 2023-11-04 at 19:08 +, Abraham, Danny wrote: > Asking for help with a JDBC related issue. > Environment: Linux 7.9 PG 14.9 , very busy PG Server. > > A big query - 3 unions and about 10 joins runs : > - 70ms on psql , DBeaver with JDBC 42 and in our Server using old JDBC 9.2 > -

Re: Performance implications of 8K pread()s

Hi, On 2023-07-17 16:42:31 +0200, Dimitrios Apostolou wrote: > Thanks, it sounds promising! Are the changes in the 16 branch already, > i.e. is it enough to fetch sources for 16-beta2? No, this is in a separate branch. https://github.com/anarazel/postgres/tree/aio > If so do I just configure

Re: Performance implications of 8K pread()s

Thanks, it sounds promising! Are the changes in the 16 branch already, i.e. is it enough to fetch sources for 16-beta2? If so do I just configure --with-liburing (I'm on linux) and run with io_method=io_uring? Else, if I use the io_method=worker what is a sensible amount of worker threads? Should

Re: Performance implications of 8K pread()s

On Thu, Jul 13, 2023 at 6:50 AM Dimitrios Apostolou wrote: > Interesting and kind of sad that the last update on the wiki page is from > 2021. What is the latest prototype? I'm not sure I'm up to the task of > putting my database to the test. ;-) It works pretty well, certainly well enough to

Re: Performance implications of 8K pread()s

Hello and thanks for the feedback! On Wed, 12 Jul 2023, Thomas Munro wrote: On Wed, Jul 12, 2023 at 1:11 AM Dimitrios Apostolou wrote: Note that I suspect my setup being related, (btrfs compression behaving suboptimally) since the raw device can give me up to 1GB/s rate. It is however

Re: Performance implications of 8K pread()s

On Wed, Jul 12, 2023 at 5:12 AM Thomas Munro wrote: > "gathering" (Oops, for reads, that's "scattering". As in scatter/gather I/O but I picked the wrong one...).

Re: Performance implications of 8K pread()s

On Wed, Jul 12, 2023 at 1:11 AM Dimitrios Apostolou wrote: > Note that I suspect my setup being related, (btrfs compression behaving > suboptimally) since the raw device can give me up to 1GB/s rate. It is however > evident that reading in bigger chunks would mitigate such setup >

Re: Performance issues in query with multiple joins

On Fri, 2023-04-28 at 15:19 +0300, Παρασκευη Πασσαρη wrote: > We are facing a performance issue with the following query. Executing this > query takes about 20 seconds. > (the database version is 14.1) The execution plan seems to be incomplete. Yours, Laurenz Albe

Re: Performance issues in query with multiple joins

=?UTF-8?B?zqDOsc+BzrHPg866zrXPhc63IM6gzrHPg8+DzrHPgc63?= writes: > We are facing a performance issue with the following query. Executing this > query takes about 20 seconds. Raising join_collapse_limit (to more than the number of joins in the query) might help. But I think really if

Re: Performance of UPDATE operation

On Mon, Feb 13, 2023 at 10:09 AM Mkrtchyan, Tigran wrote: > > 0.524 0 BEGIN; > 0.819 0 INSERT INTO t_inodes (inumber, icrtime, > igeneration) > 0.962 0 UPDATE t_inodes SET igeneration = igeneration > + 1 where inumber = :inumber; >

Re: Performance of UPDATE operation

Maybe reconsider your expectation. Note: Every “update” have to “select” before modifying data. Even if the page is in memory, there still work…reading ,acquiring lock, modifying and request to write to disk. Regards, Tobi > On 13 Feb 2023, at 18:48, Laurenz Albe wrote: > > On Mon,

Re: Performance of UPDATE operation

On Mon, 2023-02-13 at 16:09 +0100, Mkrtchyan, Tigran wrote: > Typically we expect that UPDATE is a slow operation in PostgreSQL, however, > there are cases where it's hard to understand why. In particular, I have a > table like > > ``` > CREATE SEQUENCE t_inodes_inumber_seq >     START WITH 1 >  

Re: Performance differential when 0 values present vs when 1 values present. Planner return 52k rows when 0 expected.

> I don't think that index can be used for your original query. It could > only be used if "channel" is unique in "valueseries" and you'd written > the query as: Thanks! That explanation I can understand, now I know how to avoid this in future. > I guess "channel" must not be the primary key

Re: Performance differential when 0 values present vs when 1 values present. Planner return 52k rows when 0 expected.

On Wed, 27 Apr 2022 at 19:54, Emil Iggland wrote: > > > You've got the wrong column order (for this query anyway) in that > > index. It'd work a lot better if dataview were the first column; > I might be misunderstanding you, but I assume that you are suggesting an > index on (dataview,

Re: Performance differential when 0 values present vs when 1 values present. Planner return 52k rows when 0 expected.

> You've got the wrong column order (for this query anyway) in that > index. It'd work a lot better if dataview were the first column; I might be misunderstanding you, but I assume that you are suggesting an index on (dataview, valuetimestamp). We have that index, it is the primary key. For

Re: Performance differential when 0 values present vs when 1 values present. Planner return 52k rows when 0 expected.

Emil Iggland writes: > The query that is giving us issues is the following, channel 752433 has > NO values, 752431 has values. > (Channel 752433 only has valueseries 752434) > select * from datavalue > where dataview in ( select id from valueseries where channel = > %channel_idx%) > ORDER BY

Re: Performance for SQL queries on Azure PostgreSQL PaaS instance

Azure VM's are incredibly slow. I couldn't host a OpenStreetMap database because the disk IO would die off from reasonable performance to about 5KB/s and the data import wouldn't finish. Reboot and it would be fine for a while then repeat. $400 a month for that.  You are better off on bare metal

Re: Performance for SQL queries on Azure PostgreSQL PaaS instance

On Wed, Apr 13, 2022 at 10:34:24AM +0200, Laurenz Albe wrote: > On Tue, 2022-04-12 at 09:10 +, Kumar, Mukesh wrote: > > We have recently done the migration from Oracle Database Version 12C to > > Azure > > PostgreSQL PaaS instance version 11.4 and most of the application > > functionality >

RE: Performance for SQL queries on Azure PostgreSQL PaaS instance

- From: Laurenz Albe Sent: Wednesday, April 13, 2022 2:04 PM To: Kumar, Mukesh ; pgsql-performa...@postgresql.org; MUKESH KUMAR Subject: Re: Performance for SQL queries on Azure PostgreSQL PaaS instance On Tue, 2022-04-12 at 09:10 +, Kumar, Mukesh wrote: > We have recently done the migrat

Re: Performance for SQL queries on Azure PostgreSQL PaaS instance

On Tue, 2022-04-12 at 09:10 +, Kumar, Mukesh wrote: > We have recently done the migration from Oracle Database Version 12C to Azure > PostgreSQL PaaS instance version 11.4 and most of the application > functionality > testing has been over and tested successfully >   > However, there is 1

Re: Performance for SQL queries on Azure PostgreSQL PaaS instance

On 4/12/22 16:23, Frits Jalvingh wrote: > You might be comparing apples and pears.. > > Your Oracle is running on prem while Postgres is running on Azure. Azure > does not really have disks; it seems to have just a bunch of old people > writing the data on paper - I/O on Azure is ridiculously 

Re: Performance for SQL queries on Azure PostgreSQL PaaS instance

You might be comparing apples and pears.. Your Oracle is running on prem while Postgres is running on Azure. Azure does not really have disks; it seems to have just a bunch of old people writing the data on paper - I/O on Azure is ridiculously slow. What disks/hardware does the on-prem Oracle

Re: Performance issue post upgrade on Version 13 - Incorrect Estimation Cost choosing Hash Aggregate-Nested Left Loop Join

On 3/22/22 13:57, Prajna Shetty wrote: > ++ _pgsql-performance@postgresql.org_ > >   > Hello Team, >   > There is change in query plan in 12.4 version and Version 13 resulting > in performance slowness post upgrade. >   > > * In 12.4

Re: Performance issue post upgrade on Version 13 - Incorrect Estimation Cost choosing Hash Aggregate-Nested Left Loop Join

On Tue, Mar 22, 2022 at 12:57:10PM +, Prajna Shetty wrote: > 1. We have performed Vacuum/Analyze/Reindex post Upgrade. > 2. Tweaked work_mem so it does not spill to Disk. We can Disk Usage But > it is still using Hash Aggregate and came down from 5 minutes to 20 seconds. >

Re: performance of analytical query

On Fri, Nov 12, 2021 at 09:12:38PM +0100, Jiří Fejfar wrote: > * I know that PG is focused on OLTP rather then analytics, but we are happy > with it at all and do not wish to use another engine for analytical > queries... isn't somewhere some "PG analytical best practice" available? It's a good

Re: performance of analytical query

On Fri, 12 Nov 2021 at 03:41, Justin Pryzby wrote: > On Thu, Nov 11, 2021 at 08:20:57PM +0100, Jiří Fejfar wrote: > > Hi folks, > > > > we have found that (probably after VACUUM ANALYZE) one analytical query > > starts to be slow on our production DB. Moreover, more or less the same > > plan is

Re: performance of analytical query

On Fri, Nov 12, 2021 at 10:55:53AM -0700, Michael Lewis wrote: > On Thu, Nov 11, 2021 at 7:42 PM Justin Pryzby wrote: > > > BTW, we disable nested loops for the our analytic report queries. I have > > never > > been able to avoid pathological plans any other way. > > Curious, do you see any

Re: performance of analytical query

On Thu, Nov 11, 2021 at 7:42 PM Justin Pryzby wrote: > BTW, we disable nested loops for the our analytic report queries. I have > never > been able to avoid pathological plans any other way. > Curious, do you see any problems from that? Are there certain nodes that really are best suited to a

Re: performance of analytical query

On Thu, Nov 11, 2021 at 08:20:57PM +0100, Jiří Fejfar wrote: > Hi folks, > > we have found that (probably after VACUUM ANALYZE) one analytical query > starts to be slow on our production DB. Moreover, more or less the same > plan is used on our testing data (how to restore our testing data is >

Re: Performance for initial copy when using pg_logical to upgrade Postgres

On Fri, Sep 24, 2021 at 5:02 PM Tim wrote: > I'm currently doing this with a 2.2 TB database. > > Best way IMO is to (UPDATE pg_index SET indisready = false ... ) for non > PK indexes for the largest tables. Then just set it back to indisready = > true after its done and run a REINDEX

Re: Performance for initial copy when using pg_logical to upgrade Postgres

I'm currently doing this with a 2.2 TB database. Best way IMO is to (UPDATE pg_index SET indisready = false ... ) for non PK indexes for the largest tables. Then just set it back to indisready = true after its done and run a REINDEX CONCURRENTLY on the indexes that were disabled. Got about a

Re: Performance for initial copy when using pg_logical to upgrade Postgres

On 9/24/21 10:28 AM, Westwood, Giles wrote: > At Orcid we're trying to upgrade our Postgres database (10 to 13) > using pg_logical for no downtime. The problem we have is how long the > initial copy is taking for the ~500GB database. If it takes say 20days > to complete, will we need to have

Re: Performance for initial copy when using pg_logical to upgrade Postgres

On Fri, Sep 24, 2021 at 3:39 PM Justin Pryzby wrote: > On Fri, Sep 24, 2021 at 03:28:50PM +0100, Westwood, Giles wrote: > > Did you see this thread and its suggestions to 1) set bulk load parameters; > and, 2) drop indexes and FKs ? > > >

Re: Performance for initial copy when using pg_logical to upgrade Postgres

On Fri, Sep 24, 2021 at 03:28:50PM +0100, Westwood, Giles wrote: > At Orcid we're trying to upgrade our Postgres database (10 to 13) using > pg_logical for no downtime. The problem we have is how long the initial > copy is taking for the ~500GB database. If it takes say 20days to complete, > will

Re: Performance issue with thousands of calls to procedures and functions?

>In your example, the bottleneck is calling the function f1. So you need to >check only this function. It is not important if other functions or >>procedures do database lookups. >Or if it does just one database lookup, then you can use SQL language. I >repeat, PL/pgSQL is not good for ultra

Re: Performance issue with thousands of calls to procedures and functions?

pá 30. 7. 2021 v 10:12 odesílatel Daniel Westermann (DWE) < daniel.westerm...@dbi-services.com> napsal: > > pá 30. 7. 2021 v 9:12 odesílatel Daniel Westermann (DWE) < > daniel.westerm...@dbi-services.com> napsal: > > Hi, > > we have a customer which was migrated from Oracle to PostgreSQL 12.5 (I

Re: Performance issue with thousands of calls to procedures and functions?

pá 30. 7. 2021 v 9:12 odesílatel Daniel Westermann (DWE) mailto:daniel.westerm...@dbi-services.com>> napsal: Hi, we have a customer which was migrated from Oracle to PostgreSQL 12.5 (I know, the latest version is 12.7). The migration included a lot of PL/SQL code. Attached a very simplified

Re: Performance issue with thousands of calls to procedures and functions?

pá 30. 7. 2021 v 10:04 odesílatel Pavel Stehule napsal: > Hi > > pá 30. 7. 2021 v 10:02 odesílatel Imre Samu napsal: > >> Hi Daniel, >> >> side note: >> >> Maybe you can tune the "function" with some special query optimizer >> attributes: >> IMMUTABLE | STABLE | VOLATILE | PARALLEL SAFE

Re: Performance issue with thousands of calls to procedures and functions?

Hi pá 30. 7. 2021 v 9:12 odesílatel Daniel Westermann (DWE) < daniel.westerm...@dbi-services.com> napsal: > Hi, > > we have a customer which was migrated from Oracle to PostgreSQL 12.5 (I > know, the latest version is 12.7). The migration included a lot of PL/SQL > code. Attached a very

Re: Performance issue with thousands of calls to procedures and functions?

Hi pá 30. 7. 2021 v 10:02 odesílatel Imre Samu napsal: > Hi Daniel, > > side note: > > Maybe you can tune the "function" with some special query optimizer > attributes: > IMMUTABLE | STABLE | VOLATILE | PARALLEL SAFE > > so in your example: > create or replace function f1(int)

Re: Performance issue with thousands of calls to procedures and functions?

Hi Daniel, side note: Maybe you can tune the "function" with some special query optimizer attributes: IMMUTABLE | STABLE | VOLATILE | PARALLEL SAFE so in your example: create or replace function f1(int) returns double precision as $$ declare begin return 1; end; $$ language

Re: Performance of lateral join

>Easy first question: is the temp table analyzed before being used in a > join ? No, I haven't done that. Today, I tried to run ANALYZE records_to_filter_on; on the same sample data set (3.75 million rows) before the join, and it did not seem to make much of a difference in terms

Re: Performance of lateral join

On Tue, Jul 27, 2021 at 09:08:49AM +, Simen Andreas Andreassen Lønsethagen wrote: > >Easy first question: is the temp table analyzed before being used in a > > join ? > > No, I haven't done that. Today, I tried to run > > ANALYZE records_to_filter_on; > > on the same sample

Re: Performance of lateral join

On Mon, Jul 26, 2021 at 01:56:54PM +, Simen Andreas Andreassen Lønsethagen wrote: > To create the subsets, I (or rather my application) will receive lists of > records which should be matched according to some business logic. Each of > these lists will be read into a temporary table: Easy

Re: Performance Issue on a table

On Thu, Jul 22, 2021 at 01:54:25PM +, Manoj Kumar wrote: > Recently we have noticed that in one of our DB instances there is a potential > delay in querying a table from java code. could you please check the attached > log and help understand what is the problem and which direction should be

Re: Performance benchmark of PG

Thanks a lot. On Mon, 19 Jul 2021, 22:18 Ninad Shah, wrote: > As Thomas rightly pointed about the feasibility of benchmarking. You may > still compare performance of queries on both Exadata as well as PostgreSQL. > IMO, it may not be on par, but it must be acceptable. > > In the contemporary

Re: Performance benchmark of PG

As Thomas rightly pointed about the feasibility of benchmarking. You may still compare performance of queries on both Exadata as well as PostgreSQL. IMO, it may not be on par, but it must be acceptable. In the contemporary world, 60TB isn't really a huge database. So, I hardly think you should

Re: Performance benchmark of PG

Thank you all for your swift response. Thank you again. Manish On Mon, 19 Jul 2021, 15:39 Manish Lad, wrote: > Dear all, >> > We are planning to migrate Oracle exadata database to postgresql and db > size ranges from 1 tb to 60 TB. > > Will the PG support this with the performance matching to

Re: Performance benchmark of PG

Manish Lad schrieb am 19.07.2021 um 12:09: > We are planning to migrate Oracle exadata database to postgresql and > db size ranges from 1 tb to 60 TB. > > Will the PG support this with the performance matching to that of > exadata applince? If anyone could point me in the right direction > where i

Re: Performance benchmark of PG

Hi, The question can not be answered in a proper way. Because, in PostgreSQL, performance(response time in query execution events) depends on 1. Your disk/storage hardware. The performance can vary between SSD and HDD for example. 2. Your PostgreSQL configurations. In other words, configuration

Re: Performance benchmark of PG

Yes you are right. I also experienced same in one such migration from db2 to PG which had read faster but the write was not meeting the need. We then noticed the differences in disk types. Once changed it matched the source. Thanks and Regards Manish On Mon, 19 Jul 2021, 16:34 Laurenz Albe,

Re: Performance benchmark of PG

On Mon, 2021-07-19 at 15:39 +0530, Manish Lad wrote: > We are planning to migrate Oracle exadata database to postgresql and db size > ranges from 1 tb to 60 TB. > > Will the PG support this with the performance matching to that of exadata > applince? > If anyone could point me in the right

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: 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 forward without function then

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 implement policies (for RLS -

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

Re: Performance issue

On Sun, Jun 14, 2020 at 10:45:52PM +, Nagaraj Raj wrote: > My PostgreSQL server 10.11 running on windows which are running very slow. DB > has two tables with ~200Mil records in each. user queries are very slow even > explain analyze also taking a longer. > > Could you please help me to

Re: Performance issue

On Mon, 15 Jun 2020 at 10:46, Nagaraj Raj wrote: > CREATE TABLE test1 > ( ... > CONSTRAINT pk_i_entity_proxy_id PRIMARY KEY (individual_entity_proxy_id) > > ); > CREATE TABLE test2 > ( ... > CONSTRAINT pk_entity_proxy_id PRIMARY KEY (individual_entity_proxy_id) > ); > > > User query: >

Re: Performance tunning

On Sat, May 30, 2020 at 3:37 AM sugnathi hai wrote: > Hi , > > Can you help to tune the below plan > It looks like your query (which you should show us) has something like ORDER BY modifieddate LIMIT 100 It thinks it can walk the index in order, then stop once it collects 100 qualifying

Re: Performance tunning

On Sat, May 30, 2020 at 09:43:43AM +0200, Pavel Stehule wrote: > so 30. 5. 2020 v 9:37 odesílatel sugnathi hai napsal: > > Can you help to tune the below plan Could you also send it so line breaks aren't lost, as seen here:

Re: Performance tunning

Hi so 30. 5. 2020 v 9:37 odesílatel sugnathi hai napsal: > Hi , > > Can you help to tune the below plan > > Limit (cost=0.87..336777.92 rows=100 width=57) (actual > time=599302.173..599481.552 rows=100 loops=1) Buffers: shared hit=78496066 > -> Nested Loop (cost=0.87..11005874.67 rows=3268

Re: performance degredation after upgrade from 9.6 to 12

po 16. 12. 2019 v 14:02 odesílatel Mariel Cherkassky < mariel.cherkas...@gmail.com> napsal: > I see, thank u ! > Maybe I didnt see big difference because most of my tables arent so big. > My db`s size is 17GB and the largest table contains about 20M+ records. > Postgres 12 has enabled JIT by

Re: performance degredation after upgrade from 9.6 to 12

I see, thank u ! Maybe I didnt see big difference because most of my tables arent so big. My db`s size is 17GB and the largest table contains about 20M+ records. Thanks again !

RE: performance degredation after upgrade from 9.6 to 12

task for mart construction) but such class of databases is supposed to be big and required enough resources initially. From: Mariel Cherkassky Sent: Monday, December 16, 2019 2:48 PM To: Jeff Janes Cc: Andrew Zakharov ; pgsql-performance@lists.postgresql.org Subject: Re: performance

Re: performance degredation after upgrade from 9.6 to 12

Hey Jeff,Andrew, I continued testing the 12version vs the 96 version and it seems that there is almost non diff and in some cases pg96 is faster than 12. I compared the content of pg_stat_statements after each test that I have done and it seems that the db time is almost the same and sometimes 96

Re: performance degredation after upgrade from 9.6 to 12

On Sun, Nov 24, 2019 at 1:05 PM Mariel Cherkassky < mariel.cherkas...@gmail.com> wrote: > Hey Jeff, > This example was only used to show that pg96 had better perfomance than > pg12 in a very simple case. > OK, but do you agree that a 15% slow down is more realistic than 3 fold one? Or are you

Re: performance degredation after upgrade from 9.6 to 12

Op 24-11-2019 om 19:05 schreef Mariel Cherkassky: Hey Jeff, This example was only used to show that pg96  had better perfomance than pg12 in a very simple case.  In all the tests that I run most of the queries took less time on 9.6`s version.  I dont know why, but as you can see after

Re: performance degredation after upgrade from 9.6 to 12

On Sun, Nov 24, 2019 at 8:52 AM Mariel Cherkassky < mariel.cherkas...@gmail.com> wrote: > Hey Andrew, > It seems that changing this parameter worked for me. > Setting it to zero means that there wont be any parallel workers for one > query right ? > Is it something familiar this problem with the

Re: performance degredation after upgrade from 9.6 to 12

Hey Andrew, It seems that changing this parameter worked for me. Setting it to zero means that there wont be any parallel workers for one query right ? Is it something familiar this problem with the gatherers ?

Re: performance degredation after upgrade from 9.6 to 12

Hi there - I have same feelings. Try set max_parallel_workers_per_gather to zero. I don't think that comparison non-parallel and parallel versions is correct (don't say anything about parallel in 9.6 pls) What explain says? I suppose you will have different exec plans. Optimizer stranges of

Re: performance degredation after upgrade from 9.6 to 12

Hello, did you run ananlyze on your db? Le dim. 24 nov. 2019 à 13:53, Mariel Cherkassky a écrit : > Hey all, > I'm testing performance of two identical machines one in 9.6 and the > second one is in 12. The second machine is a clone of the first one + db > upgrade to 12 beta 3 (Yes I'm aware

Re: Re: performance bottlenecks on lock transactionid

王若楠 wrote: > I want to find a way to reduce the lock waiting and improve the > performance. You either have to make the transactions shorter, or you let the different clients modify different rows, so that they don't lock each other. That concurrent writers on the same data lock each other is

Re: performance bottlenecks on lock transactionid

王若楠 wrote: > We used benchmarksql 4.1.0 to test the performance of PG12 beta TPCC. > We found performance bottlenecks on lock transactionid. You included an attachment with results from the "pg_locks" view where "granted" is FALSE for all entries. I'll assume that these are not *all* the entries

Re: Performance regressions found using sqlfuzz

Hi Andres: Could you please share your thoughts on QUERY 3? The performance impact of this regression increases *linearly* on larger databases. We concur with Andrew in that this is related to the lack of a Materialize node and mis-costing of the Nested Loop Anti-Join. We found more than 20

Re: Performance regressions found using sqlfuzz

16, 2019 5:37:49 PM To: Andres Freund Cc: Jung, Jinho; Jeff Janes; pgsql-performa...@postgresql.org Subject: Re: Performance regressions found using sqlfuzz Andres Freund writes: > On 2019-02-14 17:27:40 +, Jung, Jinho wrote: >> - Our analysis: We believe that this regression has to

Re: Performance regressions found using sqlfuzz

Andres Freund writes: > On 2019-02-14 17:27:40 +, Jung, Jinho wrote: >> - Our analysis: We believe that this regression has to do with two factors: >> 1) conditional expression (e.g., LEAST or NULLIF) are not reduced to >> constants unlike string functions (e.g., CHAR_LENGTH) 2) change in

Re: Performance regressions found using sqlfuzz

Hi, On 2019-02-14 17:27:40 +, Jung, Jinho wrote: > ### QUERY 2: > > select distinct > ref_0.i_im_id as c0, > ref_1.ol_dist_info as c1 > from > public.item as ref_0 right join > public.order_line as ref_1 > on (ref_0.i_id = 5) > > - Commit: 84f9a35 (Improve

Re: Performance regressions found using sqlfuzz

th=20) (actual time=0.000..0.000 rows=0 loops=30044) One-Time Filter: false Planning Time: 0.350 ms Execution Time: 79.237 ms From: Jeff Janes Sent: Tuesday, February 12, 2019 1:03 PM To: Jung, Jinho Cc: pgsql-performa...@postgresql.o

Re: Performance regressions found using sqlfuzz

On Tue, Feb 12, 2019 at 4:23 AM Jung, Jinho wrote: > > Hello, > > We are developing a tool called sqlfuzz for automatically finding > performance regressions in PostgreSQL. sqlfuzz performs mutational fuzzing > to generate SQL queries that take more time to execute on the latest > version of

Re: Performance regressions found using sqlfuzz

Re: Jung, Jinho 2019-02-11 > We are developing a tool called sqlfuzz for automatically finding performance > regressions in PostgreSQL. sqlfuzz performs mutational fuzzing to generate > SQL queries that take more time to execute on the latest version of > PostgreSQL compared to prior

Re: Performance impact of updating target columns with unchanged values ON CONFLICT

I take that question back – someone helped me on StackExchange and addressed it: *> It appears that Postgres is smart enough to identify cases where indexed columns are not changed , and perform HOT updates; thus , there is no difference between having or not having key columns in update

  1   2   >