Skip to content
Documentation

PostgreSQL Query Performance Tuning

FieldValue
TypeSkill Resource
Source~/.copilot/skills/database/references/postgres-performance.md
DescriptionNot specified

Source Content

PostgreSQL Query Performance Tuning

Overview

Most performance wins in a Postgres-backed app are 1–2 indexes or a query rewrite away. This guide covers diagnosis with pg_stat_statements and EXPLAIN, the right index types for different query patterns, and safe migration patterns.

Finding Slow Queries

pg_stat_statements Setup

Enable the extension in postgresql.conf:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;


-- Reset stats before a measurement window
SELECT pg_stat_statements_reset();


-- Collect data for 24 hours, then query top offenders

Ranking Queries by Impact

Top 20 by total execution time (worst total impact):

SELECT
    query,
    calls,
    total_time,
    mean_time,
    stddev_time,
    rows
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY total_time DESC
LIMIT 20;

Top 20 by mean execution time (worst per-call impact):

SELECT
    query,
    calls,
    total_time,
    mean_time,
    stddev_time,
    rows
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
  AND calls > 10  -- Only queries called at least 10 times
ORDER BY mean_time DESC
LIMIT 20;

Both lists matter. A query called once but taking 60 seconds is a problem; a query called 10,000 times taking 100ms each is also a problem.

EXPLAIN (ANALYZE, BUFFERS)

Never use bare EXPLAIN; always include ANALYZE and BUFFERS:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 10;

What to Look For

SymptomMeaningAction
Seq Scan on >100k rowsFull table scan, no index usedAdd an index on the filter column(s)
Bitmap Heap Scan on >50% of tableLossy scan, high overheadConsider covering index or column reorder
actual rows >> estimated rows (>10×)Planner mis-estimated; may pick wrong planRun ANALYZE table_name or create extended statistics
actual rows << estimated rowsPlanner overestimated; may be leaving resources unusedRe-check statistics or table changes
External SortSort spilled to diskIncrease work_mem or add index for sort order
Nested Loop on large outer setCartesian product riskCheck join conditions; consider hash/merge join

Index Types and Strategy

B-Tree (Default)

Best for equality (WHERE col = val), ranges (BETWEEN, >), and sorting (ORDER BY).

-- Single column
CREATE INDEX idx_customers_email ON customers (email);


-- Composite: equality columns first, then range
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, created_at DESC);

Composite column order matters:

  1. Equality columns first (most selective).
  2. Range/sort columns second in the order the query needs them.
  3. Never put a range column before an equality column — the planner can’t use the part after the gap.

GIN (Generalized Inverted Index)

Best for full-text search, JSONB containment, and array operations.

-- Full-text search
CREATE INDEX idx_documents_content_fts
ON documents USING GIN (to_tsvector('english', content));


-- JSONB field containment
CREATE INDEX idx_users_tags
ON users USING GIN (tags);

BRIN (Block Range Index)

Best for append-only tables with huge row counts (100M+). Uses minimal space.

-- Time-series data ordered by created_at
CREATE INDEX idx_events_created_brin
ON events USING BRIN (created_at);

Partial Indexes

Index only the rows you query most often, reducing size and write overhead.

-- Active users only
CREATE INDEX idx_active_users_email
ON users (email)
WHERE status = 'active';


-- Recent orders only
CREATE INDEX idx_recent_orders
ON orders (customer_id, created_at)
WHERE created_at > CURRENT_DATE - INTERVAL '90 days';

Covering Indexes (INCLUDE clause)

Avoid table lookups by including non-key columns in the index.

-- Index-only scan possible for this query:
-- SELECT order_total, status FROM orders WHERE customer_id = ?
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id)
INCLUDE (order_total, status);

Expression/Functional Indexes

Index the result of a function for queries on transformed data.

-- Case-insensitive email lookups
CREATE INDEX idx_users_email_lower
ON users (LOWER(email));


-- Date part extraction
CREATE INDEX idx_orders_month
ON orders (EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at));

Query Rewrites

When an index won’t help, rewrite the query.

Pagination: Cursor Over OFFSET

Bad — slow on large offsets:

SELECT * FROM orders
WHERE customer_id = ?
ORDER BY created_at DESC
LIMIT 20 OFFSET 1000;

Good — constant time, keyset pagination:

