Query Optimizer Troubleshooting Guide

---

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

ANALYZE in the current release: ANALYZE (including VACUUM ANALYZE) is accepted for PostgreSQL compatibility and returns immediately; it does not collect planner statistics yet. Statistics collection is planned. EXPLAIN ANALYZE, which runs the query and reports actual rows and timing, works today.

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

---

Query Optimizer Troubleshooting - Diagnose and fix any performance issue.