---
# === IDENTITY ===
id: software/debugging/postgresql-slow-queries/2026
canonical_question: "How do I diagnose and optimize slow PostgreSQL queries?"
aliases:
  - "PostgreSQL slow query"
  - "EXPLAIN ANALYZE PostgreSQL"
  - "pg_stat_statements slow query"
  - "PostgreSQL query optimization"
  - "PostgreSQL index missing"
  - "PostgreSQL sequential scan optimization"
  - "auto_explain PostgreSQL"
  - "PostgreSQL query tuning"
  - "PostgreSQL slow log"
  - "EXPLAIN ANALYZE buffers"
entity_type: software_reference
domain: software > debugging > postgresql_slow_queries
region: global
jurisdiction: global
temporal_scope: 2010-2026

# === VERIFICATION ===
last_verified: 2026-02-23
confidence: 0.94
version: 2.0
first_published: 2026-02-19

# === TEMPORAL VALIDITY ===
temporal_validity:
  status: stable
  last_breaking_change: "PostgreSQL 18 — EXPLAIN ANALYZE now includes BUFFERS by default (2025-09)"
  next_review: 2026-08-22
  change_sensitivity: low

# === CONSTRAINTS ===
constraints:
  - "EXPLAIN ANALYZE executes the query — never run it on UPDATE/DELETE/INSERT without wrapping in BEGIN/ROLLBACK"
  - "pg_stat_statements requires shared_preload_libraries entry and a server restart — cannot be loaded at runtime"
  - "work_mem is per-sort-operation, not per-session — setting 256MB with 10 sorts can consume 2.5GB RAM"
  - "CREATE INDEX CONCURRENTLY cannot run inside a transaction block and may leave invalid indexes on failure — verify with \\di+"
  - "Statistics from pg_stat_statements and pg_stat_user_indexes reset on server restart — idx_scan=0 is only meaningful since last restart"
  - "Autovacuum blocked by long-running transactions causes cascading stale statistics — monitor pg_stat_activity for idle-in-transaction"

# === SKIP CONDITIONS ===
skip_this_unit_if:
  - condition: "Database-wide slowdown with no single slow query (all queries degraded equally)"
    use_instead: "Check pg_stat_activity for lock contention, connection exhaustion, or I/O saturation — not a query-level issue"
  - condition: "Connection pool exhaustion causing timeouts (not slow execution)"
    use_instead: "software/debugging/postgresql-connection-pool/2026"
  - condition: "MySQL slow query diagnosis needed"
    use_instead: "software/debugging/mysql-slow-queries/2026"

# === AGENT HINTS ===
inputs_needed:
  - key: postgresql_version
    question: "Which PostgreSQL version are you running?"
    type: choice
    options: ["PostgreSQL 13 or older", "PostgreSQL 14-16", "PostgreSQL 17+", "PostgreSQL 18+ (beta)"]
  - key: symptom_type
    question: "Is it a single slow query or a database-wide performance issue?"
    type: choice
    options: ["Single slow query", "Multiple queries degraded", "Database-wide slowdown"]
  - key: access_level
    question: "Do you have superuser access to modify postgresql.conf?"
    type: choice
    options: ["Yes — full superuser", "No — managed/RDS (limited config)", "Not sure"]

# === DISTRIBUTION ===
canonical_source: "https://knowledgelib.io/software/debugging/postgresql-slow-queries/2026"
suggested_citation: "Source: knowledgelib.io — AI Knowledge Library (verified 2026-02-23)"

# === RELATED UNITS ===
related_kos:
  related_to:
    - id: "software/debugging/postgresql-connection-pool/2026"
      label: "PostgreSQL Connection Pool Exhaustion"
    - id: "software/debugging/mysql-deadlocks/2026"
      label: "MySQL Deadlocks"
  solves:
    - id: "software/debugging/database-timeout-errors/2026"
      label: "Database Timeout Errors"
  often_confused_with:
    - id: "software/debugging/postgresql-lock-contention/2026"
      label: "PostgreSQL Lock Contention (not the same as slow queries)"
    - id: "software/debugging/postgresql-connection-pool/2026"
      label: "Connection Pool Exhaustion (timeouts, not slow execution)"

