Postgresql Indexing Strategies

A secondary data structure that lets the planner find rows matching a predicate without scanning the whole table, at the cost of extra storage and write overhead.

The default index type, a balanced tree that keeps keys sorted and supports equality and range predicates, plus IS NULL, IS NOT NULL, and ORDER BY traversal in index order.

btree.

B-tree, Hash, GIN, GiST, SP-GiST, BRIN, and the extension-provided Bloom (and others like zombodb, rum, pg_trgm).

It speeds up reads (selections, joins, sorts) at the cost of additional storage, slower writes (INSERT/UPDATE/DELETE), and extra work for VACUUM.

CREATE INDEX idx_users_email ON users (email);

CREATE UNIQUE INDEX idx_users_email ON users (email); — Postgres automatically creates one when you declare UNIQUE or PRIMARY KEY.

CREATE INDEX idx_o_cust_date ON orders (customer_id, order_date);

Because the keys are sorted lexicographically; the index can only be used for predicates on a leading prefix of the columns.

Equality on a; equality on a + range on b; equality on a, b, c in that order. It cannot serve a query that filters only on b or c.

A plan where the planner reads everything it needs from the index itself (no heap fetch) because the visibility map shows the page is all-visible and all required columns are in the index.

When the query references only indexed columns (including those covered by INCLUDE) and the corresponding heap pages are marked all-visible by VACUUM.