Database Indexing: The Science of Speeding Up Slow Queries

Why do two queries against the same table finish in such wildly different times — one visibly slow, the other almost instant? The answer is usually one word: index. Database indexing for query performance runs from putting the right index on the right column in the right order to reading EXPLAIN ANALYZE output correctly, and this guide walks through it step by step, grounded in the official PostgreSQL documentation.

💡 Pro Tip: Before adding a new index, always measure the current state with EXPLAIN (ANALYZE, BUFFERS) — adding an index sometimes doesn't fix the problem, it just adds write overhead.

Table of Contents

• What an index actually speeds up, and what it slows down
• When it's worth adding an index
• When it's smarter not to add an index
• Learning to read EXPLAIN ANALYZE (seq scan vs index scan)
• Composite indexes and the leftmost-prefix rule
• Partial and expression indexes
• The hidden cost of indexes: writes, maintenance, disk
• Hunting slow queries: a practical flow with pg_stat_statements
• Seeing the query your ORM actually produces
• FAQ
• Which column should I index?
• How do I read EXPLAIN ANALYZE output?
• Why does column order matter in a composite index?
• How much does too many indexes hurt write performance?
• Does creating an index lock the table?
• Update (September 2026)
• Conclusion
• Sources

What an index actually speeds up, and what it slows down

PostgreSQL's own definition is clear: "Indexes are a common way to enhance database performance. An index allows the database server to find and retrieve specific rows much faster than it could do without an index. But indexes also add overhead to the database system as a whole, so they should be used sensibly." In other words, an index isn't a one-way win — it's a trade-off.

Without an index, the planner has exactly one option: read the entire table from start to finish (sequential scan). On small tables with few rows, this is already the fastest path — there are few disk pages, and traversing extra structure isn't worth it. But as a table grows and a query targets a small percentage of the rows, the planner prefers to jump directly to the relevant pages through an index.

The critical point here: an index existing doesn't mean it will be used. PostgreSQL's planner decides based on cost estimation; if a query returns a large portion of the table (say, more than a few percent), going through the index may be more expensive than a seq scan. That's why most "I added an index but it's not being used" complaints are actually the planner making the correct call.

When it's worth adding an index

• Selective WHERE/JOIN/ORDER BY columns: frequently used columns with high selectivity (pointing to few rows).
• Foreign key columns: constantly scanned to find related rows in JOINs and deletes.
• Reporting filter columns: filter columns of reporting queries that run often on large tables.
• Columns that need a unique constraint: these already create an implicit index.

When it's smarter not to add an index

• Small tables: seq scan is already fast; index maintenance is pure loss.
• Write-heavy columns: columns written often but rarely filtered — the index gets updated on every write too.
• Low-selectivity filters: filters that return a large percentage of the table's rows — the planner already picks seq scan.

1-- A simple but typical example: a query filtering orders by customer id
2CREATE INDEX orders_customer_id_idx ON orders (customer_id);
3 
4-- This index kicks in for a query like the one below
5SELECT id, total_amount, created_at
6FROM orders
7WHERE customer_id = 4821
8ORDER BY created_at DESC;

Saying "I added an index, done" isn't enough at this point. The next step is to measure whether this index is actually being used and how much difference it makes — and the tool for that is EXPLAIN ANALYZE.

Learning to read EXPLAIN ANALYZE (seq scan vs index scan)

The PostgreSQL documentation's tenk1 example is the clearest way to illustrate this. On the same table, the planner picks two different tactics depending on the query's selectivity:

