Indexes and Index-Only Scans in PostgreSQL

Indexes are the primary tool for query performance in PostgreSQL. Rather than scanning every row in a table (a sequential scan), an index lets the database jump directly to matching rows. Understanding the available index types and when each applies is essential for production query tuning.

Syntax

-- Standard B-tree index
CREATE INDEX index_name ON table_name (column_name);

-- Covering index with INCLUDE for index-only scans
CREATE INDEX index_name ON table_name (key_column) INCLUDE (extra_column);

-- Non-blocking build for production tables
CREATE INDEX CONCURRENTLY index_name ON table_name (column_name);

-- Drop an index
DROP INDEX CONCURRENTLY index_name;

Practical Example

Create an events and registrations schema to demonstrate index types:

CREATE TABLE events (
  id          SERIAL PRIMARY KEY,
  title       VARCHAR(200) NOT NULL,
  category    VARCHAR(50)  NOT NULL,
  starts_at   TIMESTAMPTZ  NOT NULL,
  venue_id    INTEGER,
  status      VARCHAR(20)  NOT NULL DEFAULT 'scheduled'
);

CREATE TABLE registrations (
  id         SERIAL PRIMARY KEY,
  event_id   INTEGER REFERENCES events(id),
  attendee   VARCHAR(100) NOT NULL,
  registered_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  tags        TEXT[]
);

-- B-tree index for range queries on event start time
CREATE INDEX idx_events_starts_at ON events (starts_at DESC);

-- Composite index: filter by status, sort by starts_at
CREATE INDEX idx_events_status_starts
ON events (status, starts_at DESC);

Check which index the planner chooses:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, starts_at
FROM events
WHERE status = 'scheduled'
  AND starts_at >= now()
ORDER BY starts_at
LIMIT 50;

Index Types in PostgreSQL

B-tree (default) — balanced tree, supports =, <, >, BETWEEN, IN, LIKE 'prefix%'. Use for most columns.

Composite B-tree — index on multiple columns. Column order matters: queries filtering on only the leading column can use the index; queries filtering only on a trailing column cannot.

-- Supports: WHERE event_id = 42 ORDER BY registered_at DESC
CREATE INDEX idx_reg_event_time
ON registrations (event_id, registered_at DESC);

GIN (Generalized Inverted Index) — for multi-valued data: JSONB, arrays, full-text tsvector. Supports @>, &&, @@ operators.

CREATE INDEX idx_reg_tags ON registrations USING GIN (tags);
-- Supports: WHERE tags @> ARRAY['vip']

GiST (Generalized Search Tree) — geometric types, range types, full-text. Supports nearest-neighbor and overlap operators.

CREATE INDEX idx_events_period ON events USING GIST (tstzrange(starts_at, starts_at + interval '2 hours'));

Partial index — indexes only rows matching a WHERE clause. Smaller and faster for queries that always apply the same filter.

-- Only index active events—ignores cancelled rows
CREATE INDEX idx_events_active
ON events (starts_at DESC)
WHERE status IN ('scheduled', 'live');

Expression index — indexes a computed expression. Required when queries filter on function results.

CREATE INDEX idx_events_lower_title ON events (LOWER(title));
-- Now usable by: WHERE LOWER(title) = 'product launch'

Index-Only Scans and Covering Indexes

An index-only scan occurs when all columns needed by a query—SELECT list, WHERE, ORDER BY—are present in the index. PostgreSQL reads only the index and never visits the heap.

Use the INCLUDE clause to add non-key columns to the index leaf pages without making them part of the sort structure:

-- Query: SELECT id, title FROM events WHERE status = 'scheduled'
-- Without INCLUDE: heap visit required to fetch title
-- With INCLUDE: title is on the index leaf page
CREATE INDEX idx_events_status_covering
ON events (status)
INCLUDE (id, title, starts_at);

Verify with EXPLAIN:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, starts_at
FROM events
WHERE status = 'scheduled';

Look for Index Only Scan and Heap Fetches: 0 in the output. Zero heap fetches confirms no table access occurred.

The visibility map must mark heap pages as all-visible for index-only scans to skip heap fetches. After bulk inserts or updates, run VACUUM to update it:

VACUUM events;

Testing with Vela

The safest way to validate a new index is to add it on a Vela branch cloned from production, then run EXPLAIN (ANALYZE, BUFFERS) against real production-scale data. Compare buffer reads and execution time before and after adding the index. Because the branch is an instant copy-on-write clone, you can test CREATE INDEX CONCURRENTLY without any risk to production—and discard the branch if the index does not help.

Production Tips

• Use CREATE INDEX CONCURRENTLY on live tables to build the index without blocking writes. The build takes longer but keeps the table fully accessible.
• Indexes speed reads but slow writes. Avoid indexing columns that are written frequently and rarely queried; use pg_stat_user_indexes to find idx_scan = 0 candidates for removal.
• Run VACUUM after bulk inserts to update the visibility map; without it, index-only scans fall back to heap fetches even when the index covers all required columns.
• Check cardinality before indexing: columns with very few distinct values (like a boolean flag) rarely benefit from a standard B-tree index unless combined with other columns or used as a partial index.
• After adding an index, run ANALYZE table_name so the planner has fresh statistics about the index’s selectivity.