Performance difference between Primary & Secondary in the query execution

Hello Experts! We have a PgSQL instance running with HA (secondary is being in sync with streaming replication). Both the ends, we have same version, but not sure a few SQLs behave badly in the secondary: Primary: PostgreSQL 12.20 (Ubuntu 12.20-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled b

Re: Help in dealing with OOM

he error: FATAL: hot standby is not possible because max_connections = 4000 is a lower setting than on the master server (its value was 1) If I am clubbing multiple things, sorry for the clutter. Regards Siraj On Tue, Oct 15, 2024 at 12:39 AM Joe Conway wrote: > On 10/14/24 14:37, Si

Help in dealing with OOM

Hello Experts! My secondary instance has been unstable today. The service is crashing with Out of Memory. Please see below error (/var/log/postgresql/postgresql-2024-10-14.log): 10.2.52.50,2024-10-14 15:28:11 IST,686671,finance_revamp,finance_b2b,1,LOG: duration: 1148.527 ms statement: SELECT "

Re: Need assistance in converting subqueries to joins

=IS_SEC_FILT.IS_PROJ_ID) (cost=0.72 rows=1) (actual time=0.001..0.001 rows=1 loops=517350)\n' On Fri, Sep 20, 2024 at 9:49 AM Adrian Klaver wrote: > On 9/19/24 21:07, Siraj G wrote: > > Hello Tech gents! > > > > I am sorry if I am asking the wrong question to this gr

Need assistance in converting subqueries to joins

Hello Tech gents! I am sorry if I am asking the wrong question to this group, but wanted assistance in converting a query replacing subqueries with joins. Please find the query below (whose cost is very high): select em_exists_id from IS_SEC_FILT WHERE (IS_SEC_FILT_GUID) NOT IN (SELECT IS_OBJ_GU

Problem with a query

Hello! We have a couple of queries that all of a sudden became rather slow. I took explain analyze from one of the SQLs as bdlow. Can you please check and suggest if anything can be done? '-> Table scan on (actual time=0.019..71.526 rows=38622 loops=1)\n -> Aggregate using temporary table (act

Re: Problem with a Query

Thanks Tom. Collecting full stats on the tables involved corrected the execution. On Tue, Aug 13, 2024 at 9:57 AM Tom Lane wrote: > Siraj G writes: > > We migrated a PgSQL database from Cloud SQL to compute engine and since > > then there is a SQL we observed taking a long t

Problem with a Query

Hello! We migrated a PgSQL database from Cloud SQL to compute engine and since then there is a SQL we observed taking a long time. After some study, I found that the SQL is using NESTED LOOP where the cost is too high. I tried VACUUM FULL and ANALYZE, but to no avail. Only when I disabled the nest

User roles for gathering performance metrics data

Greetings! I am from Oracle background. In Oracle, we grant select_catalog_role or select any dictionary role to users who want to study performance data. I am trying to get similar information on the roles or privileges in PgSQL that we might want to request to investigate the performance problem

Fwd: pgadmin not opening in concurrent sessions

Hello! I installed PgAdmin4 8.2 in a Windows platform, for all the users. The issue we are having is, if any user launches it, nobody else can launch it. Eg., userB logs in and launches PgAdmin, it gets launched. Now if UserA and UserB tried to login, they get nothing, no event/response from Windo

Backup certain months old data

Hello! I would like to know how we can backup certain months old data from PgSQL and then delete it. The intent is to backup everything that is older than 2 quarters to a blob storage and delete it, to improve performance and reduce billing. Regards Siraj