Indexes Basics

9 examples to get you started with PostgreSQL indexes - 6 basic and 3 intermediate.

Prerequisites

Table with thousands of rows for meaningful EXPLAIN output.

# Local dev with Docker (PostgreSQL 18)
docker run -d --name pg18 -e POSTGRES_PASSWORD=dev -p 5432:5432 postgres:18
psql postgres://postgres:dev@localhost:5432/postgres

Basic Examples

1. Create B-tree Index

Default access method for equality and range.

CREATE INDEX customers_email_idx ON app.customers (email);
EXPLAIN SELECT * FROM app.customers WHERE email = 'ada@example.com';

• Planner may choose Index Scan or Bitmap Index Scan
• Unique lookups often Index Scan
• Verify with EXPLAIN ANALYZE

2. Composite Index Order

Leading column must match predicate.

CREATE INDEX orders_account_created_idx ON app.orders (account_id, created_at DESC);

• (account_id) queries use index
• (created_at) alone may not
• Match sort order when possible

3. Index Write Cost

Every index updates on row change.

INSERT INTO app.customers (email) VALUES ('new@example.com');
-- updates customers_email_idx plus PK

• More indexes slow writes
• Drop unused indexes found in pg_stat_user_indexes
• Measure idx_scan before dropping

4. Unique Index

Enforces uniqueness and accelerates lookup.

CREATE UNIQUE INDEX customers_email_unique ON app.customers (lower(email));

• Expression indexes enforce case-insensitive unique
• Conflicts raise unique_violation
• Prefer CONSTRAINT USING INDEX when named

5. List Indexes on Table

Catalog inspection.

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'app' AND tablename = 'customers';

• indexdef shows expressions and WHERE
• Compare to migration files
• Watch duplicate overlapping indexes

6. When Seq Scan Wins

Small tables or wide fraction of rows.

EXPLAIN ANALYZE SELECT * FROM app.customers;

• Planner picks Seq Scan when cheaper
• Forcing indexes is rare
• Growing data changes choice

Intermediate Examples

7. Concurrent Index Build

Avoid long write locks in production.

CREATE INDEX CONCURRENTLY customers_created_idx
  ON app.customers (created_at);

• Cannot run inside transaction block
• Failed build may leave INVALID index
• REINDEX CONCURRENTLY for rebuilds

8. Covering INCLUDE Columns

Index-only friendly projections.

CREATE INDEX orders_cover_idx ON app.orders (account_id) INCLUDE (total);

• INCLUDE columns not in search key
• Helps index-only scans when visibility map fresh
• See index-only scans article

9. Monitor Index Usage

pg_stat_user_indexes.

SELECT relname, indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE schemaname = 'app'
ORDER BY idx_scan;

• idx_scan = 0 over weeks suggests candidate drop
• Reset stats only deliberately
• Pair with query workload review

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.