# === SOURCES (5-8 authoritative sources) ===
sources:
  - id: src1
    title: "PostgreSQL Documentation — EXPLAIN"
    author: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/sql-explain.html
    type: official_docs
    published: 2025-09-01
    reliability: authoritative
  - id: src2
    title: "PostgreSQL Documentation — pg_stat_statements"
    author: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/pgstatstatements.html
    type: official_docs
    published: 2025-09-01
    reliability: authoritative
  - id: src3
    title: "PostgreSQL Documentation — auto_explain"
    author: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/auto-explain.html
    type: official_docs
    published: 2025-09-01
    reliability: authoritative
  - id: src4
    title: "Cybertec — PostgreSQL Query Optimization"
    author: Cybertec PostgreSQL
    url: https://www.cybertec-postgresql.com/en/
    type: technical_blog
    published: 2024-08-01
    reliability: high
  - id: src5
    title: "Thoughtbot — Reading an EXPLAIN ANALYZE Query Plan"
    author: Thoughtbot
    url: https://thoughtbot.com/blog/reading-an-explain-analyze-query-plan
    type: technical_blog
    published: 2024-06-01
    reliability: high
  - id: src6
    title: "Sematext — PostgreSQL Slow Query Log"
    author: Sematext
    url: https://sematext.com/blog/postgresql-slow-query-log/
    type: technical_blog
    published: 2024-06-01
    reliability: high
  - id: src7
    title: "PostgreSQL 17 Release Notes"
    author: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/release/17.0/
    type: official_docs
    published: 2024-09-26
    reliability: authoritative
  - id: src8
    title: "PostgreSQL 18 Release Notes"
    author: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/release-18.html
    type: official_docs
    published: 2025-09-01
    reliability: authoritative
---

# How Do I Diagnose and Optimize Slow PostgreSQL Queries?

## TL;DR

- **Bottom line**: PostgreSQL slow query diagnosis is a 3-step loop: **Find** (which queries are slow via `pg_stat_statements` or slow query log) -> **Analyze** (why via `EXPLAIN (ANALYZE, BUFFERS)`) -> **Fix** (add/fix index, rewrite query, update statistics via `ANALYZE`). 80% of slow queries are caused by missing indexes, stale statistics, or sequential scans on large tables.
- **Key tool/command**: `EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)` — runs the query and shows the actual execution plan with timing, row counts, and buffer usage. In PostgreSQL 18+, `BUFFERS` is included automatically with `ANALYZE`. Use `explain.depesz.com` to interpret the output.
- **Watch out for**: Running `EXPLAIN ANALYZE` on a destructive query (`DELETE`, `UPDATE`) actually executes it. Wrap in a transaction and roll back, or use `EXPLAIN` without `ANALYZE` for destructive statements.
- **Works with**: PostgreSQL 10+. Most features (`pg_stat_statements`, `auto_explain`, `EXPLAIN BUFFERS`) available since PostgreSQL 9.4. PostgreSQL 17+ adds incremental vacuum and streaming I/O; PostgreSQL 18+ adds async I/O and skip scans.

## Constraints
<!-- Agents: read this section before recommending any action from this unit.
     These are hard constraints that must not be violated. -->

- **EXPLAIN ANALYZE executes the query** — never run it on `UPDATE`/`DELETE`/`INSERT` without wrapping in `BEGIN`/`ROLLBACK`. This is the #1 data-loss mistake.
- **`pg_stat_statements` requires `shared_preload_libraries` entry and a server restart** — it cannot be loaded at runtime with `CREATE EXTENSION` alone. On managed databases (RDS, Cloud SQL), it may already be enabled.
- **`work_mem` is per-sort-operation, not per-session** — setting `work_mem = '256MB'` in a session with 10 sort operations can use 2.5GB RAM. Increase globally with extreme caution.
- **`CREATE INDEX CONCURRENTLY` cannot run inside a transaction block** — and may leave invalid indexes on failure. Always verify with `\di+` or `pg_indexes` after creation.
- **Statistics from `pg_stat_statements` and `pg_stat_user_indexes` reset on server restart** — `idx_scan = 0` is only meaningful since the last restart. Check `pg_stat_bgwriter.stats_reset` for the reset timestamp.
- **Autovacuum blocked by long-running transactions causes cascading stale statistics** — monitor `pg_stat_activity` for `idle in transaction` sessions lasting more than a few minutes.

