Database Indexing Strategies and Query Optimization
On this page
- Symptom: a page that gets slower every month
- Diagnose: read the query plan with EXPLAIN ANALYZE
- Fix: a composite index in the right column order
- Why column order matters in a composite index
- Clustered vs secondary (nonclustered) indexes
- Prevent: index on evidence, not guesses
- Parameter sniffing: when a good plan goes bad
- Check yourself
Symptom: a page that gets slower every month
The "my orders" page took 20 ms at launch and 400 ms a year later. The code didn't change; the orders table grew from 50,000 rows to 5 million. The database is reading the entire table to find one customer's orders. An index fixes it, and a query plan proves it.
Diagnose: read the query plan with EXPLAIN ANALYZE
I loaded 500,000 orders into PostgreSQL and ran the page's query with EXPLAIN ANALYZE, which runs the query and reports what the database did:
EXPLAIN ANALYZE
SELECT order_id, order_date, total FROM orders
WHERE customer_id = 4021 AND order_date > '2026-01-01'
ORDER BY order_date;Seq Scan on orders (actual time=4.317..25.864 rows=3)
Filter: ((order_date > '2026-01-01') AND (customer_id = 4021))
Rows Removed by Filter: 499997
Execution Time: 25.992 msSeq Scan means it read every row. It threw away 499,997 rows to return 3. That's 26 ms now and grows in step with the table.
Fix: a composite index in the right column order
An index is a separate sorted structure, almost always a B-tree, mapping column values to row locations. Like a book's index, it lets the database jump to the right place instead of reading every page.
CREATE INDEX ix_orders_customer_date ON orders (customer_id, order_date);Bitmap Index Scan on ix_orders_customer_date
Index Cond: ((customer_id = 4021) AND (order_date > '2026-01-01'))
Execution Time: 0.063 msFrom 26 ms to 0.06 ms, and it stays fast as the table grows, because the B-tree's depth grows very slowly.
Why column order matters in a composite index
A composite index is sorted by its first column, then by the second within each first-column value, like a phone book sorted by last name, then first name. So the index on (customer_id, order_date) helps queries that filter on customer_id, or on customer_id and order_date. It doesn't help a query on order_date alone:
EXPLAIN SELECT order_id FROM orders WHERE order_date > '2026-06-01';
-- Seq Scan on orders (the index can't be used without customer_id)Put columns used with equality (=) first and range or sort columns after them. If a query also needs only a few extra columns, a covering index that includes them lets the database answer from the index alone, without visiting the table (INCLUDE (total) in PostgreSQL and SQL Server).
Selectivity matters too. An index on status, where 90 percent of rows are 'shipped', doesn't help a query for shipped orders; reading the table is faster. If you only ever query the rare values, index just those: a partial index in PostgreSQL (CREATE INDEX ... WHERE status = 'open') or a filtered index in SQL Server.
Clustered vs secondary (nonclustered) indexes
In SQL Server and MySQL's InnoDB, the table itself is stored in the order of its clustered index, usually the primary key. Other indexes are secondary: they store the indexed columns plus a pointer back to the row. PostgreSQL stores tables unordered and treats all indexes as secondary, but the idea is the same: an index lookup that needs other columns must go back to the table for them.
Prevent: index on evidence, not guesses
Indexes aren't free. Every insert and update must also update each index, and they take disk and memory. So:
- Add an index for a specific slow or frequent query, and confirm with the plan that it's used.
- Check slow query logs (
pg_stat_statements, Query Store in SQL Server) to find which queries deserve one. - Remove indexes nothing uses; most databases report index usage.
- Watch for functions on indexed columns:
WHERE lower(email) = ...can't use a plain index onemail. - Watch for type mismatches from your ORM. If a SQL Server column is
varcharand the query parameter arrives asnvarchar, which is the .NET string default, the database may convert every row and scan instead of seeking. Map the column type explicitly in EF Core. - Build indexes on large live tables without blocking writes:
CREATE INDEX CONCURRENTLYin PostgreSQL,WITH (ONLINE = ON)in SQL Server editions that support it. A plainCREATE INDEXon a 50 million row table can lock writes for minutes.
Parameter sniffing: when a good plan goes bad
SQL Server caches a query's plan using the parameter values from the first call. If the first call is for a customer with 3 orders, the plan uses an index seek. If it's for a marketplace account with 2 million orders, it may cache a scan, and every later small customer pays for it. This is parameter sniffing: the same query is fast or slow depending on which value compiled the plan. Query Store shows when one query has several plans with very different durations; fixes range from OPTION (RECOMPILE) for that query to forcing a known good plan.
Check yourself
Generally no. The index is sorted by customer_id first, so without a customer_id condition the database can't jump to the matching dates.
The database read about half a million rows and then threw almost all of them away to return a handful. That's the signature of a missing or unusable index for the filter condition. An index matching the WHERE clause would let it go straight to the matching rows instead of scanning.
Parameter sniffing. After the plan cache was cleared, the first call compiled a plan for an unusual parameter value. Compare plans in Query Store and recompile or force the good plan.
Every insert, update and delete must also update the index, which slows writes, and the index takes disk space and memory. Unused indexes are pure cost, so add indexes for real, measured queries and periodically drop ones nobody uses.
A clustered index stores the table's rows in the index's order, so there can be only one, as in SQL Server. A secondary index stores keys with pointers to the rows. PostgreSQL tables are heaps, so all its indexes are secondary.