In this post we are going to cover indexes in PostgreSQL: when the planner uses an index and when it ignores it, composite, covering, partial and expression indexes, the index types beyond B-tree, and how to create an index on a busy table. These are 7 real PostgreSQL interview questions, asked the way they come up on the job.
An index is a separate, sorted structure that lets Postgres find rows without reading the whole table. But an index is not free and it is not magic. It costs disk space and slows down writes, and the planner uses it only when it is cheaper than the alternative. Every plan in this post is real output from EXPLAIN.
Every query below is complete, so you can copy it, run it on the sample data and get exactly the output shown under it.
Part 5 of the Real Time PostgreSQL Interview Questions and Answers series on TechBrothersIT.
- Let's create some sample data
- Question 1: The orders table has an index on status. But the query for delivered orders still does a sequential scan. A teammate says the index is broken. Is it?
- Question 2: You created one index on customer ID and order date. A query that filters only on order date is still slow. Why, and how should you order the columns in a composite index?
- Question 3: A hot API query reads the customer ID, the order date and the amount for one customer, thousands of times per second. How can an index answer it without touching the table at all?
- Question 4: Workers ask for pending orders all day. Pending is only 1 percent of the table, but the index on status covers every row. Can the index be smaller and faster?
- Question 5: Login looks up users with lower of email equals the typed address. There is an index on email, but the query scans all 50,000 users. Why is the index ignored?
- Question 6: A search box runs LIKE with a wildcard on both sides, and it is slow even though the column has an index. Which index types does Postgres have, and which one fixes this?
- Question 7: You must add an index to a very large orders table in production, during business hours. A plain CREATE INDEX would block every insert and update until it finishes. What do you do?
- Key points to remember
- The full SQL script
Let's create some sample data
For indexes we need tables big enough for the planner to have a real choice. So this post uses two generated tables: orders_big with 200,000 orders, where only 1 percent are pending, and users with 50,000 users whose emails are stored in mixed case. The script below creates everything this post needs. It was written for and run on PostgreSQL 16.
-- 200,000 orders: 66% delivered, 33% shipped, 1% pending
CREATE TABLE orders_big AS
SELECT g AS order_id,
(g * 7 % 5000) + 1 AS customer_id,
CASE WHEN g % 100 = 0 THEN 'pending'
WHEN g % 3 = 0 THEN 'shipped'
ELSE 'delivered' END AS status,
DATE '2025-01-01' + (g % 500) AS order_date,
(g % 900) + 10 AS amount
FROM generate_series(1, 200000) AS g;
ALTER TABLE orders_big ADD PRIMARY KEY (order_id);
CREATE INDEX idx_orders_status ON orders_big (status);
-- 50,000 users with mixed-case emails
CREATE TABLE users AS
SELECT g AS user_id, 'User' || g || '@Mail.com' AS email, 'Name ' || g AS full_name
FROM generate_series(1, 50000) AS g;
ALTER TABLE users ADD PRIMARY KEY (user_id);
CREATE INDEX idx_users_email ON users (email);
VACUUM ANALYZE orders_big;
VACUUM ANALYZE users;Question 1: The orders table has an index on status. But the query for delivered orders still does a sequential scan. A teammate says the index is broken. Is it?
No. The planner compares costs. An index helps only when the filter is selective, when it matches a small part of the table. Delivered is two thirds of the table, so reading the whole table once is cheaper than jumping through the index.
Look at how many rows each filter matches. Pending is 2,000 rows out of 200,000: 1 percent. For so few rows, the index is the shortcut. Delivered is 132,000 rows: 66 percent. Through the index, Postgres would jump around the table 132,000 times. Reading every page once, in order, is simply less work.
Here is the plan for delivered. A Seq Scan with a filter, exactly what the teammate saw.
EXPLAIN (COSTS OFF)
SELECT * FROM orders_big
WHERE status = 'delivered';Seq Scan on orders_big Filter: (status = 'delivered'::text)
Same table, same index, but now we ask for pending. The plan switches to the index. Nothing was broken. The planner used its statistics about the column, and chose the cheaper path each time.
EXPLAIN (COSTS OFF)
SELECT * FROM orders_big
WHERE status = 'pending';Bitmap Heap Scan on orders_big
Recheck Cond: (status = 'pending'::text)
-> Bitmap Index Scan on idx_orders_status
Index Cond: (status = 'pending'::text)So before you blame the index, check three things. How selective is the filter? Are the statistics fresh, meaning has ANALYZE run? And does the query wrap the column in a function or a cast?
Check: selectivity, fresh statistics (ANALYZE), and no function around the column.
Question 2: You created one index on customer ID and order date. A query that filters only on order date is still slow. Why, and how should you order the columns in a composite index?
A B-tree index is sorted by its first column, and by the second column only inside each first value. A filter on the first column finds one continuous block. A filter on the second column alone is scattered across the whole index.
Think of a phone book sorted by last name, then first name. Here are the index entries, sorted by customer, then by date. Customer 42 is one continuous block. Postgres jumps to its start and reads three entries. Now find the 1st of June 2025 without a customer. The matches are spread everywhere, so the whole index has to be read.
| customer_id | order_date |
|---|---|
| 41 | 2025-03-01 |
| 41 | 2025-09-12 |
| 42 | 2025-01-15 |
| 42 | 2025-06-01 |
| 42 | 2026-01-01 |
| 43 | 2025-06-01 |
| 43 | 2025-11-20 |
| 44 | 2025-06-01 |
WHERE customer_id = 42one block: jump in, read 3 entriesWHERE order_date = '2025-06-01'scattered: every entry must be checkedLet's ask the planner, this time with costs. For customer 42, the index step costs about 4. It goes straight to the block.
EXPLAIN
SELECT * FROM orders_big
WHERE customer_id = 42;Bitmap Heap Scan on orders_big (cost=4.60..145.32 rows=40 width=25)
Recheck Cond: (customer_id = 42)
-> Bitmap Index Scan on idx_orders_cust_date (cost=0.00..4.59 rows=40 width=0)
Index Cond: (customer_id = 42)Now filter on the date alone. Postgres can still use the index, because the index is smaller than the table. But look at the cost of the index step. It is hundreds of times higher, because it walks the entire index instead of one block.
EXPLAIN
SELECT * FROM orders_big
WHERE order_date = DATE '2026-01-01';Bitmap Heap Scan on orders_big (cost=2200.39..3096.99 rows=399 width=25)
Recheck Cond: (order_date = '2026-01-01'::date)
-> Bitmap Index Scan on idx_orders_cust_date (cost=0.00..2200.30 rows=399 width=0)
Index Cond: (order_date = '2026-01-01'::date)If you often filter by date alone, create a second index that starts with the date. One note for the interview: PostgreSQL 18 added skip scan, which helps in this case, but only when the first column has few distinct values. Column order still matters.
but only when that column has few distinct values
Question 3: A hot API query reads the customer ID, the order date and the amount for one customer, thousands of times per second. How can an index answer it without touching the table at all?
Build a covering index: key columns for the search, plus INCLUDE for the extra columns the query returns. Then Postgres can do an Index Only Scan. It still needs the visibility map, so the table must be vacuumed.
A normal index scan has two steps. Find the entries in the index, then visit the table for each row to fetch the other columns. That second step is random reading, and it is the expensive part. With a covering index, every column the query needs is already in the index, so the trip to the table disappears.
CREATE INDEX ... (customer_id, order_date) INCLUDE (amount)First, the query with the composite index from the last question. Postgres finds 40 rows in the index, and then visits 40 heap blocks to get the amount.
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT customer_id, order_date, amount
FROM orders_big
WHERE customer_id = 42;Bitmap Heap Scan on orders_big (actual rows=40 loops=1)
Recheck Cond: (customer_id = 42)
Heap Blocks: exact=40
-> Bitmap Index Scan on idx_orders_cust_date (actual rows=40 loops=1)
Index Cond: (customer_id = 42)Now create the covering index with INCLUDE, and vacuum the table. The plan becomes an Index Only Scan, with Heap Fetches zero. The table was never touched.
CREATE INDEX idx_orders_cover
ON orders_big (customer_id, order_date)
INCLUDE (amount);EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT customer_id, order_date, amount
FROM orders_big
WHERE customer_id = 42;Index Only Scan using idx_orders_cover on orders_big (actual rows=40 loops=1) Index Cond: (customer_id = 42) Heap Fetches: 0
If Heap Fetches is not zero, the table has pages that are not marked all-visible yet. That happens after many updates. Vacuum fixes it. And do not include everything: a wide index is slower to write and takes more memory.
Heap Fetches > 0? Run VACUUM. And keep INCLUDE small: wide indexes cost writes.
Question 4: Workers ask for pending orders all day. Pending is only 1 percent of the table, but the index on status covers every row. Can the index be smaller and faster?
Yes, with a partial index: an index with a WHERE clause. It stores only the rows that match the condition, so it is tiny, cheap to maintain, and the planner uses it when the query's WHERE implies the index's WHERE.
Compare the two indexes. The full index on status has an entry for all 200,000 rows, even though nobody searches for delivered through it. The partial index keeps only the 2,000 pending rows. And when an order leaves the pending state, it simply drops out of the index.
CREATE INDEX ... ON orders_big (order_date) WHERE status = 'pending'Create it, and compare the sizes. The full status index is 1,384 kilobytes. The partial one is 32.
CREATE INDEX idx_orders_pending
ON orders_big (order_date)
WHERE status = 'pending';SELECT indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE indexrelname IN ('idx_orders_status', 'idx_orders_pending')
ORDER BY indexrelname;| index_name | size |
|---|---|
| idx_orders_pending | 32 kB |
| idx_orders_status | 1384 kB |
And here is the worker's query: the oldest ten pending orders. The plan is an Index Scan on the partial index, already in date order, so there is no sort step at all.
EXPLAIN (COSTS OFF)
SELECT order_id, order_date
FROM orders_big
WHERE status = 'pending'
ORDER BY order_date
LIMIT 10;Limit -> Index Scan using idx_orders_pending on orders_big
Two more uses to mention. A unique partial index enforces rules like one active subscription per user. And the query must repeat the condition as a constant. With a parameter in a prepared statement, the planner may not be able to prove the match.
Also: CREATE UNIQUE INDEX ... WHERE active = "only one active row per user".
Question 5: Login looks up users with lower of email equals the typed address. There is an index on email, but the query scans all 50,000 users. Why is the index ignored?
The index stores email, but the query searches for lower(email), a different value. A function around the column hides it from a normal index. Create an expression index on exactly the same expression.
Here is what each index contains. The normal index is sorted by the email as it was typed, with capital letters. The query asks for the lower case version. That value does not exist anywhere in the index, so Postgres has to calculate it for every row. An expression index stores the result of the function, so the lookup is direct again.
| User4241@Mail.com |
| User4242@Mail.com |
| User4243@Mail.com |
WHERE lower(email) = 'user4242@mail.com'| lower(email) |
|---|
| user4241@mail.com |
| user4242@mail.com |
| user4243@mail.com |
Before: a sequential scan, with the function applied as a filter on every row.
EXPLAIN (COSTS OFF)
SELECT user_id, email
FROM users
WHERE lower(email) = 'user4242@mail.com';Seq Scan on users Filter: (lower(email) = 'user4242@mail.com'::text)
Now create the index on lower of email, and run the same query. An Index Scan on the new index.
CREATE INDEX idx_users_email_lower
ON users (lower(email));EXPLAIN (COSTS OFF)
SELECT user_id, email
FROM users
WHERE lower(email) = 'user4242@mail.com';Index Scan using idx_users_email_lower on users Index Cond: (lower(email) = 'user4242@mail.com'::text)
The same thing happens with a cast, with date functions, and with arithmetic on the column. Keep the column bare on one side of the comparison, or index the expression. For case-insensitive text, the citext type is another clean option.
Function, cast or math on the column = index ignored. Keep the column bare, or index the expression.
Question 6: A search box runs LIKE with a wildcard on both sides, and it is slow even though the column has an index. Which index types does Postgres have, and which one fixes this?
A B-tree cannot help when the pattern starts with a wildcard. Use a GIN index with the pg_trgm extension. Postgres has five main index types, and choosing one depends on the operator you search with.
Here are the five. B-tree is the default: equals, ranges and sorting. Hash does equality only. GIN indexes values with many parts: arrays, JSONB, full text, and trigrams for LIKE. GiST handles ranges, geometry and nearest neighbour. And BRIN is a tiny index for huge tables that are naturally ordered, like logs by time.
the default
trigrams (LIKE %x%)
nearest neighbour
ordered by time
Now the search box. The pattern has a percent sign at the start, so the B-tree on email is useless. Sequential scan.
EXPLAIN (COSTS OFF)
SELECT user_id, email
FROM users
WHERE email LIKE '%er4242@%';Seq Scan on users Filter: (email ~~ '%er4242@%'::text)
Enable pg_trgm and create a GIN index with the trigram operator class. It indexes every group of three characters. Now the same LIKE uses a Bitmap Index Scan on the trigram index.
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_email_trgm
ON users USING gin (email gin_trgm_ops);EXPLAIN (COSTS OFF)
SELECT user_id, email
FROM users
WHERE email LIKE '%er4242@%';Bitmap Heap Scan on users
Recheck Cond: (email ~~ '%er4242@%'::text)
-> Bitmap Index Scan on idx_users_email_trgm
Index Cond: (email ~~ '%er4242@%'::text)A pattern that starts with fixed text, like A B C percent, can use a B-tree, with the text pattern operator class when the database does not use the C collation. And remember the price of GIN: it is bigger and slower to update than a B-tree.
LIKE 'abc%' → B-tree works. LIKE '%abc%' → GIN + pg_trgm.
Question 7: You must add an index to a very large orders table in production, during business hours. A plain CREATE INDEX would block every insert and update until it finishes. What do you do?
Use CREATE INDEX CONCURRENTLY. It builds the index without blocking writes. It is slower, it cannot run inside a transaction, and if it fails it leaves an invalid index that you must drop and build again.
Compare the two commands. A plain CREATE INDEX takes a lock that still allows reads, but makes every insert, update and delete wait. CONCURRENTLY takes a weaker lock, so writes continue. The price: it scans the table twice and waits for old transactions, so it takes longer. And if it fails halfway, the index stays behind, marked invalid.
| CREATE INDEX | CREATE INDEX CONCURRENTLY | |
|---|---|---|
| SELECT | runs | runs |
| INSERT / UPDATE / DELETE | waits until the build ends | runs |
| Build time | one table scan | two scans, slower |
| Inside BEGIN ... COMMIT | allowed | not allowed |
| If it fails | nothing is left behind | an INVALID index is left behind |
Here is the command on our table. One statement, on its own, outside any transaction.
CREATE INDEX CONCURRENTLY idx_orders_date
ON orders_big (order_date);Result: CREATE INDEX
Always verify afterwards. This query lists the indexes of the table with their valid flag. If one shows false, drop it and run the command again.
SELECT c.relname AS index_name, i.indisvalid AS valid
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE i.indrelid = 'orders_big'::regclass
ORDER BY c.relname;| index_name | valid |
|---|---|
| idx_orders_cover | t |
| idx_orders_cust_date | t |
| idx_orders_date | t |
| idx_orders_pending | t |
| idx_orders_status | t |
| orders_big_pkey | t |
And every index you add slows down writes. So from time to time, look in pg_stat_user_indexes for indexes with zero scans. If an index has not been used for weeks, and it does not enforce a constraint, it is a candidate to drop. Drop it concurrently too.
pg_stat_user_indexes.idx_scan = 0 for weeks → candidate for DROP INDEX CONCURRENTLY.
Key points to remember
- An index is used only when the filter is selective. The planner compares costs
- Composite index: equality column first, then the range or sort column
- INCLUDE + a vacuumed table = Index Only Scan with zero heap fetches
- Partial and expression indexes match the exact WHERE clause you run
- In production: CREATE INDEX CONCURRENTLY, then check that the index is valid
The full SQL script
Here is everything in one script: the sample data and every query from this post, in order.
Show the full SQL script (sample data and every query)
-- ========================================================================
-- PostgreSQL Interview Q&A #5 - Indexes
-- TechBrothersIT - run on PostgreSQL 16
-- ========================================================================
-- Sample data
-- 200,000 orders: 66% delivered, 33% shipped, 1% pending
CREATE TABLE orders_big AS
SELECT g AS order_id,
(g * 7 % 5000) + 1 AS customer_id,
CASE WHEN g % 100 = 0 THEN 'pending'
WHEN g % 3 = 0 THEN 'shipped'
ELSE 'delivered' END AS status,
DATE '2025-01-01' + (g % 500) AS order_date,
(g % 900) + 10 AS amount
FROM generate_series(1, 200000) AS g;
ALTER TABLE orders_big ADD PRIMARY KEY (order_id);
CREATE INDEX idx_orders_status ON orders_big (status);
-- 50,000 users with mixed-case emails
CREATE TABLE users AS
SELECT g AS user_id, 'User' || g || '@Mail.com' AS email, 'Name ' || g AS full_name
FROM generate_series(1, 50000) AS g;
ALTER TABLE users ADD PRIMARY KEY (user_id);
CREATE INDEX idx_users_email ON users (email);
VACUUM ANALYZE orders_big;
VACUUM ANALYZE users;
-- Step 1 (question 1): plan delivered
EXPLAIN (COSTS OFF)
SELECT * FROM orders_big
WHERE status = 'delivered';
-- Step 2 (question 1): plan pending
EXPLAIN (COSTS OFF)
SELECT * FROM orders_big
WHERE status = 'pending';
-- Step 3 (question 2): plan by customer
EXPLAIN
SELECT * FROM orders_big
WHERE customer_id = 42;
-- Step 4 (question 2): plan by date
EXPLAIN
SELECT * FROM orders_big
WHERE order_date = DATE '2026-01-01';
-- Step 5 (question 3): plan heap
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT customer_id, order_date, amount
FROM orders_big
WHERE customer_id = 42;
-- Step 6 (question 3): create covering
CREATE INDEX idx_orders_cover
ON orders_big (customer_id, order_date)
INCLUDE (amount);
-- Step 7 (question 3): plan index only
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT customer_id, order_date, amount
FROM orders_big
WHERE customer_id = 42;
-- Step 8 (question 4): create partial
CREATE INDEX idx_orders_pending
ON orders_big (order_date)
WHERE status = 'pending';
-- Step 9 (question 4): index sizes
SELECT indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE indexrelname IN ('idx_orders_status', 'idx_orders_pending')
ORDER BY indexrelname;
-- Step 10 (question 4): plan pending queue
EXPLAIN (COSTS OFF)
SELECT order_id, order_date
FROM orders_big
WHERE status = 'pending'
ORDER BY order_date
LIMIT 10;
-- Step 11 (question 5): plan lower before
EXPLAIN (COSTS OFF)
SELECT user_id, email
FROM users
WHERE lower(email) = 'user4242@mail.com';
-- Step 12 (question 5): create expression
CREATE INDEX idx_users_email_lower
ON users (lower(email));
-- Step 13 (question 5): plan lower after
EXPLAIN (COSTS OFF)
SELECT user_id, email
FROM users
WHERE lower(email) = 'user4242@mail.com';
-- Step 14 (question 6): plan like before
EXPLAIN (COSTS OFF)
SELECT user_id, email
FROM users
WHERE email LIKE '%er4242@%';
-- Step 15 (question 6): create trgm
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_email_trgm
ON users USING gin (email gin_trgm_ops);
-- Step 16 (question 6): plan like after
EXPLAIN (COSTS OFF)
SELECT user_id, email
FROM users
WHERE email LIKE '%er4242@%';
-- Step 17 (question 7): create concurrently
CREATE INDEX CONCURRENTLY idx_orders_date
ON orders_big (order_date);
-- Step 18 (question 7): check valid
SELECT c.relname AS index_name, i.indisvalid AS valid
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE i.indrelid = 'orders_big'::regclass
ORDER BY c.relname;
Part 6: EXPLAIN and query tuning
The sample data on this page is invented for teaching. Every output table and query plan is the real result of the query above it, run on PostgreSQL 16 over that sample data.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.