Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18

Great point. One of the main reasons we are using partitioning is to quickly drop partitions containing old data so we wouldn't be implementing foreign key constraints any way. On Thu, Oct 23, 2025 at 10:04 PM Laurenz Albe wrote: > On Fri, 2025-10-24 at 11:54 +1300, David Rowley wrote: > > On Fr

Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18

On Thu, Oct 23, 2025 at 10:14 PM Jonathan Reis wrote: > Can't use pg_partman (this is true?) > Apologies, this warrants an explanation. It turns out I was wrong to be concerned. I was worried about pg_partman being able to partition by the decoded value of a column, but it already handles that q

Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18

On Fri, 2025-10-24 at 11:54 +1300, David Rowley wrote: > On Fri, 24 Oct 2025 at 09:38, Laurenz Albe wrote: > > I recommend that you create a primary key on each partition rather than > > having one > > on the partitioned table. > > It might be worth mentioning that doing that would forego having

Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18

Thank you all for your input on this. Here is a summary of what I have learned from you all. Approach 1: partition on uuid_extract_timestamp(id) Pros: No need for custom function to convert from timestamptz to uuidv7 Partitions are human-readable Can use pg_partman Cons: Cannot have a

Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18

On Fri, 24 Oct 2025 at 09:38, Laurenz Albe wrote: > I recommend that you create a primary key on each partition rather than > having one > on the partitioned table. It might be worth mentioning that doing that would forego having the ability to reference the partitioned table in a foreign key co

Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18

On Thu, 2025-10-23 at 13:11 -0700, Jonathan Reis wrote: > Thank you very much for your recommendations and your sample code. I > originally had it your way, but then I found out this is not possible > > create table message ( >   id uuid PRIMARY KEY >   -- ... plus other columns > ) partition by

Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18

Greg, Thank you very much for your recommendations and your sample code. I originally had it your way, but then I found out this is not possible create table message ( id uuid PRIMARY KEY -- ... plus other columns ) partition by range (uuid_extract_timestamp(id)); whereas, this is create ta

Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18

I think from a practical standpoint, partitioning directly on uuidv7 is going to cause problems. You can't directly see the partition constraints, you have to do tricks like your floor function to make it work, and you have to be super careful in how you construct your where clauses. However, what

Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18

I don't know if it will necessarily be of much use in partition pruning, but it should work fairly well as a choice of clustered primary key together with block range indexes. On Wed, Oct 22, 2025 at 12:53 PM Jonathan Reis wrote: > Hello PostgreSQL performance team, > > I’m evaluating the new UU

Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18

