PostgreSQL indexes: B-tree, GIN and when they help

In short: An index is an additional search tree that speeds up queries but costs writes and storage; you create it deliberately for frequent filters, sorts and joins.

In more detail: Without an index PostgreSQL reads the whole table for a query (seq scan). An index finds rows quickly like a subject index in a book.

In Depth

Index types

• B-tree (default): equality, ranges (<, >, BETWEEN), sorting (ORDER BY), prefix LIKE 'abc%'.
• Hash: equality only.
• GIN: multi-valued data (arrays, JSONB, full text tsvector).
• GiST/SP-GiST: geodata, ranges, similarity.
• BRIN: very large, time-ordered tables, tiny.
• pgvector (HNSW/IVFFlat): vector search.

CREATE INDEX idx_user_email ON users (email);
CREATE UNIQUE INDEX uq_user_email ON users (lower(email));
CREATE INDEX idx_order_customer_date ON orders (customer_id, created DESC);
CREATE INDEX idx_active ON users (id) WHERE active;       -- partial
CREATE INDEX idx_tags ON articles USING gin (tags);

When is an index worthwhile?

• Columns in WHERE, JOIN (foreign keys!), ORDER BY.
• Selective conditions (few hits).
• Composite indexes: order matters; (customer_id, created) helps with WHERE customer_id = … and … AND created …, not with a filter only on created (left-prefix rule).
• No index on tiny tables, on columns with few values (yes/no) alone, or if the table is constantly written heavily.
• Cost: every index slows INSERT/UPDATE/DELETE, uses storage and maintenance.

Checking with EXPLAIN

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42 ORDER BY created DESC LIMIT 10;

Seq Scan on a large table = suspicion; Index Scan/Index Only Scan/Bitmap Heap Scan = index used. The planner chooses by statistics (ANALYZE); sometimes a seq scan is faster with many hits. pg_stat_user_indexes shows never-used indexes (drop them). CREATE INDEX CONCURRENTLY builds without a long lock.

Typical mistakes

Functions on columns (WHERE lower(email) = …) prevent a normal index (→ function index). Type conversions, leading % in LIKE, OR across different columns. Missing index on a foreign key makes joins and deletes slow. Too many indexes. With serverless databases compute time also counts: fewer seq scans = fewer CPU hours.

See also: PostgreSQL, Query planner and EXPLAIN, Optimising queries with EXPLAIN, N+1 problem, Vector databases