How to determine if a database query is slow?

A long response from the DBMS (timeout or SLA breach), high CPU and RAM load on the database server, table locks.

import time
import psycopg2 # Example for PostgreSQL

def execute_query(cursor, query):
    start_time = time.time()
    cursor.execute(query)
    end_time = time.time()
    duration = end_time - start_time
    print(f"Query executed in {duration:.4f} seconds")
    if duration > 1.0: // Example threshold of 1 second
        print("Warning: Query took longer than expected!")

Analysis of DBMS logs (slow query logs), use of monitoring tools (e.g., pg_stat_statements for PostgreSQL, MySQL Enterprise Monitor for MySQL, DataDog, New Relic), query profiling via explain plan.

Example of explain plan output for PostgreSQL:

EXPLAIN ANALYZE SELECT * FROM users WHERE status = 'active' AND registration_date < '2023-01-01';

It shows the execution plan: order of operations, used indexes, number of rows returned, and time taken for each step.

Tools:

Type	Example	Functionality
Built-in DBMS tools	pg_stat_statements (PostgreSQL), Performance Schema (MySQL)	Collect query statistics, identify slow queries
External	DataDog, New Relic, Zabbix	Comprehensive DBMS performance monitoring
Profilers	explain plan (all DBMS)	Analyze execution of specific queries