An index is a separate structure that lets Postgres find rows without scanning the whole table.
Creating indexes
CREATE INDEX idx_dept ON students(department);
CREATE UNIQUE INDEX idx_email ON students(email);
CREATE INDEX idx_dept_cgpa ON students(department, cgpa); -- composite
CREATE INDEX CONCURRENTLY idx_dept ON students(department); -- no write lock
DROP INDEX idx_dept;
\d students -- indexes listed at the bottom
CREATE INDEX CONCURRENTLY takes much longer but does not block writes — always use it on a live production table.
Index types
Postgres is unusual in offering several genuinely different index structures, each suited to a different kind of query:
| Type | Good for |
|---|---|
| B-tree (default) | Equality, ranges, sorting — the general-purpose choice |
| Hash | Equality only; rarely worth it over B-tree today |
| GIN | Arrays, JSONB, full-text search — "does this contain that" |
| GiST | Geometric data, ranges, nearest-neighbour, exclusion constraints |
| SP-GiST | Non-balanced structures: text prefixes, IP ranges |
| BRIN | Huge tables physically ordered by the indexed column, e.g. time-series by timestamp |
CREATE INDEX idx_tags ON posts USING gin (tags);
CREATE INDEX idx_attrs ON products USING gin (attrs jsonb_path_ops);
CREATE INDEX idx_during ON bookings USING gist (during);
CREATE INDEX idx_created ON logs USING brin (created_at); -- tiny, great for append-only
A BRIN index on a billion-row, insert-only log table can be a few hundred kilobytes, versus gigabytes for a B-tree, because it only stores the min/max per block range.
The leftmost-prefix rule
A composite B-tree index on (a, b, c) serves queries filtering on a, a,b, or a,b,c, but not on b alone.
CREATE INDEX idx_abc ON t(a, b, c);
WHERE a = 1 -- uses it
WHERE a = 1 AND b = 2 -- uses it
WHERE b = 2 -- cannot use it
Put the most selective equality column first, and any range condition (>, <, BETWEEN) last.
Covering and index-only scans
CREATE INDEX idx_cover ON students(department, cgpa) INCLUDE (name_en);
SELECT name_en, cgpa FROM students WHERE department = 'CSE';
INCLUDE adds columns to the index leaf pages without making them part of the sort key, purely so the query can be answered from the index alone — an index-only scan. This requires the table's visibility map to be up to date, which VACUUM maintains.
Partial indexes
Index only the rows you actually query — smaller, faster to maintain:
CREATE INDEX idx_active ON students(department) WHERE status = 'active';
CREATE UNIQUE INDEX idx_one_active_sub ON subscriptions(user_id) WHERE status = 'active';
Expression indexes
CREATE INDEX idx_lower_email ON students (lower(email));
CREATE INDEX idx_year ON students ((extract(year FROM admitted_on)));
SELECT * FROM students WHERE lower(email) = 'a@b.com'; -- now uses the index
The expression in the query must match the indexed expression exactly.
When an index is not used
WHERE age + 1 > 20 -- arithmetic on the column
WHERE cast(id AS text) = '5' -- type mismatch
WHERE name_en LIKE '%uddin' -- leading wildcard
Rewrite so the column stands alone, or build an expression index for the exact form you need.
Maintenance and unused indexes
Every index slows down INSERT, UPDATE and DELETE and takes disk space. Find ones that are not earning their keep:
SELECT relname AS table, indexrelname AS index, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;
SELECT pg_size_pretty(pg_relation_size('idx_dept'));
REINDEX INDEX CONCURRENTLY idx_dept; -- rebuild a bloated index without locking