Quick Start Guide¶

-- Connect as superuser
psql -U postgres

-- Enable pg_stat_insights in shared preload libraries
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_insights';

-- Exit psql
\q

-- Connect to your database
psql -U postgres -d your_database

-- Create extension
CREATE EXTENSION pg_stat_insights;

-- Create a test table
CREATE TABLE performance_test (
  id serial PRIMARY KEY,
  name text,
  value numeric,
  created_at timestamp DEFAULT now()
);

-- Insert data
INSERT INTO performance_test (name, value)
SELECT 
  'item_' || i,
  random() * 1000
FROM generate_series(1, 10000) i;

-- Run some queries
SELECT COUNT(*) FROM performance_test;
SELECT AVG(value) FROM performance_test;
SELECT * FROM performance_test WHERE value > 500 ORDER BY value DESC LIMIT 100;
SELECT name, value FROM performance_test WHERE name LIKE 'item_1%';

-- Wait a moment for stats to be collected
SELECT pg_sleep(0.5);

SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM pg_stat_insights_top_by_time 
LIMIT 10;

SELECT 
    query,
    calls,
    cache_hit_ratio,
    shared_blks_hit,
    shared_blks_read
FROM pg_stat_insights_top_cache_misses
LIMIT 10;

SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time
FROM pg_stat_insights_top_by_calls 
LIMIT 10;

SELECT 
    query,
    calls,
    shared_blks_read + local_blks_read + temp_blks_read AS total_blks_read,
    shared_blk_read_time + local_blk_read_time + temp_blk_read_time AS total_read_time
FROM pg_stat_insights_top_by_io
LIMIT 10;

SELECT 
    query,
    calls,
    wal_records,
    wal_bytes,
    wal_fpi
FROM pg_stat_insights
WHERE wal_bytes > 0
ORDER BY wal_bytes DESC
LIMIT 10;

SELECT 
    bucket_label,
    query_count,
    total_time,
    avg_time
FROM pg_stat_insights_histogram_summary
ORDER BY bucket_order;

-- View queries consuming the most time
SELECT 
    LEFT(query, 80) AS query_preview,
    calls,
    ROUND(total_exec_time::numeric, 2) AS total_time_ms,
    ROUND(mean_exec_time::numeric, 2) AS mean_time_ms,
    ROUND((total_exec_time / SUM(total_exec_time) OVER () * 100)::numeric, 1) AS pct_total_time
FROM pg_stat_insights
WHERE total_exec_time > 0
ORDER BY total_exec_time DESC
LIMIT 20;

-- Queries with poor cache hit ratio
SELECT 
    LEFT(query, 80) AS query_preview,
    calls,
    ROUND(cache_hit_ratio::numeric, 3) AS cache_ratio,
    shared_blks_read,
    shared_blks_hit
FROM pg_stat_insights_top_cache_misses
WHERE cache_hit_ratio < 0.9
LIMIT 15;

-- Queries generating most WAL
SELECT 
    LEFT(query, 80) AS query_preview,
    calls,
    wal_records,
    pg_size_pretty(wal_bytes::bigint) AS wal_size,
    ROUND((wal_bytes / NULLIF(calls, 0))::numeric, 0) AS bytes_per_call
FROM pg_stat_insights
WHERE wal_bytes > 1000000
ORDER BY wal_bytes DESC
LIMIT 15;

-- Check parallel worker utilization
SELECT 
    LEFT(query, 80) AS query_preview,
    calls,
    parallel_workers_to_launch,
    parallel_workers_launched,
    ROUND((parallel_workers_launched::numeric / NULLIF(parallel_workers_to_launch, 0) * 100), 1) AS worker_efficiency_pct
FROM pg_stat_insights
WHERE parallel_workers_to_launch > 0
ORDER BY calls DESC
LIMIT 15;

-- Create a monitoring view
CREATE VIEW daily_performance_report AS
SELECT 
    current_timestamp AS report_time,
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    cache_hit_ratio
FROM pg_stat_insights_top_by_time
LIMIT 50;

-- Query the report
SELECT * FROM daily_performance_report;

-- Find queries exceeding thresholds
CREATE OR REPLACE FUNCTION check_slow_queries()
RETURNS TABLE (
    alert_type text,
    query_preview text,
    metric_value numeric
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        'SLOW_QUERY' AS alert_type,
        LEFT(query, 100) AS query_preview,
        mean_exec_time AS metric_value
    FROM pg_stat_insights
    WHERE mean_exec_time > 1000  -- > 1 second
    ORDER BY mean_exec_time DESC
    LIMIT 10;
END;
$$ LANGUAGE plpgsql;

-- Run alert check
SELECT * FROM check_slow_queries();

-- Reset all statistics
SELECT pg_stat_insights_reset();

-- Reset specific query
SELECT pg_stat_insights_reset(
    (SELECT oid FROM pg_roles WHERE rolname = 'myuser'),
    (SELECT oid FROM pg_database WHERE datname = current_database()),
    1234567890  -- queryid
);

-- Top 10 slowest queries
SELECT * FROM pg_stat_insights_top_by_time LIMIT 10;

-- Most called queries
SELECT * FROM pg_stat_insights_top_by_calls LIMIT 10;

-- Highest I/O queries
SELECT * FROM pg_stat_insights_top_by_io LIMIT 10;

-- Poor cache performers
SELECT * FROM pg_stat_insights_top_cache_misses LIMIT 10;

-- Slow queries (>100ms)
SELECT * FROM pg_stat_insights_slow_queries;

-- Response time distribution
SELECT * FROM pg_stat_insights_histogram_summary;

-- Reset all stats
SELECT pg_stat_insights_reset();