Appearance
Indexes and Query Performance
The plans on this page come from a real PostgreSQL run on an orders table with 200,000 rows and 50,000 customers.
sql
CREATE TABLE orders (
id bigserial PRIMARY KEY, customer_id int NOT NULL, status text NOT NULL,
total numeric(10,2) NOT NULL, created_at timestamptz NOT NULL
);23. What is a database index, and how does it improve query performance?
The short answer
An index is a separate, sorted data structure that maps column values to the rows that hold them, like the index at the back of a book. Without one, finding customer_id = 42 means reading every row, a sequential scan. With a B-tree index, the database walks a shallow tree to the right place in a few page reads, then fetches just the matching rows. The cost: indexes use disk and memory, and every insert, update and delete must also update every index on the table.
Before and after, the same query:
text
SELECT id, total FROM orders WHERE customer_id = 42;
-- no index
Seq Scan on orders (cost=0.00..4127.00 rows=4 width=14) (actual rows=4 loops=1)
Filter: (customer_id = 42)
Rows Removed by Filter: 199996
-- after CREATE INDEX orders_customer_idx ON orders (customer_id);
Index Scan using orders_customer_idx on orders (cost=0.42..19.78 rows=4 width=14) (actual rows=4 loops=1)
Index Cond: (customer_id = 42)Reading it: without the index, PostgreSQL read all 200,000 rows and threw away 199,996 of them. With it, the estimated cost fell from about 4,127 to about 20, roughly 200 times less work, to find the same 4 rows.
When an index does not help:
- The query returns a large share of the table. Reading most rows through an index is slower than one sequential scan, so the planner ignores it.
- The column is wrapped in a function or expression.
WHERE customer_id + 0 = 42orWHERE lower(email) = …cannot use a plain index on that column. The real plan for the first one is aSeq Scanagain. Index the expression instead:CREATE INDEX ON users (lower(email)). - A leading wildcard.
LIKE '%shoe'cannot use a B-tree;LIKE 'shoe%'can. - The type differs. Comparing an integer column to a string can force a conversion per row.
24. What are common index types?
The short answer
B-tree is the default and handles equality, ranges, sorting and prefix LIKE; use it for almost everything. Hash handles only equality. GIN indexes the parts of a composite value, so it suits JSONB, arrays and full-text search. GiST handles geometric data, ranges and nearest-neighbour searches. BRIN stores a tiny summary per block of rows, ideal for huge append-only tables ordered by time. You can also make unique, partial, with a WHERE, and expression indexes.
| Type | Good for | Example |
|---|---|---|
| B-tree | =, <, >, BETWEEN, ORDER BY, LIKE 'abc%' | CREATE INDEX ON orders (customer_id) |
| Hash | Only = | Rarely better than a B-tree |
| GIN | JSONB keys, arrays, full-text | CREATE INDEX ON products USING gin (tags) |
| GiST | Geometry, ranges, nearest neighbour | CREATE INDEX ON shops USING gist (location) |
| BRIN | Huge tables in time order | CREATE INDEX ON events USING brin (created_at) |
Two variations worth knowing:
sql
CREATE INDEX orders_pending_idx ON orders (created_at) WHERE status = 'pending'; -- (1)
CREATE UNIQUE INDEX users_email_lower_idx ON users (lower(email)); -- (2)- A partial index covers only pending orders, which are a small fraction. It is tiny and fast for the worker query "oldest pending orders".
- A unique expression index stops
Asha@shop.exampleandasha@shop.examplefrom both registering.
25. What are composite and covering indexes?
The short answer
A composite index has several columns, such as (customer_id, created_at). It is sorted by the first column, then by the second within each first value, like a phone book sorted by surname then first name. It serves queries that filter on the first column, or the first and second together, and can return rows already sorted. A covering index contains every column a query needs, either as key columns or added with INCLUDE, so the database answers from the index alone without visiting the table, which PostgreSQL shows as an Index Only Scan.
"A customer's latest 2 orders", served entirely by a composite index:
text
CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at DESC);
SELECT id, total, created_at FROM orders
WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 2;
Limit (cost=0.42..10.26 rows=2 width=22) (actual rows=2 loops=1)
-> Index Scan using orders_customer_created_idx on orders (cost=0.42..20.09 rows=4 width=22) (actual rows=2 loops=1)
Index Cond: (customer_id = 42)Reading it: there is no Sort step. The index already stores customer 42's orders newest first, so the database reads the first two entries and stops.
A covering index with INCLUDE:
sql
CREATE INDEX orders_status_created_cover ON orders (status, created_at) INCLUDE (total);
SELECT created_at, total FROM orders
WHERE status = 'paid' AND created_at >= '2026-04-01' AND created_at < '2026-04-02';Every column this query touches, status, created_at and total, is in the index. INCLUDE adds total to the leaf entries without making it part of the sort order. When PostgreSQL's visibility map says the table pages are all visible, the plan becomes an Index Only Scan with Heap Fetches: 0, meaning no table pages were read at all. Running VACUUM keeps that map up to date.
26. How do you choose the order of columns in a composite index?
The short answer
Put columns tested with equality first, then the column used for a range or sorting last. An index on (customer_id, created_at) serves "customer 42's orders in October" perfectly; one on (created_at, customer_id) must scan every customer's October orders. The index is only useful if the query constrains its leading column; an index on (customer_id, created_at) does nothing for a query filtering only on created_at. Among equality columns, order matters less, so pick the order that also serves your other queries.
The leftmost-prefix rule, using (customer_id, status, created_at):
| Query filters on | Uses the index? |
|---|---|
customer_id | Yes |
customer_id, status | Yes |
customer_id, status, range on created_at | Yes, fully |
customer_id, range on created_at | Partly: customer_id only, then filters |
status only | No, or only by a full index scan |
created_at only | No |
Proof from the real run. The table had indexes on (customer_id), (customer_id, created_at) and (status, created_at). None of them starts with created_at, so a query filtering on created_at alone produced:
text
Seq Scan on orders (cost=0.00..4627.00 rows=10 width=8) (actual rows=10 loops=1)
Filter: ((created_at >= '2026-04-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-04-01 00:10:00+00'::timestamp with time zone))
Rows Removed by Filter: 199990The leading columns, customer_id and status, were not constrained, so the indexes' sort order was useless. This query needs its own index that starts with created_at.
Why equality before range. In (created_at, customer_id), entries are sorted by time first. October's entries form one long stretch containing every customer, and customer 42's rows are scattered through it. In (customer_id, created_at), customer 42's October rows sit next to each other in one short stretch.
27. What is the N+1 query problem, and how can it be avoided?
The short answer
N+1 happens when code runs one query to load a list, then one more query per item to load something related: 1 query for 50 orders, then 50 queries for their customers, 51 in total. Each is fast alone, but the round trips add up, and pages get slower as data grows. It usually hides inside ORM lazy loading or a loop with await. Fix it by loading the related data in one query: a JOIN, one WHERE id IN (…) or = ANY($1) query for all IDs, the ORM's eager loading, or a DataLoader that batches requests.
ts
// N+1: 1 query for orders, then one per order
const orders = await db.query('SELECT id, customer_id, total FROM orders ORDER BY id DESC LIMIT 50')
for (const o of orders.rows) {
o.customer = (await db.query('SELECT name FROM customers WHERE id = $1', [o.customer_id])).rows[0]
}
// 2 queries in total, however many orders
const { rows } = await db.query('SELECT id, customer_id, total FROM orders ORDER BY id DESC LIMIT 50')
const ids = [...new Set(rows.map((o) => o.customer_id))] // (1)
const customers = await db.query('SELECT id, name FROM customers WHERE id = ANY($1)', [ids]) // (2)
const byId = new Map(customers.rows.map((c) => [c.id, c]))
for (const o of rows) o.customer = byId.get(o.customer_id) // (3)- Collect the distinct customer IDs from the first result.
- Fetch them all in one query.
= ANY($1)takes an array parameter. - Join them up in memory with a
Map.
The same fix in an ORM: prisma.order.findMany({ include: { customer: true } }) loads all customers in one extra query. In GraphQL resolvers, use DataLoader, which collects every load(id) call made in one tick and sends one batched query.
How to spot it: log the number of queries per request. A page that runs 200 queries is almost always N+1.
28. How do you analyze and optimize a slow database query?
The short answer
First find the slow queries, from slow-query logs or pg_stat_statements, ranked by total time, not just by the slowest single run. Then run EXPLAIN ANALYZE on one to see what the database actually did. Look for sequential scans on large tables, big gaps between estimated and actual rows, expensive sorts, and nested loops over many rows. Then fix the cause: add or change an index, rewrite the query, return fewer rows or columns, update statistics with ANALYZE, or change the data model. Measure again after each change.
The process, step by step:
Find the queries that cost the most in total. A 5 ms query run 10,000 times a minute matters more than a 2-second report run once an hour.
sqlSELECT query, calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 1) AS mean_ms FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;Run
EXPLAIN (ANALYZE, BUFFERS)on the worst one, with realistic parameter values.Read the plan from the most indented line outward (question 29).
Fix the biggest cost first.
Measure again, on production-sized data.
| Symptom in the plan | Likely fix |
|---|---|
Seq Scan on a big table, few rows returned | Add an index on the filter columns |
Rows Removed by Filter is huge | Index, or a better composite order |
| Estimated rows far from actual | ANALYZE the table; extended statistics |
Sort with external merge Disk | An index that matches ORDER BY, or more work_mem |
Nested Loop with many loops | A missing index on the inner side, or a hash join |
SELECT * on wide rows | Select only the columns you need |
Large OFFSET | Keyset pagination: WHERE id < $last ORDER BY id DESC LIMIT 20 |
Outside the query: too few connections in the pool, lock waits, which show in pg_stat_activity, or an overloaded server can make a good query look slow. Check them before rewriting the SQL.
29. What is an execution plan, and how do you read it?
The short answer
An execution plan is the step-by-step recipe the query planner chose to run your query: which tables to scan and how, in what order to join them, and where to sort or aggregate. EXPLAIN shows the plan with estimated costs. EXPLAIN ANALYZE actually runs the query and adds real row counts and times. Read it as a tree from the innermost, most indented, nodes outward. Each node shows its estimated cost, estimated rows, and with ANALYZE the actual rows. Big differences between estimated and actual rows point at the problem.
A plan with two levels, from the real run:
text
Limit (cost=0.42..10.26 rows=2 width=22) (actual rows=2 loops=1)
-> Index Scan using orders_customer_created_idx on orders (cost=0.42..20.09 rows=4 width=22) (actual rows=2 loops=1)
Index Cond: (customer_id = 42)Reading each part:
| Part | Meaning |
|---|---|
Index Scan using … on orders | The operation, and the index it used |
cost=0.42..20.09 | Estimated cost to the first row, and to the last row, in arbitrary planner units |
rows=4 | The planner expected 4 rows |
width=22 | Average row size in bytes |
actual rows=2 | It produced 2, because Limit stopped it early |
loops=1 | The node ran once; inside a nested loop it may run thousands of times |
Index Cond | The condition answered by the index itself |
Filter | A condition checked on each row afterwards |
Node types you will see most:
| Node | Means |
|---|---|
| Seq Scan | Read the whole table |
| Index Scan | Walk the index, then fetch matching rows |
| Index Only Scan | Answer from the index alone |
| Bitmap Index and Heap Scan | Collect matching row locations first, then read table pages in order |
| Nested Loop | For each outer row, look up inner rows; good when the outer side is small |
| Hash Join | Build a hash table of one side, probe it with the other; good for large joins |
| Merge Join | Walk two sorted inputs together |
| Sort, Aggregate, Limit | What their names say |
Use EXPLAIN ANALYZE carefully on writes. It really executes the statement, so an UPDATE really updates. Wrap it in BEGIN and ROLLBACK.