Metrics Guide¶

-- Find queries by specific user
SELECT 
    r.rolname AS username,
    COUNT(*) AS query_count,
    SUM(calls) AS total_calls
FROM pg_stat_insights s
JOIN pg_roles r ON s.userid = r.oid
GROUP BY r.rolname
ORDER BY total_calls DESC;

-- Find queries by database
SELECT 
    d.datname AS database,
    COUNT(*) AS query_count,
    SUM(calls) AS total_calls,
    ROUND(SUM(total_exec_time)::numeric, 2) AS total_time_ms
FROM pg_stat_insights s
JOIN pg_database d ON s.dbid = d.oid
GROUP BY d.datname
ORDER BY total_time_ms DESC;

-- Compare top-level vs nested queries
SELECT 
    toplevel,
    COUNT(*) AS query_count,
    SUM(calls) AS total_calls,
    ROUND(AVG(mean_exec_time)::numeric, 2) AS avg_time_ms
FROM pg_stat_insights
GROUP BY toplevel;

-- Track specific query over time
SELECT 
    queryid,
    query,
    calls,
    total_exec_time,
    stats_since
FROM pg_stat_insights
WHERE queryid = 1234567890;

-- Search for queries by pattern
SELECT 
    query,
    calls,
    mean_exec_time
FROM pg_stat_insights
WHERE query LIKE '%my_table%'
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Find queries that plan frequently
SELECT 
    query,
    plans,
    calls,
    ROUND((plans::numeric / NULLIF(calls, 0)), 2) AS plan_per_call_ratio
FROM pg_stat_insights
WHERE plans > 0
ORDER BY (plans::numeric / NULLIF(calls, 0)) DESC
LIMIT 20;

-- Top queries by planning time
SELECT 
    query,
    plans,
    ROUND(total_plan_time::numeric, 2) AS total_plan_ms,
    ROUND(mean_plan_time::numeric, 2) AS avg_plan_ms
FROM pg_stat_insights
WHERE total_plan_time > 0
ORDER BY total_plan_time DESC
LIMIT 20;

-- Find queries with variable planning time
SELECT 
    query,
    plans,
    ROUND(min_plan_time::numeric, 3) AS min_ms,
    ROUND(mean_plan_time::numeric, 3) AS avg_ms,
    ROUND(max_plan_time::numeric, 3) AS max_ms,
    ROUND((max_plan_time / NULLIF(min_plan_time, 1))::numeric, 1) AS variability
FROM pg_stat_insights
WHERE plans > 5
ORDER BY (max_plan_time / NULLIF(min_plan_time, 1)) DESC
LIMIT 20;

-- Queries with inconsistent planning
SELECT 
    query,
    plans,
    ROUND(mean_plan_time::numeric, 2) AS avg_ms,
    ROUND(stddev_plan_time::numeric, 2) AS stddev_ms,
    ROUND((stddev_plan_time / NULLIF(mean_plan_time, 0) * 100)::numeric, 1) AS coefficient_of_variation
FROM pg_stat_insights
WHERE plans > 10
ORDER BY stddev_plan_time DESC
LIMIT 20;

-- Most frequently executed queries
SELECT 
    query,
    calls,
    ROUND(mean_exec_time::numeric, 2) AS avg_ms,
    ROUND((calls * mean_exec_time)::numeric, 2) AS total_time_ms
FROM pg_stat_insights
ORDER BY calls DESC
LIMIT 20;

-- Queries consuming most time
SELECT 
    query,
    calls,
    ROUND(total_exec_time::numeric, 2) AS total_ms,
    ROUND((total_exec_time / SUM(total_exec_time) OVER () * 100)::numeric, 1) AS pct_of_total
FROM pg_stat_insights
ORDER BY total_exec_time DESC
LIMIT 20;