-- First page: pass cursor_id = 0 or NULL
SELECT * FROM orders
WHERE customer_id = ?
  AND created_at < ?  -- or id < ? for the cursor
ORDER BY created_at DESC
LIMIT 21;  -- Fetch one extra to detect "more"

Existence: EXISTS Over IN (subquery)

Bad — materializes the subquery:

SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE status = 'premium'
);

Good — stops at first match:

SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM customers c
  WHERE c.id = o.customer_id
    AND c.status = 'premium'
);

Top-N Per Group: LATERAL

Bad — pulls all rows then filters:

SELECT DISTINCT ON (category) product_id, category, price
FROM products
ORDER BY category, price DESC;

Good — efficient per-category top-N:

SELECT p.product_id, p.category, p.price
FROM product_categories c
CROSS JOIN LATERAL (
  SELECT product_id, price
  FROM products
  WHERE category = c.category
  ORDER BY price DESC
  LIMIT 10
) p;

Index Maintenance

Finding Unused Indexes

SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexname::regclass)) as size
FROM pg_stat_user_indexes
WHERE idx_scan = 0  -- Never used
  AND indexname NOT LIKE 'pg_toast%'
ORDER BY pg_relation_size(indexname::regclass) DESC;

Unused indexes slow every INSERT, UPDATE, DELETE. Drop them.

Index Size and Bloat

-- Large indexes
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) as index_size
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexname::regclass) DESC
LIMIT 20;


-- Check for bloat
SELECT
    schemaname,
    tablename,
    round(100.0 * (CASE WHEN otta > 0 THEN sml.relpages - otta ELSE 0 END) / sml.relpages) AS table_waste_percent,
    pg_size_pretty((CASE WHEN otta > 0 THEN sml.relpages - otta ELSE 0 END)::bigint * 8192) AS table_waste
FROM pg_stat_user_tables sml
ORDER BY table_waste DESC;

Autovacuum Tuning

The query planner relies on accurate statistics. Autovacuum keeps them fresh.

-- Check autovacuum settings for a table
SELECT schemaname, tablename,
       autovacuum_vacuum_threshold,
       autovacuum_vacuum_scale_factor
FROM pg_tables
WHERE tablename = 'orders';


-- Manually analyze for immediate stats refresh
ANALYZE orders;

Partitioning Strategy

Consider partitioning when a table exceeds ~50M rows or has a clear time-series/natural split.

Time-Series Partitioning

CREATE TABLE events (
    id BIGSERIAL,
    event_type TEXT,
    data JSONB,
    created_at TIMESTAMP,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);


-- Auto-create partitions per month
CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');


CREATE TABLE events_2024_02 PARTITION OF events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

Range Partitioning by ID

CREATE TABLE customer_data (
    customer_id BIGINT,
    data JSONB,
    PRIMARY KEY (customer_id)
) PARTITION BY RANGE (customer_id);


CREATE TABLE customer_data_0_100k PARTITION OF customer_data
    FOR VALUES FROM (0) TO (100000);


CREATE TABLE customer_data_100k_200k PARTITION OF customer_data
    FOR VALUES FROM (100000) TO (200000);

Safe Migration Pattern

Never:

  • CREATE INDEX without CONCURRENTLY in production.
  • Bundle index changes with schema changes in one migration.
  • Block writes while building an index.

Always:

-- Separate migration file, its own transaction
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_date
ON orders (customer_id, created_at DESC);

Use CREATE INDEX CONCURRENTLY so the table remains writable during index creation. In your Taskfile or CI, run index migrations in their own deployment window if needed.

Benchmarking Before/After

Capture the query and its plan before the index, and after. Commit both to bench/<query>.sql:

-- bench/top_orders_by_customer.sql
-- BEFORE:
-- Seq Scan on orders (cost=0.00..350000.00 rows=100000 width=32)
--   Filter: (customer_id = $1)
-- Planning Time: 0.115 ms
-- Execution Time: 2150.432 ms (on 100M row table)


-- AFTER index creation:
-- Index Scan using idx_orders_customer_date on orders (cost=0.15..5.32 rows=100 width=32)
--   Index Cond: (customer_id = $1)
-- Planning Time: 0.198 ms
-- Execution Time: 12.456 ms


SELECT * FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT 20;

References