Indexes and Index-Only Scans

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

Index types in PostgreSQL

B-tree (default): balanced tree structure, supports equality and range comparisons (=, <, >, BETWEEN, IN). Use for most columns.

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_orders_placed_at   ON orders (placed_at DESC);

Composite B-tree: index on multiple columns. Column order matters—queries filtering on the leading column can use the index even without filtering on later columns.

-- Supports: WHERE customer_id = 42 ORDER BY placed_at DESC
CREATE INDEX idx_orders_cust_placed
ON orders (customer_id, placed_at DESC);

GIN (Generalized Inverted Index): for multi-valued data such as JSONB, arrays, and full-text search (tsvector).

CREATE INDEX idx_products_tags ON products USING GIN (tags);
-- Supports: WHERE tags @> ARRAY['electronics']

GiST (Generalized Search Tree): for geometric types, range types, and full-text search. Supports nearest-neighbor and overlaps operators.

CREATE INDEX idx_locations_geom ON locations USING GIST (geom);

Partial index: indexes only rows that match a WHERE clause. Smaller and faster for queries that always filter on the same condition.

-- Only index active orders—ignores archived rows
CREATE INDEX idx_orders_active
ON orders (customer_id, placed_at)
WHERE status = 'active';

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

CREATE INDEX idx_users_lower_email ON users (LOWER(email));
-- Now this can use the index:
-- SELECT * FROM users WHERE LOWER(email) = '[email protected]'

Index-only scans and covering indexes

An index-only scan occurs when all columns referenced in the query (SELECT list, WHERE, ORDER BY) are present in the index. PostgreSQL reads the index and never visits the heap, eliminating most I/O for qualifying rows.

Use the INCLUDE clause to add non-key columns to an index without making them part of the sort order—these extra columns are available for index-only scans but are not used for filtering:

-- Query: SELECT order_id, total_amount FROM orders WHERE customer_id = 42
-- Without INCLUDE: requires heap visit to get total_amount
-- With INCLUDE: total_amount is stored in the index leaf pages

CREATE INDEX idx_orders_cust_covering
ON orders (customer_id)
INCLUDE (order_id, total_amount);

Verify an index-only scan is used:

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, total_amount
FROM orders
WHERE customer_id = 42;

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

Creating indexes safely in production

Use CREATE INDEX CONCURRENTLY to build an index without locking writes. The build takes longer but keeps the table available:

CREATE INDEX CONCURRENTLY idx_orders_status
ON orders (status)
WHERE status IN ('pending', 'processing');

Practical tips

• Run EXPLAIN (ANALYZE, BUFFERS) before and after adding an index to confirm it is being used and reduces buffer reads.
• Indexes speed up reads but slow down writes—avoid indexing columns that are written frequently but rarely queried.
• A visibility map must mark pages as all-visible before index-only scans can skip heap fetches. Run VACUUM after bulk inserts to update the visibility map.
• Check pg_stat_user_indexes for idx_scan = 0 to find unused indexes that add write overhead without benefiting any queries.