Views Reference¶

SELECT * FROM pg_stat_insights LIMIT 10;

-- Comprehensive query analysis
SELECT 
    LEFT(query, 100) AS query_preview,
    calls,
    ROUND(mean_exec_time::numeric, 2) AS avg_time_ms,
    ROUND(cache_hit_ratio::numeric, 3) AS cache_ratio,
    pg_size_pretty(wal_bytes::bigint) AS wal_generated,
    parallel_workers_launched,
    rows
FROM pg_stat_insights
WHERE calls > 10
ORDER BY total_exec_time DESC
LIMIT 20;

SELECT * FROM pg_stat_insights_top_by_time LIMIT 10;

-- Find queries contributing to 80% of total time
WITH total_time AS (
    SELECT SUM(total_exec_time) AS total FROM pg_stat_insights
),
ranked AS (
    SELECT 
        query,
        total_exec_time,
        SUM(total_exec_time) OVER (ORDER BY total_exec_time DESC) AS running_total,
        ROW_NUMBER() OVER (ORDER BY total_exec_time DESC) AS rank
    FROM pg_stat_insights
)
SELECT 
    rank,
    LEFT(query, 100) AS query_preview,
    ROUND(total_exec_time::numeric, 2) AS time_ms,
    ROUND((running_total / (SELECT total FROM total_time) * 100)::numeric, 1) AS cumulative_pct
FROM ranked
WHERE running_total <= (SELECT total FROM total_time) * 0.8
ORDER BY rank;

SELECT * FROM pg_stat_insights_top_by_calls LIMIT 10;

-- Frequent queries with improvement potential
SELECT 
    LEFT(query, 100) AS query_preview,
    calls,
    ROUND(mean_exec_time::numeric, 2) AS avg_ms,
    ROUND((calls * mean_exec_time)::numeric, 2) AS total_time_ms,
    ROUND(cache_hit_ratio::numeric, 3) AS cache_ratio
FROM pg_stat_insights_top_by_calls
WHERE mean_exec_time > 1  -- Room for optimization
ORDER BY (calls * mean_exec_time) DESC
LIMIT 20;

SELECT * FROM pg_stat_insights_top_by_io LIMIT 10;

ORDER BY (shared_blks_read + local_blks_read + temp_blks_read) DESC

-- I/O-intensive queries with timing
SELECT 
    LEFT(query, 100) AS query_preview,
    calls,
    shared_blks_read,
    shared_blks_hit,
    shared_blk_read_time,
    ROUND((shared_blk_read_time / NULLIF(shared_blks_read, 0))::numeric, 3) AS ms_per_block,
    ROUND(cache_hit_ratio::numeric, 3) AS cache_ratio
FROM pg_stat_insights_top_by_io
WHERE shared_blks_read > 0
LIMIT 20;

SELECT * FROM pg_stat_insights_top_cache_misses LIMIT 10;

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

-- Queries needing cache optimization
SELECT 
    LEFT(query, 100) AS query_preview,
    calls,
    ROUND(cache_hit_ratio::numeric, 3) AS cache_ratio,
    shared_blks_hit,
    shared_blks_read,
    pg_size_pretty((shared_blks_read * 8192)::bigint) AS data_read_from_disk
FROM pg_stat_insights_top_cache_misses
WHERE cache_hit_ratio < 0.95 AND calls > 5
ORDER BY (shared_blks_read * calls) DESC
LIMIT 20;

SELECT * FROM pg_stat_insights_slow_queries;

WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC

-- Slow queries with full context
SELECT 
    LEFT(query, 100) AS query_preview,
    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(stddev_exec_time::numeric, 2) AS stddev_ms,
    rows,
    ROUND(cache_hit_ratio::numeric, 3) AS cache_ratio
FROM pg_stat_insights_slow_queries
ORDER BY mean_exec_time DESC
LIMIT 20;

SELECT * FROM pg_stat_insights_histogram_summary
ORDER BY bucket_order;