## Quick Reference

| # | Symptom / Goal | Command / Solution | Notes |
|---|---|---|---|
| 1 | Find the slowest queries | `SELECT ... FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20` | Requires `pg_stat_statements` extension [src2] |
| 2 | Find currently running long queries | `SELECT pid, now()-query_start AS duration, query FROM pg_stat_activity WHERE state='active' ORDER BY duration DESC` | Real-time view [src1] |
| 3 | Analyze a query's execution plan | `EXPLAIN (ANALYZE, BUFFERS) SELECT ...` | Runs query; shows actual time + buffer hits. PG18+: BUFFERS auto-included [src1, src8] |
| 4 | Analyze without running (safe for DML) | `EXPLAIN SELECT ...` | Estimated plan only; no execution [src1] |
| 5 | Enable slow query logging | `log_min_duration_statement = 500` in `postgresql.conf` | Logs queries > 500ms [src6] |
| 6 | Auto-log slow query plans | Load `auto_explain`, set `auto_explain.log_min_duration = 1000` | No manual EXPLAIN needed [src3] |
| 7 | Check for missing indexes | Look for "Seq Scan" on large tables in EXPLAIN output | Rows x loops is cost indicator [src1, src5] |
| 8 | Check index usage statistics | `SELECT * FROM pg_stat_user_indexes` | Shows `idx_scan = 0` for unused indexes [src2] |
| 9 | Check table statistics freshness | `SELECT relname, last_analyze, last_autoanalyze FROM pg_stat_user_tables` | Stale stats -> bad plans [src1] |
| 10 | Update statistics manually | `ANALYZE tablename` | Run after bulk inserts/deletes [src1] |
| 11 | Find unused indexes | `SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0` | Unused indexes slow writes [src2] |
| 12 | Find index bloat | `SELECT * FROM pg_stat_user_tables ORDER BY n_dead_tup DESC` | High dead tuples -> VACUUM [src2] |
| 13 | Create index concurrently (no lock) | `CREATE INDEX CONCURRENTLY idx_name ON table(col)` | Safe in production [src1] |

## Decision Tree

> Full script: [decision-tree.txt](scripts/decision-tree.txt) (27 lines)

```
START — Query is slow
├── Step 1: WHERE is time being spent?
│   ├── pg_stat_statements → ORDER BY total_exec_time/mean_exec_time DESC
│   ├── pg_stat_activity → real-time long-running queries
│   └── slow query log → log_min_duration_statement = 500 [src2, src6]
│
├── Step 2: EXPLAIN (ANALYZE, BUFFERS) on the slow query
│   ├── "Seq Scan" on large table?
│   │   ├── No WHERE clause → expected full scan
│   │   ├── Low selectivity → use partial index [src1]
│   │   └── Missing index → CREATE INDEX CONCURRENTLY [src1]
│   ├── estimated rows >> actual rows (or vice versa)?
│   │   └── Stale statistics → ANALYZE tablename [src1]
│   ├── Nested Loop with large outer row count?
│   │   └── Add index on join column [src1, src4]
│   ├── Sort in plan (no index for ORDER BY)?
│   │   └── Add index on ORDER BY columns [src1]
│   ├── "Buffers: shared read=N" — high N?
│   │   └── Increase shared_buffers; check work_mem [src1, src4]
│   └── Hash Join with high Memory Usage?
│       └── SET work_mem = '64MB' for the session [src1]
│
└── Step 3: Fix
    ├── Add index → CREATE INDEX CONCURRENTLY [src1]
    ├── Rewrite query → avoid SELECT *, func on indexed col, OFFSET [src4, src5]
    ├── Update stats → ANALYZE; tune autovacuum [src1]
    └── Tune memory → work_mem, shared_buffers [src1, src4]
```

