PostgreSQL Query Performance & Advanced Indexing

Overview

PostgreSQL's query planner is sophisticated, but it needs the right indexes and query shapes to produce optimal plans. This guide covers the tools and patterns needed to move from seconds-per-query to milliseconds.

Reading EXPLAIN ANALYZE

Always start with EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT):

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id;

Key numbers to look for:

Term	Meaning
Seq Scan	Full table scan — add an index
Rows Removed by Filter	High = index not selective enough
Buffers: shared hit	Data served from memory (good)
Buffers: shared read	Data read from disk (costly)
actual rows vs rows	Large gap = stale statistics, run ANALYZE

B-tree vs GIN vs BRIN

-- B-tree (default) — equality + range on scalar types
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);

-- GIN — full-text search, JSONB containment, array overlap
CREATE INDEX idx_products_tags ON products USING GIN (tags);
-- Enables: WHERE tags @> ARRAY['electronics']

-- BRIN — huge append-only tables (logs, time-series) — tiny index size
CREATE INDEX idx_logs_ts ON logs USING BRIN (created_at) WITH (pages_per_range = 128);

Partial Indexes

Index only the rows you actually query — dramatically smaller and faster:

-- Only index pending orders (not the millions of completed ones)
CREATE INDEX idx_orders_pending ON orders (created_at DESC)
  WHERE status = 'pending';

-- Query MUST include the WHERE predicate to use this index
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC LIMIT 20;

Covering Indexes (Index-Only Scans)

Include all projected columns in the index to avoid heap fetches:

CREATE INDEX idx_users_email_covering
  ON users (email)
  INCLUDE (id, name, created_at);

-- This query never touches the table
SELECT id, name, created_at FROM users WHERE email = 'user@example.com';
-- Plan: Index Only Scan, Heap Fetches: 0

CTEs for Readable Complex Queries

WITH
  active_users AS (
    SELECT id, name FROM users WHERE last_active > NOW() - INTERVAL '30 days'
  ),
  user_revenue AS (
    SELECT
      o.user_id,
      SUM(o.total) AS ltv,
      COUNT(*) AS order_count
    FROM orders o
    WHERE o.status = 'completed'
    GROUP BY o.user_id
  )
SELECT
  u.name,
  COALESCE(r.ltv, 0) AS lifetime_value,
  COALESCE(r.order_count, 0) AS orders
FROM active_users u
LEFT JOIN user_revenue r ON r.user_id = u.id
ORDER BY lifetime_value DESC
LIMIT 100;

Note: In PostgreSQL 12+, CTEs are inlined by default. Use MATERIALIZED to force a temp table if you reference a CTE multiple times.

Window Functions for Running Totals & Ranking

SELECT
  date_trunc('day', created_at)  AS day,
  SUM(total)                     AS daily_revenue,
  SUM(SUM(total)) OVER (
    ORDER BY date_trunc('day', created_at)
  )                              AS running_total,
  RANK() OVER (
    PARTITION BY date_trunc('month', created_at)
    ORDER BY SUM(total) DESC
  )                              AS rank_in_month
FROM orders
WHERE status = 'completed'
GROUP BY day
ORDER BY day;

Connection Pooling with PgBouncer

Node.js apps should never open a raw connection per request. Use PgBouncer in transaction mode:

; pgbouncer.ini
[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25       ; one per CPU core on the DB server

// drizzle / pg — connect to PgBouncer, not directly to Postgres
const pool = new Pool({ connectionString: process.env.DATABASE_POOL_URL });

Autovacuum Tuning for High-Write Tables

-- Speed up autovacuum on a hot orders table
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,   -- vacuum when 1% of rows are dead
  autovacuum_analyze_scale_factor = 0.005  -- analyze when 0.5% changed
);

Key Takeaways

• Always EXPLAIN (ANALYZE, BUFFERS) — never guess at query plans
• Partial indexes on filtered workloads cost a fraction of full indexes
• Covering indexes eliminate heap fetches entirely for key queries
• Window functions replace self-joins for running totals and ranking
• PgBouncer transaction pooling handles thousands of Node.js connections on a small DB server