-- Response time SLA analysis
SELECT 
    bucket_label,
    query_count,
    ROUND((query_count::numeric / SUM(query_count) OVER () * 100), 1) AS pct_queries,
    ROUND(total_time::numeric, 2) AS total_ms,
    ROUND((total_time / SUM(total_time) OVER () * 100), 1) AS pct_time
FROM pg_stat_insights_histogram_summary
ORDER BY bucket_order;

SELECT * FROM pg_stat_insights_by_bucket
ORDER BY bucket_start DESC
LIMIT 24;

-- Hourly performance trends
SELECT 
    DATE_TRUNC('hour', bucket_start) AS hour,
    COUNT(*) AS bucket_count,
    SUM(query_count) AS total_queries,
    SUM(total_calls) AS total_executions,
    ROUND(AVG(avg_time)::numeric, 2) AS avg_response_ms
FROM pg_stat_insights_by_bucket
WHERE bucket_start >= NOW() - INTERVAL '24 hours'
GROUP BY DATE_TRUNC('hour', bucket_start)
ORDER BY hour DESC;

SELECT * FROM pg_stat_insights_errors;

SELECT * FROM pg_stat_insights_plan_errors;

-- Find queries with poor estimates
SELECT 
    LEFT(query, 100) AS query_preview,
    calls,
    rows AS actual_rows,
    planned_rows,
    ABS(rows - planned_rows) AS estimation_error,
    ROUND((ABS(rows - planned_rows)::numeric / NULLIF(GREATEST(rows, planned_rows), 0) * 100), 1) AS error_pct
FROM pg_stat_insights_plan_errors
WHERE ABS(rows - planned_rows) > 100
ORDER BY ABS(rows - planned_rows) DESC
LIMIT 20;

SELECT * FROM pg_stat_insights_replication;

-- Monitor replication lag
SELECT 
    application_name,
    client_addr,
    sync_state,
    pg_size_pretty(replay_lag_bytes::bigint) AS replay_lag_size,
    ROUND(replay_lag_seconds::numeric, 2) AS replay_lag_sec,
    CASE 
        WHEN replay_lag_seconds < 1 THEN '[OK] Healthy'
        WHEN replay_lag_seconds < 10 THEN '[WARNING] Warning'
        ELSE '[CRITICAL] Critical'
    END AS status
FROM pg_stat_insights_replication
ORDER BY replay_lag_seconds DESC;

-- Slow queries with poor cache performance
SELECT 
    s.query,
    s.mean_exec_time,
    c.cache_hit_ratio,
    s.calls,
    i.shared_blks_read
FROM pg_stat_insights_slow_queries s
JOIN pg_stat_insights_top_cache_misses c USING (queryid)
JOIN pg_stat_insights_top_by_io i USING (queryid)
WHERE c.cache_hit_ratio < 0.9
ORDER BY s.mean_exec_time DESC
LIMIT 15;

-- Create your own monitoring view
CREATE VIEW my_critical_queries AS
SELECT 
    queryid,
    LEFT(query, 200) AS query_preview,
    calls,
    ROUND(mean_exec_time::numeric, 2) AS avg_ms,
    ROUND(cache_hit_ratio::numeric, 3) AS cache_ratio,
    pg_size_pretty(wal_bytes::bigint) AS wal_size,
    CASE 
        WHEN mean_exec_time > 1000 THEN '[CRITICAL] Critical'
        WHEN mean_exec_time > 100 THEN '[WARNING] Warning'
        ELSE '[OK] OK'
    END AS status
FROM pg_stat_insights
WHERE calls > 10 AND (
    mean_exec_time > 100 
    OR cache_hit_ratio < 0.9
    OR wal_bytes > 1000000
)
ORDER BY mean_exec_time DESC;

-- Query your custom view
SELECT * FROM my_critical_queries;

-- No special refresh needed - data is live
SELECT * FROM pg_stat_insights_top_by_time LIMIT 10;

-- Reset all statistics (affects all views)
SELECT pg_stat_insights_reset();

-- Reset specific query
SELECT pg_stat_insights_reset(userid, dbid, queryid);