Re: Performance implications of 8K pread()s

[Dimitrios Apostolou]

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

Dimitris


On 12 April 2024 07:45:52 CEST, 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 of last week we can do a little bit of that on the master branch:
>
>postgres=# select count(*) from t;
>
>preadv(46, ..., 8, 256237568) = 131072
>preadv(46, ..., 5, 256368640) = 131072
>preadv(46, ..., 8, 256499712) = 131072
>preadv(46, ..., 5, 256630784) = 131072
>
>postgres=# set io_combine_limit = '256k';
>postgres=# select count(*) from t;
>
>preadv(47, ..., 5, 613728256) = 262144
>preadv(47, ..., 5, 613990400) = 262144
>preadv(47, ..., 5, 614252544) = 262144
>preadv(47, ..., 5, 614514688) = 262144
>
>Here's hoping the commits implementing this stick, for the PostgreSQL
>17 release.  It's just the beginning though, we can only do this for
>full table scans so far (plus a couple of other obscure places).
>Hopefully in the coming year we'll get the "streaming I/O" mechanism
>that powers this hooked up to lots more places... index scans and
>other stuff.  And writing.  Then eventually pushing the I/O into the
>background.  Your questions actually triggered us to talk about why we
>couldn't switch a few things around in our project and get the I/O
>combining piece done sooner.  Thanks!

Re: Performance implications of 8K pread()s

[Thomas Munro]

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 that on the master branch:

postgres=# select count(*) from t;

preadv(46, ..., 8, 256237568) = 131072
preadv(46, ..., 5, 256368640) = 131072
preadv(46, ..., 8, 256499712) = 131072
preadv(46, ..., 5, 256630784) = 131072

postgres=# set io_combine_limit = '256k';
postgres=# select count(*) from t;

preadv(47, ..., 5, 613728256) = 262144
preadv(47, ..., 5, 613990400) = 262144
preadv(47, ..., 5, 614252544) = 262144
preadv(47, ..., 5, 614514688) = 262144

Here's hoping the commits implementing this stick, for the PostgreSQL
17 release.  It's just the beginning though, we can only do this for
full table scans so far (plus a couple of other obscure places).
Hopefully in the coming year we'll get the "streaming I/O" mechanism
that powers this hooked up to lots more places... index scans and
other stuff.  And writing.  Then eventually pushing the I/O into the
background.  Your questions actually triggered us to talk about why we
couldn't switch a few things around in our project and get the I/O
combining piece done sooner.  Thanks!

Re: Table Partitioning and Indexes Performance Questions

[Anupam b]

Would eliminating  triggers and stored procedure would be step #1 to start 
seeing gains from partitions?
We have many triigers and stored procedure and i am trying to to kake sure if 
need to deprecate before moving to partitioning.

Many thx
Andy

Get Outlook for Android<https://aka.ms/AAb9ysg>

From: Laurenz Albe 
Sent: Thursday, February 29, 2024 9:32:48 AM
To: David Kelly ; 
pgsql-performance@lists.postgresql.org 
Subject: Re: Table Partitioning and Indexes Performance Questions

On Thu, 2024-02-29 at 11:42 -0500, David Kelly wrote:
> I was told that partitioned table indexed must always start with the 
> partition key columns.

That's not true.

Only unique indexes (as used by primary key and unique constraints) must
contain the partitioning key (but they don't have to start with it).


> Any other performance considerations when it comes to partitioned table 
> indexing?
> Specifically, partitioning by range where the range is a single value.

Not particularly - selecting from a partitioned table is like selecting
from a UNION ALL of all partitions, except that sometimes PostgreSQL
can forgo scanning some of the partitions.
If you use very many partitions, the overhead for query planning and
execution can become noticable.

Yours,
Laurenz Albe

Re: Table Partitioning and Indexes Performance Questions

[Laurenz Albe]

On Thu, 2024-02-29 at 11:42 -0500, David Kelly wrote:
> I was told that partitioned table indexed must always start with the 
> partition key columns.

That's not true.

Only unique indexes (as used by primary key and unique constraints) must
contain the partitioning key (but they don't have to start with it).


> Any other performance considerations when it comes to partitioned table 
> indexing?
> Specifically, partitioning by range where the range is a single value.

Not particularly - selecting from a partitioned table is like selecting
from a UNION ALL of all partitions, except that sometimes PostgreSQL
can forgo scanning some of the partitions.
If you use very many partitions, the overhead for query planning and
execution can become noticable.

Yours,
Laurenz Albe

Table Partitioning and Indexes Performance Questions

[David Kelly]

I was told that partitioned table indexed must always start with the
partition key columns.

Is this always the case or does it depend on use case? When would you want
to create indexes in this way?

The documentation just mentions that it is strictly unnecessary but can be
helpful. My understanding is partitions behave like normal tables. Each
gets their own index. So, I'd expect the reasoning behind creating the
index on the partition should be the same as if it were just a normal table
(assuming it has the same subset of data as the individual partition). Is
this a correct understanding?

Any other performance considerations when it comes to partitioned table
indexing? Specifically, partitioning by range where the range is a single
value.

Re: generic plan generate poor performance

[Pavel Stehule]

Hi

čt 29. 2. 2024 v 15:28 odesílatel James Pang 
napsal:

> Hi,
>we create statistics (dependencies,distinct) on (cccid,sssid);  with
> real bind variables , it make good plan of Hash join , but when it try to
> generic plan, it automatically convert to  Nestloop and then very poor sql
> performance.   why generic plan change to to a poor plan "nestloop" ? how
> to  fix that.
>

please, send result of EXPLAIN ANALYZE, try to run VACUUM ANALYZE before

probably there will not good estimation



>
>   explain execute j2eemtgatdlistsql16(27115336789879,15818676);
>QUERY PLAN
>
> 
>  Hash Left Join  (cost=11513.05..25541.17 rows=773 width=)
>Hash Cond: ((a.sssid = b.sssid) AND (a.cccid = b.cccid) AND (a.uuid =
> b.uuid))
>->  Index Scan using idx_mtgccclist_conf_j2 on mtgccclistj2 a
>  (cost=0.43..14010.19 rows=773 width=1059)
>  Index Cond: ((cccfid = '27115336789879'::bigint) AND (sssid =
> '15818676'::bigint))
>  Filter: (jstatus = ANY ('{3,7,11,2,6,10}'::bigint[]))
>->  Hash  (cost=11330.73..11330.73 rows=10393 width=51)
>  ->  Index Scan using idx_mtgccclstext_cccsssid_j2 on
> mtglistextj2 b  (cost=0.43..11330.73 rows=10393 width=51)
>Index Cond: ((cccid = '27115336789879'::bigint) AND (siteid
> = '15818676'::bigint))
> (8 rows)
>
>  explain execute j2eemtgatdlistsql16(27115336789879,15818676);
> QUERY PLAN
>
> ---
>  Nested Loop Left Join  (cost=0.87..289.53 rows=14 width=)
>->  Index Scan using idx_mtgccclist_conf_j2 on mtgccclistj2 a
>  (cost=0.43..251.94 rows=14 width=1059)
>  Index Cond: ((cccid = $1) AND (sssid = $2))
>  Filter: (jstatus = ANY ('{3,7,11,2,6,10}'::bigint[]))
>->  Index Scan using idx_mtgccclstext_cccsssid_j2 on mtglistextj2 b
>  (cost=0.43..2.66 rows=1 width=51)
>  Index Cond: ((cccid = a.cccid) AND (cccid = $1) AND (sssid =
> a.sssid) AND (sssid = $2))
>  Filter: (a.uuid = uuid)
> (7 rows)
>
> Thanks,
>
>
Regards

Pavel


> James
>

generic plan generate poor performance

[James Pang]

Hi,
   we create statistics (dependencies,distinct) on (cccid,sssid);  with
real bind variables , it make good plan of Hash join , but when it try to
generic plan, it automatically convert to  Nestloop and then very poor sql
performance.   why generic plan change to to a poor plan "nestloop" ? how
to  fix that.

  explain execute j2eemtgatdlistsql16(27115336789879,15818676);
   QUERY PLAN

 Hash Left Join  (cost=11513.05..25541.17 rows=773 width=)
   Hash Cond: ((a.sssid = b.sssid) AND (a.cccid = b.cccid) AND (a.uuid =
b.uuid))
   ->  Index Scan using idx_mtgccclist_conf_j2 on mtgccclistj2 a
 (cost=0.43..14010.19 rows=773 width=1059)
 Index Cond: ((cccfid = '27115336789879'::bigint) AND (sssid =
'15818676'::bigint))
 Filter: (jstatus = ANY ('{3,7,11,2,6,10}'::bigint[]))
   ->  Hash  (cost=11330.73..11330.73 rows=10393 width=51)
 ->  Index Scan using idx_mtgccclstext_cccsssid_j2 on
mtglistextj2 b  (cost=0.43..11330.73 rows=10393 width=51)
   Index Cond: ((cccid = '27115336789879'::bigint) AND (siteid
= '15818676'::bigint))
(8 rows)

 explain execute j2eemtgatdlistsql16(27115336789879,15818676);
QUERY PLAN
---
 Nested Loop Left Join  (cost=0.87..289.53 rows=14 width=)
   ->  Index Scan using idx_mtgccclist_conf_j2 on mtgccclistj2 a
 (cost=0.43..251.94 rows=14 width=1059)
 Index Cond: ((cccid = $1) AND (sssid = $2))
 Filter: (jstatus = ANY ('{3,7,11,2,6,10}'::bigint[]))
   ->  Index Scan using idx_mtgccclstext_cccsssid_j2 on mtglistextj2 b
 (cost=0.43..2.66 rows=1 width=51)
 Index Cond: ((cccid = a.cccid) AND (cccid = $1) AND (sssid =
a.sssid) AND (sssid = $2))
 Filter: (a.uuid = uuid)
(7 rows)

Thanks,

James

Re: sql statement not using all primary key values and poor performance

[Laurenz Albe]

On Fri, 2024-02-23 at 18:21 +0800, James Pang wrote:
> it's a third-party vendor application, not easy to change their code.

Then the application is broken, and you should make the vendor fix it.

> is it possible to   1) in Postgresql JDBC driver connection, set
> plan_cache_mode=force_custom_plan    or 2) some other parameters can 
> workaround this issue?

You can set "prepareThreshold" to 0 to keep the JDBC driver from using
prepared statements in PostgreSQL.  I am not sure if that is enough to
fix the problem.

Yours,
Laurenz Albe

Re: sql statement not using all primary key values and poor performance

[James Pang]

it's a third-party vendor application, not easy to change their code.
is it possible to   1) in Postgresql JDBC driver connection, set
plan_cache_mode=force_custom_plan
  or 2) some other parameters can workaround this issue?

Thanks,

James

Laurenz Albe  於 2024年2月23日週五 下午5:17寫道：

> On Fri, 2024-02-23 at 15:20 +0800, James Pang wrote:
> >we have a SQL from Postgresql JDBC,  primary is based on
> (bigint,varchar2,bigint),
> > but from sql plan, it convert to ::numeric so the plan just use one
> "varchar"
> > key column and use the other 2 bigint keys as filters. what's the cause
> about that ?
> >
> > Table "test.xx"
> >   Column  |  Type  | Collation |
> Nullable | Default
> >
> --++---+--+-
> >   xxxid   | bigint |   | not
> null |
> >  paramname| character varying(512) |   | not
> null |
> >  paramvalue   | character varying(1536)|   |
>  |
> >   sssid   | bigint |   | not
> null |
> >  createtime   | timestamp(0) without time zone |   |
>  |
> >  lastmodifiedtime | timestamp(0) without time zone |   |
>  |
> >  mmmuuid  | character varying(32)  |   |
>  |
> > Indexes:
> >   "pk_xx" PRIMARY KEY, btree (xxxid, paramname, sssid)
> >   "idx_xx_mmmuuid" btree (sssid, mmmuuid, paramname)
> >
> > SET extra_float_digits = 3
> >
> > duration: 7086.014 ms  plan:
> > Query Text: SELECT XXXFID, PARAMNAME, PARAMVALUE, SSSID,
> CREATETIME, LASTMODIFIEDTIME, MMMUUID FROM test.XX WHERE  ( ( XXXID =
> $1  ) )  AND  ( ( PARAMNAME = $2  ) )  AND  ( ( SSSID = $3  ) )
> > Index Scan using pk_xx on test.xx
>  (cost=0.57..2065259.09 rows=1 width=86) (actual time=7086.010..7086.011
> rows=0 loops=1)
> >   Output: confid, paramname, paramvalue, sssid, createtime,
> lastmodifiedtime, mmmuuid
> >   Index Cond: ((xx.paramname)::text = 'cdkkif'::text)
>  <<< just use only one key instead all primary keys.
> >   Filter: (((xx.xxxid)::numeric = '18174044'::numeric) AND
> ((xx.sssid)::numeric = '253352'::numeric))<<< it's bigint but
> converted to numeric
> >   Buffers: shared read=1063470
> >   I/O Timings: read=4402.029
> >
> > it's from JDBC, we saw this JDBC driver try to set extra_float_digits =
> 3 before
> > running the SQL ,does that make planner to convert bigint to numeric ?
>
> Setting "extra_float_digits" is just something the JDBC driver does so as
> to
> not lose precision with "real" and "double precision" values on old
> versions
> of PostgreSQL.
>
> The problem is that you bind the query parameters with the wrong data
> types.
> Don't use "setBigDecimal()", but "setLong()" if you want to bind a
> "bigint".
> An alternative is "setObject()" with "targetSqlType" set to "Types.BIGINT".
>
> Yours,
> Laurenz Albe
>

Re: sql statement not using all primary key values and poor performance

[Laurenz Albe]

On Fri, 2024-02-23 at 15:20 +0800, James Pang wrote:
>    we have a SQL from Postgresql JDBC,  primary is based on 
> (bigint,varchar2,bigint),
> but from sql plan, it convert to ::numeric so the plan just use one "varchar"
> key column and use the other 2 bigint keys as filters. what's the cause about 
> that ? 
> 
>                             Table "test.xx"
>       Column      |              Type              | Collation | Nullable | 
> Default
> --++---+--+-
>   xxxid           | bigint                         |           | not null |
>  paramname        | character varying(512)         |           | not null |
>  paramvalue       | character varying(1536)        |           |          |
>   sssid           | bigint                         |           | not null |
>  createtime       | timestamp(0) without time zone |           |          |
>  lastmodifiedtime | timestamp(0) without time zone |           |          |
>  mmmuuid          | character varying(32)          |           |          |
> Indexes:
>       "pk_xx" PRIMARY KEY, btree (xxxid, paramname, sssid)
>       "idx_xx_mmmuuid" btree (sssid, mmmuuid, paramname)
> 
> SET extra_float_digits = 3
> 
> duration: 7086.014 ms  plan:
>         Query Text: SELECT XXXFID, PARAMNAME, PARAMVALUE, SSSID, CREATETIME, 
> LASTMODIFIEDTIME, MMMUUID FROM test.XX WHERE  ( ( XXXID = $1  ) )  AND  ( 
> ( PARAMNAME = $2  ) )  AND  ( ( SSSID = $3  ) )
>         Index Scan using pk_xx on test.xx  (cost=0.57..2065259.09 
> rows=1 width=86) (actual time=7086.010..7086.011 rows=0 loops=1)
>           Output: confid, paramname, paramvalue, sssid, createtime, 
> lastmodifiedtime, mmmuuid
>           Index Cond: ((xx.paramname)::text = 'cdkkif'::text)   <<< 
> just use only one key instead all primary keys.
>           Filter: (((xx.xxxid)::numeric = '18174044'::numeric) AND 
> ((xx.sssid)::numeric = '253352'::numeric))    <<< it's bigint but 
> converted to numeric 
>           Buffers: shared read=1063470
>           I/O Timings: read=4402.029
> 
> it's from JDBC, we saw this JDBC driver try to set extra_float_digits = 3 
> before
> running the SQL ,does that make planner to convert bigint to numeric ?

Setting "extra_float_digits" is just something the JDBC driver does so as to
not lose precision with "real" and "double precision" values on old versions
of PostgreSQL.

The problem is that you bind the query parameters with the wrong data types.
Don't use "setBigDecimal()", but "setLong()" if you want to bind a "bigint".
An alternative is "setObject()" with "targetSqlType" set to "Types.BIGINT".

Yours,
Laurenz Albe

Re: sql statement not using all primary key values and poor performance

[James Pang]

Hi experts,
   we have a SQL from Postgresql JDBC,  primary key is based on
(bigint,varchar2,bigint),  but from sql plan, it convert to ::numeric so
the plan just use one "varchar" key column and use the other 2 bigint keys
as filters. what's the cause about that ?

Table "test.xx"
  Column  |  Type  | Collation | Nullable |
Default
--++---+--+-
 xxxid   | bigint |   | not null |
 paramname| character varying(512) |   | not null |
 paramvalue   | character varying(1536)|   |  |
 sssid   | bigint |   | not null |
 createtime   | timestamp(0) without time zone |   |  |
 lastmodifiedtime | timestamp(0) without time zone |   |  |
 mmmuuid  | character varying(32)  |   |  |
Indexes:
  "pk_xx" PRIMARY KEY, btree (xxxid, paramname, sssid)
  "idx_xx_mmmuuid" btree (sssid, mmmuuid, paramname)



SET extra_float_digits = 3


duration: 7086.014 ms  plan:
Query Text: SELECT XXXFID, PARAMNAME, PARAMVALUE, SSSID,
CREATETIME, LASTMODIFIEDTIME, MMMUUID FROM test.XX WHERE  ( ( XXXID =
$1  ) )  AND  ( ( PARAMNAME = $2  ) )  AND  ( ( SSSID = $3  ) )
Index Scan using pk_xx on test.xx  (cost=0.57..2065259.09
rows=1 width=86) (actual time=7086.010..7086.011 rows=0 loops=1)
  Output: xxxid, paramname, paramvalue, sssid, createtime,
lastmodifiedtime, mmmuuid
  Index Cond: ((xx.paramname)::text = 'cdkkif'::text)   <<<
just use only one key instead all primary keys.
  Filter: (((xx.xxxid)::numeric = '18174044'::numeric) AND
((xx.sssid)::numeric = '253352'::numeric))<<< it's bigint but
converted to numeric
  Buffers: shared read=1063470
  I/O Timings: read=4402.029

it's from JDBC, we saw this JDBC driver try to set extra_float_digits = 3
before running the SQL ,does that make planner to convert bigint to numeric
?   Postgresql 14.10 version. how to avoid this conversion and make planner
use all primary keys.

Thanks,

James

James Pang  於 2024年2月23日週五 下午3:20寫道：

> Hi experts,
>we have a SQL from Postgresql JDBC,  primary is based on
> (bigint,varchar2,bigint),  but from sql plan, it convert to ::numeric so
> the plan just use one "varchar" key column and use the other 2 bigint keys
> as filters. what's the cause about that ?
>
> Table "test.xx"
>   Column  |  Type  | Collation | Nullable
> | Default
>
> --++---+--+-
>  xxxid   | bigint |   | not null |
>  paramname| character varying(512) |   | not null |
>  paramvalue   | character varying(1536)|   |  |
>  sssid   | bigint |   | not null |
>  createtime   | timestamp(0) without time zone |   |  |
>  lastmodifiedtime | timestamp(0) without time zone |   |  |
>  mmmuuid  | character varying(32)  |   |  |
> Indexes:
>   "pk_xx" PRIMARY KEY, btree (xxxid, paramname, sssid)
>   "idx_xx_mmmuuid" btree (sssid, mmmuuid, paramname)
>
>
>
> SET extra_float_digits = 3
>
>
> duration: 7086.014 ms  plan:
> Query Text: SELECT XXXFID, PARAMNAME, PARAMVALUE, SSSID,
> CREATETIME, LASTMODIFIEDTIME, MMMUUID FROM test.XX WHERE  ( ( XXXID =
> $1  ) )  AND  ( ( PARAMNAME = $2  ) )  AND  ( ( SSSID = $3  ) )
> Index Scan using pk_xx on test.xx  (cost=0.57..2065259.09
> rows=1 width=86) (actual time=7086.010..7086.011 rows=0 loops=1)
>   Output: confid, paramname, paramvalue, sssid, createtime,
> lastmodifiedtime, mmmuuid
>   Index Cond: ((xx.paramname)::text = 'cdkkif'::text)
>  <<< just use only one key instead all primary keys.
>   Filter: (((xx.xxxid)::numeric = '18174044'::numeric) AND
> ((xx.sssid)::numeric = '253352'::numeric))<<< it's bigint but
> converted to numeric
>   Buffers: shared read=1063470
>   I/O Timings: read=4402.029
>
> it's from JDBC, we saw this JDBC driver try to set extra_float_digits = 3
> before running the SQL ,does that make planner to convert bigint to numeric
> ?
>
> Thanks,
>
> James
>

