SOP-MON-03: Query performance analysis

Date: 12th January 2022
MOSIP Version: v1.1.4.x and v1.1.5.x

Description:
2 ways we can run the query to check teh perfomace of the query

I)Here are the steps to analyze re-processor query performance using pgAdmin.

• Connect to the database using the pgAdmin application

• Run the reprocessor query

• Check the query performance analysis output

Steps

1. PG Admin should be connected via the VPN (if the database is accessible over VPN)

2. Run the below query in reg proc schema in 'Explain Analyze' mode as shown below and make sure to select all checkboxes:
   
   

   Open

   
   select * from registration where latest_trn_dtimes < '2022-01-012 01:03:19.184348+00' and latest_trn_status_code in ('SUCCESS', 'IN_PROGRESS', 'REPROCESS') and reg_process_retry_count <= 300 order by upd_dtimes asc limit 278

   Please add the values in the question as per your configuration for analysis.

3. Once the query is executed you will be able to see the analysis

   Open

   

II)EXPLAIN (ANALYZE TRUE, TIMING TRUE, FORMAT JSON)
SELECT * FROM table-name and if anywhere conditions apply the query

NOTE: Prefix the below line with the query

EXPLAIN (ANALYZE TRUE, TIMING TRUE, FORMAT JSON)

query

ex:

Open