1-- A wide range: the planner may prefer a Seq Scan
2EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten < 7;
3 
4-- Actual output (from the official PostgreSQL documentation):
5--  Seq Scan on tenk1  (cost=0.00..470.00 rows=7000 width=244)
6--                     (actual time=0.030..1.995 rows=7000.00 loops=1)
7--    Filter: (ten < 7)
8 
9-- A narrow equality targeting a single row: an Index Scan kicks in
10EXPLAIN SELECT * FROM tenk1 WHERE unique1 = 42;
11 
12-- Actual output (without ANALYZE, from the official PostgreSQL documentation):
13--  Index Scan using tenk1_unique1 on tenk1  (cost=0.29..8.30 rows=1 width=244)
14--    Index Cond: (unique1 = 42)
15-- (There's no actual time here because ANALYZE wasn't used — only the planner's estimated cost is shown.)

When reading these lines, there are five fields to look at:

If loops is greater than 1, the actual time shown is for a single iteration; to find the total time spent, you need to multiply this value by loops. In nested loop joins, the inner side re-runs for every outer row, and when this multiplication is missed, the question "why is this query so slow" goes unanswered.

The second layer of measurement is the split between Planning Time and Execution Time. Planning Time is what the planner spends finding the best path; Execution Time is the actual run time. On small but frequently run queries, Planning Time coming out close to or larger than Execution Time can signal that the planner is trying an unnecessarily complex path.

When you add BUFFERS to ANALYZE, the output gains a line like Buffers: shared hit=36 read=6: hit means the data was read from the shared buffer cache in memory, while read means it had to be read from disk. A high read count is a sign that the query can stay slow due to disk I/O even if the index itself is small.

Finally, don't overlook the Rows Removed by Filter line: it shows how many of the rows brought back by the index or scan were eliminated by an additional WHERE condition. If this number is high, the index has low selectivity — you may need to widen it (make it composite) or move it to a different column.

1-- Full measurement together with BUFFERS
2EXPLAIN (ANALYZE, BUFFERS) SELECT id, status FROM orders WHERE customer_id = 4821 AND status = 'pending';

This is exactly where the composite index decision comes in: with a single-column index on customer_id but a two-column filter, the planner narrows down with customer_id then eliminates rows using status as a filter (Rows Removed by Filter). What actually speeds up the query is a composite index covering both columns together.

Composite indexes and the leftmost-prefix rule

PostgreSQL lets you define an index on more than one column — this is called a composite (multi-column / concatenated) index. The logic works like a phone book: it's sorted by last name first, then first name. Searching by last name is fast; but trying to search the book knowing only the first name is no different from scanning it from start to finish.

The same logic applies to a composite index: the index uses its columns in the order they were defined (left to right). If a query uses the index's leftmost column (or the leftmost few columns together) in its filter, the index kicks in; but if you try to filter using only the second or third column on its own, the planner can't use that index effectively and may have to scan the whole thing — most of the benefit is lost. This is generally called "leftmost prefix" behavior.

1-- A composite index created in this order: (customer_id, status)
2CREATE INDEX orders_customer_status_idx ON orders (customer_id, status);
3 
4-- This query fully uses the index: the leftmost column (customer_id) is in the filter
5SELECT * FROM orders WHERE customer_id = 4821 AND status = 'pending';
6 
7-- This query can also use the index: only the leftmost column is in the filter
8SELECT * FROM orders WHERE customer_id = 4821;
9 
10-- This query CANNOT use the index effectively: the leftmost column (customer_id) is missing
11SELECT * FROM orders WHERE status = 'pending';

Practical takeaway: choose the column order in a composite index based on "which query pattern will use this index" — put your most frequent, most selective filter column leftmost. If two different query patterns require two different orderings, a single index can't serve both; you may need two separate indexes. But every new index also brings the write cost described in the next section, so choose column order not at random but by looking at real query logs (pg_stat_statements).

Partial and expression indexes

Not every index has to cover all rows of a table. The PostgreSQL documentation defines a partial index like this: "A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries only for those table rows that satisfy the predicate." In other words, a partial index is a smaller, faster index that covers only rows meeting a specific condition.

The documentation's rationale is clear: "One major reason for using a partial index is to avoid indexing common values. Since a query searching for a common value ... will not use the index anyway, there is no point in keeping those rows in the index at all." For example, if the vast majority of the status column in an orders table is completed and most queries search for status = 'pending', a partial index covering only the pending rows becomes both smaller and much more selective.

1-- A partial index covering only "pending" orders
2CREATE INDEX orders_pending_idx ON orders (created_at)
3WHERE status = 'pending';
4 
5-- Similar to the pattern in the documentation: excluding an internal IP range
6CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
7WHERE NOT (client_ip > inet '192.168.100.0' AND
8           client_ip < inet '192.168.100.255');

An expression index solves a different problem: what you index doesn't have to be a raw column — it can be an expression computed from that column. The documentation: "An index column need not be just a column of the underlying table, but can be a function or scalar expression computed from one or more columns of the table. This feature is useful to obtain fast access to tables based on the results of computations."

1-- To speed up case-insensitive email search
2CREATE INDEX users_email_lower_idx ON users (lower(email));
3 
4-- This index only kicks in if the query uses the SAME expression
5SELECT * FROM users WHERE lower(email) = '[email protected]';

Critical detail: an expression index only works when the query uses exactly the same expression (lower(email)). If you write the query as email ILIKE '[email protected]' instead, the index's existence does nothing.

The hidden cost of indexes: writes, maintenance, disk

Indexes aren't free. The PostgreSQL documentation says this outright: "After an index is created, the system has to keep it synchronized with the table. This adds overhead to data manipulation operations." So every INSERT, every UPDATE, every DELETE has to update every index on that table alongside the table itself. Adding a row to a table with five indexes physically requires more work than adding it to the same table without indexes.

The index build process itself is also costly: "By default, PostgreSQL allows reads (SELECT statements) to occur on the table in parallel with index creation, but writes (INSERT, UPDATE, DELETE) are blocked until the index build is finished." Running a plain CREATE INDEX on a large prod table can lock write traffic — which is why CREATE INDEX CONCURRENTLY is the preferred approach in production environments (it doesn't block writes, but takes longer and can leave an invalid index behind if it fails).

The documentation also notes that indexes can block the HOT (Heap-Only Tuples) optimization: "Indexes can also prevent the creation of heap-only tuples." HOT is a mechanism that lets PostgreSQL do a fast in-page update without touching every index when a row is updated but its indexed columns didn't change; unnecessary indexes disable this shortcut.

Ultimately, the documentation's own recommendation is clear: "Therefore indexes that are seldom or never used in queries should be removed." An unused index only accumulates write cost with no payoff on the read side.

Hunting slow queries: a practical flow with pg_stat_statements

You need to find which column needs an index through measurement, not guessing. The standard tool for this is the pg_stat_statements module. The documentation: "The pg_stat_statements module provides a means for tracking planning and execution statistics of all SQL statements executed by a server." The module keeps track of the total call count, total and average duration for every unique query pattern run on the server.

Enabling it requires a configuration file change: "The module must be loaded by adding pg_stat_statements to shared_preload_libraries in postgresql.conf, because it requires additional shared memory."

1# postgresql.conf
2shared_preload_libraries = 'pg_stat_statements'
3compute_query_id = on
4pg_stat_statements.max = 10000
5pg_stat_statements.track = all

Once enabled (and after restarting the server), the practical flow works like this:

1-- 1) Enable the extension
2CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
3 
4-- 2) Find the queries consuming the most total time
5SELECT query, calls, total_exec_time, mean_exec_time, rows
6FROM pg_stat_statements
7ORDER BY total_exec_time DESC
8LIMIT 10;
9 
10-- 3) Open the suspect query with EXPLAIN (ANALYZE, BUFFERS) and confirm the Seq Scan
11-- 4) Add an index on the relevant column(s), EXPLAIN the same query again
12-- 5) Reset pg_stat_statements and re-measure after some time
13SELECT pg_stat_statements_reset();

