Database Tutorial #4: PostgreSQL Indexing and Performance

Your query works. But it is slow. The fix is almost always an index.

This tutorial covers how indexes work in PostgreSQL, when to use them, and how to confirm they are actually helping.

How a B-tree Index Works

Without an index, PostgreSQL reads every row in the table to find matches. This is called a sequential scan. For a table with 10 million rows, that means 10 million comparisons.

A B-tree index stores the values in a sorted tree. PostgreSQL jumps straight to the matching rows — O(log n) instead of O(n).

-- Create an index on the email column
CREATE INDEX idx_users_email ON users (email);

-- PostgreSQL now uses the index for this query
SELECT * FROM users WHERE email = 'alex@example.com';

B-tree indexes work for: =, <, >, <=, >=, BETWEEN, IN, IS NULL, LIKE 'prefix%'.

They do NOT work for: LIKE '%suffix' or LIKE '%middle%'.

Note: LIKE 'prefix%' uses a B-tree index only if the database uses a C locale. For UTF-8 databases, create the index with text_pattern_ops: CREATE INDEX ON users (email text_pattern_ops);.

Reading EXPLAIN ANALYZE

EXPLAIN ANALYZE shows you exactly what PostgreSQL does for a query.

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

Output without an index:

Seq Scan on orders  (cost=0.00..2847.00 rows=12 width=84)
                    (actual time=0.032..18.4 rows=12 loops=1)
  Filter: (user_id = 42)
  Rows Removed by Filter: 99988
Planning Time: 0.1 ms
Execution Time: 18.5 ms

After adding an index:

CREATE INDEX idx_orders_user_id ON orders (user_id);

Index Scan using idx_orders_user_id on orders  (cost=0.29..6.8 rows=12 width=84)
                                                (actual time=0.03..0.08 rows=12 loops=1)
  Index Cond: (user_id = 42)
Planning Time: 0.2 ms
Execution Time: 0.1 ms

Key things to read:

Seq Scan — reading the whole table (usually bad for large tables)
Index Scan — using the index (good)
Index Only Scan — query answered entirely from the index (best)
cost=X..Y — estimated cost; lower is faster
actual time=X..Y — real execution time in milliseconds

Multi-Column Indexes

Index multiple columns when you filter on more than one:

-- Slow: two separate indexes don't combine well
SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';

-- Fast: one composite index
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

Column order matters. The index above helps:

WHERE user_id = 42 ✅
WHERE user_id = 42 AND status = 'pending' ✅
WHERE status = 'pending' ❌ (leading column must be present)

Put the most selective column first, or the column you filter most often.

Partial Indexes

A partial index only indexes rows that match a condition. Smaller, faster, and more targeted:

-- Index only active users (much smaller than indexing all users)
CREATE INDEX idx_users_active_email ON users (email)
WHERE active = true;

-- Index only unprocessed orders
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

These are perfect when most rows are in one state (e.g., 95% of orders are complete) and you only query the minority.

GIN Indexes

GIN (Generalized Inverted Index) is for columns that contain multiple values: arrays, jsonb, and full-text search vectors.

-- Index a jsonb column for containment queries
CREATE INDEX idx_products_tags ON products USING GIN (tags);

-- Now this is fast
SELECT * FROM products WHERE tags @> '["electronics", "sale"]';

-- Index an array column
CREATE INDEX idx_posts_categories ON posts USING GIN (categories);
SELECT * FROM posts WHERE categories @> ARRAY['programming'];

Unique Indexes

A unique index enforces uniqueness AND speeds up lookups:

-- Unique constraint creates a unique index automatically
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

-- Or create it directly
CREATE UNIQUE INDEX idx_users_email ON users (email);

Index Bloat and VACUUM

When you update or delete rows, PostgreSQL marks old versions as dead but does not remove them immediately. Dead rows accumulate and slow down index scans.

VACUUM cleans up dead rows:

-- Manual vacuum on a specific table
VACUUM users;

-- Full vacuum (reclaims disk space, locks table)
VACUUM FULL users;

-- Check for bloat
SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

PostgreSQL runs autovacuum automatically, but heavily updated tables may need manual tuning.

When NOT to Index

Indexes speed up reads but slow down writes. Every INSERT, UPDATE, and DELETE must also update the index.

Skip indexes for:

Small tables — sequential scans are faster below ~1000 rows
Columns with low cardinality — boolean, status with 2-3 values (unless partial)
Write-heavy tables — the write overhead outweighs read benefits
Rarely queried columns — indexes that are never used waste space and slow writes

Check which indexes are actually used:

SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC;

Indexes with idx_scan = 0 have never been used. Drop them.

Python Example

import psycopg
import time

conn = psycopg.connect("postgresql://localhost/mydb")

# Check query plan
with conn.cursor() as cur:
    cur.execute("""
        EXPLAIN ANALYZE
        SELECT * FROM orders WHERE user_id = %s AND status = %s
    """, (42, 'pending'))

    for row in cur.fetchall():
        print(row[0])

# Create index programmatically
# CONCURRENTLY cannot run inside a transaction block — use autocommit
conn.autocommit = True
with conn.cursor() as cur:
    cur.execute("""
        CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_user_status
        ON orders (user_id, status)
        WHERE status != 'completed'
    """)
    print("Index created")
conn.autocommit = False  # Restore transaction mode

CREATE INDEX CONCURRENTLY builds the index without locking the table — safe for production.

TypeScript Example

import { Pool } from "pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function analyzeQuery(userId: number) {
  const { rows } = await pool.query(
    "EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = $1",
    [userId]
  );

  rows.forEach((row: { "QUERY PLAN": string }) => {
    console.log(row["QUERY PLAN"]);
  });
}

async function createIndex() {
  await pool.query(`
    CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_user_id
    ON orders (user_id)
  `);
  console.log("Index created");
}

What’s Next?

You can now write fast queries with proper indexes. Next, let’s explore PostgreSQL’s powerful JSON support and full-text search.

Next: Database Tutorial #5: PostgreSQL JSON and Full-Text Search

How a B-tree Index Works#

Reading EXPLAIN ANALYZE#

Multi-Column Indexes#

Partial Indexes#

GIN Indexes#

Unique Indexes#

Index Bloat and VACUUM#

When NOT to Index#

Python Example#

TypeScript Example#

What’s Next?#

Related Articles#