## Step-by-Step Guide

### 1. Enable pg_stat_statements (essential first step)

`pg_stat_statements` is the most important tool for identifying slow queries across your entire database. [src2]

```sql
-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000

-- After restart, in psql:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find the 20 slowest queries by total time
SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2)  AS mean_ms,
  calls,
  round(rows / calls::numeric, 1)    AS avg_rows,
  left(query, 120)                   AS query_snippet
FROM pg_stat_statements
WHERE calls > 10
ORDER BY total_exec_time DESC
LIMIT 20;

-- Find the slowest by mean (single call cost)
SELECT
  round(mean_exec_time::numeric, 2) AS mean_ms,
  calls,
  left(query, 120) AS query_snippet
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;
```

**Verify**: `SELECT count(*) FROM pg_stat_statements;` -> should return > 0

### 2. Enable slow query logging

For real-time slow query capture in logs. [src6]

```ini
# postgresql.conf
log_min_duration_statement = 500   # log queries > 500ms (use 0 for all)
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '
log_checkpoints = on
log_lock_waits = on
log_temp_files = 0    # log all temp file usage
```

**Verify**: Run a known slow query, then check `pg_log` directory for the logged entry.

### 3. Use EXPLAIN (ANALYZE, BUFFERS)

The primary tool for understanding **why** a specific query is slow. [src1, src5]

```sql
-- Basic plan (safe, does not run the query)
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- Full diagnostic (RUNS the query!)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
  SELECT * FROM orders WHERE customer_id = 42;

-- For DML (UPDATE/DELETE) — wrap in transaction to avoid side effects
BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
  UPDATE orders SET status = 'processed' WHERE created_at < NOW() - INTERVAL '1 year';
ROLLBACK;

-- JSON format for tools:
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
  SELECT * FROM orders WHERE customer_id = 42;
```

**How to read the output**:
- `Seq Scan` on large table -> missing index
- `actual rows` >> `estimated rows` (or vice versa) -> stale statistics, run `ANALYZE`
- `Buffers: shared read=N` (high N) -> data not cached; increase `shared_buffers`
- `Sort Method: external merge Disk` -> `work_mem` too low
- `Rows Removed by Filter: N` -> index on filter column needed

**PostgreSQL 18+ note**: `BUFFERS` output is now automatically included when you use `ANALYZE`, so `EXPLAIN (ANALYZE) SELECT ...` is sufficient. [src8]

### 4. Enable auto_explain (production query plan logging)

Automatically logs plans for slow queries without manual EXPLAIN. [src3]

```ini
# postgresql.conf
shared_preload_libraries = 'pg_stat_statements,auto_explain'

auto_explain.log_min_duration = 1000   # log plans for queries > 1s
auto_explain.log_analyze = on          # include ANALYZE data (actual times)
auto_explain.log_buffers = on          # include buffer stats
auto_explain.log_timing = on           # include per-node timing
auto_explain.log_nested_statements = on # log subqueries too
auto_explain.sample_rate = 1.0         # 1.0 = all slow queries; 0.1 = 10% sample
```

**Verify**: Run a query that takes > 1s, then check PostgreSQL logs for the query plan output.

### 5. Create missing indexes

The most common fix. [src1, src4]

```sql
-- Check if an index would help (look for Seq Scan in EXPLAIN)
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
-- If Seq Scan with many rows → create index

-- Single column index
CREATE INDEX CONCURRENTLY idx_orders_customer_id
  ON orders (customer_id);

-- Composite index (column order matters — put equality columns first)
CREATE INDEX CONCURRENTLY idx_orders_status_created
  ON orders (status, created_at DESC);

-- Partial index (only index the subset you query)
CREATE INDEX CONCURRENTLY idx_orders_pending
  ON orders (created_at) WHERE status = 'pending';

-- Index for JSONB queries
CREATE INDEX CONCURRENTLY idx_events_metadata
  ON events USING GIN (metadata);

-- Covering index (includes extra columns to avoid table heap access)
CREATE INDEX CONCURRENTLY idx_orders_customer_covering
  ON orders (customer_id) INCLUDE (status, total_amount);

-- Verify index is being used after creation
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
-- Should now show "Index Scan" instead of "Seq Scan"
```

