What index types does PostgreSQL support, and why is my index sometimes not used?
B-tree is the default and handles equality, ranges and sorting. GIN indexes values that contain many items, like JSONB, arrays and full-text search. BRIN is a tiny index for huge tables whose rows are stored in a natural order, like time-series data. The planner skips an index when a full scan looks cheaper, or when the query is written in a way the index cannot match.
Explain it simply
An index is like the different ways a library helps you find a book. A B-tree is the sorted catalogue: find a title, or every title between "C" and "F". A GIN index is the back-of-the-book index: for every word, a list of the pages it appears on. A BRIN index is the shelf label that says "shelf 12 has books from 2019 to 2020": very small, and only useful because the books were shelved in order.
Having a catalogue does not mean you always use it. If you want 90% of the books in the library, walking along the shelves is faster than looking up each one. PostgreSQL's planner makes the same call.
Example
-- B-tree (default): equality, ranges, ORDER BY
CREATE INDEX idx_students_roll ON students (roll_no);
-- GIN on JSONB: "which products have this attribute?"
CREATE INDEX idx_products_attrs ON products USING gin (attributes);
SELECT * FROM products WHERE attributes @> '{"brand": "Tata"}';
-- BRIN on an append-only log ordered by time
CREATE INDEX idx_upi_txn_time ON upi_transactions USING brin (created_at);
SELECT * FROM upi_transactions
WHERE created_at >= '2026-09-01' AND created_at < '2026-09-02';| Type | Good for | Size |
|---|---|---|
| B-tree | =, <, >, BETWEEN, ORDER BY, unique constraints | Medium |
| GIN | JSONB, arrays, full-text search (@>, ?, @@) | Large, slower to update |
| BRIN | Very large tables where values follow physical row order | Very small |
| GiST | Geometry, ranges, nearest-neighbour searches | Medium |
| Hash | Equality only | Medium |
How it works
For every query the planner estimates the cost of each possible plan using table statistics (row counts and value distributions collected by ANALYZE). An index scan jumps to matching rows but reads pages in random order. A sequential scan reads the whole table in order, which disks and the OS cache handle well. If the query matches a large share of rows, the sequential scan wins.
Common reasons an index is not used:
- Low selectivity:
WHERE is_active = truewhen 95% of rows are active. - Small table: a few pages are cheaper to read fully.
- A function on the column:
WHERE lower(email) = 'a@b.com'cannot use an index onemail. Create an expression index onlower(email)instead. - Leading wildcard:
LIKE '%kumar'cannot use a normal B-tree. - Wrong column order: an index on
(city, name)is built for queries that filter oncity. Filtering only onnameusually cannot use it efficiently. - Type mismatch: comparing a text column with a number forces a cast.
- Stale statistics: after a big data load, run
ANALYZEso estimates are accurate.
Real-world use
- B-tree on foreign keys like
orders.customer_id, because joins and deletes on the parent table need it. PostgreSQL does not create these automatically. - GIN on a JSONB
attributescolumn in an e-commerce catalogue, or on atsvectorcolumn for search. - BRIN on a billions-row log table, where a B-tree would take gigabytes.
Common mistakes
- Adding an index on every column. Each index slows down INSERT and UPDATE and uses disk.
- Using
SET enable_seqscan = offin production to "force" an index. It is a debugging switch, not a fix. - Expecting BRIN to help on a column whose values are scattered randomly across the table.
What to say in the interview
"B-tree is the default and covers equality, ranges and sorting. GIN is an inverted index for values with many parts, like JSONB, arrays and full-text search. BRIN stores only a min and max per block range, so it is tiny and works well on huge, naturally ordered tables like logs. If an index is not used, I run EXPLAIN and check selectivity, whether I wrapped the column in a function, a leading wildcard, column order in a composite index, type mismatches, and whether statistics are up to date."
Follow-up questions
- What is a partial index, and when would you use one?
- What is an index-only scan?
- How do you find unused indexes? (Hint:
pg_stat_user_indexes.idx_scan.)
Go deeper
Read these once the basics above feel easy.
A partial index covers only some rows, for example CREATE INDEX ON orders (created_at) WHERE status = 'pending';. It is smaller and useful when queries always target that subset. A covering index uses INCLUDE to store extra columns in the leaf pages so the query can be answered from the index alone (an index-only scan), as long as the visibility map shows the pages are all-visible.
For LIKE 'abc%' prefix searches, a B-tree only helps if the column uses the C collation or the index is built with text_pattern_ops. For substring searches like LIKE '%abc%', the pg_trgm extension provides trigram operator classes for GIN and GiST. On busy tables, build indexes with CREATE INDEX CONCURRENTLY so writes are not blocked during the build.