Before You Add Another Index: Understand the Work Your Query Requests

A query is slow. Someone checks the WHERE clause, finds a column without an index, and suggests creating one.

Sometimes that is exactly the right fix. But I think the more useful starting question is: how much work does this query ask the database to do before it can return the result?

A query can use an index and still discard hundreds of thousands of rows. It can join records only to remove the resulting duplicates. It can return fifty rows after skipping half a million. Adding another access path may help, but it may leave the expensive part of the request intact.

The distinction I want to explore is between three causes: the work expressed by the query, the access paths available to execute it, and the estimates used to choose a plan. The examples below use PostgreSQL. The SQL and abbreviated plan fragments are illustrations, not measurements from a production incident.

An index gives the planner an option

SQL describes the result we want. The planner chooses how to produce it, considering available indexes alongside other strategies. An index is useful when the planner estimates that accessing data through it costs less than the alternatives. Its existence does not guarantee its use. PostgreSQL: Introduction to Indexes

Consider a hypothetical table containing ten million payments, nine million of which are paid. For a query returning every paid payment, a sequential scan may be a reasonable choice. Reading most of the table through an index is not automatically cheaper.

Now change the request to payments belonging to one user with only thirty-five records. The access pattern is very different. If there is no index on user_id, adding one is an obvious candidate to investigate.

Both cases involve filtering. What changes is the fraction of the table required by the answer, together with the cost of retrieving it.

This is why “the table is indexed” tells me very little. I want to know whether the available index supports this particular request.

Start with the question the SQL is answering

Suppose a screen needs customers who have at least one open order:

SELECT DISTINCT c.id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'OPEN';

The join describes every matching customer–order combination. A customer with fifty open orders can contribute fifty matches, which DISTINCT then collapses.

Assuming customers.id is the primary key, the existence question can instead be expressed as:

SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
      AND o.status = 'OPEN'
);

EXISTS asks whether a match exists and produces at most one output row per outer customer. PostgreSQL can generally stop evaluating an existence test once a match is established. That does not promise a particular physical plan or make EXISTS universally faster than a join. PostgreSQL: Subquery Expressions

The rewrite makes the requirement explicit. The execution plan and measurements must establish whether it reduces work in this dataset.

There is also a correctness boundary: if the screen needs order details, the join may be necessary. Removing it would change the answer. Query optimization starts by preserving what the application actually needs.

Make the predicate match the available access path

Assume orders.created_at is a timestamp without time zone, with an ordinary B-tree index:

CREATE INDEX idx_orders_created_at ON orders (created_at);

This filter applies a transformation to the indexed column:

SELECT id, customer_id, total
FROM orders
WHERE created_at::date = DATE '2026-09-14';

For that column type, the same day can be expressed as a half-open interval:

SELECT id, customer_id, total
FROM orders
WHERE created_at >= TIMESTAMP '2026-09-14 00:00:00'
  AND created_at <  TIMESTAMP '2026-09-15 00:00:00';

The predicate now compares the indexed value directly with range boundaries. Whether that access path wins still depends on the data and the plan.

This is not a rule against functions in WHERE. PostgreSQL supports expression indexes: an index on lower(name), for example, can support a predicate using that expression. An index on a column and an index on a transformation of that column serve different requests. PostgreSQL: Indexes on Expressions

The timestamp assumption matters. For timestamptz, define which time zone determines the business day and use boundaries representing its consecutive local midnights. Do not silently change a local-day requirement into a UTC-day requirement while rewriting the SQL.

Returning fewer rows can still require substantial work

Pagination is a particularly clear example:

SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 500000;

An index matching the ordering can help avoid sorting, but skipped rows still have to be computed. LIMIT 50 describes the output size; it does not mean the database only processes fifty rows. PostgreSQL: LIMIT and OFFSET

For sequential navigation, a cursor changes the request:

SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Here $1 and $2 are the last timestamp and ID from the previous page. Assume both fields are non-null and id is unique. A matching index is:

CREATE INDEX idx_posts_created_id
ON posts (created_at DESC, id DESC);

The query now provides a boundary from which the index can seek. It still needs to find qualifying records, but it no longer requests a fixed prefix to discard.

This changes navigation semantics: jumping directly to an arbitrary page number becomes less convenient, and concurrent changes still require thought. I covered those trade-offs in my article on offset and cursor pagination.

Projection matters too. If the application only needs id and status, selecting every column can increase transferred data and prevent an otherwise possible index-only scan. In PostgreSQL, avoiding heap visits also depends on visibility information; merely including all selected columns in an index is insufficient. PostgreSQL: Index-Only Scans

Read the work behind the operator name

An illustrative fragment might look like this:

Index Scan
  Index Cond: (user_id = 2372)
  Filter: (status = 'PAID')
  Rows Removed by Filter: 982421