**Verify**: `EXPLAIN ANALYZE` should show "Index Scan" or "Index Only Scan" instead of "Seq Scan".

### 6. Update stale statistics

When the planner estimates badly. [src1]

```sql
-- Update statistics for one table
ANALYZE orders;

-- Update all tables in schema
ANALYZE;

-- Check when tables were last analyzed
SELECT relname, last_analyze, last_autoanalyze, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
ORDER BY last_analyze ASC NULLS FIRST;

-- Tune autovacuum for high-churn tables
ALTER TABLE orders SET (
  autovacuum_analyze_scale_factor = 0.01,  -- analyze after 1% of rows change (vs 20% default)
  autovacuum_analyze_threshold = 100
);
```

**Verify**: Re-run `EXPLAIN ANALYZE` — `estimated rows` should now be close to `actual rows`.

## Code Examples

### SQL: comprehensive slow query diagnostic report

> Full script: [sql-comprehensive-slow-query-diagnostic-report.sql](scripts/sql-comprehensive-slow-query-diagnostic-report.sql) (55 lines)

```sql
-- Input:  pg_stat_statements and pg_stat_user_tables/indexes must be available
-- Output: Actionable report: slowest queries, missing index candidates, unused indexes, stale stats
-- 1. Top 10 slowest queries by total time
SELECT '=== TOP 10 SLOW QUERIES ===' AS section, NULL AS detail UNION ALL
SELECT
# ... (see full script)
```

### Python: automated slow query detector and reporter

> Full script: [python-automated-slow-query-detector-and-reporter.py](scripts/python-automated-slow-query-detector-and-reporter.py) (73 lines)

```python
#!/usr/bin/env python3
"""
Input:  PostgreSQL connection string + threshold_ms (default 100ms)
Output: Ranked slow query report with index recommendations
Requirements: pip install psycopg2-binary
# ... (see full script)
```

### SQL + bash: index recommendation script

> Full script: [sql-bash-index-recommendation-script.sh](scripts/sql-bash-index-recommendation-script.sh) (56 lines)

```bash
#!/bin/bash
# Input:  PostgreSQL connection params
# Output: Index creation candidates based on sequential scan statistics
DB="${PGDATABASE:-mydb}"
HOST="${PGHOST:-localhost}"
# ... (see full script)
```

## Anti-Patterns

### Wrong: Using EXPLAIN alone and trusting estimates

```sql
-- ❌ BAD — EXPLAIN without ANALYZE is estimates only; can be wildly wrong [src1, src5]
EXPLAIN SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending';
-- Shows "rows=42" but actual rows might be 420,000
-- Plan looks fine on paper but is catastrophically slow in reality
```

### Correct: Always use EXPLAIN (ANALYZE, BUFFERS) for diagnosis

```sql
-- ✅ GOOD — actual execution data reveals the truth [src1, src5]
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending';
-- Shows actual rows, actual time, buffer hits vs disk reads
-- "actual rows=420000 loops=1" vs "rows=42" → stale statistics!
-- Fix: ANALYZE orders;
```

### Wrong: Creating an index on a low-cardinality column

```sql
-- ❌ BAD — indexing a boolean or status column with few distinct values [src1, src4]
CREATE INDEX idx_orders_is_active ON orders (is_active);
-- If 95% of rows have is_active = true, the index is useless for true queries
-- PostgreSQL will ignore it and still do a Seq Scan
```

### Correct: Use a partial index for low-cardinality columns

```sql
-- ✅ GOOD — index only the rare/actionable subset [src1, src4]
CREATE INDEX CONCURRENTLY idx_orders_is_active_false
  ON orders (created_at) WHERE is_active = false;
-- Only includes the 5% of rows where is_active = false
-- Very fast for: SELECT * FROM orders WHERE is_active = false ORDER BY created_at

-- Or for status columns with a skewed distribution:
CREATE INDEX CONCURRENTLY idx_orders_pending
  ON orders (created_at) WHERE status = 'pending';
```

