Re: LWlock:LockManager waits

you mean too many concurrent sessions trying to acquire lock on same relation , then waiting on "LockManager" LWlock,right? this contention occurred on parsing ,planning, or execute step ? Thanks, James Laurenz Albe 於 2024年4月9日週二 下午12:31寫道： > On Tue, 2024-04-09 at 11:07 +080

LWlock:LockManager waits

we found sometimes , with many sessions running same query "select ..." at the same time, saw many sessions waiting on "LockManager". for example, pg_stat_activity show. It's a production server, so no enable trace_lwlocks flag. could you direct me what's the possible reason and how to reduce

Re: FW: huge SubtransSLRU and SubtransBuffer wait_event

rows) Thanks, James Alvaro Herrera 於 2024年3月1日週五 下午3:35寫道： > On 2024-Mar-01, James Pang wrote: > > > one question: > > we need to increase all SLRU buffers together , MULTIXACT, XACT, > > Subtrans, COMMIT TS , for example, got all of them doubled based on > &g

Re: FW: huge SubtransSLRU and SubtransBuffer wait_event

eed to increase all SLRU buffers together , MULTIXACT, XACT, Subtrans, COMMIT TS , for example, got all of them doubled based on existing size ? or only increase Subtrans , or Subtrans and multixact ? Thanks, James James Pang (chaolpan) 於 2024年3月1日週五 下午2:45寫道： > > > -Original Message

generic plan generate poor performance

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

Fwd: extend statistics help reduce index scan a lot of shared buffer hits.

Postgresql 14.8, Redhat8. looks like have to create extend statistics on indexed and joined columns to make join filters pushed down to secondary index scan in nestloop, and the shared buffer hits show big difference. is it expected ? SELECT

extend statistics help reduce index scan a lot of shared buffer hits.

Postgresql 14.8, Redhat8. looks like have to create extend statistics on indexed and joined columns to make join filters pushed down to secondary index scan in nestloop, and the shared buffer hits show big difference. is it expected ? SELECT

Re: FW: huge SubtransSLRU and SubtransBuffer wait_event

> > > From this link, looks like "onfigurable buffer pool and partitioning the > SLRU lock" is one the plan, maybe from v18,19 version, > https://www.postgresql.org/message-id/202402221843.ibzvpndbacbi@alvherre.pgsql > James > *From:* James Pang (chaolpan

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

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 big

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

BC 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

sql statement not using all primary key values and poor performance

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

RE: huge SubtransSLRU and SubtransBuffer wait_event

We finally identified the cause, a pl/pgsql procedure proc1 (for 1…5000 loop call proc2()); proc2 (begin ..exception..end); at the same time, more than 200 sessions coming in milliseconds and do same query during the “call proc1 long running transaction”. The code change and cutdown the

RE: huge SubtransSLRU and SubtransBuffer wait_event

e time , then quickly see MultiXactoffset and SubtransSLRU increased very quickly. Possible to increase Subtrans SLRU buffer size ?PL/PGSQL proc1--> procedure2(updates table) it use substransation in procedure2 ,right? Thanks, James -Original Message----- From: James Pang (chaolpan) Sent:

RE: huge SubtransSLRU and SubtransBuffer wait_event

a subtransaction like pg_background ? for JDBC driver option to simulate statement level rollback, could you share more details ? Thanks, James -Original Message- From: Laurenz Albe Sent: Thursday, February 1, 2024 8:42 PM To: James Pang (chaolpan) ; pgsql-performance@lists.postgresql.org

huge SubtransSLRU and SubtransBuffer wait_event

Hi, We have a Postgresqlv14.8 server, client use Postgresql JDBC connections, today, our server see a lot of "SubtransBuffer" and "SubtransSLRU" wait_event. Could you help direct me what's the possible cause and how to resolve this waits ? Thanks, James

RE: simple query running long time within a long transaction.

transaction. Am 17.11.23 um 09:10 schrieb James Pang (chaolpan): > > Hi, > >    We found one simple query manually run very fast(finished in > several milliseconds), but there are 2 sessions within long > transaction to run same sql with same bind variables took tens of

simple query running long time within a long transaction.

Hi, We found one simple query manually run very fast(finished in several milliseconds), but there are 2 sessions within long transaction to run same sql with same bind variables took tens of seconds. Manually run this sql only show <100 shared_blks_hit and very small reads, but for these 2

query pg_stat_ssl hang 100%cpu

Hi, PGv14.8, OS RHEL8, no SSL enabled in this database, we have a lot of client sessions who check it's ssl state by query, all other sessions got done very quickly, but only 1 session hang there in 100% cpu tens of hours, even pg_terminate_backend does not make it stopped either. It

query pg_stat_ssl hang 100%cpu

