Query Optimizer Troubleshooting Guide

Query Optimizer Troubleshooting Guide

Version: 7.0 Status: Production Ready Last Updated: 2026-01-04

---

Table of Contents

1. Identifying Bottlenecks
2. Index Recommendations
3. Query Rewriting Tips
4. Common Anti-Patterns
5. Performance Diagnostics

---

Identifying Bottlenecks

Step 1: Find Slow Queries

-- Using pg_stat_statements (if enabled)
SELECT
  query,
  calls,
  total_exec_time / 1000 as total_seconds,
  mean_exec_time as avg_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

/*
Example output:
query                                    | calls | total_seconds | avg_ms | rows
-----------------------------------------+-------+---------------+--------+------
SELECT * FROM orders WHERE customer...   | 50000 | 1250.5        | 25.01  | 10
SELECT o.*, c.name FROM orders o JO...   | 10000 | 850.2         | 85.02  | 1000
*/

-- Check currently running slow queries
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 seconds'
  AND state = 'active'
ORDER BY duration DESC;

Step 2: Analyze the Query Plan

-- Full analysis with all details
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 123;

/*
Key metrics to examine:

1. actual time vs estimated time
   - Large difference = stale statistics

2. rows vs estimated rows
   - 10x+ difference = cardinality error

3. Buffers: shared read vs hit
   - high read = cold cache or too much data

4. Sort Method: external merge
   - spilling to disk = increase work_mem
*/

Step 3: Use Why-Not Analysis

EXPLAIN ANALYZE (WHY_NOT)
SELECT * FROM orders WHERE customer_id = 123;

/*
WHY_NOT ANALYSIS
================

UNUSED INDEXES (1):
  idx_orders_customer_date
  Reason: Statistics stale (45 days old)
  Suggestion: ANALYZE orders;

CARDINALITY MISMATCH:
  Estimated: 10 rows
  Actual: 5000 rows
  Error: 500x underestimate
  Cause: Outdated statistics

STATISTICS WARNINGS:
  Table: orders
  Last analyzed: 45 days ago
  Rows modified: 35%
  Action: ANALYZE orders;
*/

Common Bottleneck Patterns

Symptom	Likely Cause	Solution
Seq Scan on large table	Missing index	Create index
High “Rows Removed by Filter”	Low selectivity	More selective predicate
Actual >> Estimated rows	Stale statistics	ANALYZE table
”external merge” sort	Insufficient memory	Increase work_mem
Nested Loop with large outer	Wrong join type	Check indexes, memory
Many “loops” in plan	Correlated subquery	Rewrite as JOIN

---

Index Recommendations

Diagnosing Missing Indexes

-- Find sequential scans on large tables
SELECT
  schemaname,
  relname,
  seq_scan,
  seq_tup_read,
  idx_scan,
  seq_tup_read / GREATEST(seq_scan, 1) as avg_seq_rows
FROM pg_stat_user_tables
WHERE seq_scan > 0
  AND seq_tup_read > 100000
ORDER BY seq_tup_read DESC;

/*
If a table has:
- High seq_scan count
- High seq_tup_read
- Low or zero idx_scan

It likely needs an index on commonly filtered columns.
*/

Index Advisor

-- HeliosDB Index Advisor
EXPLAIN ADVISOR
SELECT * FROM orders
WHERE customer_id = 123
  AND order_date > '2025-01-01';

/*
INDEX RECOMMENDATIONS
=====================

1. Composite Index (Recommended)
   CREATE INDEX idx_orders_cust_date
   ON orders(customer_id, order_date);

   Impact: 95% cost reduction
   Reason: Covers both WHERE conditions

2. Partial Index (Alternative)
   CREATE INDEX idx_orders_recent
   ON orders(customer_id)
   WHERE order_date > '2025-01-01';

   Impact: 90% cost reduction
   Size: Smaller than full index
   Use if: Most queries filter recent dates
*/

Choosing the Right Index Type

-- B-Tree: Default, best for most cases
CREATE INDEX idx_orders_date ON orders(order_date);
-- Supports: =, <, >, <=, >=, BETWEEN, IN, IS NULL
-- Use for: Most columns

-- Hash: Equality only, slightly faster
CREATE INDEX idx_users_email ON users USING HASH(email);
-- Supports: = only
-- Use for: Exact match lookups only

-- GiST: Geometric and range types
CREATE INDEX idx_geo ON locations USING GIST(coordinates);
-- Supports: Spatial queries, ranges, full-text
-- Use for: Geographic data, IP ranges

