An index is valuable because it changes the work needed by a real query. Counting indexed columns is not a useful success measure. A narrow B-tree that finds one customer’s newest three orders can outperform a wider structure that cannot supply the needed ordering, while a query reading almost every row may correctly ignore both. Evaluate access, ordering, and result volume together.

Start from the requested result

Suppose the application asks for the newest three orders for one customer. Equality on customer narrows the population, and descending placed_at chooses the newest records within that customer. A final id key gives deterministic ordering when timestamps tie. The index (customer, placed_at DESC, id DESC) expresses that useful sequence of keys.

The benefit is not just avoiding a table scan. An ordered index path can stop after producing the limited result instead of collecting and sorting every matching order. Without LIMIT, or when most rows are needed, the balance can change. The same index must be judged against the full statement and its common parameter values, not an isolated WHERE fragment.

Textual condition order does not set index order. PostgreSQL plans logical expressions, so moving customer equality before or after another AND condition does not redefine the tree. Index direction, null ordering, leading-key constraints, and the requested output order are relevant structural properties. Verify them explicitly when adapting a query rather than applying a blanket column-order slogan.

Read the chosen plan honestly

EXPLAIN shows the planner’s proposed work, and EXPLAIN ANALYZE executes it to show actual rows and loops. A sequential scan can be the right answer when a query returns most of a table, especially when it also needs wide columns absent from the index. Such a plan is not proof that column order is wrong or that index creation failed.

Conversely, seeing an index name does not prove a query is cheap. An index scan can visit many entries and heap tuples, a bitmap path can need a separate sort, and an index-only plan can still perform heap visibility checks. Read actual rows, Heap Fetches where applicable, buffer activity, and sorting together. A LIMIT near the top is useful only if lower nodes avoid doing excessive work first.

When it breaks

A production parameter can select thousands of times more rows than a development value. The index that looked excellent for a small customer may be less beneficial for an exceptionally large one. Data distribution, current statistics, caching, and concurrency all affect the plan and observed latency. Retain representative parameter cases when validating a change.

Indexes also impose persistent costs: storage, cache pressure, insert/update maintenance, and build time. Adding note as payload merely because one diagnostic query selected it can make a hot write path more expensive. Separate the narrow application lookup from occasional broad reporting, and require evidence that each extra key or included column serves a recurring need.

A final failure is using unspecified ordering in pagination. The first few rows can change across plans or tied timestamps unless ORDER BY defines a deterministic choice. An index does not independently establish an API ordering contract. Keep that order explicit in SQL and validate exact identifiers, not just the number of returned rows.

Check it quickly

Save the following as topn.sql and run psql -X -v ON_ERROR_STOP=1 -f topn.sql against a disposable PostgreSQL 18 database. It creates twenty thousand orders spread over one hundred customers, including a wide note column for the broad-query comparison.

\set ON_ERROR_STOP on
BEGIN;
CREATE TEMP TABLE orders (id integer, customer integer, placed_at integer, note text);
INSERT INTO orders SELECT g, g%100, g, repeat('x',100) FROM generate_series(1,20000) AS g;
CREATE INDEX orders_customer_recent ON orders (customer, placed_at DESC, id DESC);
ANALYZE orders;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id,placed_at FROM orders WHERE customer=7 ORDER BY placed_at DESC,id DESC LIMIT 3;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM orders WHERE customer>=0;
SELECT array_agg(id ORDER BY placed_at DESC,id DESC) AS newest
FROM (SELECT id,placed_at FROM orders WHERE customer=7
      ORDER BY placed_at DESC,id DESC LIMIT 3) s;
DO $$ BEGIN
  IF (SELECT array_agg(id ORDER BY placed_at DESC,id DESC)
      FROM (SELECT id,placed_at FROM orders WHERE customer=7
            ORDER BY placed_at DESC,id DESC LIMIT 3) s)
     IS DISTINCT FROM ARRAY[19907,19807,19707] THEN
    RAISE EXCEPTION 'wrong newest orders';
  END IF;
END $$;
ROLLBACK;

The customer-seven query returns ids 19907,19807,19707 in that order. PostgreSQL 18.6 chose a Limit over an index-only scan and stopped after three matching rows, with heap checks because the new table was not vacuumed. The all-customers query selected a sequential scan and returned twenty thousand rows including their notes. Both choices are consistent with their different tasks.

The assertion checks the deterministic top-three result; timing and buffer numbers are deliberately not fixed expectations. Rollback removes the fixture. This demonstrates workload-shaped index use, not a universal speed ratio or proof that the same plan will remain optimal as production data and query requirements evolve.

Sources