sql statement not using all primary key values and poor performance

[James Pang]

Hi experts,
   we have a SQL from Postgresql JDBC,  primary is based on
(bigint,varchar2,bigint),  but from sql plan, it convert to ::numeric so
the plan just use one "varchar" key column and use the other 2 bigint keys
as filters. what's the cause about that ?

Table "test.xx"
  Column  |  Type  | Collation | Nullable |
Default
--++---+--+-
 xxxid   | bigint |   | not null |
 paramname| character varying(512) |   | not null |
 paramvalue   | character varying(1536)|   |  |
 sssid   | bigint |   | not null |
 createtime   | timestamp(0) without time zone |   |  |
 lastmodifiedtime | timestamp(0) without time zone |   |  |
 mmmuuid  | character varying(32)  |   |  |
Indexes:
  "pk_xx" PRIMARY KEY, btree (xxxid, paramname, sssid)
  "idx_xx_mmmuuid" btree (sssid, mmmuuid, paramname)



SET extra_float_digits = 3


duration: 7086.014 ms  plan:
Query Text: SELECT XXXFID, PARAMNAME, PARAMVALUE, SSSID,
CREATETIME, LASTMODIFIEDTIME, MMMUUID FROM test.XX WHERE  ( ( XXXID =
$1  ) )  AND  ( ( PARAMNAME = $2  ) )  AND  ( ( SSSID = $3  ) )
Index Scan using pk_xx on test.xx  (cost=0.57..2065259.09
rows=1 width=86) (actual time=7086.010..7086.011 rows=0 loops=1)
  Output: confid, paramname, paramvalue, sssid, createtime,
lastmodifiedtime, mmmuuid
  Index Cond: ((xx.paramname)::text = 'cdkkif'::text)   <<<
just use only one key instead all primary keys.
  Filter: (((xx.xxxid)::numeric = '18174044'::numeric) AND
((xx.sssid)::numeric = '253352'::numeric))<<< it's bigint but
converted to numeric
  Buffers: shared read=1063470
  I/O Timings: read=4402.029

it's from JDBC, we saw this JDBC driver try to set extra_float_digits = 3
before running the SQL ,does that make planner to convert bigint to numeric
?

Thanks,

James

Re: "not related" code blocks for removal of dead rows when using vacuum and this kills the performance

[Lars Aksel Opsahl]

From: Laurenz Albe 
Sent: Tuesday, February 20, 2024 8:29 AM
>Re: "not related" code blocks for removal of dead rows when using vacuum and 
>this kills the performance
>Laurenz Albe 
>​Lars Aksel Opsahl;​
>pgsql-performance@lists.postgresql.org​
>On Tue, 2024-02-20 at 05:46 +, Lars Aksel Opsahl wrote:
>> If this is expected behavior it means that any user on the database that 
>> writes
>> a long running sql that does not even insert any data can kill performance 
>> for
>> any other user in the database.
>
>Yes, that is the case.  A long running query will hold a snapshot, and no data
>visible in that snapshot can be deleted.
>
>That can cause bloat, which can impact performance.
>

Hi

Thanks for the chat, seems like I finally found solution that seems work for 
this test code.

Adding a commit's  like here 
/uploads/031b350bc1f65752b013ee4ae5ae64a3/test_issue_67_with_commit.sql to 
master code even if there are nothing to commit seems to solve problem and that 
makes sense based on what you say, because then the master code gets a new 
visible snapshot and then releases the old snapshot.

The reason why I like to use psql as the master/Orchestration code and not 
C/Python/Bash and so is to make more simple to use/code and test.

Lars

Re: "not related" code blocks for removal of dead rows when using vacuum and this kills the performance

[Laurenz Albe]

On Tue, 2024-02-20 at 05:46 +, Lars Aksel Opsahl wrote:
> If this is expected behavior it means that any user on the database that 
> writes
> a long running sql that does not even insert any data can kill performance for
> any other user in the database.

Yes, that is the case.  A long running query will hold a snapshot, and no data
visible in that snapshot can be deleted.

That can cause bloat, which can impact performance.

> So applications like QGIS who seems to keep open connections for a while can
> then also kill the performance for any other user in the data.

No, that is not a problem.  Keeping *connections* open is a good thing. It is
keeping data modifying transactions, cursors or long-running queries open
that constitutes a problem.

Yours,
Laurenz Albe

Re: "not related" code blocks for removal of dead rows when using vacuum and this kills the performance

[Lars Aksel Opsahl]

From: Lars Aksel Opsahl 
>From: Laurenz Albe 
>>
>>It is not entirely clear what you are doing, but it seems like you are holding
>>a database transaction open, and yes, then it is expected behavior that
>>VACUUM cannot clean up dead rows in the table.
>>
>>Make sure that your database transactions are short.
>>Don't use table or row locks to synchronize application threads.
>>What you could use to synchronize your application threads are advisory locks,
>>they are not tied to a database transaction.
>>
>
>Hi
>
>The details are here at 
>https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/issues/67#note_1779300212
> and
>here is also a ref. to this test script that shows problem 
>https://gitlab.com/nibioopensource/resolve-overlap-and-gap/uploads/9a0988b50f05386ec9d91d6600bb03ec/test_issue_67.sql
>
>I am not doing any locks I just do plain CRUD operations .
>
>The key is that the master code is not creating any table or insert rows that 
>is done by many short operations as you suggested.
>
>But even if the master code is not doing any operations against the test table 
>it's blocking removal of dead rows.
>
>If this expected behavior, it's means that any long running transactions will 
>block for removal of any dead rows for all visible tables in the database and 
>that seems like problem or weakness of Postgresql.
>
>While writing this I now was thinking maybe I can get around problem by not 
>making the table not visible by the master code but that makes it very 
>complicated for mee.
>
>Thanks.
>
>Lars

Hi

I now tested running the master (Orchestration) code  as user joe.

In the master code I connect back as user lop and creates the test table 
test_null and inserts data in many tiny operations.

User joe who has the long running operation does not know anything about table 
test_null and does not have any grants to that table.

The table test_null is not granted to public either.

The problem is the same, the long running transaction to joe will kill the 
performance on a table which user joe does not have any access to or know 
anything about .

If this is expected behavior it means that any user on the database that writes 
a long running sql that does not even insert any data can kill performance for 
any other user in the database.

So applications like QGIS who seems to keep open connections for a while can 
then also kill the performance for any other user in the data.

Having postgresql working like this also makes it very difficult to debug 
performance issues because a problem may just have been a side effect of a not 
related sql.

So I hope this is not the case and that I have done something wrong or that 
there are some parameters that can be adjusted on get around this problem.

Thanks

Lars

Re: "not related" code blocks for removal of dead rows when using vacuum and this kills the performance

[Lars Aksel Opsahl]

From: Laurenz Albe 
>
>It is not entirely clear what you are doing, but it seems like you are holding
>a database transaction open, and yes, then it is expected behavior that
>VACUUM cannot clean up dead rows in the table.
>
>Make sure that your database transactions are short.
>Don't use table or row locks to synchronize application threads.
>What you could use to synchronize your application threads are advisory locks,
>they are not tied to a database transaction.
>

Hi

The details are here at 
https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/issues/67#note_1779300212
 and
here is also a ref. to this test script that shows problem 
https://gitlab.com/nibioopensource/resolve-overlap-and-gap/uploads/9a0988b50f05386ec9d91d6600bb03ec/test_issue_67.sql

I am not doing any locks I just do plain CRUD operations .

The key is that the master code is not creating any table or insert rows that 
is done by many short operations as you suggested.

But even if the master code is not doing any operations against the test table 
it's blocking removal of dead rows.

If this expected behavior, it's means that any long running transactions will 
block for removal of any dead rows for all visible tables in the database and 
that seems like problem or weakness of Postgresql.

While writing this I now was thinking maybe I can get around problem by not 
making the table not visible by the master code but that makes it very 
complicated for mee.

Thanks.

Lars

Re: "not related" code blocks for removal of dead rows when using vacuum and this kills the performance

[Laurenz Albe]

On Mon, 2024-02-19 at 16:14 +, Lars Aksel Opsahl wrote:
> Then we start testing VACUUM and very simple SQL testing in another window.
> 
> We can now show we have performance of "3343.794 ms" and not "0.123 ms", which
> is what we get when we are able to remove dead rows and run a new analyze.
> 
> The problem is that as long as the master code is active, we cannot remove
> alle dead rows and that what seems to be killing the performance.
> 
> With active I mean in hanging on pg_sleep and remember that this master has
> not created the test table or inserted any data in this test table it self.
> 
> Is the expected behavior ?

It is not entirely clear what you are doing, but it seems like you are holding
a database transaction open, and yes, then it is expected behavior that
VACUUM cannot clean up dead rows in the table.

Make sure that your database transactions are short.
Don't use table or row locks to synchronize application threads.
What you could use to synchronize your application threads are advisory locks,
they are not tied to a database transaction.

Yours,
Laurenz Albe

"not related" code blocks for removal of dead rows when using vacuum and this kills the performance

[Lars Aksel Opsahl]

Hi

We have a master code block which starts small, tiny operations that create a 
table and inserts data into that table in many threads.

Nothing is done the master code, we follow an Orchestration pattern , where 
master just sends a message about what to do and that is done in other database 
connections not related connections used by master code.

In the master code I add sleep after the CRUD operations are done to make it 
easier to test. The test table will not change in the rest of this master code 
(in real life it happens more in the master code off course) .

Then we start testing VACUUM and very simple SQL testing in another window.

We can now show we have performance of "3343.794 ms" and not "0.123 ms", which 
is what we get when we are able to remove dead rows and run a new analyze.

The problem is that as long as the master code is active, we cannot remove alle 
dead rows and that what seems to be killing the performance.

With active I mean in hanging on pg_sleep and remember that this master has not 
created the test table or inserted any data in this test table it self.

Is the expected behavior ?

Is possible to around this problem in any way ?

In this note you find a detailed description and a simple standalone test 
script  
https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/issues/67#note_1779300212

I have tested on "PostgreSQL 14.10 (Homebrew) on aarch64-apple-darwin23.0.0, 
compiled by Apple clang version 15.0.0 (clang-1500.0.40.1), 64-bit" and 
"PostgreSQL 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, 
compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit"

Thanks .

Lars

Re: Weird performance differences between cloud vendors

[Laurenz Albe]

On Thu, 2024-02-01 at 10:23 +0100, Dirk Krautschick wrote:
> I have run a test with pgbench against two cloud vendors (settings, 
> parameters almost the same).
> Both Postgres (or whatever they do internally when they call it as Postgres 
> offering, NOT Aurora or so :-) )
> 
> I have got a strange result that cloud vendor 1 is performing almost 
> everywhere better in matter of
> read and write but except in the init phase of pgbench it took almost double 
> the time.

Nobody except those vendors could tell you for certain, but perhaps on the one
system the initial data load is fast, because you have not yet exceeded your 
I/O quota,
and then I/O is throttled.

Yours,
Laurenz Albe

Weird performance differences between cloud vendors

[Dirk Krautschick]

Hi,


I have run a test with pgbench against two cloud vendors (settings, 
parameters almost the same).


Both Postgres (or whatever they do internally when they call it as 
Postgres offering, NOT Aurora or so :-) )



I have got a strange result that cloud vendor 1 is performing almost 
everywhere better in matter of


read and write but except in the init phase of pgbench it took almost 
double the time.




/pgbench -i -IdtGvp -s 3000 "${PG_DATABASE}"/
/pgbench -c 50 -j 10 -P 60 -r -T 3600 "${PG_DATABASE}"/



| Metric | cloud vendor 1 (small) | cloud vendor 1 (large) | cloud 
vendor 2 (small) | cloud vendor 2 (large) | 
|--|||---|---| 
| **Initialization Time** | 60m52.932s | 3h0m8.97s | 32m7.154s | 
5h14m16s | | **Benchmark Duration** | 3600s (1 hour) | 3600s (1 hour) | 
3600s (1 hour) | 3600s (1 hour) | | **Transactions per Second** | 
399.460720 | 9833.737455 | 326.551036 | 3314.363264 | | **Latency 
Average (ms)** | 125.124 | 6.507 | 153.106 | 19.309 | | **Latency StdDev 
(ms)** | 154.483 | 44.403 | 59.522 | 4.015 | | **Initial Connection Time 
(ms)** | 557.696 | 174.318 | 1688.474 | 651.087 | | **Transactions 
Processed** | 1,438,437 | 35,400,215 | 1,175,081 | 11,929,631 | | 
Statement (ms) | cloud vendor 1 (small) | cloud vendor 1 (large) | cloud 
vendor 2 (small) | cloud vendor 2 (large) | 
|-|||---|---| 
| BEGIN | 8.599 | 0.545 | 9.008 | 1.398 | | UPDATE pgbench_accounts | 
38.648 | 2.031 | 27.124 | 4.722 | | SELECT pgbench_accounts | 12.332 | 
0.676 | 17.922 | 1.798 | | UPDATE pgbench_tellers | 17.275 | 0.853 | 
20.843 | 1.831 | | UPDATE pgbench_branches | 18.478 | 0.862 | 21.941 | 
1.743 | | INSERT INTO pgbench_history | 16.613 | 0.827 | 18.710 | 1.501 
| | END | 13.177 | 0.708 | 37.553 | 6.317 |



Of course no one knows the magig underneath what some cloud vendors are 
doing underneath but does anyone have some


ideas what the reason could be or how I could do better testing to find 
this out?



Cheers


Dirk

Re: Performance

[Samed YILDIRIM]

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 our Project:
> It will be in Client/Server Architecture. Windows Application users will
> access the server as clients and they are all in different locations. There
> will be a simple ERP system that will perform CRUD transactions and report
> them.
>

I hope you are not thinking of keeping business logic on the application
side and querying the database from different locations. If you treat the
database as a regular application's database and run multiple DML's for
each request through the internet, performance of the application will be
horrible due to latency between the application and the database. In case
you plan to use such a model, the best approach would be to decrease the
number of queries as much as possible, and achieve multiple operations by a
single request, instead of reading from multiple tables, doing some
calculations, writing back something to the database etc. I would move the
logic to the database side as much as possible and do function/procedure
calls, or have an application nearby the database and make clients'
applications interact with it. So, the business logic would still be in an
application and close to the database.


> We are considering connecting to the Embarcadero Firedac dataset. We can
> also connect clients with PosgreRestAPI.
> Our number of clients can be between 5k-20K.
> We have a maximum of 200 tables consisting of invoice, order, customer,
> bank and stock information. I can create a second Postgre SQL for reporting
> if necessary.
>

This is an interesting point. Because, if you plan to have 20k clients, you
should also be planning high availability, backups, replications etc.
Serving 20k clients with a standalone server would not be something I would
like to involve :)


> Question 1 :
> Should we install PostgreSQL on Windows server operating system or Linux
> operating system?
>

My personal opinion, this is not even a question. The answer is and will
always be Linux for me :D
However, the actual question is what is the cost of managing a Linux server
for you. If you are not familiar with Linux, if you don't have any
experience with linux, and if you don't have a company or budget to
hire/work with you on this who is a professional linux or PostgreSQL admin,
going with Windows is a much more sensible option for you even though it is
not the best OS or not the best performing option for PostgreSQL.


> 2:
> Is it correct to open a field named client_id for each table, for example
> the customer table, and use this field in CRUD operations to host the same
> single customer table for all users?
>

It depends on the data size and your project's isolation/security
requirements. You may also consider partitioning and row level security
features of PostgreSQL. There is not a single recipe that is good for all
multi-tenancy needs. :)


> 3:
> Create a separate table for each User? (result: 5000 users x 200 Tables =
> 1,000,000 tables)
> 4:
> Create a database per user? (result: 5000 databases)
> 5:
> Is each user a separate schema? (result: 5000 schemas)
>
> Can you share your ideas with me?
> Thank you.
>

Best regards.
Samed YILDIRIM

Performance

[Mehmet COKCEVIK]

Hi,
We want to work with PostgreSQL in our new project. I need your opinion on
the best way to create a database.

Description of our Project:
It will be in Client/Server Architecture. Windows Application users will
access the server as clients and they are all in different locations. There
will be a simple ERP system that will perform CRUD transactions and report
them.
We are considering connecting to the Embarcadero Firedac dataset. We can
also connect clients with PosgreRestAPI.
Our number of clients can be between 5k-20K.
We have a maximum of 200 tables consisting of invoice, order, customer,
bank and stock information. I can create a second Postgre SQL for reporting
if necessary.


Question 1 :
Should we install PostgreSQL on Windows server operating system or Linux
operating system?
2:
Is it correct to open a field named client_id for each table, for example
the customer table, and use this field in CRUD operations to host the same
single customer table for all users?
3:
Create a separate table for each User? (result: 5000 users x 200 Tables =
1,000,000 tables)
4:
Create a database per user? (result: 5000 databases)
5:
Is each user a separate schema? (result: 5000 schemas)

Can you share your ideas with me?
Thank you.

Re: [EXTERNAL] Need help with performance tuning pg12 on linux

[Matheus de Oliveira]

On Wed, Dec 27, 2023 at 2:23 PM Ranier Vilela  wrote:

> ...
>
> Although granule_file has an index as a foreign key, it seems to me that
> it is not being considered.
>

You seem to be mistaken here, a foreign key does not automatically create
an index on the columns, you need to do it by yourself you really want that.

Best regards,
-- 
Matheus de Oliveira

Re: [EXTERNAL] Need help with performance tuning pg12 on linux

[Matheus de Oliveira]

On Wed, Dec 27, 2023 at 2:11 PM Wilson, Maria Louise (LARC-E301)[RSES] <
m.l.wil...@nasa.gov> wrote:

> Thanks for the reply!!  Having some issues due to nulls….  Any other
> thoughts?
>
>
>
> i=# ALTER TABLE granule_file ADD PRIMARY KEY (granule_uuid, file_id);
>
> ERROR:  column "granule_uuid" contains null values
>
>
>

Seems like an odd design for your table. Check if those rows with null
value make any sense for your design.

In any case, for performance, you can try a plain index:

CREATE INDEX ON granule_file (granule_uuid);

Since you are filtering for granule_uuid first, an index starting with this
column seems to make more sense (that is why I made the PK starting with it
before).

A composite index is not really necessary, but could help if you get an
index-only scan, if you wanna try:

CREATE INDEX ON granule_file (granule_uuid, file_id);

Best regards,
-- 
Matheus de Oliveira

Re: [EXTERNAL] Need help with performance tuning pg12 on linux

[Wilson, Maria Louise (LARC-E301)[RSES]]

tent,range_date_times,0,beginning_date_time}'::text[]) > 
'2015-10-06T23:59:59+00:00'::text) OR ((properties #>> 
'{temporal_extent,single_date_times,0}

'::text[]) > '2015-10-06T23:59:59+00:00'::text) OR ((properties #>> 
'{temporal_extent,periodic_date_times,0,start_date}'::text[]) > 
'2015-10-06T23:59:59+00:00'::text)) AND (((properties #>> 
'{temporal_extent,range_date_times,0,end_date_time}'::text[]) < '

2015-10-09T00:00:00+00:00'::text) OR ((properties #>> 
'{temporal_extent,single_date_times,0}'::text[]) < 
'2015-10-09T00:00:00+00:00'::text) OR ((properties #>> 
'{temporal_extent,periodic_date_times,0,end_date}'::text[]) < 
'2015-10-09T00:00:00+00:00'::text

)))

   Rows Removed by Filter: 243

   Buffers: shared hit=989

   ->  Index Scan using collection_pkey on collection 