-- GIN: Arrays, JSONB, full-text
CREATE INDEX idx_tags ON products USING GIN(tags);
-- Supports: Array containment, JSONB operators
-- Use for: Multi-value columns

-- BRIN: Very large sequential tables
CREATE INDEX idx_logs_time ON logs USING BRIN(timestamp);
-- Supports: Range queries on ordered data
-- Use for: Append-only tables (time-series)

Composite Index Column Order

-- RULE: Put equality columns BEFORE range columns

-- Query pattern:
SELECT * FROM orders
WHERE customer_id = 123          -- Equality
  AND order_date > '2025-01-01'; -- Range

-- GOOD: Equality first
CREATE INDEX idx_good ON orders(customer_id, order_date);
-- Uses index fully

-- BAD: Range first
CREATE INDEX idx_bad ON orders(order_date, customer_id);
-- Can only use first column for range scan

-- For multiple columns, order by selectivity

-- Most selective column first
CREATE INDEX idx_selective ON orders(
  email,         -- Unique (selectivity ~0.001%)
  status,        -- 5 values (selectivity ~20%)
  created_date   -- 365 values (selectivity ~0.3%)
);

Covering Indexes

-- Query accesses specific columns frequently
SELECT customer_id, order_date, amount
FROM orders
WHERE customer_id = 123;

-- Covering index includes all needed columns
CREATE INDEX idx_covering ON orders(customer_id)
INCLUDE (order_date, amount);

-- Result: Index-Only Scan (no table access)
EXPLAIN SELECT customer_id, order_date, amount
FROM orders WHERE customer_id = 123;

/*
Index Only Scan using idx_covering on orders
  Index Cond: (customer_id = 123)
  Heap Fetches: 0  <-- No table access!
*/

Partial Indexes

-- Only 5% of orders are 'pending'
SELECT * FROM orders WHERE status = 'pending';

-- Full index (indexes all 1M rows)
CREATE INDEX idx_status ON orders(status);
-- Size: 25MB

-- Partial index (indexes only 50K pending rows)
CREATE INDEX idx_pending ON orders(order_date)
WHERE status = 'pending';
-- Size: 1.5MB, 16x smaller!

-- Query uses partial index
EXPLAIN SELECT * FROM orders
WHERE status = 'pending' AND order_date > '2025-01-01';

/*
Index Scan using idx_pending on orders
  Index Cond: (order_date > '2025-01-01')
  Filter: (status = 'pending')  -- Already satisfied by index condition
*/

Removing Unused Indexes

-- Find unused indexes
SELECT
  schemaname,
  relname,
  indexrelname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE 'pg_%'
ORDER BY pg_relation_size(indexrelid) DESC;

/*
Unused indexes:
- Waste storage space
- Slow down INSERT/UPDATE/DELETE
- Should be reviewed and potentially dropped
*/

-- Drop after confirming not needed
DROP INDEX idx_unused_index;

---

Query Rewriting Tips

Replace Correlated Subqueries with JOINs

-- SLOW: Correlated subquery (executes per row)
SELECT o.id, o.amount,
  (SELECT c.name FROM customers c WHERE c.id = o.customer_id) as customer_name
FROM orders o;

-- FAST: LEFT JOIN (single hash join)
SELECT o.id, o.amount, c.name as customer_name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id;

/*
Performance difference: 100x faster for large tables
Reason: JOIN builds hash table once, subquery runs N times
*/

Use EXISTS Instead of COUNT

-- SLOW: Count all matching rows
SELECT * FROM customers c
WHERE (SELECT COUNT(*) FROM orders WHERE customer_id = c.id) > 0;

-- FAST: Stop at first match
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = c.id);

/*
EXISTS stops after finding first match
COUNT must scan all matching rows
*/

Replace DISTINCT with GROUP BY

-- SLOW: DISTINCT on large result
SELECT DISTINCT customer_id, order_date
FROM orders;

-- FASTER: GROUP BY (same result, often better plan)
SELECT customer_id, order_date
FROM orders
GROUP BY customer_id, order_date;

/*
GROUP BY can use hash aggregation
DISTINCT may require sort
*/

Avoid SELECT *

-- SLOW: Fetches all columns
SELECT * FROM orders WHERE customer_id = 123;

-- FAST: Fetch only needed columns
SELECT id, order_date, amount FROM orders WHERE customer_id = 123;

/*
Benefits:
- Less I/O (fewer bytes read)
- Better cache utilization
- May enable index-only scan
*/

Push Filters Down

-- SLOW: Filter after join
SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date > '2025-01-01';