Hi, PGv14.8, OS RHEL8, no SSL enabled in this database, we have a lot of client sessions who check it's ssl state by query, all other sessions got done very quickly, but only 1 session hang there in 100% cpu. It looks like abnormal. select ssl from pg_stat_ssl where

RE: Postgresql equal join on function with columns not use index

11:01 PM To: Tom Lane Cc: James Pang (chaolpan) ; pgsql-performance@lists.postgresql.org Subject: Re: Postgresql equal join on function with columns not use index út 13. 6. 2023 v 16:17 odesílatel Pavel Stehule mailto:pavel.steh...@gmail.com>> napsal: út 13. 6. 2023 v 15:50 odesílat

extended statistics n-distinct on multiple columns not used when join two tables

Hi, When join two table on multiple columns equaljoin, rows estimation always use selectivity = multiplied by distinct multiple individual columns, possible to use extended n-distinct statistics on multiple columns? PG v14.8-1, attached please check test case with details. Thanks,

RE: Postgresql equal join on function with columns not use index

HONENUMBER,'[^0-9]',''), Index on a.phonenumber got used. Thanks, James Pang -Original Message- From: Tom Lane Sent: Monday, June 12, 2023 9:19 PM To: James Pang (chaolpan) Cc: pgsql-performance@lists.postgresql.org Subject: Re: Postgresql equal join on function with columns not

Postgresql equal join on function with columns not use index

Hi, We migrate from Oracle to Postgresql14.8, one SQL has regression in Postgres run in 5800 milliseconds in Postgresql v14.8, but the same SQL got done in several hundred milliseconds in Oracle database. With multiple table JOINs, if the join condition is

wrong rows estimation by hash join

How does hash join estimation rows ? pg v14, it make wrong rows estimation then leave nest loop lef join that make poor sql plan. A -> Nested Loop Left Join (cost=171112.69..475856.90 rows=1 width=521) ->

RE: thousands of CachedPlan entry per backend

n 2 blocks; 200 free (0 chunks); 1848 used: xxx CachedPlanQuery: 2048 total in 2 blocks; 704 free (0 chunks); 1344 used From: Pavel Stehule Sent: Friday, June 2, 2023 12:57 PM To: James Pang (chaolpan) Cc: Laurenz Albe ; pgsql-performance@lists.postgresql.org Subject: Re:

RE: thousands of CachedPlan entry per backend

nks); 1744 used: xxx CachedPlanSource: 2048 total in 2 blocks; 200 free (0 chunks); 1848 used: xxx CachedPlanQuery: 2048 total in 2 blocks; 704 free (0 chunks); 1344 used Thanks, James -Original Message- From: Laurenz Albe Sent: Thursday, June 1, 2023 8:48 PM To:

RE: thousands of CachedPlan entry per backend

PM To: Laurenz Albe Cc: James Pang (chaolpan) ; pgsql-performance@lists.postgresql.org Subject: Re: thousands of CachedPlan entry per backend Hi čt 1. 6. 2023 v 8:53 odesílatel Laurenz Albe mailto:laurenz.a...@cybertec.at>> napsal: On Thu, 2023-06-01 at 03:36 +, James Pang (chaolpan)

RE: DML sql execution time slow down PGv14 compared with PGv13

Did you check pg_stat_statements ? looks like select better, but DML decreased, so average tps looks similar . -Original Message- From: David Rowley Sent: Thursday, December 15, 2022 6:42 PM To: James Pang (chaolpan) Cc: pgsql-performance@lists.postgresql.org Subject: Re: DML sql

RE: DML sql execution time slow down PGv14 compared with PGv13

Did you check pg_stat_statements ? looks like select some better , but DML decreased. -Original Message- From: David Rowley Sent: Thursday, December 15, 2022 6:42 PM To: James Pang (chaolpan) Cc: pgsql-performance@lists.postgresql.org Subject: Re: DML sql execution time slow down

RE: DML sql execution time slow down PGv14 compared with PGv13

Actually, with our application that’s JDBC clients instead of pgbench , we saw similar DML exec_time increase too. From: James Pang (chaolpan) Sent: Thursday, December 15, 2022 4:45 PM To: Samed YILDIRIM Cc: pgsql-performance@lists.postgresql.org Subject: RE: DML sql execution time slow down

RE: DML sql execution time slow down PGv14 compared with PGv13

, primary keys 640.91 s). Thanks, James From: Samed YILDIRIM Sent: Thursday, December 15, 2022 4:38 PM To: James Pang (chaolpan) Cc: pgsql-performance@lists.postgresql.org Subject: Re: DML sql execution time slow down PGv14 compared with PGv13 Hello James, Could you please add configurations

DML sql execution time slow down PGv14 compared with PGv13