collection_1  (cost=0.28..6.14 rows=1 width=56) (actual time=0.008..0.008 
rows=1 loops=4)

 Index Cond: (id = granule.collection_id)

 Buffers: shared hit=12

 ->  Hash  (cost=1.52..1.52 rows=52 width=16) (actual time=0.045..0.045 
rows=52 loops=1)

   Buckets: 1024  Batches: 1  Memory Usage: 11kB

   Buffers: shared hit=1

   ->  Seq Scan on visibility visibility_1  (cost=0.00..1.52 
rows=52 width=16) (actual time=0.026..0.029 rows=52 loops=1)

 Buffers: shared hit=1

 Planning Time: 7.354 ms

 Execution Time: 64789.927 ms

(52 rows)


From: Ranier Vilela 
Date: Wednesday, December 27, 2023 at 12:23 PM
To: "Wilson, Maria Louise (LARC-E301)[RSES]" 
Cc: Matheus de Oliveira , Frits Hoogland 
, "pgsql-performance@lists.postgresql.org" 

Subject: Re: [EXTERNAL] Need help with performance tuning pg12 on linux

CAUTION: This email originated from outside of NASA.  Please take care when 
clicking links or opening attachments.  Use the "Report Message" button to 
report suspicious messages to the NASA SOC.


Em qua., 27 de dez. de 2023 às 14:11, Wilson, Maria Louise (LARC-E301)[RSES] 
mailto:m.l.wil...@nasa.gov>> escreveu:
Thanks for the reply!!  Having some issues due to nulls….  Any other thoughts?


i=# ALTER TABLE granule_file ADD PRIMARY KEY (granule_uuid, file_id);

ERROR:  column "granule_uuid" contains null values
Well, uuid is a bad datatype for primary keys.
If possible in the long run, consider replacing them with bigint.

Can you try a index:
CREATE INDEX granule_file_file_id_key ON granule_file USING btree(file_id);

Although granule_file has an index as a foreign key, it seems to me that it is 
not being considered.

My 2cents.

Best regards,
Ranier Vilela

Re: [EXTERNAL] Need help with performance tuning pg12 on linux

[Ranier Vilela]

Em qua., 27 de dez. de 2023 às 14:11, Wilson, Maria Louise
(LARC-E301)[RSES]  escreveu:

> Thanks for the reply!!  Having some issues due to nulls….  Any other
> thoughts?
>
>
>
> i=# ALTER TABLE granule_file ADD PRIMARY KEY (granule_uuid, file_id);
>
> ERROR:  column "granule_uuid" contains null values
>
Well, uuid is a bad datatype for primary keys.
If possible in the long run, consider replacing them with bigint.

Can you try a index:
CREATE INDEX granule_file_file_id_key ON granule_file USING btree(file_id);

Although granule_file has an index as a foreign key, it seems to me that it
is not being considered.

My 2cents.

Best regards,
Ranier Vilela

Re: [EXTERNAL] Need help with performance tuning pg12 on linux

[Wilson, Maria Louise (LARC-E301)[RSES]]

Thanks for the reply!!  Having some issues due to nulls….  Any other thoughts?


i=# ALTER TABLE granule_file ADD PRIMARY KEY (granule_uuid, file_id);

ERROR:  column "granule_uuid" contains null values


From: Matheus de Oliveira 
Date: Wednesday, December 27, 2023 at 11:36 AM
To: "Wilson, Maria Louise (LARC-E301)[RSES]" 
Cc: Frits Hoogland , 
"pgsql-performance@lists.postgresql.org" 

Subject: Re: [EXTERNAL] Need help with performance tuning pg12 on linux

CAUTION: This email originated from outside of NASA.  Please take care when 
clicking links or opening attachments.  Use the "Report Message" button to 
report suspicious messages to the NASA SOC.


   ->  Hash Join  (cost=644250.54..10734700.30 rows=22333224 
width=223) (actual time=7864.023..44546.392 rows=22325462 loops=1)
 Hash Cond: (file_1.id<http://file_1.id/> = 
granule_file_1.file_id)
 Buffers: shared hit=780882 read=8345236
 ->  Seq Scan on file file_1  (cost=0.00..9205050.88 
rows=2206 width=207) (actual time=402.706..25222.525 rows=22057988 loops=1)
   Buffers: shared hit=639126 read=8345236
 ->  Hash  (cost=365085.24..365085.24 rows=22333224 
width=20) (actual time=7288.228..7288.235 rows=22325462 loops=1)
   Buckets: 33554432  Batches: 1  Memory Usage: 
1391822kB
   Buffers: shared hit=141753
   ->  Seq Scan on granule_file granule_file_1  
(cost=0.00..365085.24 rows=22333224 width=20) (actual time=0.030..2151.380 
rows=22325462 loops=1)
 Buffers: shared hit=141753

This part above is the most expensive so far, and taking a look at your 
`granule_file` table on the first message, it has no indexes nor constraints, 
which certainly looks like a mistake. I'd start optimizing this, you could add 
an index on it, but seems that you need a primary key on both columns of this 
(junction?) table:

ALTER TABLE granule_file ADD PRIMARY KEY (granule_uuid, file_id);

There are certainly more things to optimize on this query, but I prefer doing 
one thing at a time. Could you try with the PK and send the EXPLAIN ANALYZE of 
the query again after that?

Best regards,
Matheus de Oliveira

Re: [EXTERNAL] Need help with performance tuning pg12 on linux

[Matheus de Oliveira]

>
>->  Hash Join  (cost=644250.54..10734700.30 rows=22333224
> width=223) (actual time=7864.023..44546.392 rows=22325462 loops=1)
>  Hash Cond: (file_1.id = granule_file_1.file_id)
>  Buffers: shared hit=780882 read=8345236
>  ->  Seq Scan on file file_1  (cost=0.00..9205050.88
> rows=2206 width=207) (actual time=402.706..25222.525 rows=22057988
> loops=1)
>Buffers: shared hit=639126 read=8345236
>  ->  Hash  (cost=365085.24..365085.24 rows=22333224
> width=20) (actual time=7288.228..7288.235 rows=22325462 loops=1)
>Buckets: 33554432  Batches: 1  Memory Usage:
> 1391822kB
>Buffers: shared hit=141753
>->  Seq Scan on granule_file granule_file_1  
> (cost=0.00..365085.24
> rows=22333224 width=20) (actual time=0.030..2151.380 rows=22325462 loops=1)
>  Buffers: shared hit=141753


This part above is the most expensive so far, and taking a look at your
`granule_file` table on the first message, it has no indexes nor
constraints, which certainly looks like a mistake. I'd start optimizing
this, you could add an index on it, but seems that you need a primary key
on both columns of this (junction?) table:

ALTER TABLE granule_file ADD PRIMARY KEY (granule_uuid, file_id);

There are certainly more things to optimize on this query, but I prefer
doing one thing at a time. Could you try with the PK and send the EXPLAIN
ANALYZE of the query again after that?

Best regards,
Matheus de Oliveira

Re: Need help with performance tuning pg12 on linux

[Frits Hoogland]

Hi Maria, could you please run explain analyse for the problem query?
The ‘analyze’ addition will track actual spent time and show statistics to 
validate the planner’s assumptions.

Frits Hoogland




> On 27 Dec 2023, at 16:38, Wilson, Maria Louise (LARC-E301)[RSES] 
>  wrote:
> 
> Hello folks!
>  
> I am having a complex query slowing over time increasing in duration.  If 
> anyone has a few cycles that they could lend a hand or just point me in the 
> right direction with this – I would surely appreciate it!  Fairly beefy Linux 
> server with Postgres 12 (latest) – this particular query has been getting 
> slower over time & seemingly slowing everything else down.  The server is 
> dedicated entirely to this particular database.  Let me know if I can provide 
> any additional information!!  Thanks in advance!
>  
> Here’s my background – Linux RHEL 8 – PostgreSQL 12.17.  – 
> MemTotal:   263216840 kB
> MemFree: 3728224 kB
> MemAvailable:   197186864 kB
> Buffers:6704 kB
> Cached: 204995024 kB
> SwapCached:19244 kB
>  
> free -m
>   totalusedfree  shared  buff/cache   
> available
> Mem: 257047   518603722   10718  201464  
> 192644
> Swap:  4095 8553240
>  
> Here are a few of the settings in our postgres server:
> max_connections = 300   # (change requires restart)
> shared_buffers = 10GB
> temp_buffers = 24MB
> work_mem = 2GB
> maintenance_work_mem = 1GB
>  
> most everything else is set to the default.
>  
> The query is complex with several joins:
>  
> SELECT anon_1.granule_collection_id AS anon_1_granule_collection_id, 
> anon_1.granule_create_date AS anon_1_granule_create_date, 
> anon_1.granule_delete_date AS anon_1_granule_delete_date, 
> ST_AsGeoJSON(anon_1.granule_geography) AS anon_1_granule_geography, 
> ST_AsGeoJSON(anon_1.granule_geometry) AS anon_1_granule_geometry, 
> anon_1.granule_is_active AS anon_1_granule_is_active, 
> anon_1.granule_properties AS anon_1_granule_properties, 
> anon_1.granule_update_date AS anon_1_granule_update_date, anon_1.granule_uuid 
> AS anon_1_granule_uuid, anon_1.granule_visibility_last_update_date AS 
> anon_1_granule_visibility_last_update_date, anon_1.granule_visibility_id AS 
> anon_1_granule_visibility_id, collection_1.id  AS 
> collection_1_id, collection_1.entry_id AS collection_1_entry_id, 
> collection_1.short_name AS collection_1_short_name, collection_1.version AS 
> collection_1_version, file_1.id  AS file_1_id, 
> file_1.location AS file_1_location, file_1.md5 AS file_1_md5, file_1.name AS 
> file_1_name, file_1.size AS file_1_size, file_1.type AS file_1_type, 
> visibility_1.id  AS visibility_1_id, 
> visibility_1.name AS visibility_1_name, visibility_1.value AS 
> visibility_1_value
> FROM (SELECT granule.collection_id AS granule_collection_id, 
> granule.create_date AS granule_create_date, granule.delete_date AS 
> granule_delete_date, granule.geography AS granule_geography, granule.geometry 
> AS granule_geometry, granule.is_active AS granule_is_active, 
> granule.properties AS granule_properties, granule.update_date AS 
> granule_update_date, granule.uuid AS granule_uuid, 
> granule.visibility_last_update_date AS granule_visibility_last_update_date, 
> granule.visibility_id AS granule_visibility_id
> FROM granule JOIN collection ON collection.id  
> = granule.collection_id
> WHERE granule.is_active = true AND (collection.entry_id LIKE 
> 'AJAX_CO2_CH4_1' OR collection.entry_id LIKE 'AJAX_O3_1' OR 
> collection.entry_id LIKE 'AJAX_CH2O_1' OR collection.entry_id LIKE 
> 'AJAX_MMS_1') AND ((granule.properties #>> '{temporal_extent, 
> range_date_times, 0, beginning_date_time}') > '2015-10-06T23:59:59+00:00' OR 
> (granule.properties #>> '{temporal_extent, single_date_times, 0}') > 
> '2015-10-06T23:59:59+00:00' OR (granule.properties #>> '{temporal_extent, 
> periodic_date_times, 0, start_date}') > '2015-10-06T23:59:59+00:00') AND 
> ((granule.properties #>> '{temporal_extent, range_date_times, 0, 
> end_date_time}') < '2015-10-09T00:00:00+00:00' OR (granule.properties #>> 
> '{temporal_extent, single_date_times, 0}') < '2015-10-09T00:00:00+00:00' OR 
> (granule.properties #>> '{temporal_extent, periodic_date_times, 0, 
> end_date}') < '2015-10-09T00:00:00+00:00') ORDER BY granule.uuid
>  LIMIT 26) AS anon_1 LEFT OUTER JOIN collection AS collection_1 ON 
> collection_1.id  = anon_1.granule_collection_id LEFT 
> OUTER JOIN (granule_file AS granule_file_1 JOIN file AS file_1 ON file_1.id 
>  = granule_file_1.file_id) ON anon_1.granule_uuid = 
> granule_file_1.granule_uuid LEFT OUTER JOIN visibility AS visibility_1 ON 
> visibility_1.id  = anon_1.granule_visibility_id 
> ORDER BY anon_1.granule_uuid
>  
> Here’s 

Need help with performance tuning pg12 on linux

[Wilson, Maria Louise (LARC-E301)[RSES]]

Hello folks!

I am having a complex query slowing over time increasing in duration.  If 
anyone has a few cycles that they could lend a hand or just point me in the 
right direction with this – I would surely appreciate it!  Fairly beefy Linux 
server with Postgres 12 (latest) – this particular query has been getting 
slower over time & seemingly slowing everything else down.  The server is 
dedicated entirely to this particular database.  Let me know if I can provide 
any additional information!!  Thanks in advance!

Here’s my background – Linux RHEL 8 – PostgreSQL 12.17.  –

MemTotal:   263216840 kB

MemFree: 3728224 kB

MemAvailable:   197186864 kB

Buffers:6704 kB

Cached: 204995024 kB

SwapCached:19244 kB


free -m

  totalusedfree  shared  buff/cache   available

Mem: 257047   518603722   10718  201464  192644

Swap:  4095 8553240

Here are a few of the settings in our postgres server:

max_connections = 300   # (change requires restart)

shared_buffers = 10GB

temp_buffers = 24MB

work_mem = 2GB

maintenance_work_mem = 1GB

most everything else is set to the default.

The query is complex with several joins:


SELECT anon_1.granule_collection_id AS anon_1_granule_collection_id, 
anon_1.granule_create_date AS anon_1_granule_create_date, 
anon_1.granule_delete_date AS anon_1_granule_delete_date, 
ST_AsGeoJSON(anon_1.granule_geography) AS anon_1_granule_geography, 
ST_AsGeoJSON(anon_1.granule_geometry) AS anon_1_granule_geometry, 
anon_1.granule_is_active AS anon_1_granule_is_active, anon_1.granule_properties 
AS anon_1_granule_properties, anon_1.granule_update_date AS 
anon_1_granule_update_date, anon_1.granule_uuid AS anon_1_granule_uuid, 
anon_1.granule_visibility_last_update_date AS 
anon_1_granule_visibility_last_update_date, anon_1.granule_visibility_id AS 
anon_1_granule_visibility_id, collection_1.id AS collection_1_id, 
collection_1.entry_id AS collection_1_entry_id, collection_1.short_name AS 
collection_1_short_name, collection_1.version AS collection_1_version, 
file_1.id AS file_1_id, file_1.location AS file_1_location, file_1.md5 AS 
file_1_md5, file_1.name AS file_1_name, file_1.size AS file_1_size, file_1.type 
AS file_1_type, visibility_1.id AS visibility_1_id, visibility_1.name AS 
visibility_1_name, visibility_1.value AS visibility_1_value

FROM (SELECT granule.collection_id AS granule_collection_id, 
granule.create_date AS granule_create_date, granule.delete_date AS 
granule_delete_date, granule.geography AS granule_geography, granule.geometry 
AS granule_geometry, granule.is_active AS granule_is_active, granule.properties 
AS granule_properties, granule.update_date AS granule_update_date, granule.uuid 
AS granule_uuid, granule.visibility_last_update_date AS 
granule_visibility_last_update_date, granule.visibility_id AS 
granule_visibility_id

FROM granule JOIN collection ON collection.id = granule.collection_id

WHERE granule.is_active = true AND (collection.entry_id LIKE 
'AJAX_CO2_CH4_1' OR collection.entry_id LIKE 'AJAX_O3_1' OR collection.entry_id 
LIKE 'AJAX_CH2O_1' OR collection.entry_id LIKE 'AJAX_MMS_1') AND 
((granule.properties #>> '{temporal_extent, range_date_times, 0, 
beginning_date_time}') > '2015-10-06T23:59:59+00:00' OR (granule.properties #>> 
'{temporal_extent, single_date_times, 0}') > '2015-10-06T23:59:59+00:00' OR 
(granule.properties #>> '{temporal_extent, periodic_date_times, 0, 
start_date}') > '2015-10-06T23:59:59+00:00') AND ((granule.properties #>> 
'{temporal_extent, range_date_times, 0, end_date_time}') < 
'2015-10-09T00:00:00+00:00' OR (granule.properties #>> '{temporal_extent, 
single_date_times, 0}') < '2015-10-09T00:00:00+00:00' OR (granule.properties 
#>> '{temporal_extent, periodic_date_times, 0, end_date}') < 
'2015-10-09T00:00:00+00:00') ORDER BY granule.uuid

 LIMIT 26) AS anon_1 LEFT OUTER JOIN collection AS collection_1 ON 
collection_1.id = anon_1.granule_collection_id LEFT OUTER JOIN (granule_file AS 
granule_file_1 JOIN file AS file_1 ON file_1.id = granule_file_1.file_id) ON 
anon_1.granule_uuid = granule_file_1.granule_uuid LEFT OUTER JOIN visibility AS 
visibility_1 ON visibility_1.id = anon_1.granule_visibility_id ORDER BY 
anon_1.granule_uuid