-- FASTER: Filter in subquery (explicit pushdown)
SELECT o.*, c.name
FROM (SELECT * FROM orders WHERE order_date > '2025-01-01') o
JOIN customers c ON o.customer_id = c.id;

/*
Note: Modern optimizers do this automatically, but explicit
subquery ensures it happens. Useful for views and CTEs.
*/

Use UNION ALL Instead of UNION

-- SLOW: UNION removes duplicates (requires sort)
SELECT name FROM customers_2024
UNION
SELECT name FROM customers_2025;

-- FAST: UNION ALL keeps all rows (no dedup)
SELECT name FROM customers_2024
UNION ALL
SELECT name FROM customers_2025;

/*
Use UNION ALL when:
- Duplicates are acceptable
- Source tables don't overlap
- You'll filter/aggregate afterward
*/

Limit Subquery Results

-- SLOW: Subquery returns many rows
SELECT * FROM products
WHERE category_id IN (SELECT id FROM categories WHERE type = 'active');

-- FASTER: Use EXISTS for existence check
SELECT * FROM products p
WHERE EXISTS (
  SELECT 1 FROM categories c
  WHERE c.id = p.category_id AND c.type = 'active'
);

-- OR: If you need the category data, use JOIN
SELECT p.*
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE c.type = 'active';

---

Common Anti-Patterns

Anti-Pattern 1: Function on Indexed Column

-- BAD: Function prevents index use
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

/*
Seq Scan on users
  Filter: (lower(email) = 'john@example.com')

Problem: Index on 'email' can't be used because of LOWER()
*/

-- SOLUTION 1: Create functional index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- SOLUTION 2: Store normalized data
ALTER TABLE users ADD COLUMN email_lower VARCHAR(255);
UPDATE users SET email_lower = LOWER(email);
CREATE INDEX idx_users_email_lower ON users(email_lower);

Anti-Pattern 2: Leading Wildcard

-- BAD: Leading wildcard prevents index use
SELECT * FROM products WHERE name LIKE '%laptop%';

/*
Seq Scan on products
  Filter: (name ~~ '%laptop%')

Problem: B-tree index can't help with leading wildcard
*/

-- SOLUTION: Use full-text search
CREATE INDEX idx_products_fts ON products USING GIN(to_tsvector('english', name));

SELECT * FROM products
WHERE to_tsvector('english', name) @@ to_tsquery('english', 'laptop');

Anti-Pattern 3: OR with Different Columns

-- BAD: OR prevents index optimization
SELECT * FROM orders
WHERE customer_id = 123 OR region = 'US';

/*
Seq Scan on orders
  Filter: ((customer_id = 123) OR (region = 'US'))

Problem: Optimizer can't use single index for both conditions
*/

-- SOLUTION: Rewrite as UNION
SELECT * FROM orders WHERE customer_id = 123
UNION
SELECT * FROM orders WHERE region = 'US';

/*
Append
  -> Index Scan using idx_orders_customer
  -> Index Scan using idx_orders_region

Each branch uses its optimal index
*/

Anti-Pattern 4: Implicit Type Conversion

-- BAD: Type mismatch causes conversion
SELECT * FROM users WHERE id = '123';  -- id is INTEGER

/*
Seq Scan on users
  Filter: ((id)::text = '123')

Problem: Integer column cast to text, index unusable
*/

-- SOLUTION: Use correct type
SELECT * FROM users WHERE id = 123;  -- No quotes

/*
Index Scan using users_pkey
  Index Cond: (id = 123)
*/

Anti-Pattern 5: NOT IN with NULLs

-- BAD: NOT IN with NULLs gives unexpected results
SELECT * FROM orders
WHERE customer_id NOT IN (SELECT id FROM deleted_customers);

/*
If deleted_customers contains NULL, NO rows are returned!
Also performs poorly (anti-join not optimized)
*/

-- SOLUTION: Use NOT EXISTS
SELECT * FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM deleted_customers dc WHERE dc.id = o.customer_id
);

/*
Hash Anti Join (efficient)
  -> Seq Scan on orders
  -> Hash
       -> Seq Scan on deleted_customers

Handles NULLs correctly and performs better
*/

Anti-Pattern 6: ORDER BY in Subquery

-- BAD: Sorting in subquery is usually ignored
SELECT * FROM (
  SELECT * FROM orders ORDER BY order_date DESC
) sub
WHERE customer_id = 123;

/*
Subquery ORDER BY is discarded unless LIMIT is present
Wasted sort operation
*/