calls shows the call count, total_exec_time the total milliseconds spent, mean_exec_time the average duration, and rows the total rows processed. The logic here is simple: a query called rarely but very slow each time, and a query called often that's individually fast but adds up to a large total load, both become visible once sorted by total_exec_time — index decisions should be grounded in this table, not the feeling "this seemed slow to me."

Seeing the query your ORM actually produces

If you're using an ORM like Prisma, the source of a slow query is often that you've never actually seen the SQL the ORM generates behind the scenes. Prisma Client can print the generated queries to the console via the log option when the connection is set up:

1// Query logging is turned on when creating the PrismaClient instance
2const prisma = new PrismaClient({
3  log: ["query"],
4});
5 
6// Now every prisma.* call drops the raw generated SQL to the console
7const orders = await prisma.order.findMany({
8  where: { customerId: 4821, status: "pending" },
9  orderBy: { createdAt: "desc" },
10});

When you turn this log on, you typically notice two things: first, whether the query the ORM generates as WHERE customerId = $1 AND status = $2 actually matches the leftmost-prefix pattern of the composite index above; second, whether an unexpectedly high number of separate queries (the N+1 problem) run in a single page render. Both are things you need to see before adding an index — because an index doesn't fix the N+1 problem, changing the query shape (fetching relations in a single query with include/select) does.

FAQ

Which column should I index?

The priority order should be: columns that appear frequently in WHERE and JOIN conditions with high selectivity (pointing to few rows); foreign key columns; and frequently used ORDER BY columns. Instead of guessing, look at the queries sorted by total_exec_time in the pg_stat_statements output and target the WHERE/JOIN columns of those queries.

How do I read EXPLAIN ANALYZE output?

Look at the node type in the top line (Seq Scan or Index Scan), then multiply the actual time=start..end value by the loops count to get the real total time, check how close the rows value is to the planner's estimate (the cost=... rows= value in the first parenthesis), and check whether the read count is high when BUFFERS is included. A high read indicates disk I/O is the bottleneck.

