Query Profiling Across Database Engines

-- PostgreSQL: EXPLAIN ANALYZE with all options

-- Full diagnostic output:
EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE, FORMAT TEXT)
SELECT c.name, COUNT(o.id) AS order_count, SUM(o.total) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.order_date >= '2024-01-01'
GROUP BY c.name
HAVING SUM(o.total) > 1000
ORDER BY total_spent DESC
LIMIT 20;

-- Key metrics to read:
-- actual time=0.015..45.230   (startup..total in ms)
-- rows=20                     (actual rows retu
...

-- pg_stat_statements: find your slowest queries
-- (Must be enabled in postgresql.conf)
-- shared_preload_libraries = 'pg_stat_statements'

SELECT
    LEFT(query, 80) AS query_preview,
    calls,
    ROUND(total_exec_time::numeric / 1000, 2) AS total_seconds,
    ROUND(mean_exec_time::numeric, 2) AS avg_ms,
    ROUND(max_exec_time::numeric, 2) AS max_ms,
    rows AS total_rows,
    ROUND((shared_blks_hit::decimal /
        NULLIF(shared_blks_hit + shared_blks_read, 0)) * 100, 1)
        AS cach
...

-- MySQL: Performance Schema and EXPLAIN ANALYZE

-- Enable performance monitoring:
-- performance_schema = ON  (in my.cnf)

-- MySQL 8.0+ EXPLAIN ANALYZE (actually runs the query):
-- EXPLAIN ANALYZE
-- SELECT c.name, COUNT(*) FROM customers c
-- JOIN orders o ON c.id = o.customer_id
-- GROUP BY c.name LIMIT 10;

-- MySQL slow query log:
-- slow_query_log = ON
-- slow_query_log_file = /var/log/mysql/slow.log
-- long_query_time = 1  (log queries > 1 second)

-- Find slow queries in Performance S
...

-- SQL Server: Query Store and Execution Plans

-- Enable Query Store:
-- ALTER DATABASE mydb SET QUERY_STORE = ON;

-- Find top resource-consuming queries:
-- SELECT TOP 20
--     qs.query_id,
--     qt.query_sql_text,
--     rs.avg_duration / 1000.0 AS avg_ms,
--     rs.count_executions,
--     rs.avg_logical_io_reads,
--     rs.avg_cpu_time / 1000.0 AS avg_cpu_ms
-- FROM sys.query_store_runtime_stats rs
-- JOIN sys.query_store_plan qp ON rs.plan_id = qp.plan_id
-- JOIN sys.query_store_query q
...

-- PROFILING WORKFLOW: systematic performance tuning

-- Step 1: IDENTIFY slow queries
-- Use pg_stat_statements, slow query log, or APM tools
-- Focus on: highest total time (not just slowest single execution)

-- Step 2: ANALYZE the execution plan
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 42 AND status = 'pending';
-- Look for: Seq Scan, high actual rows, buffer reads

-- Step 3: CHECK statistics freshness
SELECT relname, last_analyze, last_autoanalyze,
       n_live_
...