Hi, We had some load test ( DML inserts/deletes/updates) and found that PGV14 slow down 10-15% compared with PGV13. Same test server, same schema tables and data. From pg_stat_statements, sql exec_time, we did found similar mean_exec_time increased from 20%-30% with same SQL statements.

DML sql execution time slow down PGv14 compared with PGv13

Hi, We had some load test ( DML inserts/deletes/updates/ on tens of hash partition tables) and found that PGV14 slow down 10-15% compared with PGV13. Same test server, same schema tables and data. From pg_stat_statements, sql exec_time, we did found similar mean_exec_time increased from

wrong rows and cost estimation when generic plan

Hi, It's a prepared sql statement on a non-partitioned table , 16millions tuples and multiple indexes on this table. pk_x primary key (aid,bid,btype) all 3 cols are bigint datatype, there is another index idx_x(starttime,endtime) , both cols are "timestamp(0) without time zone".

RE: wrong rows and cost estimation when generic plan

al Message- From: David Rowley Sent: Tuesday, December 6, 2022 1:59 PM To: James Pang (chaolpan) Cc: pgsql-performance@lists.postgresql.org; jamespang...@gmail.com Subject: Re: wrong rows and cost estimation when generic plan On Tue, 6 Dec 2022 at 18:28, James Pang (chaolpan) wrote: >->

RE: wrong rows and cost estimation when generic plan

Hi, It's a prepared sql statement on a non-partitioned table , 16millions tuples and multiple indexes on this table. pk_x primary key (aid,bid,btype) all 3 cols are bigint datatype, there is another index idx_x(starttime,endtime) , both cols are "timestamp(0) without time zone".

RE: wrong rows and cost estimation when generic plan

Hi, It's a prepared sql statement on a non-partitioned table , 16millions tuples and multiple indexes on this table. pk_x primary key (aid,bid,btype) all 3 cols are bigint datatype, there is another index idx_x(starttime,endtime) , both cols are "timestamp(0) without time zone".

RE: Postgresql JDBC process consumes more memory with partition tables update delete

for non-partitioned tables with same table attributes and data volume size. -Original Message- From: Justin Pryzby Sent: Wednesday, September 7, 2022 12:15 AM To: James Pang (chaolpan) Cc: pgsql-performance@lists.postgresql.org Subject: Re: Postgresql JDBC process consumes more memory than

RE: Postgresql JDBC process consumes more memory than psql client

- From: Justin Pryzby Sent: Wednesday, September 7, 2022 12:15 AM To: James Pang (chaolpan) Cc: pgsql-performance@lists.postgresql.org Subject: Re: Postgresql JDBC process consumes more memory than psql client On Tue, Sep 06, 2022 at 04:15:03AM +, James Pang (chaolpan) wrote: > We mak

RE: Postgresql JDBC process consumes more memory than psql client

is about 128GB. Thanks, James -Original Message- From: James Pang (chaolpan) Sent: Monday, September 5, 2022 8:52 PM To: Justin Pryzby Cc: pgsql-performance@lists.postgresql.org Subject: RE: Postgresql JDBC process consumes more memory than psql client PG V13, yes JDBC use prepared state

RE: Postgresql JDBC process consumes more memory than psql client

PG V13, yes JDBC use prepared statements , from psql use pruned ,but even all partitions it NOT consumes too much memory. Any idea how to print SQL plan from JDBC driver ? -Original Message- From: Justin Pryzby Sent: Monday, September 5, 2022 8:47 PM To: James Pang (chaolpan) Cc

Postgresql JDBC process consumes more memory than psql client

We run same update or delete SQL statement " DELETE FROM ... WHERE ... " the table is a hash partition table (256 hash partitions). When run the sql from Postgresql JDBC driver, it soon increased to 150MB memory (RES filed from top command), but when run the same SQL from psql , it

RE: alter table xxx set unlogged take long time