### Wrong: OFFSET pagination on large tables

```sql
-- ❌ BAD — OFFSET forces PostgreSQL to scan and discard all preceding rows [src4, src5]
SELECT * FROM events ORDER BY created_at DESC LIMIT 20 OFFSET 100000;
-- Must read, sort, and discard 100,000 rows before returning 20
-- Gets slower with every page
```

### Correct: Keyset (cursor) pagination

```sql
-- ✅ GOOD — use a WHERE clause on indexed column to skip rows efficiently [src4, src5]
-- First page:
SELECT * FROM events ORDER BY created_at DESC, id DESC LIMIT 20;

-- Next page (use last row's values as cursor):
SELECT * FROM events
WHERE (created_at, id) < ('2026-02-19 12:00:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- O(log n) instead of O(n) — same speed regardless of page number
-- Requires: CREATE INDEX ON events (created_at DESC, id DESC);
```

## Common Pitfalls

- **EXPLAIN ANALYZE executes DML statements**: Running `EXPLAIN ANALYZE` on an `UPDATE`, `DELETE`, or `INSERT` actually modifies data. Always wrap in `BEGIN`/`ROLLBACK` for destructive statements, or use `EXPLAIN` without `ANALYZE`. [src1]
- **Stale statistics cause bad plans**: After bulk inserts, imports, or deletions of > 10% of a table, PostgreSQL's autovacuum may not have run yet. The planner uses outdated row count estimates and picks wrong join strategies. Run `ANALYZE tablename` manually after large data changes. [src1, src4]
- **Index on wrong column order for composite indexes**: For a query `WHERE status = 'pending' AND created_at > '2026-01-01'`, the index `(created_at, status)` is less efficient than `(status, created_at)`. Put equality columns first, range columns last. [src1, src4]
- **Function calls on indexed columns prevent index use**: `WHERE lower(email) = 'foo@bar.com'` cannot use an index on `email`. Use expression indexes: `CREATE INDEX ON users (lower(email))`. [src1]
- **Over-indexing slows writes**: Every index must be updated on `INSERT`, `UPDATE`, and `DELETE`. On write-heavy tables, 10+ indexes can make writes 5x slower. Regularly audit with `pg_stat_user_indexes WHERE idx_scan = 0`. [src2, src4]
- **`SELECT *` prevents index-only scans**: When you `SELECT *` but only filter on one column, PostgreSQL must visit the heap for every row. Using covering indexes (`INCLUDE`) or selecting only needed columns enables index-only scans — much faster. [src1, src5]

## Diagnostic Commands

> Full script: [diagnostic-commands.sql](scripts/diagnostic-commands.sql) (41 lines)

```sql
-- === Find slow queries ===
-- Top queries by total time
SELECT round(total_exec_time::numeric,1) AS total_ms,
       round(mean_exec_time::numeric,1)  AS mean_ms,
       calls,
# ... (see full script)
```

## Version History & Compatibility