The index identifies candidates by user. The status filter then rejects many of them. That is a reason to investigate the access pattern, not a successful diagnosis just because the plan says Index Scan.

For a suitable diagnostic environment, start with:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status
FROM payments
WHERE user_id = 2372
  AND status = 'PAID';

ANALYZE executes the statement, including writes if the statement modifies data. BUFFERS adds information about buffer activity. Compare estimated and actual output rows, examine rejected rows, and account for repetition. PostgreSQL: Using EXPLAIN

For example:

(actual time=0.003..0.005 rows=1 loops=500000)

Times and rows here are averages per execution. Half a million repetitions make a tiny inner operation worth investigating. Multiplying the average time by loops helps interpret that node; adding every node's time together would double-count nested work. PostgreSQL: EXPLAIN ANALYZE

I find the relationship between useful output and intermediate work more informative than the operator's label. Discarded rows are one clue, but so are repeated probes, large intermediate results, and fetching columns the caller never uses. There is no single universal “rows examined” ratio that captures all of those costs.

A reasonable query can still receive a poor plan

Query shape and indexes are only part of the diagnosis. The planner also needs a useful estimate of how many rows will pass through each operation.

Imagine a node estimated to return twelve rows that actually returns nearly half a million. A strategy selected for the smaller result may behave very differently at the real scale. Adding an index does not necessarily repair that assumption.

The study How Good Are Query Optimizers, Really? investigated this using the Join Order Benchmark over IMDb data. Its experiments identified cardinality estimation as a major source of poor plans. Additional indexes could expose plans whose performance was more sensitive to those errors. This is evidence about the studied workload, not a claim that indexes generally make queries slower.

A practical example is correlation:

SELECT id
FROM addresses
WHERE country = 'BR'
  AND state = 'RJ';

If those values are related in the dataset, treating the predicates as independent can underestimate their combined result. Refreshing statistics with ANALYZE helps when statistics are stale, but single-column statistics cannot fully describe every relationship.

PostgreSQL supports extended statistics for selected column combinations. For an existing addresses table, one candidate is:

CREATE STATISTICS addresses_country_state_stats (dependencies, mcv)
ON country, state FROM addresses;

ANALYZE addresses;

This collects information about dependencies and frequent value combinations. It does not create an access path. Its usefulness depends on the predicates and data; extended statistics are not a general cure for every estimation error, including join estimates. PostgreSQL: Planner Statistics

The next step is to compare the estimates and plan again. The existence of a statistics object is no more proof of success than the existence of an index.

Avoid replacing one automatic rule with another

Once you start inspecting query structure, it is tempting to collect syntax rules. Several need qualifications.

A substring search needs an appropriate structure. An ordinary B-tree on name is not the general answer to ILIKE '%matthew%'. PostgreSQL's pg_trgm extension supports GIN and GiST indexes for these searches. Effectiveness depends on the pattern and the trigrams available to extract. PostgreSQL: pg_trgm

An OR is not automatically an indexing failure. PostgreSQL can combine index scans with bitmap operations. Rewriting to UNION ALL also requires checking correctness: a row satisfying both branches would appear twice. PostgreSQL: Combining Multiple Indexes

A CTE is not inherently a performance problem. Materialization can prevent outer restrictions from reaching the underlying scan, but it can also avoid repeating expensive computation. NOT MATERIALIZED is an option to evaluate for eligible CTEs, not a default optimization to apply everywhere. PostgreSQL: WITH Queries

A composite index must fit the access pattern. (user_id, created_at) and (created_at, user_id) are not interchangeable. Leading-column conditions usually determine the most efficient range to scan, although PostgreSQL can sometimes use techniques such as skip scan for other patterns. PostgreSQL: Multicolumn Indexes

Each of these is a question to investigate with the actual query, parameters, and dataset.

Give every change a hypothesis

For the customer query, the hypothesis might be that expressing existence avoids costly duplicate elimination. For the date filter, it might be that direct boundaries enable a useful range scan. For the address query, it might be that correlated predicates explain an estimation error.

Those are concrete claims we can check. “Add an index and see what happens” leaves too much unexplained.

My investigation sequence would be:

1. Confirm the required result, including duplicates, nulls, ordering, and time boundaries.
2. Capture a baseline with representative parameters and data.
3. Identify where the plan creates, repeats, or discards substantial work.
4. Choose a targeted query, index, or statistics change based on that evidence.
5. Check result equivalence and compare performance under comparable conditions.

Index changes also need evaluation against the write workload. Indexes consume storage and add maintenance overhead to data modifications. A benefit to one read path must justify that ongoing cost. PostgreSQL: Introduction to Indexes

Locks, concurrency, cache state, and resource limits can also explain latency. This article focuses on query design and planning; a plausible execution plan does not rule out those other causes.

The question I want to keep asking is simple: where is the work coming from? Once that is clear, choosing between a rewrite, an index, and better statistics becomes a much more grounded engineering decision.