Real Time Scenario - Step By Step Slow Running Query Tuning(performance Tuning)

                        Real Time Scenario  - Step By Step Slow Running Query Tuning(performance Tuning)

1) We got a requirement that below query is running slow.

SELECT creationtime, serviceId FROM (

SELECT DISTINCT ON (serviceuuid, n.siteId) e.id AS serviceId, creationtime, status 

FROM NotificationMessages n

JOIN EquinetServices e ON n.serviceuuid = e.uuid AND n.siteid = e.siteid

JOIN sites_view sv ON sv.id = e.siteid AND NOT sv.decommissioned

WHERE e.id IN (SELECT instanceId FROM Alarms WHERE ruleName=$1 AND outAlarmTime IS NULL)

AND notificationTypeId=$2

ORDER BY serviceuuid, n.siteId, creationtime DESC, n.id DESC

) notalarmed

2) Checked the tables and indexes used in this query -

3)  Got the $1 and $2 values from tables -

4) Generated the explain plan for the above query -

here we can see the total execution time is 1178 milliseconds.

5) Fix - Index creation - upon investigation, we found that below indexes are needed to improve the performance -

6) Explain plan after index creation - 

7) Summary of the issue - 

The query was taking approximately 1180 ms to execute. The slowness was caused by sequential scans and high sort operations on large tables such as NotificationMessages, Alarms, and Sites. This occurred because required composite indexes were missing on key filtering and join columns.

8) Root Cause of the issue -

• No supporting indexes for serviceuuid, siteid, and creationtime ordering
• Missing indexes on notificationtypeid filter and alarm rule filtering
• Inefficient site-state filtering due to lack of index support
• As a result,  query performed full-table scans and expensive sort operation.

9) Fix Applied - 

To improve the query performance, the following indexes were created:

1. NotificationMessages Table –

                 CREATE INDEX idx_notificationmessages_type_uuid_site_ctime

                ON NotificationMessages (notificationtypeid, serviceuuid, siteid, creationtime DESC);

 

2. Alarms Table –

                 CREATE INDEX idx_alarms_rule_out_instance

                ON Alarms (ruleName, outAlarmTime, instanceId);

 

3. Sites Table –

                 CREATE INDEX idx_sites_notdeleted_notdecomm

                ON sites (deleted, decommissioned, id);

 

4. EquinetServices

                 CREATE INDEX idx_equinetservices_uuid_siteid

                    ON EquinetServices(uuid, siteid);

10. Final Result - 

    

    

Metric	Before	After
Execution Time	1180 ms	5.862 ms

        

Also, After applying the new indexes, please run the following commands:

VACUUM ANALYZE NotificationMessages;

VACUUM ANALYZE Alarms;

VACUUM ANALYZE EquinetServices;

VACUUM ANALYZE sites;

 

These commands will refresh the table statistics so query planner can fully utilize the newly created indexes. If you don’t have permissions to run them, please let me know and can run them right after the index creation.

============================The End================================