| Feature | Available Since | Notes |
|---|---|---|
| `EXPLAIN ANALYZE` | PostgreSQL 7.1 | Core diagnostic tool [src1] |
| `EXPLAIN BUFFERS` | PostgreSQL 9.0 | Shows buffer hits/misses [src1] |
| `pg_stat_statements` | PostgreSQL 9.2 | Must be in `shared_preload_libraries` [src2] |
| `auto_explain` | PostgreSQL 8.4 | Must be in `shared_preload_libraries` [src3] |
| `CREATE INDEX CONCURRENTLY` | PostgreSQL 8.2 | Non-locking index creation [src1] |
| Covering indexes (`INCLUDE`) | PostgreSQL 11 | Avoids heap access for covered columns [src1] |
| `EXPLAIN FORMAT JSON/XML/YAML` | PostgreSQL 9.0 | Machine-readable plan output [src1] |
| `pg_stat_statements.track_planning` | PostgreSQL 13 | Track planning time separately [src2] |
| `log_min_duration_sample` | PostgreSQL 14 | Sample fraction of long queries [src1] |
| Incremental VACUUM (dirty page bitmap) | PostgreSQL 17 | Overhead proportional to write volume, not table size [src7] |
| Streaming I/O (sequential reads) | PostgreSQL 17 | Read stream API for faster seq scans and ANALYZE [src7] |
| B-tree IN-clause optimization | PostgreSQL 17 | Multi-value lookups significantly faster [src7] |
| VACUUM memory usage reduced 20x | PostgreSQL 17 | No longer limited to 1GB for maintenance_work_mem [src7] |
| `EXPLAIN ANALYZE` auto-includes BUFFERS | PostgreSQL 18 | No need to specify BUFFERS separately [src8] |
| Async I/O subsystem | PostgreSQL 18 | `io_method` config; benefits seq scans, bitmap heap scans, vacuum [src8] |
| Skip scan (multi-column B-tree) | PostgreSQL 18 | Indexes usable even without restrictions on leading columns [src8] |
| Self-join elimination | PostgreSQL 18 | Planner removes unnecessary self-joins automatically [src8] |
| `pg_stat_statements` parallel worker tracking | PostgreSQL 18 | `parallel_workers_to_launch`, `parallel_workers_launched` columns [src8] |
| Parallel GIN index builds | PostgreSQL 18 | Faster GIN index creation for JSONB/full-text workloads [src8] |
| `pg_upgrade` preserves optimizer stats | PostgreSQL 18 | No expensive re-ANALYZE after major version upgrade [src8] |

## When to Use / When Not to Use

| Use for | Don't confuse with |
|---|---|
| Slow individual query -> `EXPLAIN (ANALYZE, BUFFERS)` | Database-wide slowdown -> check pg_stat_activity for locks |
| Finding worst queries -> `pg_stat_statements` | Connection limit issues -> check `max_connections` |
| Missing index -> `pg_stat_user_tables` seq_scan count | Hardware I/O bottleneck -> check OS iostat |
| Stale statistics -> `ANALYZE` | Table bloat -> `VACUUM` (not `ANALYZE`) |
| Production plan capture -> `auto_explain` | Real-time lock waits -> `pg_locks` join `pg_stat_activity` |

## Important Caveats

- **`EXPLAIN ANALYZE` has measurement overhead**: The instrumentation adds 5-15% overhead to query execution. Plans for very fast queries (< 1ms) may appear disproportionately slow under EXPLAIN ANALYZE. [src1]
- **`pg_stat_statements` normalizes parameters**: It replaces literal values with `$1`, `$2` etc. to group similar queries. This is intentional but means you can't see the actual parameter values. Use `pg_stat_activity` or slow query logs for that. [src2]
- **Autovacuum may be blocked**: If autovacuum is blocked by long-running transactions, statistics become stale and plans degrade. Monitor via `pg_stat_activity WHERE backend_type = 'autovacuum worker'`. [src1, src4]
- **`work_mem` is per-operation, not per-session**: Setting `work_mem = '256MB'` in a session with 10 sort operations uses up to 2.5GB of RAM. Increase globally with caution. Set per-session for known heavy queries. [src1]
- **Statistics reset on PostgreSQL restart**: `pg_stat_statements` and `pg_stat_user_indexes/tables` counters reset on restart. `idx_scan = 0` is only meaningful since the last reset. Check `pg_stat_reset_shared('bgwriter')` timestamp. [src2]
- **PostgreSQL 18 changes EXPLAIN defaults**: In PG18+, `EXPLAIN (ANALYZE)` automatically includes `BUFFERS` output. Existing scripts that parse EXPLAIN output may see unexpected additional lines. [src8]

## Related Units

- [PostgreSQL Connection Pool Exhaustion](/software/debugging/postgresql-connection-pool/2026)
- [MySQL Deadlocks](/software/debugging/mysql-deadlocks/2026)
- [Database Timeout Errors](/software/debugging/database-timeout-errors/2026)