Here’s the explain:


 Sort  (cost=10914809.92..10914810.27 rows=141 width=996)

   Sort Key: granule.uuid

   ->  Hash Left Join  (cost=740539.73..10914804.89 rows=141 width=996)

 Hash Cond: (granule.visibility_id = visibility_1.id)

 ->  Hash Right Join  (cost=740537.56..10914731.81 rows=141 width=1725)

   Hash Cond: (granule_file_1.granule_uuid = granule.uuid)

   ->  Hash Join  (cost=644236.90..10734681.93 rows=22332751 
width=223)

 Hash Cond: (file_1.id = granule_file_1.file_id)

 ->  Seq Scan on file file_1  (cost=0.00..9205050.88 

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

[Tom Lane]

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

PG 11 is out of support as of earlier this month, so your users really
need to be prioritizing getting onto more modern versions.

regards, tom lane

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

[Jean-Christophe Boggio]

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 clients with older versions >> of 
PG and I guess blindly adding the "materialized" keyword will >> cause 
errors. > yeah, prior to 11 CTEs are a optimizer barrier. You can try to 
> rewrite the queries to not using CTEs - or upgrade. If i were you i > 
would upgrade.
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 problem.


Best regards,

JC

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

[Andreas Kretschmer]

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 "materialized" keyword will cause 
errors.




yeah, prior to 11 CTEs are a optimizer barrier. You can try to rewrite 
the queries to not using CTEs - or upgrade. If i were you i would upgrade.



Regards, Andreas

--
Andreas Kretschmer - currently still (garden leave)
Technical Account Manager (TAM)
www.enterprisedb.com

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

[Jean-Christophe Boggio]

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 situations. Before proceeding > 
further, please perform a plain vacuum on the DB. After that, check > if 
there are still problems with your queries.
Did both VACUUM ANALYZE and VACUUM (which one did you recommend 
exactly?) and things go much faster now, thanks a lot. I will also check 
why autovacuum did not do its job.


Is there anything I can do to prevent that kind of behaviour ? I'm  >> a little afraid to have to review all the queries in my softwares 
>> to keep good performances with PG 15 ? Maybe there's a way to >> 
configure the server so that CTEs are materialized by default ? > > 
There is no such a way. It would be surely be useful for some users > to 
have a way to slowly migrate query plans to new planner versions, > but 
that's not how it works today.
Thanks for your input so I know I did not miss a parameter. And yes, 
that would be handy.


Best regards,

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

[John Naylor]

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 removes the visibility map, which
is necessary to allow index-only scans. Plain vacuum should always be
used except for certain dire situations. Before proceeding further,
please perform a plain vacuum on the DB. After that, check if there
are still problems with your queries.

> 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 "materialized" keyword will cause errors.

Yes, meaning 11 and earlier don't recognize that keyword keyword.

> Is there anything I can do to prevent that kind of behaviour ? I'm a
> little afraid to have to review all the queries in my softwares to keep
> good performances with PG 15 ? Maybe there's a way to configure the
> server so that CTEs are materialized by default ?

There is no such a way. It would be surely be useful for some users to
have a way to slowly migrate query plans to new planner versions, but
that's not how it works today.

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

[Jean-Christophe Boggio]

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.


I have a relatively simple query that used to be fast and is now taking 
very long (from less than 10 seconds to 3mn+)


If I remove a WHERE condition changes the calculation time dramatically. 
The result is not exactly the same but that extra filtering seems very 
long...


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 "materialized" keyword will cause errors.


Is there anything I can do to prevent that kind of behaviour ? I'm a 
little afraid to have to review all the queries in my softwares to keep 
good performances with PG 15 ? Maybe there's a way to configure the 
server so that CTEs are materialized by default ? Not ideal but I could 
slowly refine queries to enforce "not materialized" and benefit from the 
improvement without affecting all our users.


Thanks for your inputs.

JC


Here is the query:

explain (analyze,buffers)
WITH myselect AS (
 SELECT DISTINCT og.idoeu
 FROM oegroupes og
 WHERE (og.idgroupe = 4470)
)
   , withcwrack0 AS (
    SELECT idoeu, idthirdparty, ackcode
    FROM (
 SELECT imd.idoeu,
    imd.idthirdparty,
    imd.ackcode,
    RANK() OVER (PARTITION BY imd.idoeu, 
imd.idthirdparty ORDER BY imd.idimport DESC) AS rang

 FROM importdetails imd
 WHERE imd.ackcode NOT IN ('RA', '')
    ) x
    WHERE x.rang = 1
)
   , withcwrack AS (
   SELECT idoeu,
  STRING_AGG(DISTINCT tp.nom, ', ' ORDER BY 
tp.nom) FILTER (WHERE ackcode IN ('AS', 'AC', 'NP', 'DU')) AS cwrackok,
  STRING_AGG(DISTINCT tp.nom, ', ' ORDER BY 
tp.nom) FILTER (WHERE ackcode IN ('CO', 'RJ', 'RC')) AS cwracknotok

   FROM withcwrack0
   JOIN thirdparty tp USING (idthirdparty)
   GROUP BY idoeu
)
SELECT DISTINCT og.idoegroupe,
    og.idoeu,
    o.titrelong,
    o.created,
    o.datedepotsacem,
    s.nom AS companyname,
    na.aggname AS actorsnames,
    COALESCE(TRIM(o.repnom1), '') || COALESCE(' / ' || 
TRIM(o.repnom2), '') ||
    COALESCE(' / ' || TRIM(o.repnom3), '') AS 
actorsnamesinfosrepart,

    o.cocv AS favcode,
    o.contrattiredufilm,
    o.interprete,
    o.codecocv,
    o.idsociete,
    o.idimport,
    o.donotexport,
    o.observations,
    withcwrack.cwracknotok AS cwracknotok,
    withcwrack.cwrackok AS cwrackok,
    oghl.idgroupe IS NOT NULL AS list_highlight1
FROM oegroupes og
JOIN myselect ON myselect.idoeu = og.idoeu
JOIN oeu o ON o.idoeu = og.idoeu
LEFT JOIN societes s ON s.idsociete = o.idsociete
LEFT JOIN nomsad na ON na.idoeu = o.idoeu
LEFT JOIN withcwrack ON withcwrack.idoeu = o.idoeu
LEFT JOIN oegroupes oghl ON o.idoeu = oghl.idoeu AND oghl.idgroupe = NULL

-- Commenting out the following line makes the query fast :

    WHERE (og.idgroupe=4470)





Fast version (without the final where) :

Unique  (cost=.76..8906.76 rows=360 width=273) (actual 
time=343.424..345.687 rows=3004 loops=1)

  Buffers: shared hit=26366
  ->  Sort  (cost=.76..8889.66 rows=360 width=273) (actual 
time=343.422..343.742 rows=3004 loops=1)
    Sort Key: og.idoegroupe, og.idoeu, o.titrelong, o.created, 
o.datedepotsacem, s.nom, na.aggname, (((COALESCE(TRIM(BOTH FROM 
o.repnom1), ''::text) || COALESCE((' / '::text || TRIM(BOTH FROM 
o.repnom2)), ''::text)) || COALESCE((' / '::text || TRIM(BOTH FROM 
o.repnom3)), ''::text))), o.cocv, o.contrattiredufilm, o.interprete, 
(codecocv(o.*)), o.idsociete, o.idimport, o.donotexport, o.observations, 
(string_agg(DISTINCT (tp.nom)::text, ', '::text ORDER BY (tp.nom)::text) 
FILTER (WHERE ((x.ackcode)::text = ANY ('{CO,RJ,RC}'::text[], 
(string_agg(DISTINCT (tp.nom)::text, ', '::text ORDER BY (tp.nom)::text) 
FILTER (WHERE ((x.ackcode)::text = ANY ('{AS,AC,NP,DU}'::text[], 
((idgroupe IS NOT NULL))

    Sort Method: quicksort  Memory: 524kB
    Buffers: shared hit=26366
    ->  Nested Loop Left Join  (cost=6811.39..8873.48 rows=360 
width=273) (actual time=291.636..340.755 rows=3004 loops=1)

  Join Filter: false
  Buffers: shared hit=26355
  ->  Nested Loop  (cost=6811.39..8773.58 rows=360 
width=2964) (actual time=290.747..301.506 rows=3004 loops=1)

    Join Filter: 

RE: [EXTERNAL] Performance down with JDBC 42

[Abraham, Danny]

Hi guys,
Thanks for the help.
I was able to recreate the problem , on the same DB, with PSQL only. No JDBC.

A plain run of a complicated query :  50ms
A prepare and then execute of the same query:   2500ms.

The plans are different, as discussed above. The fast one is using Materialize 
and Memoize.

Thanks

Danny

Re: Performance problems with Postgres JDBC 42.4.2

[Dave Cramer]

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 more than 10million rows each and I have two
> questions here:
>
> 1. I need to extract the path of a file without the file itself. For this
> I use two alternatives as I found that sentence "A" is much faster than
> the "B" one:
>
> "A" sentence:
>
> SELECT DISTINCT ( LEFT(opf.file_path, length(opf.file_path) - position('/'
> in reverse(opf.file_path))) ) AS path
>FROM product AS op JOIN product_file AS opf ON
> opf.product_id = op.id
>WHERE op.proprietary_end_date <= CURRENT_DATE
> AND op.id LIKE 'urn:esa:psa:%'
>
> "B" sentence:
>
> SELECT DISTINCT ( regexp_replace(opf.file_path, '(.*)\/(.*)$', '\1') ) AS
> path
>FROM product AS op JOIN product_file AS opf ON
> opf.product_id = op.id
>WHERE op.proprietary_end_date <= CURRENT_DATE
> AND op.id LIKE 'urn:esa:psa:%'
>
> 2. Running sentence "A" on the pgAdmin client takes 4-5 minutes to finish
> but running it from a Java program it never ends. This is still the case
> when I limit the output to the first 100 rows so I assume this is not a
> problem with the amount of data being transferred but the way postgres
> resolve the query. To make it work in Java I had to define a postgres
> function that I call from the Java code instead of running the query
> directly.
>
> I had a similar problem in the past with a query that performed very
> poorly from a Java client while it was fine from pgAdmin or a python
> script. In that case it was a matter of column types not compatible with
> the JDBC (citext) deriving in an implicit cast that prevented the
> postgres engine from using a given index or to cast all the values of that
> column before using it, not sure now. But I don't think this is not the
> case here.
>
> Could anyone help me again?
>

Can you share your java code ?

If you are using a PreparedStatement the driver will use the extended
protocol which may be slower. Statements use SimpleQuery which is faster
and more like pgadmin

Issuing a Query and Processing the Result | pgJDBC (postgresql.org)



Dave

>
>

Performance problems with Postgres JDBC 42.4.2

[Jose Osinde]

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 more than 10million rows each and I have two
questions here:

1. I need to extract the path of a file without the file itself. For this I
use two alternatives as I found that sentence "A" is much faster than the
"B" one:

"A" sentence:

SELECT DISTINCT ( LEFT(opf.file_path, length(opf.file_path) - position('/'
in reverse(opf.file_path))) ) AS path
   FROM product AS op JOIN product_file AS opf ON
opf.product_id = op.id
   WHERE op.proprietary_end_date <= CURRENT_DATE
AND op.id LIKE 'urn:esa:psa:%'

"B" sentence:

SELECT DISTINCT ( regexp_replace(opf.file_path, '(.*)\/(.*)$', '\1') ) AS
path
   FROM product AS op JOIN product_file AS opf ON
opf.product_id = op.id
   WHERE op.proprietary_end_date <= CURRENT_DATE
AND op.id LIKE 'urn:esa:psa:%'

2. Running sentence "A" on the pgAdmin client takes 4-5 minutes to finish
but running it from a Java program it never ends. This is still the case
when I limit the output to the first 100 rows so I assume this is not a
problem with the amount of data being transferred but the way postgres
resolve the query. To make it work in Java I had to define a postgres
function that I call from the Java code instead of running the query
directly.

I had a similar problem in the past with a query that performed very poorly
from a Java client while it was fine from pgAdmin or a python script. In
that case it was a matter of column types not compatible with the JDBC (citext)
deriving in an implicit cast that prevented the postgres engine from using
a given index or to cast all the values of that column before using it, not
sure now. But I don't think this is not the case here.

Could anyone help me again?

Many thanks in advance
Jose

Re: [EXTERNAL] Performance down with JDBC 42

[Frits Hoogland]

Very good point from Danny: generic and custom plans.

One thing that is almost certainly not at play here, and is mentioned: there 
are some specific cases where the planner does not optimise for the query in 
total to be executed as fast/cheap as possible, but for the first few rows. One 
reason for that to happen is if a query is used as a cursor.

(Warning: shameless promotion) I did a writeup on JDBC clientside/serverside 
prepared statements and custom and generic plans: 
https://dev.to/yugabyte/postgres-query-execution-jdbc-prepared-statements-51e2
The next obvious question then is if something material did change with JDBC 
for your old and new JDBC versions, I do believe the prepareThreshold did not 
change.


Frits Hoogland




> On 5 Nov 2023, at 20:47, David Rowley  wrote:
> 
> 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
> that's planned specifically using the parameter values given.
> 
>> Slow – when using JDBC 42
> 
> Because this query has $1, $2, etc, that's a generic plan. When
> looking up statistics histogram bounds and MCV slots cannot be
> checked. Only ndistinct is used. If you have a skewed dataset, then
> this might not be very good.
> 
> You might find things run better if you adjust postgresql.conf and set
> plan_cache_mode = force_custom_plan then select pg_reload_conf();
> 
> Please also check the documentation so that you understand the full
> implications for that.
> 
> David
> 
> 

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

[David Rowley]

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
that's planned specifically using the parameter values given.

> Slow – when using JDBC 42

Because this query has $1, $2, etc, that's a generic plan. When
looking up statistics histogram bounds and MCV slots cannot be
checked. Only ndistinct is used. If you have a skewed dataset, then
this might not be very good.

You might find things run better if you adjust postgresql.conf and set
plan_cache_mode = force_custom_plan then select pg_reload_conf();

Please also check the documentation so that you understand the full
implications for that.

David

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

[Abraham, Danny]

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  (cost=13112.24..13112.48 rows=24 width=622)
 Output: dm.calname, dm.jobyear, dm.caltype, ((dm.daymask)::character 
varying(400))
 Group Key: dm.calname, dm.jobyear, dm.caltype, 
((dm.daymask)::character varying(400))
 ->  Append  (cost=4603.96..13112.00 rows=24 width=622)
   ->  Unique  (cost=4603.96..4604.20 rows=19 width=535)
 Output: dm.calname, dm.jobyear, dm.caltype, 
((dm.daymask)::character varying(400))
 ->  Sort  (cost=4603.96..4604.01 rows=19 width=535)
   Output: dm.calname, dm.jobyear, dm.caltype, 
((dm.daymask)::character varying(400))
   Sort Key: dm.calname, dm.jobyear, dm.caltype, 
((dm.daymask)::character varying(400))
   ->  Nested Loop  (cost=0.00..4603.56 rows=19 
width=535)
 Output: dm.calname, dm.jobyear, dm.caltype, 
(dm.daymask)::character varying(400)
 Join Filter: (((dm.calname)::text = 
(jd.dayscal)::text) OR ((dm.calname)::text = (jd.weekcal)::text) OR 
((dm.calname)::text = (jd.confcal)::text))
 ->  Seq Scan on public.cms_datemm dm  
(cost=0.00..16.33 rows=171 width=389)
   Output: dm.calname, dm.jobyear, 
dm.daymask, dm.caltype, dm.caldesc
   Filter: ((dm.jobyear >= '2021'::bpchar) 
AND (dm.jobyear <= '2025'::bpchar))
 ->  Materialize  (cost=0.00..4559.84 rows=8 
width=3)
   Output: jd.dayscal, jd.weekcal, 
jd.confcal
   ->  Seq Scan on public.cms_jobdef jd  
(cost=0.00..4559.80 rows=8 width=3)
 Output: jd.dayscal, jd.weekcal, 
jd.confcal
 Filter: (((jd.schedtab)::text = 
'PZL-ZETA_REDIS_UTILITY_PSE'::text) OR ((jd.schedtab)::text ~~ 
'PZL-ZETA_REDIS_UTILITY_PSE/%'::text))
   ->  Unique  (cost=3857.44..3857.46 rows=1 width=535)
 Output: dm_1.calname, dm_1.jobyear, dm_1.caltype, 
((dm_1.daymask)::character varying(400))
 ->  Sort  (cost=3857.44..3857.45 rows=1 width=535)
   Output: dm_1.calname, dm_1.jobyear, dm_1.caltype, 
((dm_1.daymask)::character varying(400))
   Sort Key: dm_1.calname, dm_1.jobyear, dm_1.caltype, 
((dm_1.daymask)::character varying(400))
   ->  Nested Loop  (cost=0.30..3857.43 rows=1 
width=535)
 Output: dm_1.calname, dm_1.jobyear, 
dm_1.caltype, (dm_1.daymask)::character varying(400)
 Join Filter: (((dm_1.calname)::text = 
(tag.dayscal)::text) OR ((dm_1.calname)::text = (tag.weekcal)::text) OR 
((dm_1.calname)::text = (tag.confcal)::text))
 ->  Nested Loop  (cost=0.30..3838.11 rows=1 
width=3)
   Output: tag.dayscal, tag.weekcal, 
tag.confcal
   Inner Unique: true
   ->  Seq Scan on public.cms_tag tag  
(cost=0.00..30.96 rows=1396 width=7)
 Output: tag.tagname, tag.groupid, 
tag.maxwait, tag.cal_andor, tag.monthstr, tag.dayscal, tag.weekcal, 
tag.confcal, tag.shift, tag.retro, tag.daystr, tag.wdaystr, tag.tagfrom, 
tag.tagtill, tag.roworder, tag.exclude_rbc
   ->  Memoize  (cost=0.30..4.02 rows=1 
width=4)
 Output: jd_1.jobno
 Cache Key: tag.groupid
 Cache Mode: logical
 ->  Index Scan using job on 
public.cms_jobdef jd_1  (cost=0.29..4.01 rows=1 width=4)
   Output: jd_1.jobno
   Index Cond: (jd_1.jobno = 
tag.groupid)
   Filter: 
(((jd_1.schedtab)::text = 'PZL-ZETA_REDIS_UTILITY_PSE'::text) OR 
((jd_1.schedtab)::text ~~ 'PZL-ZETA_REDIS_UTILITY_PSE/%'::text))
 ->  Seq Scan on public.cms_datemm dm_1  
(cost=0.00..16.33 rows=171 width=389)
   Output: dm_1.calname, dm_1.jobyear, 
dm_1.daymask, dm_1.caltype, dm_1.caldesc
   Filter: ((dm_1.jobyear >= 
'2021'::bpchar) AND (dm_1.jobyear <= '2025'::bpchar))
   ->  Unique  (cost=4649.93..4649.98 

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

[Jeff Janes]

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 drivers fetch
partial results is different, such that with one the planner knows to
expect only partial results to be fetched and with the other it does not.
So in one case it chooses the fast-start plan, and in the other it
doesn't.  But it will be hard to get anywhere if you just dribble
information at us a bit at a time.  Can you come up with a self-contained
test case?  Or at least show the entirety of both plans?

Cheers,

Jeff

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

[Frits Hoogland]

Are you absolutely sure that the two databases you’re comparing the executing 
with are identical, and that the objects involved in the query are physically 
and logically identical?

The planning is done based on cost/statistics of the objects. If the statistics 
are different, the planner may come up with another plan.

Frits



> Op 5 nov 2023 om 17:20 heeft Abraham, Danny  het 
> volgende geschreven:
> 
> 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?
> 
> 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 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 ms in our Server using new JDBC 42 driver. ( and  this is
>> running many times)
>> 
>> Question: Is there a structured way to identify optimization setup ( Planner 
>> Method s ) changes?
>> Are there any known changes specific to JDBC 42.
> 
> What I would do is enable auto_explain and look at the execution plan when 
> the statement is run by the JDBC driver.  Then you can compare the execution 
> plans and spot the difference.
> 
> Yours,
> Laurenz Albe

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

[Andreas Kretschmer]

Am 05.11.23 um 17:20 schrieb Abraham, Danny:

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?


*maybe* the driver changed some settings. You can check it with

select 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 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 ms in our Server using new JDBC 42 driver. ( and  this is
running many times)

Question: Is there a structured way to identify optimization setup ( Planner 
Method s ) changes?
Are there any known changes specific to JDBC 42.

What I would do is enable auto_explain and look at the execution plan when the 
statement is run by the JDBC driver.  Then you can compare the execution plans 
and spot the difference.

Yours,
Laurenz Albe


--
Andreas Kretschmer - currently still (garden leave)
Technical Account Manager (TAM)
www.enterprisedb.com

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

[Abraham, Danny]

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?

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 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 ms in our Server using new JDBC 42 driver. ( and  this is 
> running many times)
> 
> Question: Is there a structured way to identify optimization setup ( Planner 
> Method s ) changes?
> Are there any known changes specific to JDBC 42. 

What I would do is enable auto_explain and look at the execution plan when the 
statement is run by the JDBC driver.  Then you can compare the execution plans 
and spot the difference.

Yours,
Laurenz Albe

Re: Performance down with JDBC 42

[Laurenz Albe]

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 ms in our Server using new JDBC 42 driver. ( and  this is running many 
> times) 
> 
> Question: Is there a structured way to identify optimization setup ( Planner 
> Method s ) changes?
> Are there any known changes specific to JDBC 42. 

What I would do is enable auto_explain and look at the execution plan
when the statement is run by the JDBC driver.  Then you can compare the
execution plans and spot the difference.

Yours,
Laurenz Albe

Performance down with JDBC 42

[Abraham, Danny]

Hi,

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 ms in our Server using new JDBC 42 driver. ( and  this is running many 
times) 

Question: Is there a structured way to identify optimization setup ( Planner 
Method s ) changes?
Are there any known changes specific to JDBC 42. 
Capture a vector of session optimization setup?  
Any other Idea ?

Thanks

Danny

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

[Philippe Pepiot]

On 29/08/2023, David Rowley wrote:
> On Tue, 29 Aug 2023 at 19:40, Philippe Pepiot  wrote:
> > I'm trying to implement some range partitioning on timeseries data. But it
> > looks some queries involving date_trunc() doesn't make use of partitioning.
> >
> > BEGIN;
> > CREATE TABLE test (
> > time TIMESTAMP WITHOUT TIME ZONE NOT NULL,
> > value FLOAT NOT NULL
> > ) PARTITION BY RANGE (time);
> > CREATE INDEX test_time_idx ON test(time DESC);
> > CREATE TABLE test_y2010 PARTITION OF test FOR VALUES FROM ('2020-01-01') TO 
> > ('2021-01-01');
> > CREATE TABLE test_y2011 PARTITION OF test FOR VALUES FROM ('2021-01-01') TO 
> > ('2022-01-01');
> > CREATE VIEW vtest AS SELECT DATE_TRUNC('year', time) AS time, SUM(value) AS 
> > value FROM test GROUP BY 1;
> > EXPLAIN (COSTS OFF) SELECT * FROM vtest WHERE time >= TIMESTAMP 
> > '2021-01-01';
> > ROLLBACK;
> >
> > The plan query all partitions:
> 
> > I wonder if there is a way with a reasonable amount of SQL code to achieve 
> > this
> > with vanilla postgres ?
> 
> The only options I see for you are
> 
> 1) partition by LIST(date_Trunc('year', time)), or;
> 2) use a set-returning function instead of a view and pass the date
> range you want to select from the underlying table via parameters.
> 
> I imagine you won't want to do #1. However, it would at least also
> allow the aggregation to be performed before the Append if you SET
> enable_partitionwise_aggregate=1.
> 
> #2 isn't as flexible as a view as you'd have to create another
> function or expand the parameters of the existing one if you want to
> add items to the WHERE clause.
> 
> Unfortunately, date_trunc is just a black box to partition pruning, so
> it's not able to determine that DATE_TRUNC('year', time) >=
> '2021-01-01'  is the same as time >= '2021-01-01'.  It would be
> possible to make PostgreSQL do that, but that's a core code change,
> not something that you can do from SQL.

Ok I think I'll go for Set-returning function since
LIST or RANGE on (date_trunc('year', time)) will break advantage of
partitioning when querying with "time betwen x and y".

Thanks!

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

[David Rowley]

On Tue, 29 Aug 2023 at 19:40, Philippe Pepiot  wrote:
> I'm trying to implement some range partitioning on timeseries data. But it
> looks some queries involving date_trunc() doesn't make use of partitioning.
>
> BEGIN;
> CREATE TABLE test (
> time TIMESTAMP WITHOUT TIME ZONE NOT NULL,
> value FLOAT NOT NULL
> ) PARTITION BY RANGE (time);
> CREATE INDEX test_time_idx ON test(time DESC);
> CREATE TABLE test_y2010 PARTITION OF test FOR VALUES FROM ('2020-01-01') TO 
> ('2021-01-01');
> CREATE TABLE test_y2011 PARTITION OF test FOR VALUES FROM ('2021-01-01') TO 
> ('2022-01-01');
> CREATE VIEW vtest AS SELECT DATE_TRUNC('year', time) AS time, SUM(value) AS 
> value FROM test GROUP BY 1;
> EXPLAIN (COSTS OFF) SELECT * FROM vtest WHERE time >= TIMESTAMP '2021-01-01';
> ROLLBACK;
>
> The plan query all partitions:

> I wonder if there is a way with a reasonable amount of SQL code to achieve 
> this
> with vanilla postgres ?

The only options I see for you are

1) partition by LIST(date_Trunc('year', time)), or;
2) use a set-returning function instead of a view and pass the date
range you want to select from the underlying table via parameters.

I imagine you won't want to do #1. However, it would at least also
allow the aggregation to be performed before the Append if you SET
enable_partitionwise_aggregate=1.

#2 isn't as flexible as a view as you'd have to create another
function or expand the parameters of the existing one if you want to
add items to the WHERE clause.

Unfortunately, date_trunc is just a black box to partition pruning, so
it's not able to determine that DATE_TRUNC('year', time) >=
'2021-01-01'  is the same as time >= '2021-01-01'.  It would be
possible to make PostgreSQL do that, but that's a core code change,
not something that you can do from SQL.

David

Range partitioning query performance with date_trunc (vs timescaledb)

[Philippe Pepiot]

Hi,

I'm trying to implement some range partitioning on timeseries data. But it
looks some queries involving date_trunc() doesn't make use of partitioning.

BEGIN;
CREATE TABLE test (
time TIMESTAMP WITHOUT TIME ZONE NOT NULL,
value FLOAT NOT NULL
) PARTITION BY RANGE (time);
CREATE INDEX test_time_idx ON test(time DESC);
CREATE TABLE test_y2010 PARTITION OF test FOR VALUES FROM ('2020-01-01') TO 
('2021-01-01');
CREATE TABLE test_y2011 PARTITION OF test FOR VALUES FROM ('2021-01-01') TO 
('2022-01-01');
CREATE VIEW vtest AS SELECT DATE_TRUNC('year', time) AS time, SUM(value) AS 
value FROM test GROUP BY 1;
EXPLAIN (COSTS OFF) SELECT * FROM vtest WHERE time >= TIMESTAMP '2021-01-01';
ROLLBACK;

The plan query all partitions:

HashAggregate
  Group Key: (date_trunc('year'::text, test."time"))
  ->  Append
->  Seq Scan on test_y2010 test_1
  Filter: (date_trunc('year'::text, "time") >= '2021-01-01 
00:00:00'::timestamp without time zone)
->  Seq Scan on test_y2011 test_2
  Filter: (date_trunc('year'::text, "time") >= '2021-01-01 
00:00:00'::timestamp without time zone)


The view is there so show the use case, but we get almost similar plan with 
SELECT * FROM test WHERE DATE_TRUNC('year', time) >= TIMESTAMP '2021-01-01';


I tested a variation with timescaledb which seem using trigger based
partitioning:

BEGIN;
CREATE EXTENSION IF NOT EXISTS timescaledb;
CREATE TABLE test (
time TIMESTAMP WITHOUT TIME ZONE NOT NULL,
value FLOAT NOT NULL
);
SELECT create_hypertable('test', 'time', chunk_time_interval => INTERVAL '1 
year');
CREATE VIEW vtest AS SELECT time_bucket('1 year', time) AS time, SUM(value) AS 
value FROM test GROUP BY 1;
-- insert some data as partitions are created on the fly
INSERT INTO test VALUES (TIMESTAMP '2020-01-15', 1.0), (TIMESTAMP '2021-12-15', 
2.0);
\d+ test
EXPLAIN (COSTS OFF) SELECT * FROM vtest WHERE time >= TIMESTAMP '2021-01-01';
ROLLBACK;


The plan query a single partition:

GroupAggregate
  Group Key: (time_bucket('1 year'::interval, _hyper_1_2_chunk."time"))
  ->  Result
->  Index Scan Backward using _hyper_1_2_chunk_test_time_idx on 
_hyper_1_2_chunk
  Index Cond: ("time" >= '2021-01-01 00:00:00'::timestamp without 
time zone)
  Filter: (time_bucket('1 year'::interval, "time") >= '2021-01-01 
00:00:00'::timestamp without time zone)


Note single partition query only works with time_bucket(), not with 
date_trunc(), I guess
there is some magic regarding this in time_bucket() implementation.


I wonder if there is a way with a reasonable amount of SQL code to achieve this
with vanilla postgres ?

Maybe by taking assumption that DATE_TRUNC(..., time) <= time ?

Thanks!

Re: TOAST Fields serialisation/deserialization performance

[Piyush Katariya]

Thanks for the feedback. Appreciate it.

On Thu, 27 Jul, 2023, 01:09 Laurenz Albe,  wrote:

> On Wed, 2023-07-26 at 18:15 +0530, Piyush Katariya wrote:
> > I have a few queries regarding the TOAST Fields
> serialisation/deserialization performance.
> >
> > The use case i am trying to solve here is to have millions of partitions
> and aggregate the data in array field.
> >
> > I wish to know if i declare certain column in table as "array of
> UDT/JSONB" and enable
> > either lz4 or zstd compression on it, does appending or prepending to
> that array or even
> > changing the intermediate fields of UDT/JSONB objects. in that array has
> a runtime cost
> > of full array data de-serialization every single time. If i perform any
> UPDATE operation
> > on its elements or add/remove new elements from any position, does PG
> rewrites the new
> > version of the column value regardless of its size.
>
> Updating even a small part of a large JSONB value requires that the entire
> thing is
> read and written, causing a lot of data churn.
>
> This is inefficient, and you shouldn't use large JSONB values if you plan
> to do that.
>
> If the data have a regular structure, use a regular relational data model.
> Otherwise, one idea might be to split the JSONB in several parts and store
> each
> of those parts in a different table row.  That would reduce the impact.
>
> Yours,
> Laurenz Albe
>

Re: TOAST Fields serialisation/deserialization performance

[Laurenz Albe]

On Wed, 2023-07-26 at 18:15 +0530, Piyush Katariya wrote:
> I have a few queries regarding the TOAST Fields serialisation/deserialization 
> performance.
> 
> The use case i am trying to solve here is to have millions of partitions and 
> aggregate the data in array field.
> 
> I wish to know if i declare certain column in table as "array of UDT/JSONB" 
> and enable
> either lz4 or zstd compression on it, does appending or prepending to that 
> array or even
> changing the intermediate fields of UDT/JSONB objects. in that array has a 
> runtime cost
> of full array data de-serialization every single time. If i perform any 
> UPDATE operation
> on its elements or add/remove new elements from any position, does PG 
> rewrites the new
> version of the column value regardless of its size.

Updating even a small part of a large JSONB value requires that the entire 
thing is
read and written, causing a lot of data churn.

This is inefficient, and you shouldn't use large JSONB values if you plan to do 
that.

If the data have a regular structure, use a regular relational data model.
Otherwise, one idea might be to split the JSONB in several parts and store each
of those parts in a different table row.  That would reduce the impact.

Yours,
Laurenz Albe

TOAST Fields serialisation/deserialization performance

[Piyush Katariya]

Hello Gents,

I have a few queries regarding the TOAST Fields
serialisation/deserialization performance.

The use case i am trying to solve here is to have millions of partitions
and aggregate the data in array field.

I wish to know if i declare certain column in table as "array of UDT/JSONB"
and enable either lz4 or zstd compression on it, does appending or
prepending to that array or even changing the intermediate fields of
UDT/JSONB objects. in that array has a runtime cost of full array data
de-serialization every single time. If i perform any UPDATE operation on
its elements or add/remove new elements from any position, does PG rewrites
the new version of the column value regardless of its size.

Let me know if more inputs are required

-- 
*Thanks,*
*Piyush Katariya*

Re: Performance implications of 8K pread()s

[Andres Freund]

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 --with-liburing (I'm on linux) and run with
> io_method=io_uring?

It's probably worth trying out both io_uring and worker. I've not looked at
performance on btrfs. I know that some of the optimized paths for io_uring
(being able to perform filesystem IO without doing so synchronously in an
in-kernel thread) require filesystem cooperation, and I do not know how much
attention btrfs has received for that.


> Else, if I use the io_method=worker what is a sensible amount of worker
> threads?

Depends on your workload :/. If you just want to measure whether it fixes your
single-threaded query execution issue, the default should be just fine.


> Should I also set all the flags for direct I/O?  (io_data_direct=on
> io_wal_direct=on).

FWIW, I just pushed a rebased version to the aio branch, and there the config
for direct io is
io_direct = 'data, wal, wal_init'
(or a subset thereof).

>From what I know of btrfs, I don't think you want direct IO though. Possibly
for WAL, but definitely not for data. IIRC it currently can cause corruption.

Greetings,

Andres Freund

Re: Performance implications of 8K pread()s

[Dimitrios Apostolou]

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 I also set all the flags for direct I/O?
(io_data_direct=on io_wal_direct=on).

Re: Performance implications of 8K pread()s

[Thomas Munro]

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 try out, and work is
happening.  I'll try to update the wiki with some more up-to-date
information soon.  Basically, compare these two slides (you could also
look at slide 11, which is the most most people are probably
interested in, but then you can't really see what's going on with
system call-level tools):

https://speakerdeck.com/macdice/aio-and-dio-for-postgresql-on-freebsd?slide=7
https://speakerdeck.com/macdice/aio-and-dio-for-postgresql-on-freebsd?slide=9

Not only are the IOs converted into 128KB preadv() calls, they are
issued concurrently and ahead of time while your backend is chewing on
the last lot of pages.  So even if your file system completely fails
at prefetching, we'd have a fighting chance at getting closer to
device/line speed.  That's basically what you have to do to support
direct I/O, where there is no system-provided prefetching.

Re: Performance implications of 8K pread()s

[Dimitrios Apostolou]

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 that reading in bigger chunks would mitigate such setup inefficiencies.
On a system that reads are already optimal and the read rate remains the same,
then bigger block size would probably reduce the sys time postgresql consumes
because of the fewer system calls.


I don't know about btrfs but maybe it can be tuned to prefetch
sequential reads better...


I tried a lot to tweak the kernel's block layer read-ahead and to change
different I/O schedulers, but it made no difference. I'm now convinced
that the problem manifests specially on compressed btrfs: the filesystem
doesn't do any read-ahed (pre-fetch) so no I/O requests "merge" on the
block layer.

Iostat gives an interesting insight in the above measurements.  For both
postgres doing sequential scan and for dd with bs=8k, the kernel block
layer does not appear to merge the I/O requests. `iostat -x` shows 16
sectors average read request size, 0 merged requests, and very high
reads/s IOPS number.

The dd commands with bs=32k block size show fewer IOPS on `iostat -x` but
higher speed(!), larger average block size and high number of merged
requests.

Example output for some random second out of dd bs=8k:

Devicer/s rMB/s   rrqm/s  %rrqm r_await rareq-sz
sdc   1313.00 20.93 2.00   0.150.5316.32

with dd bs=32k:

Devicer/s rMB/s   rrqm/s  %rrqm r_await rareq-sz
sdc290.00 76.44  4528.00  93.981.71   269.92

On the same filesystem, doing dd bs=8k reads from a file that has not been
compressed by the filesystem I get 1GB/s device read throughput!

I sent this feedback to the btrfs list, but got no feedback yet:

https://www.spinics.net/lists/linux-btrfs/msg137200.html




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?


It is hard to implement.  But people are working on it.  One of the
problems is that the 8KB blocks that we want to read data into aren't
necessarily contiguous so you can't just do bigger pread() calls
without solving a lot more problems first.


This kind of overhaul is good, but goes much deeper. Same with async I/O
of course. But what I have in mind should be much simpler (add grains
of salt since I don't know postgres internals :-)

+ A process wants to read a block from a file
+ Postgres' buffer cache layer (shared_buffers?) looks it up in the cache,
  if not found it passes the request down to
+ postgres' block layer; it submits an I/O request for 32KB that include
  the 8K block requested; it returns the 32K block to
+ postgres' buffer cache layer; it stores all 4 blocks read from the disk
  into the buffer cache, and returns only the 1 block requested.

The danger here is that in random non-contiguous 8K reads, the buffer
cache gets satsurated by 4x the amount of data because of 32K reads, and
75% of that data is useless, but may still evict useful data. The answer
is that is should be marked as unused then (by putting it in front of the
cache's LRU for example) so that those unused read-ahead pages are re-used
for upcoming read-ahead, without evicting too much useful pages.


The project at
https://wiki.postgresql.org/wiki/AIO aims to deal with the
"clustering" you seek plus the "gathering" required for non-contiguous
buffers by allowing multiple block-sized reads to be prepared and
collected on a pending list up to some size that triggers merging and
submission to the operating system at a sensible rate, so we can build
something like a single large preadv() call.  In the current
prototype, if io_method=worker then that becomes a literal preadv()
call running in a background "io worker" process, but it could also be
OS-specific stuff (io_uring, ...) that starts an asynchronous IO
depending on settings.  If you take that branch and run your test you
should see 128KB-sized preadv() calls.



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


Thanks and regards,
Dimitris

Re: Performance implications of 8K pread()s

[Thomas Munro]

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

[Thomas Munro]

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.
> On a system that reads are already optimal and the read rate remains the same,
> then bigger block size would probably reduce the sys time postgresql consumes
> because of the fewer system calls.

I don't know about btrfs but maybe it can be tuned to prefetch
sequential reads better...

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

It is hard to implement.  But people are working on it.  One of the
problems is that the 8KB blocks that we want to read data into aren't
necessarily contiguous so you can't just do bigger pread() calls
without solving a lot more problems first.  The project at
https://wiki.postgresql.org/wiki/AIO aims to deal with the
"clustering" you seek plus the "gathering" required for non-contiguous
buffers by allowing multiple block-sized reads to be prepared and
collected on a pending list up to some size that triggers merging and
submission to the operating system at a sensible rate, so we can build
something like a single large preadv() call.  In the current
prototype, if io_method=worker then that becomes a literal preadv()
call running in a background "io worker" process, but it could also be
OS-specific stuff (io_uring, ...) that starts an asynchronous IO
depending on settings.  If you take that branch and run your test you
should see 128KB-sized preadv() calls.

Performance implications of 8K pread()s

[Dimitrios Apostolou]

Hello list,

I have noticed that the performance during a SELECT COUNT(*) command is
much slower than what the device can provide. Parallel workers improve the
situation but for simplicity's sake, I disable parallelism for my
measurements here by setting max_parallel_workers_per_gather to 0.

Strace'ing the postgresql process shows that all reads happen in offset'ed 8KB
blocks using pread():

  pread64(172, ..., 8192, 437370880) = 8192

The read rate I see on the device is only 10-20 MB/s. My case is special
though, as this is on a zstd-compressed btrfs filesystem, on a very fast
(1GB/s) direct attached storage system. Given the decompression ratio is around
10x, the above rate corresponds to about 100 to 200 MB/s of data going into the
postgres process.

Can the 8K block size cause slowdown? Here are my observations:

+ Reading a 1GB postgres file using dd (which uses read() internally) in
   8K and 32K chunks:

 # dd if=4156889.4 of=/dev/null bs=8k
 1073741824 bytes (1.1 GB, 1.0 GiB) copied, 6.18829 s, 174 MB/s

 # dd if=4156889.4 of=/dev/null bs=8k# 2nd run, data is cached
 1073741824 bytes (1.1 GB, 1.0 GiB) copied, 0.287623 s, 3.7 GB/s

 # dd if=4156889.8 of=/dev/null bs=32k
 1073741824 bytes (1.1 GB, 1.0 GiB) copied, 1.02688 s, 1.0 GB/s

 # dd if=4156889.8 of=/dev/null bs=32k# 2nd run, data is cached
 1073741824 bytes (1.1 GB, 1.0 GiB) copied, 0.264049 s, 4.1 GB/s

   The rates displayed are after decompression (the fs does it
   transparently) and the results have been verified with multiple runs.

   Notice that the read rate with bs=8k is 174MB/s (I see ~20MB/s on the
   device), slow and similar to what Postgresql gave us above. With bs=32k
   the rate increases to 1GB/s (I see ~80MB/s on the device, but the time
   is very short to register properly).

  The cached reads are fast in both cases.

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.
On a system that reads are already optimal and the read rate remains the same,
then bigger block size would probably reduce the sys time postgresql consumes
because of the fewer system calls.

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?

Regards,
Dimitris

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

[Akash Anand]

Hi,

Is there a way to visualize RLS policy check(s) in the query plan?

Regards,
Akash Anand

On Mon, Jul 10, 2023 at 11:33 AM Akash Anand  wrote:

> Hi,
>
> --
> Postgres version
> --
> postgres=# SELECT version();
>   version
>
>
> ---
>  PostgreSQL 15.3 (Debian 15.3-1.pgdg110+1) on aarch64-unknown-linux-gnu,
> compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
> (1 row)
> --
>
> --
> Load data
> --
> Chinook database
>
> https://github.com/lerocha/chinook-database/blob/master/ChinookDatabase/DataSources/Chinook_PostgreSql.sql
> --
>
> --
> Insert dummy data into Track to bring rows count to 10 million
> --
> INSERT INTO "Track"("TrackId", "Name", "AlbumId", "MediaTypeId",
> "GenreId", "Milliseconds", "Bytes", "UnitPrice")
> SELECT i::int, i::text, 1, 1, 1, 276349, 9056902, 0.99
> FROM generate_series(3504, 1000) AS t(i);
> --
>
> --
> Setup role and policies
> --
> create role "User";
> grant select on "Album" to "User";
> CREATE POLICY artist_rls_policy ON "Album" FOR SELECT TO public USING
> ("ArtistId"=((current_setting('rls.artistID'))::integer));
> ALTER TABLE "Album" ENABLE ROW LEVEL SECURITY;
> grant select on "Track" to "User";
> CREATE POLICY album_rls_policy ON "Track" FOR SELECT to public
> USING (
>   EXISTS (
> select 1 from "Album" where "Track"."AlbumId" = "Album"."AlbumId"
>   )
> );
> ALTER TABLE "Track" ENABLE ROW LEVEL SECURITY;
> --
>
> --
> Query and verify the policies through psql
> --
> set role "User";
> set rls.artistID = '116';
> select * from "Track";
> --
>
> --
> Query plan for postgres
> --
> postgres=> explain analyze select * from "Track";
>  QUERY PLAN
>
>
> -
>  Seq Scan on "Track"  (cost=0.00..34589179.11 rows=2110303 width=58)
> (actual time=68.097..350.074 rows=14 loops=1)
>Filter: (hashed SubPlan 2)
>Rows Removed by Filter: 4220538
>SubPlan 2
>  ->  Index Scan using "IFK_AlbumArtistId" on "Album"  (cost=0.15..8.17
> rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=1)
>Index Cond: ("ArtistId" =
> (current_setting('rls.artistID'::text))::integer)
>  Planning Time: 0.091 ms
>  JIT:
>Functions: 17
>Options: Inlining true, Optimization true, Expressions true, Deforming
> true
>Timing: Generation 1.008 ms, Inlining 11.450 ms, Optimization 33.233
> ms, Emission 22.443 ms, Total 68.135 ms
>  Execution Time: 350.922 ms
> (12 rows)
> --
>
> --
> Disabled ROW LEVEL SECURITY and get appropriate tracks
> --
>
>
> QUERY PLAN
>
>
> --
>  Aggregate  (cost=7657.40..7657.41 rows=1 width=32) (actual
> time=0.070..0.071 rows=1 loops=1)
>->  Nested Loop Left Join  (cost=7650.01..7657.38 rows=1 width=55)
> (actual time=0.061..0.068 rows=1 loops=1)
>  ->  Seq Scan on "Album"  (cost=0.00..7.34 rows=1 width=27)
> (actual time=0.020..0.026 rows=1 loops=1)
>Filter: ("ArtistId" = 116)
>Rows Removed by Filter: 346
>  ->  Aggregate  (cost=7650.01..7650.02 rows=1 width=32) (actual
> time=0.040..0.040 rows=1 loops=1)
>->  Nested Loop  (cost=0.43..6107.07 rows=102863 width=11)
> (actual time=0.016..0.026 rows=14 loops=1)
>  ->  Seq Scan on "Album" "__be_0_Album"
>  (cost=0.00..8.21 rows=1 width=4) (actual time=0.008..0.015 rows=1 loops=1)
>Filter: (("AlbumId" = "Album"."AlbumId") AND
> ("ArtistId" = 116))
>Rows Removed by Filter: 346
>  ->  Index Scan using "IFK_TrackAlbumId" on "Track"
>  (cost=0.43..5070.23 rows=102863 width=15) (actual time=0.008..0.009
> rows=14 loops=1)
>Index Cond: ("AlbumId" = "Album"."AlbumId")
>SubPlan 2
>  ->  Result  (cost=0.00..0.01 rows=1 width=32) (actual
> time=0.000..0.000 rows=1 loops=14)
>SubPlan 1
>  ->  Result  (cost=0.00..0.01 rows=1 width=32) (actual
> time=0.000..0.000 rows=1 loops=1)
>  Planning Time: 0.182 ms
>  Execution Time: 0.094 ms
> (18 rows)
> --
>
> Why did Postgres choose to do a sequential scan on Track when RLS is
> enabled?
>
> Regards,
> Akash Anand
>
>

Why is query performance on RLS enabled Postgres worse?

[Akash Anand]

Hi,

--
Postgres version
--
postgres=# SELECT version();
  version

---
 PostgreSQL 15.3 (Debian 15.3-1.pgdg110+1) on aarch64-unknown-linux-gnu,
compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)
--

--
Load data
--
Chinook database
https://github.com/lerocha/chinook-database/blob/master/ChinookDatabase/DataSources/Chinook_PostgreSql.sql
--

--
Insert dummy data into Track to bring rows count to 10 million
--
INSERT INTO "Track"("TrackId", "Name", "AlbumId", "MediaTypeId", "GenreId",
"Milliseconds", "Bytes", "UnitPrice")
SELECT i::int, i::text, 1, 1, 1, 276349, 9056902, 0.99
FROM generate_series(3504, 1000) AS t(i);
--

--
Setup role and policies
--
create role "User";
grant select on "Album" to "User";
CREATE POLICY artist_rls_policy ON "Album" FOR SELECT TO public USING
("ArtistId"=((current_setting('rls.artistID'))::integer));
ALTER TABLE "Album" ENABLE ROW LEVEL SECURITY;
grant select on "Track" to "User";
CREATE POLICY album_rls_policy ON "Track" FOR SELECT to public
USING (
  EXISTS (
select 1 from "Album" where "Track"."AlbumId" = "Album"."AlbumId"
  )
);
ALTER TABLE "Track" ENABLE ROW LEVEL SECURITY;
--

--
Query and verify the policies through psql
--
set role "User";
set rls.artistID = '116';
select * from "Track";
--

--
Query plan for postgres
--
postgres=> explain analyze select * from "Track";
 QUERY PLAN

-
 Seq Scan on "Track"  (cost=0.00..34589179.11 rows=2110303 width=58)
(actual time=68.097..350.074 rows=14 loops=1)
   Filter: (hashed SubPlan 2)
   Rows Removed by Filter: 4220538
   SubPlan 2
 ->  Index Scan using "IFK_AlbumArtistId" on "Album"  (cost=0.15..8.17
rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=1)
   Index Cond: ("ArtistId" =
(current_setting('rls.artistID'::text))::integer)
 Planning Time: 0.091 ms
 JIT:
   Functions: 17
   Options: Inlining true, Optimization true, Expressions true, Deforming
true
   Timing: Generation 1.008 ms, Inlining 11.450 ms, Optimization 33.233 ms,
Emission 22.443 ms, Total 68.135 ms
 Execution Time: 350.922 ms
(12 rows)
--

--
Disabled ROW LEVEL SECURITY and get appropriate tracks
--


QUERY PLAN

--
 Aggregate  (cost=7657.40..7657.41 rows=1 width=32) (actual
time=0.070..0.071 rows=1 loops=1)
   ->  Nested Loop Left Join  (cost=7650.01..7657.38 rows=1 width=55)
(actual time=0.061..0.068 rows=1 loops=1)
 ->  Seq Scan on "Album"  (cost=0.00..7.34 rows=1 width=27) (actual
time=0.020..0.026 rows=1 loops=1)
   Filter: ("ArtistId" = 116)
   Rows Removed by Filter: 346
 ->  Aggregate  (cost=7650.01..7650.02 rows=1 width=32) (actual
time=0.040..0.040 rows=1 loops=1)
   ->  Nested Loop  (cost=0.43..6107.07 rows=102863 width=11)
(actual time=0.016..0.026 rows=14 loops=1)
 ->  Seq Scan on "Album" "__be_0_Album"
 (cost=0.00..8.21 rows=1 width=4) (actual time=0.008..0.015 rows=1 loops=1)
   Filter: (("AlbumId" = "Album"."AlbumId") AND
("ArtistId" = 116))
   Rows Removed by Filter: 346
 ->  Index Scan using "IFK_TrackAlbumId" on "Track"
 (cost=0.43..5070.23 rows=102863 width=15) (actual time=0.008..0.009
rows=14 loops=1)
   Index Cond: ("AlbumId" = "Album"."AlbumId")
   SubPlan 2
 ->  Result  (cost=0.00..0.01 rows=1 width=32) (actual
time=0.000..0.000 rows=1 loops=14)
   SubPlan 1
 ->  Result  (cost=0.00..0.01 rows=1 width=32) (actual
time=0.000..0.000 rows=1 loops=1)
 Planning Time: 0.182 ms
 Execution Time: 0.094 ms
(18 rows)
--

Why did Postgres choose to do a sequential scan on Track when RLS is
enabled?

Regards,
Akash Anand

Fwd: Helping planner to chose sequential scan when it improves performance

[Patrick O'Toole]

Hello! I tried asking this over on the general listserv before realizing
pgsql-performance is probably better suited.

Hi all,

I recently started at a new firm and have been trying to help to grok
certain planner behavior. A strip-down example of the sort of join we do in
the database looks like this, wherein we join two tables that have about 1
million rows:

-- VACUUM (FULL, VERBOSE, ANALYZE), run the query twice first, then...
EXPLAIN(ANALYZE, VERBOSE, COSTS, SETTINGS, BUFFERS, WAL, TIMING, SUMMARY)
SELECT
ci.conversation_uuid,
ci.item_uuid,
ci.tenant_id,
it.item_subject,
it.item_body_start
FROM
conversation_item AS ci
INNER JOIN item_text AS it ON it.item_uuid = ci.item_uuid;

-- The necessary DDL that creates these tables and indexes is attached.
I've commented out some extra stuff that isn't directly related to the
above query.

Depending on config, we get different results in terms of performance
(EXPLAIN output attached):

PLAN A (default config, effective cache size just shy of 15GB): 3.829
seconds. A nested loop is used to probe the hash index
`conversation_item_item_hash_index` for each row of item_text. Although the
cost of probing once is low, a fair amount of time passes because the
operation is repeated ~1.3 million times.

PLAN B (enable_indexscan off, effective cache same as before): 3.254
seconds (~15% speedup, sometimes 30%). Both tables are scanned sequentially
and conversation_item is hashed before results are combined with a hash
join.

PLAN C: (random_page_cost = 8.0, instead of default 4, effective cache same
as before): 2.959 (~23% speedup, sometimes 38%). Same overall plan as PLAN
B, some differences in buffers and I/O. I'll note we had to get to 8.0
before we saw a change to planner behavior; 5.0, 6.0, and 7.0 were too low
to make a difference.

Environment:

Postgres 15.2
Amazon RDS — db.m6g.2xlarge


Questions:

   1. In Plan A, what factors (read: which GUC settings) are causing the
   planner to select a substantially slower plan despite having recent stats
   about number of rows?
   2. Is there a substantial difference between the on-the-fly hash done in
   Plan B and Plan C compared to the hash-index used in Plan A? Can I assume
   they are essentially the same? Perhaps there are there differences in how
   they're applied?
   3. Is it common to see values for random_page_cost set as high as 8.0?
   We would of course need to investigate whether we see a net positive or net
   negative impact on other queries, to adopt this as a general setting, but
   is it a proposal we should actually consider?
   4. Maybe we are barking up the wrong tree with the previous questions.
   Are there other configuration parameters we should consider first to
   improve performance in situations like the one illustrated?
   5. Are there other problems with our schema, query, or plans shown here?
   Other approaches (or tools/analyses) we should consider?

Attached also are the results of

SELECT name, current_setting(name), source
  FROM pg_settings
  WHERE source NOT IN ('default', 'override');

- Patrick
Hash Join  (cost=53933.69..255802.87 rows=1395542 width=216) (actual 
time=422.466..2868.948 rows=1394633 loops=1)"
  Output: ci.conversation_uuid, ci.item_uuid, ci.tenant_id, it.item_subject, 
it.item_body_start"
  Inner Unique: true"
  Hash Cond: (it.item_uuid = ci.item_uuid)"
  Buffers: shared hit=12283 read=95974 dirtied=77, temp read=44113 
written=44113"
  I/O Timings: shared/local read=462.195, temp read=86.413 write=423.363"
  WAL: records=1 bytes=58"
  ->  Seq Scan on item_text it  (cost=0.00..110641.18 rows=1401518 width=196) 
(actual time=0.012..854.842 rows=1394633 loops=1)"
Output: it.item_subject, it.item_body_start, it.item_uuid"
Buffers: shared hit=652 read=95974 dirtied=77"
I/O Timings: shared/local read=462.195"
WAL: records=1 bytes=58"
  ->  Hash  (cost=25586.42..25586.42 rows=1395542 width=36) (actual 
time=422.386..422.387 rows=1394633 loops=1)"
Output: ci.conversation_uuid, ci.item_uuid, ci.tenant_id"
Buckets: 131072  Batches: 16  Memory Usage: 6799kB"
Buffers: shared hit=11631, temp written=8932"
I/O Timings: temp write=78.785"
->  Seq Scan on conversation_item ci  (cost=0.00..25586.42 rows=1395542 
width=36) (actual time=0.004..133.304 rows=1394633 loops=1)"
  Output: ci.conversation_uuid, ci.item_uuid, ci.tenant_id"
  Buffers: shared hit=11631"
Settings: effective_cache_size = '16053152kB', jit = 'off', random_page_cost = 
'8'"
Query Identifier: 5005100605804837348"
Planning:"
  Buffers: shared hit=398 read=7"
  I/O Timings: shared/local read=1.400"
Planning Time: 2.379 ms"
Execution Time: 2956.264 ms"

Generated with:
EXPLAIN(ANALYZE, VERBOSE, COSTS, SETTINGS, BUFFERS, WAL, TIMING, 
SUMMARY)

Re: PostgreSQL performance on ARM i.MX6

[Ranier Vilela]

Em ter., 23 de mai. de 2023 às 08:43, Druckenmueller, Marc <
marc.druckenmuel...@philips.com> escreveu:

> Hi there,
>
>
>
> I am investigating possible throughput with PostgreSQL 14.4 on an ARM
> i.MX6 Quad CPU (NXP sabre board).
>
> Testing with a simple python script (running on the same CPU), I get ~1000
> request/s.
>
Can you share kernel and python detalis? (version, etc).

regards,
Ranier Vilela

Re: PostgreSQL performance on ARM i.MX6

[Richard Huxton]

On 2023-05-23 12:42, Druckenmueller, Marc wrote:

Hi there,

I am investigating possible throughput with PostgreSQL 14.4 on an ARM
i.MX6 Quad CPU (NXP sabre board).

Testing with a simple python script (running on the same CPU), I get
~1000 request/s.


I tweaked your script slightly, but this is what I got on the Raspberry 
Pi 4 that I have in the corner of the room. Almost twice the speed you 
are seeing.


0: this = 0.58 tot = 0.58
1: this = 0.55 tot = 1.13
2: this = 0.59 tot = 1.72
3: this = 0.55 tot = 2.27
4: this = 0.56 tot = 2.83
5: this = 0.57 tot = 3.40
6: this = 0.56 tot = 3.96
7: this = 0.55 tot = 4.51
8: this = 0.59 tot = 5.11
9: this = 0.60 tot = 5.71

That's with governor=performance and a couple of background tasks 
running as well as the python. PostgreSQL 15 in a container on a Debian 
O.S. I've not done any tuning on PostgreSQL (but your call isn't doing 
anything really) nor the Pi.


The minor tweaks to your script were as below:

import psycopg as pg
import time

conn = pg.connect('')
conn.autocommit = True
cur = conn.cursor()
start = time.time()
prev = start
end = start
for j in range(10):
for i in range(1000):
cur.execute("call dummy_call(%s,%s,%s, ARRAY[%s, %s, 
%s]::real[]);", (1,2,3, 4.0, 5.0, 6.0), binary=True )

end = time.time()
print(f"{j}: this = {(end - prev):.2f} tot = {(end - 
start):.2f}")

prev = end

--
  Richard Huxton

Re: PostgreSQL performance on ARM i.MX6

[Tom Lane]

"Druckenmueller, Marc"  writes:
> I am investigating possible throughput with PostgreSQL 14.4 on an ARM i.MX6 
> Quad CPU (NXP sabre board).
> Testing with a simple python script (running on the same CPU), I get ~1000 
> request/s.

That does seem pretty awful for modern hardware, but it's hard to
tease apart the various potential causes.  How beefy is that CPU
really?  Maybe the overhead is all down to client/server network round
trips?  Maybe psycopg is doing something unnecessarily inefficient?

For comparison, on my development workstation I get

[ create the procedure manually in db test ]
$ cat bench.sql
call dummy_call(1,2,3,array[1,2,3]::float8[]);
$ pgbench -f bench.sql -n -T 10 test
pgbench (16beta1)
transaction type: bench.sql
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
duration: 10 s
number of transactions actually processed: 353891
number of failed transactions: 0 (0.000%)
latency average = 0.028 ms
initial connection time = 7.686 ms
tps = 35416.189844 (without initial connection time)

and it'd be more if I weren't using an assertions-enabled
debug build.  It would be interesting to see what you get
from exactly that test case on your ARM board.

BTW, one thing I see that's definitely an avoidable inefficiency in
your test is that you're forcing the array parameter to real[]
(i.e. float4) when the procedure takes double precision[]
(i.e. float8).  That forces an extra run-time conversion.  Swapping
between float4 and float8 in my pgbench test doesn't move the needle
a lot, but it's noticeable.

Another thing to think about is that psycopg might be defaulting
to a TCP rather than Unix-socket connection, and that might add
overhead depending on what kernel you're using.  Although, rather
than try to micro-optimize that, you probably ought to be thinking
of how to remove network round trips altogether.  I can get upwards
of 300K calls/second if I push the loop to the server side:

test=# \timing
Timing is on.
test=# do $$
declare x int := 1; a float8[] := array[1,2,3];
begin
for i in 1..100 loop
  call dummy_call (x,x,x,a);
end loop;
end $$;
DO
Time: 3256.023 ms (00:03.256)
test=# select 100/3.256023;
  ?column?   
-
 307123.137643683721
(1 row)

Again, it would be interesting to compare exactly that
test case on your ARM board.

regards, tom lane

Re: PostgreSQL performance on ARM i.MX6

[Daniele Varrazzo]

On Tue, 23 May 2023 at 13:43, Druckenmueller, Marc
 wrote:

> Testing with a simple python script (running on the same CPU), I get ~1000 
> request/s.

Is the time spent in the client or in the server? Are there noticeable
differences if you execute that statement in a loop in psql (with the
variables already bound)?

-- Daniele

PostgreSQL performance on ARM i.MX6

[Druckenmueller, Marc]

Hi there,

I am investigating possible throughput with PostgreSQL 14.4 on an ARM i.MX6 
Quad CPU (NXP sabre board).
Testing with a simple python script (running on the same CPU), I get ~1000 
request/s.

import psycopg as pg
conn = pg.connect('dbname=test')
conn.autocommit = True
cur = conn.cursor()
while True:
cur.execute("call dummy_call(%s,%s,%s, ARRAY[%s, %s, %s]::real[]);", 
(1,2,3, 4.0, 5.0, 6.0), binary=True )

where the called procedure is basically a no-op:

CREATE OR REPLACE PROCEDURE dummy_call(
in arg1 int,
in arg2 int,
in arg3 int,
in arg4 double precision[])
AS $$
BEGIN
END
$$ LANGUAGE plpgsql;

This seems to be a quite low number of requests/s, given that there are no 
changes to the database.
Looking for suggestions what could cause this poor performance and where to 
start investigations.

Thanks,

Marc


The information contained in this message may be confidential and legally 
protected under applicable law. The message is intended solely for the 
addressee(s). If you are not the intended recipient, you are hereby notified 
that any use, forwarding, dissemination, or reproduction of this message is 
strictly prohibited and may be unlawful. If you are not the intended recipient, 
please contact the sender by return e-mail and destroy all copies of the 
original message.

Re: Performance issues in query with multiple joins

[Laurenz Albe]

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

[Tom Lane]

=?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 is a
problem you should think about ditching the star schema design.

regards, tom lane

Performance issues in query with multiple joins

[David G. Johnston]

On Friday, April 28, 2023, Παρασκευη Πασσαρη 
wrote:

> Dear all,
>
> We are facing a performance issue with the following query. Executing this
> query takes about 20 seconds.
> (the database version is 14.1)
>

Given the possibility of this working better in the supported 14.7 I
suggest starting with a minor version update then posting again if still
having problems.

David J.

Performance issues in query with multiple joins

[Παρασκευη Πασσαρη]

Dear all,

We are facing a performance issue with the following query. Executing this
query takes about 20 seconds.
(the database version is 14.1)

The query:

- SLOW QUERY -

SELECT lead_record.id AS id
  FROM "lead_record" lead_record
LEFT JOIN  "lead_record__field_msisdn"
"lead_record__field_msisdn" ON "lead_record".id =
"lead_record__field_msisdn".entity_id
LEFT JOIN  "lead_record__field_campaign"
"lead_record__field_campaign" ON "lead_record".id =
"lead_record__field_campaign".entity_id
LEFT JOIN  "lead_record__field_number_of_calls"
"lead_record__field_number_of_calls" ON "lead_record".id =
"lead_record__field_number_of_calls".entity_id
LEFT JOIN  "lead_record__field_last_call"
"lead_record__field_last_call" ON "lead_record".id =
"lead_record__field_last_call".entity_id
LEFT JOIN  "lead_record__field_last_offered_plan"
"lead_record__field_last_offered_plan" ON "lead_record".id =
"lead_record__field_last_offered_plan".entity_id
LEFT JOIN  "lead_record__field_status"
"lead_record__field_status" ON "lead_record".id =
"lead_record__field_status".entity_id
LEFT JOIN  "lead_record__field_comment_text"
"lead_record__field_comment_text" ON "lead_record".id =
"lead_record__field_comment_text".entity_id
LEFT JOIN  "lead_record__field_date_of_visit"
"lead_record__field_date_of_visit" ON "lead_record".id =
"lead_record__field_date_of_visit".entity_id
LEFT JOIN  "lead_record__field_current_mf"
"lead_record__field_current_mf" ON "lead_record".id =
"lead_record__field_current_mf".entity_id
LEFT JOIN  "lead_record__field_pos_code"
"lead_record__field_pos_code" ON "lead_record".id =
"lead_record__field_pos_code".entity_id
LEFT JOIN  "lead_record__field_assignee"
"lead_record__field_assignee" ON "lead_record".id =
"lead_record__field_assignee".entity_id
LEFT JOIN  "lead_record__field_checks_passed"
"lead_record__field_checks_passed" ON "lead_record".id =
"lead_record__field_checks_passed".entity_id
LEFT JOIN  "lead_record__field_last_offer_name"
"lead_record__field_last_offer_name" ON "lead_record".id =
"lead_record__field_last_offer_name".entity_id
LEFT JOIN  "lead_record__field_next_scheduled_call"
"lead_record__field_next_scheduled_call" ON "lead_record".id =
"lead_record__field_next_scheduled_call".entity_id
LEFT JOIN "taxonomy_term_field_data"
"taxonomy_term_field_data_lead_record__field_campaign" ON
"lead_record__field_campaign".field_campaign_target_id =
"taxonomy_term_field_data_lead_record__field_campaign".tid
LEFT JOIN "taxonomy_term__field_active"
"taxonomy_term__field_active" ON
taxonomy_term_field_data_lead_record__field_campaign.tid =
taxonomy_term__field_active.entity_id
LEFT JOIN "users_field_data"
"users_field_data_lead_record__field_assignee" ON
"lead_record__field_assignee".field_assignee_target_id =
"users_field_data_lead_record__field_assignee".uid
LEFT JOIN "taxonomy_term__field_campaign_end_date"
"taxonomy_term_field_data_lead_record__field_campaign__taxonomy_term__field_campaign_end_date"
ON "taxonomy_term_field_data_lead_record__field_campaign".tid =
"taxonomy_term_field_data_lead_record__field_campaign__taxonomy_term__field_campaign_end_date".entity_id

WHERE
((TO_DATE("taxonomy_term_field_data_lead_record__field_campaign__taxonomy_term__field_campaign_end_date".field_campaign_end_date_value,
'-MM-DDTHH24:MI:SS')  >= (now() - INTERVAL '1 days'))
and "taxonomy_term__field_active".field_active_value = 1
and "lead_record__field_assignee".field_assignee_target_id = 140
and "lead_record__field_pos_code".field_pos_code_value =
'10064'
and
"lead_record__field_checks_passed".field_checks_passed_value = 1
and
"lead_record__field_number_of_calls".field_number_of_calls_value < 10);


This is the execution plan:

- EXPLAIN ANALYZE -

Nested Loop Left Join  (cost=65337.38..77121.07 rows=1 width=4) (actual
time=27164.156..27209.338 rows=0 loops=1)
  ->  Nested Loop  (cost=65337.11..77120.46 rows=1 width=12) (actual
t

Re: Performance of UPDATE operation

[Jeff Janes]

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;
>  9.203   0  END;
> ```
>
> My naive expectation will be that updating the newly inserted record
> should cost nothing


It takes less than 1/10 of the total time.  That is pretty close to
nothing.  Why would you expect it to be truly free?


> ... Are there ways
> to make it less expensive?
>

Obviously here you could just insert the correct value in the first place
and not do the update at all.

Cheers,

Jeff

Re: Performance of UPDATE operation

[Oluwatobi Ogunsola]

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-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
>> INCREMENT BY 1
>> NO MINVALUE
>> NO MAXVALUE
>> CACHE 1;
>> 
>> 
>> CREATE TABLE t_inodes (
>> inumber bigint PRIMARY KEY,
>> icrtime timestamp with time zone NOT NULL,
>> igeneration bigint NOT NULL
>> );
>> ```
>> 
>> and a transaction that inserts and update an entry in that table:
>> 
>> ```
>> BEGIN;
>> INSERT INTO t_inodes (inumber, icrtime, igeneration)
>>VALUES (nextval('t_inodes_inumber_seq'), now(), 0) RETURNING inumber \gset
>> 
>> UPDATE t_inodes SET igeneration = igeneration + 1 where  inumber = :inumber;
>> END;
>> ```
>> 
>> The pgbench shows the following result:
>> 
>> ```
>> $ pgbench -h localhost -n -r  -f update.sql -t 1 -c 64 -j 64 testdb
>> pgbench (15.0 (Debian 15.0-1.pgdg110+1))
>> transaction type: update.sql
>> scaling factor: 1
>> query mode: simple
>> number of clients: 64
>> number of threads: 64
>> maximum number of tries: 1
>> number of transactions per client: 1
>> number of transactions actually processed: 64/64
>> number of failed transactions: 0 (0.000%)
>> latency average = 11.559 ms
>> initial connection time = 86.038 ms
>> tps = 5536.736898 (without initial connection time)
>> statement latencies in milliseconds and failures:
>>  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;
>>  9.203   0  END;
>> ```
>> 
>> My naive expectation will be that updating the newly inserted record should 
>> cost nothing... Are there ways
>> to make it less expensive?
> 
> Updating a newly inserted row is about as expensive as inserting the row in 
> the first place.
> 
> You can reduce the overall impact somewhat by creating the table with a 
> "fillfactor" below
> 100, in your case 90 would probably be enough.  That won't speed up the 
> UPDATE itself, but
> it should greatly reduce the need for VACUUM.
> 
> Yours,
> Laurenz Albe
> 
> 

Re: Performance of UPDATE operation

[Laurenz Albe]

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
>     INCREMENT BY 1
>     NO MINVALUE
>     NO MAXVALUE
>     CACHE 1;
> 
> 
> CREATE TABLE t_inodes (
>     inumber bigint PRIMARY KEY,
>     icrtime timestamp with time zone NOT NULL,
>     igeneration bigint NOT NULL
> );
> ```
> 
> and a transaction that inserts and update an entry in that table:
> 
> ```
> BEGIN;
> INSERT INTO t_inodes (inumber, icrtime, igeneration)
>    VALUES (nextval('t_inodes_inumber_seq'), now(), 0) RETURNING inumber \gset
> 
> UPDATE t_inodes SET igeneration = igeneration + 1 where  inumber = :inumber;
> END;
> ```
> 
> The pgbench shows the following result:
> 
> ```
> $ pgbench -h localhost -n -r  -f update.sql -t 1 -c 64 -j 64 testdb
> pgbench (15.0 (Debian 15.0-1.pgdg110+1))
> transaction type: update.sql
> scaling factor: 1
> query mode: simple
> number of clients: 64
> number of threads: 64
> maximum number of tries: 1
> number of transactions per client: 1
> number of transactions actually processed: 64/64
> number of failed transactions: 0 (0.000%)
> latency average = 11.559 ms
> initial connection time = 86.038 ms
> tps = 5536.736898 (without initial connection time)
> statement latencies in milliseconds and failures:
>  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;
>  9.203   0  END;
> ```
> 
> My naive expectation will be that updating the newly inserted record should 
> cost nothing... Are there ways
> to make it less expensive?

Updating a newly inserted row is about as expensive as inserting the row in the 
first place.

You can reduce the overall impact somewhat by creating the table with a 
"fillfactor" below
100, in your case 90 would probably be enough.  That won't speed up the UPDATE 
itself, but
it should greatly reduce the need for VACUUM.

Yours,
Laurenz Albe

Performance of UPDATE operation

[Mkrtchyan, Tigran]

Dear Postgres Folks,

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
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;


CREATE TABLE t_inodes (
inumber bigint PRIMARY KEY,
icrtime timestamp with time zone NOT NULL,
igeneration bigint NOT NULL
);
```

and a transaction that inserts and update an entry in that table:

```
BEGIN;
INSERT INTO t_inodes (inumber, icrtime, igeneration)
   VALUES (nextval('t_inodes_inumber_seq'), now(), 0) RETURNING inumber \gset

UPDATE t_inodes SET igeneration = igeneration + 1 where  inumber = :inumber;
END;
```

The pgbench shows the following result:

```
$ pgbench -h localhost -n -r  -f update.sql -t 1 -c 64 -j 64 testdb
pgbench (15.0 (Debian 15.0-1.pgdg110+1))
transaction type: update.sql
scaling factor: 1
query mode: simple
number of clients: 64
number of threads: 64
maximum number of tries: 1
number of transactions per client: 1
number of transactions actually processed: 64/64
number of failed transactions: 0 (0.000%)
latency average = 11.559 ms
initial connection time = 86.038 ms
tps = 5536.736898 (without initial connection time)
statement latencies in milliseconds and failures:
 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;
 9.203   0  END;
```

My naive expectation will be that updating the newly inserted record should 
cost nothing... Are there ways
to make it less expensive?

Best regards,
   Tigran.

smime.p7s
Description: S/MIME Cryptographic Signature

Re: Catching up with performance & PostgreSQL 15

[Jeff Janes]

On Tue, Nov 29, 2022 at 4:07 PM David Rowley  wrote:

> On Wed, 30 Nov 2022 at 03:31, Tom Lane  wrote:
> >
> > Alvaro Herrera  writes:
> > > IMO it was a mistake to turn JIT on in the default config, so that's
> one
> > > thing you'll likely want to change.
> >
> > I wouldn't necessarily go quite that far, but I do think that the
> > default cost thresholds for invoking it are enormously too low,
> > or else there are serious bugs in the cost-estimation algorithms
> > for deciding when to use it.  A nearby example[1] of a sub-1-sec
> > partitioned query that took 30sec after JIT was enabled makes me
> > wonder if we're accounting correctly for per-partition JIT costs.
>
> I'm very grateful for JIT. However, I do agree that the costs need to work.
>
> The problem is that the threshold to turn JIT on does not consider how
> many expressions need to be compiled. It's quite different to JIT
> compile a simple one-node plan with a total cost of 10 than to JIT
> compile a plan that  costs the same but queries 1000 partitions. I
> think we should be compiling expressions based on the cost of the
> individial node rather than the total cost of the plan.


I think a big win for JIT would be to be able to do it just once per cached
plan, not once per execution.  And then have it turned on only for prepared
statements.  Of course that means JIT couldn't do parameter folding, but I
don't know if it does that anyway.  Also, very expensive plans are
generally dominated by IO cost estimates, and I think it doesn't make sense
to drive JIT decisions based predominantly on the expected cost of the IO.
If the planner separated IO cost estimate totals from CPU cost estimate
totals, it might open up better choices.

Cheers,

Jeff

Re: Catching up with performance & PostgreSQL 15

[Andrew Dunstan]

On 2022-11-30 We 11:36, Tom Lane wrote:
> Andres Freund  writes:
>> On November 30, 2022 3:47:32 AM PST, Andrew Dunstan  
>> wrote:
>>> I think Alvaro's point is that it would have been better to work out
>>> these wrinkles before turning on JIT by default. Based on anecdotal
>>> reports from the field I'm inclined to agree.
>> The problem is that back when it was introduced these problems didn't exist 
>> to a significant degree. JIT was developed when partitioning was very 
>> minimal- and the problems we're seeing are almost exclusively with queries 
>> with many partitions. The problems really only started much more recently. 
>> It also wasn't enabled in the first release..
> Well, wherever you want to pin the blame, it seems clear that we
> have a problem now.  And I don't think flipping back to off-by-default
> is the answer -- surely there is some population of users who will
> not be happy with that.  We really need to prioritize fixing the
> cost-estimation problems, and/or tweaking the default thresholds.
>
>   


+1


FTR I am not trying to pin blame anywhere. I think the work that's been
done on JIT is more than impressive.


cheers


andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

Re: Catching up with performance & PostgreSQL 15

[Tom Lane]

Andres Freund  writes:
> On November 30, 2022 3:47:32 AM PST, Andrew Dunstan  
> wrote:
>> I think Alvaro's point is that it would have been better to work out
>> these wrinkles before turning on JIT by default. Based on anecdotal
>> reports from the field I'm inclined to agree.

> The problem is that back when it was introduced these problems didn't exist 
> to a significant degree. JIT was developed when partitioning was very 
> minimal- and the problems we're seeing are almost exclusively with queries 
> with many partitions. The problems really only started much more recently. It 
> also wasn't enabled in the first release..

Well, wherever you want to pin the blame, it seems clear that we
have a problem now.  And I don't think flipping back to off-by-default
is the answer -- surely there is some population of users who will
not be happy with that.  We really need to prioritize fixing the
cost-estimation problems, and/or tweaking the default thresholds.

regards, tom lane

Re: Catching up with performance & PostgreSQL 15

[Andres Freund]

Hi, 

On November 30, 2022 3:47:32 AM PST, Andrew Dunstan  wrote:
>
>On 2022-11-29 Tu 16:06, David Rowley wrote:
>> On Wed, 30 Nov 2022 at 03:31, Tom Lane  wrote:
>>> Alvaro Herrera  writes:
 IMO it was a mistake to turn JIT on in the default config, so that's one
 thing you'll likely want to change.
>>> I wouldn't necessarily go quite that far, but I do think that the
>>> default cost thresholds for invoking it are enormously too low,
>>> or else there are serious bugs in the cost-estimation algorithms
>>> for deciding when to use it.  A nearby example[1] of a sub-1-sec
>>> partitioned query that took 30sec after JIT was enabled makes me
>>> wonder if we're accounting correctly for per-partition JIT costs.
>> I'm very grateful for JIT. However, I do agree that the costs need to work.
>>
>> The problem is that the threshold to turn JIT on does not consider how
>> many expressions need to be compiled. It's quite different to JIT
>> compile a simple one-node plan with a total cost of 10 than to JIT
>> compile a plan that  costs the same but queries 1000 partitions. I
>> think we should be compiling expressions based on the cost of the
>> individial node rather than the total cost of the plan. We need to
>> make some changes so we can more easily determine the number of times
>> a given node will be executed before we can determine how worthwhile
>> JITting an expression in a node will be.
>>
>
>I think Alvaro's point is that it would have been better to work out
>these wrinkles before turning on JIT by default. Based on anecdotal
>reports from the field I'm inclined to agree.

The problem is that back when it was introduced these problems didn't exist to 
a significant degree. JIT was developed when partitioning was very minimal- and 
the problems we're seeing are almost exclusively with queries with many 
partitions. The problems really only started much more recently. It also wasn't 
enabled in the first release..

Andres
-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.

Re: Catching up with performance & PostgreSQL 15

[Andrew Dunstan]

On 2022-11-29 Tu 16:06, David Rowley wrote:
> On Wed, 30 Nov 2022 at 03:31, Tom Lane  wrote:
>> Alvaro Herrera  writes:
>>> IMO it was a mistake to turn JIT on in the default config, so that's one
>>> thing you'll likely want to change.
>> I wouldn't necessarily go quite that far, but I do think that the
>> default cost thresholds for invoking it are enormously too low,
>> or else there are serious bugs in the cost-estimation algorithms
>> for deciding when to use it.  A nearby example[1] of a sub-1-sec
>> partitioned query that took 30sec after JIT was enabled makes me
>> wonder if we're accounting correctly for per-partition JIT costs.
> I'm very grateful for JIT. However, I do agree that the costs need to work.
>
> The problem is that the threshold to turn JIT on does not consider how
> many expressions need to be compiled. It's quite different to JIT
> compile a simple one-node plan with a total cost of 10 than to JIT
> compile a plan that  costs the same but queries 1000 partitions. I
> think we should be compiling expressions based on the cost of the
> individial node rather than the total cost of the plan. We need to
> make some changes so we can more easily determine the number of times
> a given node will be executed before we can determine how worthwhile
> JITting an expression in a node will be.
>

I think Alvaro's point is that it would have been better to work out
these wrinkles before turning on JIT by default. Based on anecdotal
reports from the field I'm inclined to agree.


cheers


andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

Re: Catching up with performance & PostgreSQL 15

[Mladen Gogala]

On Tue, 2022-11-29 at 19:09 +0100, Alvaro Herrera wrote:
> On 2022-Nov-29, Mladen Gogala wrote:
> 
> > Hmmm, I think I will run pgbench with and without JIT on and see
> > the
> > difference.
> 
> I doubt you'll notice anything, because the pgbench queries will be
> far
> below the JIT cost, so nothing will get JIT compiled at all.  Or are
> you
> planning on using a custom set of queries?
> 

Nope. I am planning to set jit_above_cost parameter to 5. That should
take care of the pgbench problem. Other than that, you're right: JIT
should not be used for OLTP. However, pure OLTP or DW databases are a
rarity these days. Reporting is a crucial function and almost every
OLTP database that I've seen also has reporting function, which means
that there are complex queries to be executed.
-- 
Mladen Gogala
Database Consultant
https://dbwhisperer.wordpress.com

Re: Catching up with performance & PostgreSQL 15

[David Rowley]

On Wed, 30 Nov 2022 at 03:31, Tom Lane  wrote:
>
> Alvaro Herrera  writes:
> > IMO it was a mistake to turn JIT on in the default config, so that's one
> > thing you'll likely want to change.
>
> I wouldn't necessarily go quite that far, but I do think that the
> default cost thresholds for invoking it are enormously too low,
> or else there are serious bugs in the cost-estimation algorithms
> for deciding when to use it.  A nearby example[1] of a sub-1-sec
> partitioned query that took 30sec after JIT was enabled makes me
> wonder if we're accounting correctly for per-partition JIT costs.

I'm very grateful for JIT. However, I do agree that the costs need to work.

The problem is that the threshold to turn JIT on does not consider how
many expressions need to be compiled. It's quite different to JIT
compile a simple one-node plan with a total cost of 10 than to JIT
compile a plan that  costs the same but queries 1000 partitions. I
think we should be compiling expressions based on the cost of the
individial node rather than the total cost of the plan. We need to
make some changes so we can more easily determine the number of times
a given node will be executed before we can determine how worthwhile
JITting an expression in a node will be.

David

> [1] 
> https://www.postgresql.org/message-id/B6025887-D73F-4B5B-9925-4DA4B675F7E5%40elevated-dev.com

Re: Catching up with performance & PostgreSQL 15

[Alvaro Herrera]

On 2022-Nov-29, Mladen Gogala wrote:

> Hmmm, I think I will run pgbench with and without JIT on and see the
> difference.

I doubt you'll notice anything, because the pgbench queries will be far
below the JIT cost, so nothing will get JIT compiled at all.  Or are you
planning on using a custom set of queries?

-- 
Álvaro Herrera PostgreSQL Developer  —  https://www.EnterpriseDB.com/

Re: Catching up with performance & PostgreSQL 15

[Tom Lane]

Alvaro Herrera  writes:
> IMO it was a mistake to turn JIT on in the default config, so that's one
> thing you'll likely want to change.

I wouldn't necessarily go quite that far, but I do think that the
default cost thresholds for invoking it are enormously too low,
or else there are serious bugs in the cost-estimation algorithms
for deciding when to use it.  A nearby example[1] of a sub-1-sec
partitioned query that took 30sec after JIT was enabled makes me
wonder if we're accounting correctly for per-partition JIT costs.

regards, tom lane

[1] 
https://www.postgresql.org/message-id/B6025887-D73F-4B5B-9925-4DA4B675F7E5%40elevated-dev.com

Re: Catching up with performance & PostgreSQL 15

[Mladen Gogala]

On 11/29/22 03:36, Alvaro Herrera wrote:

On 2022-Nov-28, Mladen Gogala wrote:


As for JIT, I've recently asked that question myself. I was told that
PostgreSQL with LLVM enabled performs approximately 25% better than without
it.

Hmm, actually, normally you're better off turning JIT off, because it's
very common to diagnose cases of queries that become much, much slower
because of it.  Some queries do become faster, but it's not a wide
margin, and it's not a lot.  There are rare cases where JIT is
beneficial, but those tend to be queries that take upwards of several
seconds already.

IMO it was a mistake to turn JIT on in the default config, so that's one
thing you'll likely want to change.

Hmmm, I think I will run pgbench with and without JIT on and see the 
difference.


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: Catching up with performance & PostgreSQL 15

[Mladen Gogala]

On 11/29/22 03:31, Alvaro Herrera wrote:

On 2022-Nov-28, Mladen Gogala wrote:


You'll probably be glad to learn that we have hints now.

What hints are you talking about?  As I understand, we still don't have
Oracle-style query hints.


https://github.com/ossc-db/pg_hint_plan

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: Catching up with performance & PostgreSQL 15

[Alvaro Herrera]

On 2022-Nov-28, Mladen Gogala wrote:

> As for JIT, I've recently asked that question myself. I was told that
> PostgreSQL with LLVM enabled performs approximately 25% better than without
> it.

Hmm, actually, normally you're better off turning JIT off, because it's
very common to diagnose cases of queries that become much, much slower
because of it.  Some queries do become faster, but it's not a wide
margin, and it's not a lot.  There are rare cases where JIT is
beneficial, but those tend to be queries that take upwards of several
seconds already.

IMO it was a mistake to turn JIT on in the default config, so that's one
thing you'll likely want to change.

-- 
Álvaro Herrera   48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"Thou shalt not follow the NULL pointer, for chaos and madness await
thee at its end." (2nd Commandment for C programmers)

Re: Catching up with performance & PostgreSQL 15

[Alvaro Herrera]

On 2022-Nov-28, Mladen Gogala wrote:

> You'll probably be glad to learn that we have hints now.

What hints are you talking about?  As I understand, we still don't have
Oracle-style query hints.

-- 
Álvaro HerreraBreisgau, Deutschland  —  https://www.EnterpriseDB.com/

Re: Catching up with performance & PostgreSQL 15

[Josh Berkus]

On 11/28/22 19:34, Justin Pryzby wrote:

In addition to setting work_mem, you can also (since v13) set
hash_mem_multiplier.


Is there any guidance on setting this?  Or is it still "use the default 
unless you can play around with it"?



default_toast_compression = lz4 # v14
recovery_init_sync_method = syncfs # v14
check_client_connection_interval = ... # v14
wal_compression = {lz4,zstd} # v15


If anyone has links to blogs or other things that discuss the 
performance implications of the above settings that would be wonderful!


--
Josh Berkus

Re: Catching up with performance & PostgreSQL 15

[Mladen Gogala]

On 11/28/22 21:59, Josh Berkus wrote:

Hey, folks:

I haven't configured a PostgreSQL server since version 11 (before 
that, I did quite a few).


What's changed in terms of performance configuration since then? Have 
the fundamentals of shared_buffers/work_mem/max_connections changed at 
all?  Which new settings are must-tunes?


I've heard about new parallel stuff an JIT, but neither is that 
applicable to my use-case.


Well, well! Long time no see! You'll probably be glad to learn that we 
have hints now. Thank you for the following page you created:


https://laptrinhx.com/why-postgresql-doesn-t-have-query-hints-2912445911/

I've used it several times, with great success. It's priceless.

Now, to answer your question: no, fundamentals of shared buffers, work 
memory and connections haven't changed. Parallelism works fine, it's 
reliable and easy to enable. All you need is to set 
max_parallel_workers_per_gather to an integer > 0 and PgSQL 15 will 
automatically use parallel plan if the planner decides that it's the 
best path. However, to warn you in advance, parallel query is not a 
panacea. On OLTP databases, I usually disable it on purpose. Parallel 
query will speed up sequential scans, but if your application is OLTP, 
sequential scan is a sign of trouble. Parallelism is a data warehouse 
only feature. And even then, you don't want it ti be run by multiple 
users at the same time. Namely, the number of your CPU resources is 
finite and having multiple users launch multiple processes is the best 
way to run out of the CPU power fast. Normally, you would package an 
output of the parallel query into a materialized view and let the users 
query the view.


As for JIT, I've recently asked that question myself. I was told that 
PostgreSQL with LLVM enabled performs approximately 25% better than 
without it. I haven't measured it so I can't  either confirm or deny the 
number.  I can tell you that there is a noticeable throughput 
improvement with PL/PGSQL intensive applications. There was also an 
increase in CPU consumption. I wasn't doing benchmarks, I was looking 
for a generic settings to install via Ansible so I don't have the 
numbers, only the feeling. One way of quantifying the difference would 
be to run pgbench with and without JIT.


PS:

I am still an Oracle DBA, just as you wrote in the paper.

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: Catching up with performance & PostgreSQL 15

[Justin Pryzby]

On Mon, Nov 28, 2022 at 06:59:41PM -0800, Josh Berkus wrote:
> Hey, folks:
> 
> I haven't configured a PostgreSQL server since version 11 (before that, I
> did quite a few).
> 
> What's changed in terms of performance configuration since then?  Have the
> fundamentals of shared_buffers/work_mem/max_connections changed at all?
> Which new settings are must-tunes?
> 
> I've heard about new parallel stuff an JIT, but neither is that applicable
> to my use-case.

shared buffers is the same, but btree indexes are frequently (IME) 3x
smaller (!) since deduplication was added in v13, so s_b might not need
to be as large.

In addition to setting work_mem, you can also (since v13) set
hash_mem_multiplier.

default_toast_compression = lz4 # v14
recovery_init_sync_method = syncfs # v14
check_client_connection_interval = ... # v14
wal_compression = {lz4,zstd} # v15

Peeking at my notes, there's also: partitioning, parallel query, brin
indexes, extended statistics, reindex concurrently, ...

... but I don't think anything is radically changed :)

-- 
Justin

Catching up with performance & PostgreSQL 15

[Josh Berkus]

Hey, folks:

I haven't configured a PostgreSQL server since version 11 (before that, 
I did quite a few).


What's changed in terms of performance configuration since then?  Have 
the fundamentals of shared_buffers/work_mem/max_connections changed at 
all?  Which new settings are must-tunes?


I've heard about new parallel stuff an JIT, but neither is that 
applicable to my use-case.


--
Josh Berkus

Re: =ANY(ARRAY) vs =ANY(ARRAY(expr)) performance

[Guillaume Cottenceau]

Rick Otten  writes:

>  I was able to reproduce a similar issue with using `= ANY(VALUES)` instead 
> of `= ANY(ARRAY)`:
>
>  1. fast query using =ANY(ARRAY): https://explain.depesz.com/s/dwP8
>  2. slow query using =ANY(ARRAY(expr)): https://explain.depesz.com/s/3hGb
>  3. slow query using =ANY(VALUES): https://explain.depesz.com/s/cYrn
>
>  I have found the "ANY" operator to be slow in general.  It is almost always 
> faster to use the "<@" operator:
> ```
> -- more intuitive:
> select
>   count(*)
> from
>   testarray
> where
>   'test' = ANY (myarray)
> ;
>
> -- faster:
> select
>   count(*)
> from
>   testarray
> where
>   ARRAY['test'::varchar] <@ myarray
> ;
> ```
> It is just one of those things, like replacing "OR" with "UNION ALL" whenever 
> possible too, that just make queries faster in PostgreSQL without a
> ton of effort or fuss.

depends^^

db=> select count(*) from table where uid = any( 
string_to_array('11290331,11290332,11290333,11290431',',')::int[]);
 count 
---
 4
(1 row)

Time: 0.837 ms
db=> select count(*) from table where uid = any( 
string_to_array('11290331,11290332,11290333,11290431',',')::int[]);
 count 
---
 4
(1 row)

Time: 0.854 ms
db=> select count(*) from table where array[uid] <@ 
string_to_array('11290331,11290332,11290333,11290431',',')::int[];
 count 
---
 4
(1 row)

Time: 52.335 ms
db=> select count(*) from table where array[uid] <@ 
string_to_array('11290331,11290332,11290333,11290431',',')::int[];
 count 
---
 4
(1 row)

Time: 44.176 ms


-- 
Guillaume Cottenceau

Re: =ANY(ARRAY) vs =ANY(ARRAY(expr)) performance

[Rick Otten]

I was able to reproduce a similar issue with using `= ANY(VALUES)`
> instead of `= ANY(ARRAY)`:
>
> 1. fast query using =ANY(ARRAY): https://explain.depesz.com/s/dwP8
> 2. slow query using =ANY(ARRAY(expr)): https://explain.depesz.com/s/3hGb
> 3. slow query using =ANY(VALUES): https://explain.depesz.com/s/cYrn
>
>
 I have found the "ANY" operator to be slow in general.  It is almost
always faster to use the "<@" operator:
```
-- more intuitive:
select
  count(*)
from
  testarray
where
  'test' = ANY (myarray)
;

-- faster:
select
  count(*)
from
  testarray
where
  ARRAY['test'::varchar] <@ myarray
;
```
It is just one of those things, like replacing "OR" with "UNION ALL"
whenever possible too, that just make queries faster in PostgreSQL without
a ton of effort or fuss.

Re: =ANY(ARRAY) vs =ANY(ARRAY(expr)) performance

[Ramdip Gill]

Okay, increasing the collection of statistics seems to have helped. I used
`ALTER TABLE report ALTER COLUMN reporter_id SET STATISTICS 1` and now
queries which previously didn't finish at all now finish in < 1 ms.

The following gave me the hint:

“The amount of information stored in `pg_statistic` by `ANALYZE`, in
particular the maximum number of entries in
the `most_common_vals` and `histogram_bounds` arrays for each column, can
be set on a column-by-column basis using the `ALTER TABLE SET
STATISTICS` command, or globally by setting the default_statistics_target
configuration variable. The default limit is presently 100 entries. *Raising
the limit might allow more accurate planner estimates to be made,
particularly for columns with irregular data distributions*, at the price
of consuming more space in `pg_statistic` and slightly more time to compute
the estimates. Conversely, a lower limit might be sufficient for columns
with simple data distributions.”

— https://www.postgresql.org/docs/current/planner-stats.html

>

=ANY(ARRAY) vs =ANY(ARRAY(expr)) performance

[Ramdip Gill]

Hello!

I have been struggling with finding a proper solution for this query for
some time and wanted to ask if someone here knows how to approach this?

I have a table named "report" which has an index on report.reporter_id.
This column consists of IDs which are grouped together using a table named
"group_links".
So for every reporter id which is part of the same group, there is a row in
"group_links" with the same group_id.

Now, I noticed that I can select reports for a group in two ways. Both
queries return the same but one is using =ANY(ARRAY(expr)) ("subselect")
and one is using =ANY(ARRAY) ("static array") with the same array as the
expression would return.
The static array query is running very fast for small selections and where
not a lot of rows match the condition. It uses a bitmap index scan.
The subselect is running very slow and uses an index scan. However, it is
particularly slow if not many rows match the condition and thus a lot of
rows are filtered while scanning the index.
I was able to reproduce a similar issue with using `= ANY(VALUES)`
instead of `= ANY(ARRAY)`:

1. fast query using =ANY(ARRAY): https://explain.depesz.com/s/dwP8
2. slow query using =ANY(ARRAY(expr)): https://explain.depesz.com/s/3hGb
3. slow query using =ANY(VALUES): https://explain.depesz.com/s/cYrn

I guess the difference comes from the query planner not being able to know
the exact values for the WHERE condition beforehand. But how should cases
like this be best handled?

Should I denormalize the data such that I have a table with columns
report.id and group_id and report.created such that I can create an index
on (created, group_id)? Then I don't have to do a subselect anymore.

I would be very glad for any help regarding this!

Postgres version: PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on
x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit


> \d report
>Table "public.report"
> Column |   Type   | Collation | Nullable | Default
> ---+--+---+--+-
>  reporter_id   | uuid |   | not null |
>  parsed| boolean  |   |  |
>  id| text |   | not null |
>  request_id| uuid |   |  |
>  created   | timestamp with time zone |   | not null | now()
>  customer  | text |   |  |
>  subject   | text |   |  |
>  parser_result | text |   | not null |
>  parser| text |   |  |
>  event_types   | jsonb|   |  |
>  event_count   | integer  |   |  |
>  account_id| integer  |   |  |
>  reviewable| boolean  |   | not null | false
>  reviewed  | boolean  |   | not null | false
> Indexes:
> "PK_99e4d0bea58cba73c57f935a546" PRIMARY KEY, btree (id)
> "idx_report_created_desc_id_asc" btree (created DESC, id)
> "idx_report_created_desc_reporter_id_asc" btree (created DESC,
> reporter_id)
> "idx_report_event_types" gin (event_types)
> "idx_report_parser_gin" gin (parser gin_trgm_ops)
> "idx_report_parser_result_created_desc" btree (parser_result, created
> DESC)
> "idx_report_reporter_id_asc_created_desc" btree (reporter_id, created
> DESC)
> "idx_report_request_id_asc_created_desc" btree (request_id, created
> DESC)
> "idx_report_subject_gin" gin (subject gin_trgm_ops)
> Check constraints:
> "report_parser_result_constraint" CHECK (parser_result = ANY
> (ARRAY['PARSED'::text, 'UNPARSED'::text, 'REJECTED'::text]))
> Foreign-key constraints:
> "FK_5b809608bb38d119333b69f65f9" FOREIGN KEY (request_id) REFERENCES
> request(id)
> "FK_d41df66b60944992386ed47cf2e" FOREIGN KEY (reporter_id) REFERENCES
> reporter(id)
> Referenced by:
> TABLE "event" CONSTRAINT "event_report_id_foreign" FOREIGN KEY
> (report_id) REFERENCES report(id)



> SELECT relname, relpages, reltuples, relallvisible, relkind, relnatts,
> relhassubclass, reloptions, pg_table_size(oid) FROM pg_class WHERE
> relname='report';
>  relname | relpages |   reltuples   | relallvisible | relkind | relnatts |
> relhassubclass | reloptions | pg_table_size
>
> -+--+---+---+-+--+++---
>  report  |  2062252 | 8.5893344e+07 |   2062193 | r   |   22 |
> f  ||   16898801664
> (1 row)



> \d group_links
>Table "public.group_links"
>   Column  |   Type   | Collation | 

Re: Milions of views - performance, stability

[Laurenz Albe]

On Sat, 2022-09-17 at 01:05 +0200, Hubert Rutkowski wrote:
> Hello! I have written python program to benchmark view efficiency, because in 
> our platform
> they have a role to play and we noticed the performance is less than expected.

If your platform plans to use millions of views, you should revise your design. 
 As you
see, that is not going to fly.  And no, I don't consider that a bug.

> Basically, benchmark creates table:
> 
> CREATE TABLE IF NOT EXISTS foobar ( id int, text varchar(40) );
> 
> for i in range(1200300):
> INSERT INTO foobar (id, text) VALUES ({i}, 'some string');
> CREATE VIEW foobar_{i} as select * from foobar where id={i};
> 
> Couldn't be any simpler. 
> [general slowness]
> 
> What's even stranger is dropping performance: DROP TABLE foobar CASCADE;. 
> First of all, had to
> increase locks to allow it to finish, otherwise it was quickly bailing 
> because of "too little shared memory".
> alter system set  max_locks_per_transaction=4;
> 
> But even after that, it took almost 7 hours and crashed:
> 
> 2022-09-13 23:16:31.113 UTC [1] LOG:  server process (PID 404) was terminated 
> by signal 9: Killed
> 
> After updating Postgres to 14.5, it crashed in a bit different way:
> 
> 2022-09-15 19:20:26.000 UTC [67] LOG:  checkpoints are occurring too 
> frequently (23 seconds apart)
> 2022-09-15 19:20:26.000 UTC [67] HINT:  Consider increasing the configuration 
> parameter "max_wal_size".
> 2022-09-15 19:20:39.058 UTC [1] LOG:  server process (PID 223) was terminated 
> by signal 9: Killed
> 2022-09-15 19:20:39.058 UTC [1] DETAIL:  Failed process was running: drop 
> table foobar cascade;
> 
> Wihout the views, table can be dropped in 20ms. 

You misconfigured your operating system and didn't disable memory overcommit, 
so you got killed
by the OOM killer.  Basically, the operation ran out of memory.

Yours,
Laurenz Albe

Milions of views - performance, stability

[Hubert Rutkowski]

Hello! I have written python program to benchmark view efficiency,
because in our platform they have a role to play and we noticed the
performance is less than expected.
Basically, benchmark creates table:

CREATE TABLE IF NOT EXISTS foobar ( id int, text varchar(40) );

for i in range(1200300):
INSERT INTO foobar (id, text) VALUES ({i}, 'some string');
CREATE VIEW foobar_{i} as select * from foobar where id={i};

Couldn't be any simpler. Postgres 13.1 running in docker, on Ubuntu
20. However, noticed that performance of certain commands is strangely
slow:
- dumping through pg_dump to tar took 13 minutes. Same table but
without views: less than 1 second.
- restoring through pg_restore took 147 minutes. Same table but
without views: half a second.

In other situation (not observed by me) the dumping process of real
world db with not only 1.2M empty views but in addition gigabytes of
data in rows, lasted for many many hours, and ultimately had to be
stopped.


What's even stranger is dropping performance: DROP TABLE foobar
CASCADE;. First of all, had to increase locks to allow it to finish,
otherwise it was quickly bailing because of "too little shared
memory".
alter system set  max_locks_per_transaction=4;

But even after that, it took almost 7 hours and crashed:

2022-09-13 23:16:31.113 UTC [1] LOG:  server process (PID 404) was
terminated by signal 9: Killed
2022-09-13 23:16:31.113 UTC [1] DETAIL:  Failed process was running:
drop table foobar cascade;
2022-09-13 23:16:31.115 UTC [1] LOG:  terminating any other active
server processes
2022-09-13 23:16:31.115 UTC [1247] WARNING:  terminating connection
because of crash of another server process
2022-09-13 23:16:31.115 UTC [1247] DETAIL:  The postmaster has
commanded this server process to roll back the current transaction and
exit, because another server process exited abnormally and possibly
corrupted shared memory.
2022-09-13 23:16:31.117 UTC [97] HINT:  In a moment you should be able
to reconnect to the database and repeat your command.
2022-09-13 23:16:31.136 UTC [1248] FATAL:  the database system is in
recovery mode
2022-09-13 23:16:31.147 UTC [1249] FATAL:  the database system is in
recovery mode
2022-09-13 23:16:31.192 UTC [1] LOG:  all server processes terminated;
reinitializing
2022-09-13 23:16:31.819 UTC [1250] LOG:  database system was
interrupted; last known up at 2022-09-13 23:15:47 UTC
2022-09-13 23:16:34.959 UTC [1250] LOG:  database system was not
properly shut down; automatic recovery in progress
2022-09-13 23:16:34.965 UTC [1250] LOG:  redo starts at 2/3A3FEEC8
2022-09-13 23:16:36.421 UTC [1250] LOG:  invalid record length at
2/5F355008: wanted 24, got 0
2022-09-13 23:16:36.421 UTC [1250] LOG:  redo done at 2/5F354FD0
2022-09-13 23:16:37.166 UTC [1] LOG:  database system is ready to
accept connections

After updating Postgres to 14.5, it crashed in a bit different way:

2022-09-15 19:20:26.000 UTC [67] LOG:  checkpoints are occurring too
frequently (23 seconds apart)
2022-09-15 19:20:26.000 UTC [67] HINT:  Consider increasing the
configuration parameter "max_wal_size".
2022-09-15 19:20:39.058 UTC [1] LOG:  server process (PID 223) was
terminated by signal 9: Killed
2022-09-15 19:20:39.058 UTC [1] DETAIL:  Failed process was running:
drop table foobar cascade;


Wihout the views, table can be dropped in 20ms.

There must be something inherently slow in the way that Postgres
manages views. I know that under the hood, views are like
table+relation to parent table, so it could be
compared to having about million of tables. They are not that light,
aren't they?
Probably the issue is made worse because of atomicity: dropping the
foobar table with cascade needs to have all views dropped first, in
transaction. But why handling them would be so slow?


Assuming the above is true, I'm wondering if there's a way to improve
the performance of Postgres commands like (the most important) backup
and restore, in situation
of so many views. Dropping table is not that important, but would be
good to have it working too, ie. by first deleting the views in
batches (my idea, will test).
But backups and restores must be faster and reliable in order to
implement one feature in our platform.
Perhaps adding index on views, so that it can quickly assess how many
there are and to lock them, disabling something, tweaking some perf
option... throwing ideas.

Please advice. Or maybe there's no hope to make this behave better :)

Regards,
Hubert