Re: Performance difference between Primary & Secondary in the query execution

[Muhammad Usman Khan <usman.k@xxxxxxxxxxx>]

Hi,
You can check and verify the following points:

*Check the cpu resources on both primary and secondary

*Check the execution plans on both the primary and secondary by running EXPLAIN (ANALYZE, BUFFERS) for the problematic queries like the following

explain (analyze,buffers) select * from test ;
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..22.70 rows=1270 width=36) (actual time=0.013..0.014 rows=1 loops=1)
   Buffers: shared hit=1
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.082 ms
 Execution Time: 0.033 ms
(6 rows)

*check vacuuming and bloating using the following query

select * from pg_stat_user_tables;

select * from pg_stat_all_tables;

*Check the shared_buffers, work_mem, and maintenance_work_mem settings on both instances.

*Compare the disk I/O performance using the following query
select * from pg_stat_bgwriter;

*Check for any replication lag (pg_stat_replication on the primary) to ensure the secondary isn't falling behind.

On Thu, 17 Oct 2024 at 19:08, Siraj G <tosiraj.g@xxxxxxxxx> wrote:

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 by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit

OS: Ubuntu 20.04.6 LTS \n \l

Secondary:

ostgreSQL 12.20 (Ubuntu 12.20-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit

OS: Ubuntu 20.04.6 LTS \n \l

The application consumes more data from secondary, hence the server has extra vCPUs.

Can you please advise what needs to be checked.

FYI, I am attaching the query with the different execution plans.

Regards

Siraj