What is the default index type in PostgreSQL, and which queries can it serve?
B-tree.
CREATE INDEXbuilds one unless you name another method, and so do primary keys and unique constraints. It serves=,<,>,BETWEEN,IN,IS NULLandORDER BY, because it keeps values sorted.It serves
LIKE 'abc%'only with theCcollation or an index built withtext_pattern_ops, and neverLIKE '%abc'. Every index also slows writes, since eachINSERTupdates it too.When do you use GIN, GiST, BRIN or hash indexes in PostgreSQL?
Choose by the operator the query uses:
Index Use it for B-tree equality, ranges, sorting (the default) GIN jsonb(@>,?), arrays, full-text search, trigramsGiST ranges and geometry ( &&, nearest neighbor), exclusion constraintsBRIN huge tables whose physical order follows a column, like a log by time Hash equality only GIN is fast to search but slower to update. BRIN keeps one summary per block range, so it is tiny, and useless when values are scattered. Index types
Can a unique index allow duplicates for soft-deleted rows in PostgreSQL?
PostgreSQLCREATE TABLE accounts (id int PRIMARY KEY, email text NOT NULL, deleted_at timestamptz); CREATE UNIQUE INDEX accounts_live_email ON accounts (email) WHERE deleted_at IS NULL; INSERT INTO accounts VALUES (1, 'asha@example.com', '2026-01-10'); INSERT INTO accounts VALUES (2, 'asha@example.com', '2026-05-02'); INSERT INTO accounts VALUES (3, 'asha@example.com', NULL); SELECT count(*) FROM accounts;It returns 3. A partial index covers only the rows that match its
WHERE, so uniqueness applies to live accounts only; a second live row with that email would fail. The planner uses a partial index only when the query'sWHEREimplies the index condition, so queries must repeatdeleted_at IS NULL.Why does
WHERE lower(email) = ...not use the index onemail, and how do you fix it?An index on
emailstores the raw values, so it cannot answer a condition onlower(email). Index the expression itself and query with exactly the same expression.PostgreSQLCREATE TABLE users (id int PRIMARY KEY, email text); INSERT INTO users SELECT g, 'User' || g || '@Example.com' FROM generate_series(1, 1000) g; CREATE INDEX users_lower_email_idx ON users (lower(email)); ANALYZE users; EXPLAIN SELECT id FROM users WHERE lower(email) = 'user42@example.com';Look for
Index Scan using users_lower_email_idxin the plan; withWHERE email = ...it falls back to aSeq Scan.CREATE UNIQUE INDEX ON users (lower(email))also makes emails unique regardless of case.What is a covering index with
INCLUDE, and when do you get an index-only scan?INCLUDE(PostgreSQL 11 and later) stores extra columns in the index without making them part of the key. A query that reads only indexed columns can then skip the table: an Index Only Scan.PostgreSQLCREATE TABLE orders (id int PRIMARY KEY, customer_id int, total numeric); INSERT INTO orders SELECT g, g % 100, g * 1.5 FROM generate_series(1, 10000) g; CREATE INDEX orders_customer_idx ON orders (customer_id) INCLUDE (total); VACUUM ANALYZE orders; EXPLAIN SELECT customer_id, total FROM orders WHERE customer_id = 7;Look for
Index Only Scan using orders_customer_idx. The catch: pages not marked all-visible in the visibility map still need a table visit, andVACUUMmaintains that map.Heap FetchesinEXPLAIN ANALYZEcounts those visits. Index-only scansDoes column order matter in a multicolumn index in PostgreSQL?
Yes. A B-tree on
(customer_id, created_at)is sorted by customer first, then by time within each customer. It servescustomer_id = 7alone or together with a range orORDER BYoncreated_at. Forcreated_atalone it is a poor fit, because the matching entries are spread through the whole index.Put equality columns first, then the range or sort column. PostgreSQL 18 added skip scan, which helps when the leading column has few distinct values.
What is the difference between
EXPLAINandEXPLAIN ANALYZEin PostgreSQL?EXPLAINshows the chosen plan with its estimates, without running the query.EXPLAIN ANALYZEruns it and adds the actual time, rows and loops of every node.PostgreSQLCREATE TABLE orders (id int PRIMARY KEY, customer_id int, total numeric); INSERT INTO orders SELECT g, g % 100, g * 1.5 FROM generate_series(1, 10000) g; ANALYZE orders; EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 7;Compare estimated and actual
rowsper node: a large gap is a misestimate, the usual root of a bad plan. Here you see aSeq ScanwithRows Removed by Filter, sincecustomer_idhas no index.EXPLAIN ANALYZEruns the statement for real, so wrap anUPDATEorDELETEinBEGINandROLLBACK. Using EXPLAINWhy is PostgreSQL not using my index?
Either the planner estimates a sequential scan is cheaper, or the index cannot match the condition. The usual causes:
- A small table, or a query that returns a large share of the rows.
- A function on the column:
date(created_at) = ...; write a range instead. - A type mismatch that casts the column:
id = 5.0on anintegercolumn becomesid::numeric = 5.0. - A leading wildcard:
LIKE '%term'. - A filter on only the second column of a multicolumn index.
- Stale statistics; run
ANALYZE.
To test, run
SET enable_seqscan = offin your session and compare the plans.What does
ANALYZEdo in PostgreSQL, and why does it matter for performance?It samples a table and stores per-column statistics (share of NULLs, distinct values, most common values, a histogram) that the planner uses to estimate row counts. Bad estimates mean bad join orders and scan choices.
PostgreSQLCREATE TABLE signups (country text, plan text); INSERT INTO signups SELECT CASE WHEN g % 10 < 7 THEN 'IN' ELSE 'US' END, 'free' FROM generate_series(1, 1000) g; ANALYZE signups; SELECT attname, n_distinct, most_common_vals, most_common_freqs FROM pg_stats WHERE tablename = 'signups' ORDER BY attname;The planner now knows 70% of the rows are
IN. Autovacuum analyzes after enough changes, but can lag behind a bulk load and skips temporary tables, so runANALYZEyourself then.CREATE STATISTICScovers correlated columns such as city and country.How do you add an index to a large production table without blocking writes?
Use
CREATE INDEX CONCURRENTLY. A plainCREATE INDEXblocksINSERT,UPDATEandDELETEfor the whole build; the concurrent build lets writes continue.CREATE INDEX CONCURRENTLY orders_customer_idx ON orders (customer_id);The price: it scans the table twice, waits for older transactions to finish, and cannot run inside a transaction block. If it fails, it leaves an
INVALIDindex that serves no queries but still slows writes: drop it and retry.REINDEX CONCURRENTLYexists since PostgreSQL 12. CREATE INDEXHow do you speed up
LIKE '%term%'searches in PostgreSQL?With a trigram index from the
pg_trgmextension. A B-tree cannot help a pattern that starts with a wildcard, but a GIN index on three-character chunks of the text servesLIKE,ILIKEand regular expressions that match anywhere.CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX products_name_trgm ON products USING gin (name gin_trgm_ops); SELECT id, name FROM products WHERE name ILIKE '%wireless%';For searching words rather than substrings, full-text search (
tsvectorwith a GIN index) handles word forms and ranking. Trigram indexes are large, so add one where a search box needs it, not on every text column.
PostgreSQL Indexes and Performance Interview Questions
B-tree and other index types, partial, expression and covering indexes, reading EXPLAIN, planner statistics and indexing without blocking writes.