On Wed, 22 Oct 2025 at 23:53, Jonathan Reis wrote: > Will the planner efficiently prune partitions when queries filter by UUIDv7 > ranges (e.g., WHERE id BETWEEN uuidv7_floor(timestamp1) AND > uuidv7_floor(timestamp2) that align with time periods? It depends. What are timestamp1 and timestamp2?

Re: Performance implications of 8K pread()s

On Fri, 12 Apr 2024, Thomas Munro wrote: 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

Re: Performance loss after upgrading from 12.15 to 17.2

On Mon, 2025-02-03 at 15:11 +0100, Tobias Orlamünde wrote: > We are currently in the process of upgrading from 12.15 with Timescale > 2.11 to 17.2 with Timescale 2.17.2 > On our pre-prod env we have already upgraded and noticed a remarkable > performance issue vs. the so far not upgraded producti

Re: Performance of Query 60 on TPC-DS Benchmark

Hi! Please check the following proposal (patch in attach). The main idea is to reject only obviously worse paths (costs considerably more than compared one), and to pass pre-calculated startup cost to precheck function for more accurate comparison. > > -- Regards, Nikita Malakhov Postgres Profess

Re: Performance of Query 60 on TPC-DS Benchmark

Hi! I would rather do not exclude add_partial_path_precheck, but modify it to check just path costs and do not count key chains length: foreach(p1, parent_rel->partial_pathlist) { Path *old_path = (Path *) lfirst(p1); if (total_cost > old_path->total_cost * STD_FUZZ_FACTOR) return

Re: Performance of TPC-DS Query 95

On Tue, 26 Nov 2024 at 09:55, Ba Jinsheng wrote: > TPC-DS query 95: > Its execution time is nearly 1 min: >CTE ws_wh > -> Hash Join (cost=37772.14..198810.77 rows=7242361 width=12) (actual > time=211.161..1443.926 rows=6644004 loops=1) > If applying this patch: > - if (is_inne

Re: Performance of Query 60 on TPC-DS Benchmark

On 22/11/2024 18:12, Ba Jinsheng wrote: I think the key difference is that the patch disables the usage of Hash Join, which incurs a worse performance. Discovering your case a little more I found out the origins of the problem: Memoize+NestLoop was not chosen because top-query LIMIT node wasn't

Re: Performance of Query 60 on TPC-DS Benchmark

On 22/11/2024 18:12, Ba Jinsheng wrote: I think the key difference is that the patch disables the usage of Hash Join, which incurs a worse performance. I see here a problem with a number of groups: when predicting it incorrectly, Postgres doesn't use the Memoize node. Disabling HashJoin puts Ne

Re: Performance of Query 4 on TPC-DS Benchmark

On 11/11/24 17:49, Ba Jinsheng wrote: >It is all the time a challenge for PostgreSQL to estimate such a filter >because of absent information on joint column distribution. >Can you research this way by building extended statistics on these >clauses? It could move the plan to the more optimal

Re: Performance of Query 4 on TPC-DS Benchmark

>> The default configurations of PostgreSQL incur the error: "ERROR: could not >> resize shared memory segment "/PostgreSQL.3539600020" to 2097152 bytes: No >> space left on device" >No comment on your optimiser experiments for now, but for this error: >it reminds me of a low/default --shm-siz

Re: Performance of Query 4 on TPC-DS Benchmark

>It is all the time a challenge for PostgreSQL to estimate such a filter >because of absent information on joint column distribution. >Can you research this way by building extended statistics on these >clauses? It could move the plan to the more optimal direction. Thanks a lot for your effort to

Re: Performance of Query 4 on TPC-DS Benchmark

On 11/11/24 02:35, Ba Jinsheng wrote: Hi all, Please see this case: Query 4 on TPC-DS benchmark: Thank you for interesting example! Looking into explains I see two sortings: -> Sort (cost=794037.94..794037.95 rows=1 width=132) (actual time=3024403.310..3024403.313 rows=8 loops=1) -> Sor

Re: Performance of Query 4 on TPC-DS Benchmark

On 10.11.2024 23:16, Alena Rybakina wrote: Hi! On 10.11.2024 22:35, Ba Jinsheng wrote: Hi all, Please see this case: Query 4 on TPC-DS benchmark: with year_total as (  select c_customer_id customer_id        ,c_first_name customer_first_name        ,c_last_name customer_last_name        ,c

Re: Performance of Query 4 on TPC-DS Benchmark

Hi! On 10.11.2024 22:35, Ba Jinsheng wrote: Hi all, Please see this case: Query 4 on TPC-DS benchmark: with year_total as (  select c_customer_id customer_id        ,c_first_name customer_first_name        ,c_last_name customer_last_name        ,c_preferred_cust_flag customer_preferred_cust_

Re: Performance of Query 4 on TPC-DS Benchmark

On Mon, Nov 11, 2024 at 8:36 AM Ba Jinsheng wrote: > The default configurations of PostgreSQL incur the error: "ERROR: could not > resize shared memory segment "/PostgreSQL.3539600020" to 2097152 bytes: No > space left on device" No comment on your optimiser experiments for now, but for this e

Re: Performance of Query 2 in TPC-H

On 11/4/24 15:42, Ba Jinsheng wrote: The estimated cost is reduced by 90%, and the execution time is reduced by 68%. The second query plan includes the operation Memoize, while the first query plan does not. I am wondering if we can optimize the logic anywhere to enable the second query plan.

Re: Performance degradation in Index searches with special characters

Hi Joe, Yes, it seems that the issue is indeed related to that glibc commit. I also found a bug report that highlights a similar problem: https://sourceware.org/bugzilla/show_bug.cgi?id=18441 . Unfortunately, the situation doesn't look promising. As Carlos O'Donell mentioned in his last comment b

Re: Performance degradation in Index searches with special characters

On 10/6/24 14:13, Tom Lane wrote: Joe Conway writes: This is not surprising. There is a performance regression that started in glibc 2.21 with regard to sorting unicode. Test with RHEL 7.x (glibc 2.17) and I bet you will see comparable results to ICU. The best answer in the long term, IMHO, i

Re: Performance degradation in Index searches with special characters

On Mon, Oct 7, 2024 at 9:02 AM Shiv Iyer wrote: >- As the string length increases, the performance degrades exponentially > when using special characters. This is due to the collation’s computational > complexity for each additional character comparison. That's a pretty interesting observat

Re: Performance degradation in Index searches with special characters

Hi Andrey, I have tried my best to answer your queries below: ### Performance Degradation with Special Characters in PostgreSQL **Explanation**: The performance degradation you're experiencing when using special characters like `<`, `@`, `#`, etc., is likely due to how PostgreSQL handles *

Re: Performance degradation in Index searches with special characters

Joe Conway writes: > This is not surprising. There is a performance regression that started > in glibc 2.21 with regard to sorting unicode. Test with RHEL 7.x (glibc > 2.17) and I bet you will see comparable results to ICU. The best answer > in the long term, IMHO, is likely to use the new buil

Re: Performance degradation in Index searches with special characters

On 10/6/24 13:28, Andrey Stikheev wrote: Thanks for your feedback. After looking into it further, it seems the performance issue is indeed related to the default collation settings, particularly when handling certain special characters like |<| in the glibc |strcoll_l| function. This was confir

Re: Performance degradation in Index searches with special characters

Hi, Tom! Thanks for your feedback. After looking into it further, it seems the performance issue is indeed related to the default collation settings, particularly when handling certain special characters like < in the glibc strcoll_l function. This was confirmed during my testing on Debian 12 wit

Re: Performance degradation in Index searches with special characters

Andrey Stikheev writes: >- Changing the collation to 'C' in the query significantly improves >performance. What collation are you using, pray tell? (And what database encoding?) >- Is this performance degradation expected due to collation handling of >certain special characters

Re: performance of sql and plpgsql functions

On 2024-06-17 17:24, Tom Lane wrote: Julius Tuskenis writes: EXPLAIN (ANALYZE, BUFFERS, VERBOSE)   SELECT     COALESCE(sum(mok_nepadengta), 0)   FROM     public.b_pardavimai     JOIN public.b_mokejimai ON (mok_pardavimas = pard_id)   WHERE     (pard_tipas = ANY('{1, 2, 6, 7}'))

Re: performance of sql and plpgsql functions

Julius Tuskenis writes: > EXPLAIN (ANALYZE, BUFFERS, VERBOSE) >   SELECT >     COALESCE(sum(mok_nepadengta), 0) >   FROM >     public.b_pardavimai >     JOIN public.b_mokejimai ON (mok_pardavimas = pard_id) >   WHERE >     (pard_tipas = ANY('{1, 2, 6, 7}')) >     AND (mok_saskaita = 714196

Re: performance of sql and plpgsql functions

po 17. 6. 2024 v 15:55 odesílatel Julius Tuskenis napsal: > On 2024-06-17 15:59, Philip Semanchuk wrote: > > On Jun 17, 2024, at 5:35 AM, Julius Tuskenis > wrote: > > > Isn't PosgreSQL supposed to inline simple SQL functions that are stable or > immutable? > > Postgres inlines SQL functions u

Re: performance of sql and plpgsql functions

On 2024-06-17 15:59, Philip Semanchuk wrote: On Jun 17, 2024, at 5:35 AM, Julius Tuskenis wrote: Isn't PosgreSQL supposed to inline simple SQL functions that are stable or immutable? Postgres inlines SQL functions under certain conditions: https://wiki.postgresql.org/wiki/Inlining_of_SQL_f

Re: performance of sql and plpgsql functions

> On Jun 17, 2024, at 5:35 AM, Julius Tuskenis > wrote: > > > Isn't PosgreSQL supposed to inline simple SQL functions that are stable or > immutable? Postgres inlines SQL functions under certain conditions: https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions One of those conditions

Re: performance of sql and plpgsql functions

Thank you Pavel for your input. You wrote: no, PLpgSQL functions are not inlined Yes, I understand that. I was referring to SQL functions (not plpgsql). Regards, Julius Tuskenis On 2024-06-17 12:44, Pavel Stehule wrote: po 17. 6. 2024 v 11:35 odesílatel Julius Tuskenis napsal: D

Re: performance of sql and plpgsql functions

po 17. 6. 2024 v 11:35 odesílatel Julius Tuskenis napsal: > Dear Postgresql performance guru, > > For some reason on our client server a function written in SQL language > executes *100 times slower* than the one written in plpgsql... > > After updating to "PostgreSQL 12.18, compiled by Visual C+

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 thr

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 t

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 p

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 c

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 situatio

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 r

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 t

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

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 dri

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

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

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

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 tr

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 evident

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

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 performance

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, 2023-02-1

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 t

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

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 some

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 VA

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 o

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 ; [email protected]; 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 pro

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 slow

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 have

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: > ++ [email protected]_ > >   > Hello Team, >   > There is change in query plan in 12.4 version and Version 13 resulting > in performance slowness post upgrade. >   > > * In 12.4 versio

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. > (Expected

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 qu

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 u

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 pro

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 n

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

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 CONCURRENTL

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 tran

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

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 ? > > > https://www.postgresql.org/message-id/flat/4a8efc4e-a264-4

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 w

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 ve

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

pá 30. 7. 2021 v 10:12 odesílatel Daniel Westermann (DWE) < [email protected]> napsal: > > pá 30. 7. 2021 v 9:12 odesílatel Daniel Westermann (DWE) < > [email protected]> 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:[email protected]>> 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 t

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) < [email protected]> 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 simplifie

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

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 plpgsql

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 data

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 f

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

  1   2   3   >