Why does column order matter in a composite index?

Because composite index columns are used left to right — if the leftmost column (or the leftmost few columns together) isn't in the query's filter, the planner can't use that index effectively. So column order should be chosen based on which query pattern will use the index the most.

How much does too many indexes hurt write performance?

As the documentation states, every index must be kept in sync with its table, adding overhead to every data-modifying (INSERT/UPDATE/DELETE) operation. An exact percentage would be misleading — impact depends on index count, table size, and write frequency; measure with pg_stat_statements and real load testing instead. The documentation's clear recommendation: remove indexes that are rarely or never used.

Does creating an index lock the table?

The default CREATE INDEX blocks writes (INSERT/UPDATE/DELETE) on the table until the build completes; reads (SELECT) can continue. To add an index in production without blocking writes, use CREATE INDEX CONCURRENTLY; the cost is a longer build time and the risk of an invalid index if it fails.

Update (September 2026)

This article was written based on the PostgreSQL version current in early 2025. PostgreSQL 18, released on September 25, 2025, brought changes that directly affect two topics in this article.

First, EXPLAIN ANALYZE now shows BUFFERS automatically — the release notes call it "Automatically include BUFFERS output in EXPLAIN ANALYZE." So the manual EXPLAIN (ANALYZE, BUFFERS) shown earlier is no longer necessary in PostgreSQL 18 — hit/read now appears in the default output. The release notes also note that index lookups per index scan node are now reported: "report the number of index lookups used per index scan node." Output formatting changed too — "Modify EXPLAIN to output fractional row counts" — so from PostgreSQL 18 on, the actual line's rows value prints with two decimals, like rows=7000.00, versus plain rows=7000 before (the tenk1 example above, with actual time=0.030..1.995 rows=7000.00 loops=1, comes from current docs and reflects this PostgreSQL 18 format; at first publication the same output printed as rows=7000).

Second, and more striking, PostgreSQL 18 brought some flexibility to the leftmost-prefix rule described above: B-tree "skip scan" support. The release notes: "Allow skip scans of btree indexes... This allows multi-column btree indexes to be used in more cases such as when there are no restrictions on the first or early indexed columns (or there are non-equality ones), and there are useful restrictions on later indexed columns." In practice: before PostgreSQL 18, the (customer_id, status) index above couldn't be used effectively with just a status = 'pending' filter; from 18 onward, the planner can still use it under certain conditions, because if the leftmost column has few distinct values, it can scan by "skipping" through them in sequence. This doesn't invalidate the leftmost-prefix rule — putting the leftmost column in the filter is still the most reliable path — but it turns a strict requirement into an optimization opportunity in some scenarios.

Conclusion

Adding an index looks as easy as running EXPLAIN ANALYZE, but the real payoff lies in the gap between adding without measuring and adding with measurement. In this guide I tried to tie three things together: when an index actually helps, reading EXPLAIN ANALYZE output correctly, and using composite/partial/expression index types in the right scenario.

For the next step, you might compare PostgreSQL's approach with a different data layer's, like Core Data: Core Data Advanced: Migration, Performance and CloudKit Sync (in Turkish) shows the same index/query optimization logic on the mobile side. To see how backend query performance intersects with API design, check REST API Design Principles: Resources, Errors, Pagination (in Turkish). For a different server-side approach using Swift, Backend API with Swift Vapor: Full-Stack Swift Development is useful. For a cloud-based sync model, see iCloud Synchronization with CloudKit. Finally, to apply "measure first, then optimize" on the client side, Network Layer Optimization: Building a Production-Ready API Layer (in Turkish) repeats this same principle at a different layer.

Sources

• PostgreSQL Documentation — 11. Indexes — the general purpose of indexes and the cost-benefit trade-off.
• PostgreSQL Documentation — Using EXPLAIN — a field-by-field explanation of EXPLAIN ANALYZE output.
• PostgreSQL Documentation — Partial Indexes — partial index definition and usage example.
• PostgreSQL Documentation — Indexes on Expressions — expression index definition and examples.
• PostgreSQL Documentation — pg_stat_statements — the query statistics tracking module.
• PostgreSQL Documentation — Index Introduction — locking and maintenance cost during index creation.
• Use The Index, Luke — Concatenated Keys — a visual explanation of composite index column order and leftmost-prefix logic.
• PostgreSQL 18.0 Release Notes — automatic BUFFERS in EXPLAIN ANALYZE and B-tree skip scan changes.