PostgreSQL Query Optimization: EXPLAIN ANALYZE, Indexes, and Schema Design

Database performance is the most common production bottleneck. The difference between 2ms and 2 seconds is understanding how PostgreSQL reads query plans and uses indexes.

Reading EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) AS order_count, SUM(o.total) AS revenue
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2025-01-01'
  AND o.status = 'completed'
GROUP BY u.id, u.name
ORDER BY revenue DESC
LIMIT 100;

Key things to look for:

• Seq Scan: Reading every row — bad for large tables
• Index Scan: Using an index — usually good
• Index Only Scan: All data in index — best, no heap access
• rows=X (actual rows=Y): When X >> Y, statistics are stale → run ANALYZE
• Buffers: hit=N read=M: High "read" means disk I/O, consider buffer pool size

Index Design

-- 1. Composite: most selective column first
-- Query: WHERE status = 'active' AND created_at > '2025-01-01'
CREATE INDEX idx_orders_status_created
ON orders (status, created_at)
WHERE status IN ('pending', 'processing');  -- Partial index!

-- 2. Index covering ORDER BY
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);
-- Serves: WHERE user_id = ? ORDER BY created_at DESC LIMIT 10

-- 3. Covering index: avoid heap access entirely
CREATE INDEX idx_users_email_covering ON users (email)
INCLUDE (name, id, subscription_tier);
-- Query: SELECT name, id, tier FROM users WHERE email = ?
-- → Index Only Scan (no table access)

-- 4. GIN for arrays and JSONB
CREATE INDEX idx_products_tags ON products USING GIN(tags);
CREATE INDEX idx_events_payload ON events USING GIN(payload jsonb_path_ops);

SELECT * FROM products WHERE tags @> ARRAY['python', 'ml'];
SELECT * FROM events WHERE payload @@ '$.user_id == 123';

Avoiding N+1 Queries

-- N+1: get users, then loop to get each user's orders separately

-- Solution: JOIN with aggregation
SELECT
    u.id, u.name, u.email,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'completed'
WHERE u.created_at > '2025-01-01'
GROUP BY u.id, u.name, u.email;

-- LATERAL join for top-N per group
SELECT u.*, recent.orders
FROM users u
CROSS JOIN LATERAL (
    SELECT json_agg(o ORDER BY o.created_at DESC) AS orders
    FROM (
        SELECT id, total, status
        FROM orders WHERE user_id = u.id
        ORDER BY created_at DESC LIMIT 3
    ) o
) recent;

CTE for Complex Multi-Step Queries

WITH
revenue_90d AS (
    SELECT user_id, SUM(total) AS revenue
    FROM orders
    WHERE created_at >= NOW() - INTERVAL '90 days'
    GROUP BY user_id
),
segments AS (
    SELECT user_id,
        CASE
            WHEN revenue >= 1000 THEN 'high_value'
            WHEN revenue >= 200  THEN 'medium_value'
            ELSE 'low_value'
        END AS segment
    FROM revenue_90d
)
SELECT u.*, COALESCE(s.segment, 'inactive') AS segment
FROM users u
LEFT JOIN segments s ON s.user_id = u.id;

Partitioning Large Tables

-- Range partitioning by date (time-series data)
CREATE TABLE events (
    id BIGSERIAL,
    user_id INT NOT NULL,
    event_type TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    payload JSONB
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_04 PARTITION OF events
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

-- Indexes apply to all partitions automatically
CREATE INDEX ON events (user_id, created_at);

-- Dropping old data: instant vs hours of DELETE
DROP TABLE events_2024_01;  -- Instant!

Connection Pooling

# pgbouncer.ini
[pgbouncer]
pool_mode = transaction      # Release after each transaction
default_pool_size = 25       # Connections per database
max_client_conn = 500        # Max application connections
listen_port = 6432

PostgreSQL optimization is about data distribution, index selectivity, and join strategies. EXPLAIN ANALYZE tells you exactly what happened — and usually how to fix it.

→ Format your SQL queries with the SQL Prettify tool.