-- Find queries with high variability
SELECT 
    query,
    calls,
    ROUND(min_exec_time::numeric, 2) AS min_ms,
    ROUND(mean_exec_time::numeric, 2) AS avg_ms,
    ROUND(max_exec_time::numeric, 2) AS max_ms,
    ROUND((max_exec_time - min_exec_time)::numeric, 2) AS range_ms,
    CASE 
        WHEN min_exec_time > 0 THEN ROUND((max_exec_time / min_exec_time)::numeric, 1)
        ELSE NULL
    END AS variability_ratio
FROM pg_stat_insights
WHERE calls > 10
ORDER BY (max_exec_time - min_exec_time) DESC
LIMIT 20;

-- Queries with unstable performance
SELECT 
    query,
    calls,
    ROUND(mean_exec_time::numeric, 2) AS avg_ms,
    ROUND(stddev_exec_time::numeric, 2) AS stddev_ms,
    ROUND((stddev_exec_time / NULLIF(mean_exec_time, 0))::numeric, 3) AS relative_stddev
FROM pg_stat_insights
WHERE calls > 20 AND stddev_exec_time > 0
ORDER BY (stddev_exec_time / NULLIF(mean_exec_time, 0)) DESC
LIMIT 20;

-- Queries returning most rows
SELECT 
    query,
    calls,
    rows,
    ROUND((rows::numeric / NULLIF(calls, 0)), 0) AS avg_rows_per_call,
    ROUND(mean_exec_time::numeric, 2) AS avg_ms,
    ROUND((mean_exec_time / NULLIF(rows::numeric / NULLIF(calls, 0), 0))::numeric, 4) AS ms_per_row
FROM pg_stat_insights
WHERE rows > 0
ORDER BY rows DESC
LIMIT 20;

SELECT 
    query,
    shared_blks_hit,
    shared_blks_read,
    ROUND((shared_blks_hit::numeric / 
           NULLIF(shared_blks_hit + shared_blks_read, 0))::numeric, 3) AS cache_hit_ratio,
    pg_size_pretty((shared_blks_read * 8192)::bigint) AS data_from_disk
FROM pg_stat_insights
WHERE (shared_blks_hit + shared_blks_read) > 0
ORDER BY shared_blks_read DESC
LIMIT 20;

-- Find queries using temp files
SELECT 
    query,
    calls,
    temp_blks_read,
    temp_blks_written,
    pg_size_pretty((temp_blks_written * 8192)::bigint) AS temp_written_size
FROM pg_stat_insights
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 20;

-- Find slow I/O operations
SELECT 
    query,
    calls,
    shared_blks_read,
    ROUND(shared_blk_read_time::numeric, 2) AS read_time_ms,
    ROUND((shared_blk_read_time / NULLIF(shared_blks_read, 0))::numeric, 3) AS ms_per_block,
    CASE 
        WHEN shared_blk_read_time / NULLIF(shared_blks_read, 0) > 10 THEN '[SLOW] Very Slow'
        WHEN shared_blk_read_time / NULLIF(shared_blks_read, 0) > 1 THEN '[WARNING] Slow'
        ELSE '[OK] Normal'
    END AS disk_speed
FROM pg_stat_insights
WHERE shared_blks_read > 0
ORDER BY (shared_blk_read_time / NULLIF(shared_blks_read, 0)) DESC
LIMIT 20;

-- Comprehensive WAL analysis
SELECT 
    query,
    calls,
    wal_records,
    wal_fpi,
    pg_size_pretty(wal_bytes::bigint) AS wal_size,
    wal_buffers_full,
    ROUND((wal_records::numeric / NULLIF(calls, 0)), 2) AS records_per_call,
    pg_size_pretty((wal_bytes / NULLIF(calls, 0))::bigint) AS bytes_per_call
FROM pg_stat_insights
WHERE wal_bytes > 0
ORDER BY wal_bytes DESC
LIMIT 20;

