Query debugging and troubleshooting techniques

-- Debug cardinality estimation errors
-- Check actual vs estimated rows
EXPLAIN (ANALYZE, VERBOSE)
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending';

-- If "rows=10" but "actual rows=10000", statistics are stale
ANALYZE orders;
ANALYZE users;

-- Increase statistics target for better estimates
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

-- Debug join order issues
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM large_table l
JOIN small_table s ON l.small_id = s.id
WHERE s.category = 'active';

-- PostgreSQL should join small filtered table first
-- If not, check statistics or force order with subquery

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM large_table l
JOIN (
  SELECT id FROM small_table WHERE category = 'active'
) s ON l.small_id = s.id;

-- Debug parallel query issues
SET max_parallel_workers_per_gather = 4;
SET parallel_setup_cost = 1000;
SET parallel_tuple_cost = 0.1;

EXPLAIN (ANALYZE, VERBOSE)
SELECT COUNT(*) FROM large_table;
-- Look for "Parallel Seq Scan" or "Gather"

-- If not using parallel:
-- 1. Table too small (< parallel_threshold)
-- 2. max_parallel_workers_per_gather = 0
-- 3. Transaction or function context

-- Debug function performance
CREATE OR REPLACE FUNCTION slow_function(p_id INT)
RETURNS TABLE(id INT, name VARCHAR) AS $$
BEGIN
  -- Add timing
  RAISE NOTICE 'Function started at %', clock_timestamp();

  RETURN QUERY
  SELECT u.id, u.username
  FROM users u
  WHERE u.id = p_id;

  RAISE NOTICE 'Function ended at %', clock_timestamp();
END;
$$ LANGUAGE plpgsql;

-- Call and check NOTICE messages
SELECT * FROM slow_function(123);

-- Profile function with plprofiler
/*
CREATE EXTENSION plprofiler;
SELECT plprofiler_enable(true);

SELECT * FROM slow_function(123);

SELECT * FROM plprofiler_collect_data();
SELECT plprofiler_enable(false);
*/

-- Debug trigger performance
CREATE OR REPLACE FUNCTION debug_trigger()
RETURNS TRIGGER AS $$
BEGIN
  RAISE NOTICE 'Trigger % fired at %', TG_NAME, clock_timestamp();

  -- Original trigger logic...

  RAISE NOTICE 'Trigger % completed at %', TG_NAME, clock_timestamp();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Monitor table statistics
SELECT
  schemaname,
  tablename,
  seq_scan,
  seq_tup_read,
  idx_scan,
  idx_tup_fetch,
  n_tup_ins,
  n_tup_upd,
  n_tup_del,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

-- High seq_scan on large table = missing index

-- Debug cache effectiveness
SELECT
  'Buffer cache hit ratio' AS metric,
  ROUND(
    sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit + heap_blks_read), 0) * 100,
    2
  ) AS percentage
FROM pg_statio_user_tables
UNION ALL
SELECT
  'Index cache hit ratio',
  ROUND(
    sum(idx_blks_hit) / NULLIF(sum(idx_blks_hit + idx_blks_read), 0) * 100,
    2
  )
FROM pg_statio_user_indexes;
-- Target: > 99%

-- Debug connection pool issues
SELECT
  count(*) AS total,
  count(*) FILTER (WHERE state = 'active') AS active,
  count(*) FILTER (WHERE state = 'idle') AS idle,
  count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_tx,
  count(*) FILTER (WHERE state = 'idle in transaction (aborted)') AS idle_aborted
FROM pg_stat_activity;

-- High idle_in_tx = application not committing

-- Find idle in transaction
SELECT
  pid,
  usename,
  application_name,
  state,
  NOW() - state_change AS idle_duration,
  query
FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%'
ORDER BY state_change;

-- Terminate long idle transactions
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND NOW() - state_change > INTERVAL '10 minutes';

-- Debug disk I/O issues
SELECT
  tablename,
  heap_blks_read AS table_disk_reads,
  heap_blks_hit AS table_cache_hits,
  idx_blks_read AS index_disk_reads,
  idx_blks_hit AS index_cache_hits,
  ROUND(
    heap_blks_hit::NUMERIC / NULLIF(heap_blks_hit + heap_blks_read, 0),
    4
  ) AS table_hit_ratio
FROM pg_statio_user_tables
WHERE heap_blks_read > 0
ORDER BY heap_blks_read DESC
LIMIT 20;

-- Debug query plan regression
-- Save good plan
CREATE TABLE saved_plans (
  query_id BIGINT PRIMARY KEY,
  query TEXT,
  plan JSONB,
  created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO saved_plans (query_id, query, plan)
SELECT
  queryid,
  query,
  to_jsonb(
    (EXPLAIN (FORMAT JSON)
     SELECT * FROM orders WHERE user_id = 123)
  )
FROM pg_stat_statements
WHERE query LIKE '%orders%user_id%'
LIMIT 1;

-- Compare current plan to saved
/*
Compare EXPLAIN output to saved plan
Look for:
- Different join methods (Hash Join vs Nested Loop)
- Different scan methods (Index vs Seq Scan)
- Different row estimates
*/

-- Enable query logging for debugging
-- postgresql.conf:
/*
log_statement = 'all'  # or 'ddl', 'mod'
log_duration = on
log_min_duration_statement = 1000  # Log queries > 1s
log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h '
*/

-- Debug data type conversion issues
EXPLAIN ANALYZE
SELECT * FROM users WHERE id = '123'; -- String instead of INT
-- May not use index if type mismatch

-- Fix: Use correct type
EXPLAIN ANALYZE
SELECT * FROM users WHERE id = 123;

-- Verbose logging for troubleshooting
SET client_min_messages = DEBUG1;
-- Or DEBUG2, DEBUG3, DEBUG4, DEBUG5 for more detail

SELECT * FROM users LIMIT 1;
-- Shows detailed planner output

SET client_min_messages = NOTICE; -- Reset