Indexes

An index is a separate data structure that PostgreSQL maintains alongside a table to speed up data retrieval. Without indexes, every query performs a sequential scan — reading every row in the table. With the right indexes, queries find rows in logarithmic time.

How Indexes Work

When you create an index on a column, PostgreSQL builds a sorted copy of that column's data (typically as a B-tree) with pointers back to the full rows. When a query filters or sorts by that column, the planner can use the index to jump directly to the relevant rows instead of scanning the whole table.

Indexes speed up reads but add overhead to writes (INSERT, UPDATE, DELETE must also update the index). Always benchmark before adding indexes to write-heavy tables.

Creating and Dropping Indexes

-- Default B-tree index
CREATE INDEX idx_products_price ON products(price);

-- Unique index (enforces uniqueness like a UNIQUE constraint)
CREATE UNIQUE INDEX idx_products_sku ON products(sku);

-- Composite index (useful when filtering by multiple columns together)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Drop an index
DROP INDEX idx_products_price;

Index Types

Type	Best for
B-tree (default)	Equality, range, sorting — works with most operators
Hash	Equality comparisons only (=)
GIN	Full-text search, JSONB containment, arrays
GiST	Geometric data, full-text, range types
BRIN	Very large tables with naturally ordered data (e.g. timestamps)

Partial Indexes

A partial index only indexes rows that satisfy a condition, making it smaller and faster:

-- Only index active products
CREATE INDEX idx_active_products ON products(name)
WHERE is_active = true;

Checking Index Usage

Use EXPLAIN ANALYZE to see whether PostgreSQL is using your index:

EXPLAIN ANALYZE
SELECT * FROM products WHERE price < 50;

Look for Index Scan or Bitmap Index Scan in the output. A Seq Scan means the planner chose a sequential scan — either no suitable index exists or the planner determined the index would not be faster for this query.

Guidelines

• Index columns used in WHERE, JOIN ON, and ORDER BY clauses.
• Avoid indexing columns with very low cardinality (e.g. a boolean column on a 50/50 split).
• Use pg_stat_user_indexes to identify unused indexes and drop them.