-- SOLUTION: Sort at the outer level
SELECT * FROM orders
WHERE customer_id = 123
ORDER BY order_date DESC;

Anti-Pattern 7: OFFSET for Pagination

-- BAD: Large OFFSET is slow
SELECT * FROM orders
ORDER BY order_date DESC
OFFSET 100000 LIMIT 10;

/*
Must scan and discard 100,000 rows before returning 10
Gets slower as OFFSET increases
*/

-- SOLUTION: Keyset pagination
SELECT * FROM orders
WHERE order_date < '2025-06-15 10:30:00'  -- Last seen value
ORDER BY order_date DESC
LIMIT 10;

/*
Uses index directly, constant time regardless of page number
*/

---

Performance Diagnostics

Memory Analysis

-- Check for disk spills
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders ORDER BY customer_id;

/*
Sort (actual time=1500.5..2000.3 ms)
  Sort Method: external merge  Disk: 250000kB  <-- PROBLEM!
*/

-- Solution: Increase work_mem
SET work_mem = '512MB';

/*
Sort (actual time=200.5..300.3 ms)
  Sort Method: quicksort  Memory: 150000kB  <-- Fixed!
*/

Buffer Cache Analysis

-- Analyze buffer usage
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 123;

/*
Index Scan using idx_orders_customer
  Buffers: shared hit=5 read=2  <-- 2 pages from disk

High 'read' vs 'hit' indicates:
- Cold cache (normal after restart)
- Working set larger than shared_buffers
- Table/index fragmentation
*/

-- Check shared_buffers size
SHOW shared_buffers;  -- e.g., 1GB

-- Check cache hit ratio
SELECT
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as cache_hit_ratio
FROM pg_statio_user_tables;

-- Target: > 99% cache hit ratio

Lock Contention Analysis

-- Check for locks
SELECT
  blocked_locks.pid AS blocked_pid,
  blocked_activity.query AS blocked_query,
  blocking_locks.pid AS blocking_pid,
  blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
  AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
  ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

/*
If queries are waiting on locks:
- Long-running transactions blocking others
- Deadlock potential
- Consider query optimization or schema redesign
*/

Query Plan Regression Detection

-- Compare plan costs over time
WITH current_plan AS (
  SELECT
    'current' as version,
    50.0 as cost  -- From EXPLAIN
),
previous_plan AS (
  SELECT
    'previous' as version,
    30.0 as cost  -- From historical data
)
SELECT
  current_plan.cost as current_cost,
  previous_plan.cost as previous_cost,
  (current_plan.cost - previous_plan.cost) / previous_plan.cost * 100 as regression_pct
FROM current_plan, previous_plan;

/*
If regression_pct > 20%, investigate:
- Statistics changed
- Data distribution changed
- Index added/removed
- Configuration changed
*/

Diagnostic Queries Summary

-- 1. Find queries needing optimization
SELECT query, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC LIMIT 10;

-- 2. Find tables needing ANALYZE
SELECT relname, last_analyze, n_mod_since_analyze
FROM pg_stat_user_tables
WHERE n_mod_since_analyze > 1000;

-- 3. Find unused indexes
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

-- 4. Check cache hit ratio
SELECT
  schemaname, relname,
  heap_blks_hit::float / (heap_blks_hit + heap_blks_read) as hit_ratio
FROM pg_statio_user_tables
WHERE heap_blks_read > 0
ORDER BY hit_ratio;

-- 5. Find table bloat
SELECT
  relname,
  n_dead_tup,
  n_live_tup,
  n_dead_tup::float / GREATEST(n_live_tup, 1) as dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY dead_ratio DESC;

---

Troubleshooting Checklist

Before Optimizing

• Identified the slow query
• Collected EXPLAIN ANALYZE output
• Checked actual vs estimated rows
• Verified statistics are current
• Checked buffer hit ratio

Index Issues

• Reviewed unused indexes
• Checked for missing indexes
• Verified index column order
• Considered covering indexes
• Checked for index bloat

Query Issues

• Removed SELECT *
• Replaced correlated subqueries
• Used EXISTS vs COUNT
• Fixed function-on-column issues
• Addressed OR conditions

Configuration Issues

• Verified work_mem adequate
• Checked shared_buffers size
• Reviewed random_page_cost
• Verified parallel settings
• Checked connection limits

---

Related Documentation

• Query Optimizer Overview
• Quick Start Guide
• User Guide
• EXPLAIN Plan Guide
• Index Strategy Guide

---

Query Optimizer Troubleshooting - Diagnose and fix any performance issue.