-- Analyze JIT cost vs benefit
SELECT 
    query,
    calls,
    jit_functions,
    ROUND(jit_generation_time::numeric, 2) AS jit_gen_ms,
    ROUND(mean_exec_time::numeric, 2) AS exec_ms,
    ROUND((jit_generation_time / NULLIF(mean_exec_time, 0) * 100)::numeric, 1) AS jit_overhead_pct,
    CASE 
        WHEN (jit_generation_time / NULLIF(mean_exec_time, 0) * 100) > 10 THEN '[WARNING] High Overhead'
        WHEN jit_functions > 0 THEN '[OK] Using JIT'
        ELSE '[NONE] No JIT'
    END AS jit_status
FROM pg_stat_insights
WHERE mean_exec_time > 0
ORDER BY jit_generation_time DESC
LIMIT 20;

-- Parallel query efficiency
SELECT 
    query,
    calls,
    parallel_workers_to_launch AS planned,
    parallel_workers_launched AS actual,
    ROUND((parallel_workers_launched::numeric / 
           NULLIF(parallel_workers_to_launch, 0) * 100), 1) AS efficiency_pct,
    ROUND(mean_exec_time::numeric, 2) AS avg_ms,
    rows
FROM pg_stat_insights
WHERE parallel_workers_to_launch > 0
ORDER BY calls DESC
LIMIT 20;

-- Check stats freshness
SELECT 
    query,
    calls,
    stats_since,
    NOW() - stats_since AS stats_age,
    minmax_stats_since,
    NOW() - minmax_stats_since AS minmax_age
FROM pg_stat_insights
WHERE stats_since < NOW() - INTERVAL '1 hour'
ORDER BY stats_since
LIMIT 10;

cache_hit_ratio = shared_blks_hit::numeric / 
                  NULLIF(shared_blks_hit + shared_blks_read, 0)

ms_per_row = mean_exec_time / NULLIF(rows / NULLIF(calls, 0), 0)

ms_per_block = shared_blk_read_time / NULLIF(shared_blks_read, 0)

-- Total across all queries
SELECT 
    COUNT(*) AS total_queries,
    SUM(calls) AS total_calls,
    SUM(rows) AS total_rows,
    ROUND(SUM(total_exec_time)::numeric, 2) AS total_time_ms,
    SUM(shared_blks_read) AS total_blocks_read,
    pg_size_pretty(SUM(wal_bytes)::bigint) AS total_wal
FROM pg_stat_insights;

-- Average across all queries
SELECT 
    ROUND(AVG(mean_exec_time)::numeric, 2) AS avg_exec_time,
    ROUND(AVG(cache_hit_ratio)::numeric, 3) AS avg_cache_ratio,
    ROUND(AVG(parallel_workers_launched)::numeric, 2) AS avg_workers
FROM pg_stat_insights
WHERE calls > 0;

-- Define monitoring thresholds
CREATE VIEW performance_alerts AS
SELECT 
    queryid,
    LEFT(query, 100) AS query_preview,
    CASE 
        WHEN mean_exec_time > 10000 THEN 'CRITICAL: >10s avg'
        WHEN mean_exec_time > 1000 THEN 'WARNING: >1s avg'
        WHEN mean_exec_time > 100 THEN 'INFO: >100ms avg'
        ELSE 'OK'
    END AS execution_alert,
    CASE 
        WHEN cache_hit_ratio < 0.80 THEN 'CRITICAL: <80% cache'
        WHEN cache_hit_ratio < 0.90 THEN 'WARNING: <90% cache'
        WHEN cache_hit_ratio < 0.95 THEN 'INFO: <95% cache'
        ELSE 'OK'
    END AS cache_alert,
    CASE 
        WHEN wal_bytes > 100000000 THEN 'WARNING: >100MB WAL'
        WHEN wal_bytes > 10000000 THEN 'INFO: >10MB WAL'
        ELSE 'OK'
    END AS wal_alert
FROM pg_stat_insights
WHERE calls > 10;

-- Check for alerts
SELECT * FROM performance_alerts 
WHERE execution_alert != 'OK' 
   OR cache_alert != 'OK' 
   OR wal_alert != 'OK';