e- From: Joe Conway Sent: Wednesday, July 27, 2022 11:02 PM To: Tom Lane Cc: James Pang (chaolpan) ; Jim Mlodgenski ; pgsql-performance@lists.postgresql.org Subject: Re: alter table xxx set unlogged take long time On 7/27/22 10:46, Tom Lane wrote: > Joe Conway writes: >> Then (c

RE: alter table xxx set unlogged take long time

….” With wal_level=minimal, is it ok to make copy and create index without logging ? James From: Jim Mlodgenski Sent: Tuesday, July 26, 2022 8:53 PM To: James Pang (chaolpan) Cc: Tom Lane ; pgsql-performance@lists.postgresql.org Subject: Re: alter table xxx set unlogged take long time On Tue, Jul 26

RE: alter table xxx set unlogged take long time

Without step 3 , copy data take long time. Use wal_level=minimal can help make COPY load data without logging ? Thanks, James -Original Message- From: Tom Lane Sent: Tuesday, July 26, 2022 8:43 PM To: James Pang (chaolpan) Cc: Jim Mlodgenski ; pgsql-performance

RE: alter table xxx set unlogged take long time

long time ,especially for large tables. Thank, James From: Jim Mlodgenski Sent: Tuesday, July 26, 2022 8:21 PM To: James Pang (chaolpan) Cc: pgsql-performance@lists.postgresql.org Subject: Re: alter table xxx set unlogged take long time On Tue, Jul 26, 2022 at 4:53 AM James Pang (chaolpan

alter table xxx set unlogged take long time

Hi , We have PG v13.6 in RHEL8.4, we try to set table unlogged before load data. There are a lot of existing data in this table, when 'alter table xxx set unlogged', we found it take long time and spend time on IO datafileread. Is it expected? Thanks, James

RE: partition pruning only works for select but update

ay to improve partition table update for large tables, and as first step of tuning , we try to reduce partition count for these tables in PGV13. James -Original Message- From: Tom Lane Sent: Friday, July 1, 2022 9:18 PM To: Justin Pryzby Cc: James Pang (chaolpan) ; pgsq

RE: partition pruning only works for select but update

Pang (chaolpan) Cc: pgsql-performance@lists.postgresql.org Subject: Re: partition pruning only works for select but update "James Pang (chaolpan)" writes: > But when > Explain update table set .. where partitionkey between to_timestamp() and > to_timestamp(); &g

RE: partition pruning only works for select but update

Pang (chaolpan) Cc: pgsql-performance@lists.postgresql.org Subject: Re: partition pruning only works for select but update "James Pang (chaolpan)" writes: > But when > Explain update table set .. where partitionkey between to_timestamp() and > to_timestamp(); > It still

partition pruning only works for select but update

Hi, We have a table have range partition (about 5K partitions) , when Explain select count(*) from table where partitionkey between to_timestamp() and to_timestamp(); It show Aggregate (cost=15594.72..15594.73 rows=1 width=8) -> Append (cost=0.15..15582.00 rows=5088 width=0)

RE: reindex option for tuning load large data

constraints. The questions is instead of drop index and create index, we check update pg_index set indisready=false and reindex again after load. From: Jeff Janes Sent: Sunday, June 19, 2022 4:01 AM To: James Pang (chaolpan) Cc: pgsql-performance@lists.postgresql.org Subject: Re: reindex option

RE: reindex option for tuning load large data

dex the one index done in serial instead of parallel ? Compared with “set max_maintain_parallel_workers, and run CREATE INDEX …” , which is faster ? Thanks, From: Vitalii Tymchyshyn Sent: Saturday, June 18, 2022 11:49 AM To: James Pang (chaolpan) Cc: pgsql-performance@lists.postgresql.org Subj

reindex option for tuning load large data

Hi , We plan to migrate large database from Oracle to Postgres(version 13.6, OS Redhat8 Enterprise), we are checking options to make data load in Postgres fast. Data volume is about several TB, thousands of indexes, many large table with partitions. We want to make data load running fast

RE: postgres backend process hang on " D " state

to support security compliance requirements. systemctl stop fapolicyd , after that, everything go back to be normal soon. Regards, James -Original Message----- From: James Pang (chaolpan) Sent: Monday, May 30, 2022 11:00 AM To: Tom Lane Cc: Justin Pryzby ; pgsql-performance@lists.post

RE: postgres backend process hang on " D " state

Maybe any bugs from these extensions ? I can try that removing all extensions, but we need these extensions. Thanks, James -Original Message- From: Tom Lane Sent: Monday, May 30, 2022 10:21 AM To: James Pang (chaolpan) Cc: Justin Pryzby ; pgsql-performance@lists.postgresql.org

RE: postgres backend process hang on " D " state

192.168.205.133(48754) SELECT 1070260 -Z ?00:05:02 [postmaster] ... Strace / gdb will hang there too for trace a process. Regards, James -Original Message- From: Justin Pryzby Sent: Monday, May 30, 2022 10:20 AM To: James Pang (chaolpan) Cc: pgsql

RE: postgres backend process hang on " D " state

- From: Justin Pryzby Sent: Sunday, May 29, 2022 11:02 PM To: James Pang (chaolpan) Cc: pgsql-performance@lists.postgresql.org Subject: Re: postgres backend process hang on " D " state On Sun, May 29, 2022 at 01:20:12PM +0000, James Pang (chaolpan) wrote: >We have a per

postgres backend process hang on " D " state

Hi, We have a performance test on Postgresql 13.4 on RHEL8.4 , just after connection storm in ( 952 new connections coming in 1 minute), a lot of backends start on " D " state, and when more sessions got disconnected, they do not exit successfully, instead became "defunct". No errors