An index is justified by an access requirement or an integrity rule. The first might be retrieving one customer’s latest five orders; the second might be preventing duplicate order identifiers. Both are real purposes, even if only one appears as a frequent read in query statistics. Index design starts by recording those purposes and measuring their costs, not by accumulating interesting data structures or deleting everything with a low scan count.

Describe the complete query

A filter alone does not describe a workload. Include its ordering, limit, projected columns, parameter distribution, and frequency. An index on order identifiers already provides identifier ordering, yet may inspect many unrelated customers before finding five matches. A composite B-tree starting with the equality-filtered customer and followed by the requested ordering can narrow the search before walking recent entries.

This differs from adding separate indexes and assuming they provide the same result. A bitmap combination can help filtering but does not preserve index ordering for output. A covering index can supply selected values when heap visibility checks can be avoided; otherwise an index-only scan still visits the heap. Wider entries also consume space and must be maintained. Add included columns only when the actual query and measured heap access justify them.

When it breaks

A query can use an index and still do excessive work. Reading backward through a primary key while discarding thousands of other customers is an index scan, but its filter occurs after entry selection. Check Index Cond, removed rows, loops, and buffers instead of using the presence of an index name as a success criterion.

Adding every conceivable index increases storage and maintenance. The exact write cost depends on indexed columns, predicates, and update behavior; it is inaccurate to claim that every update rewrites every index identically. Nevertheless, read improvements must be balanced against actual inserts, updates, vacuum work, and cache pressure in the target workload.

Removal also needs evidence. idx_scan is an observation over a particular statistics window, not proof that an index has no purpose. A primary or unique index may enforce correctness with few reads. Rare reports, operational queries, and referencing relationships can have requirements outside a short sample. Inspect constraints and dependencies before considering removal.

Check it quickly

Use PostgreSQL 18 and psql for this controlled example. It creates fifty thousand temporary orders spread evenly across two hundred fifty customers. The primary key remains in place throughout, and rollback removes the fixture.

\set ON_ERROR_STOP on
BEGIN;
CREATE TEMP TABLE orders (id integer PRIMARY KEY,customer_id integer,total integer);
INSERT INTO orders SELECT g,g%250,g*2 FROM generate_series(1,50000) AS g;
ANALYZE orders;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id,total FROM orders WHERE customer_id=7 ORDER BY id DESC LIMIT 5;
CREATE INDEX orders_customer_recent ON orders(customer_id,id DESC);
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id,total FROM orders WHERE customer_id=7 ORDER BY id DESC LIMIT 5;
SELECT id,total FROM orders WHERE customer_id=7 ORDER BY id DESC LIMIT 5;
DO $$ BEGIN
  IF (SELECT array_agg(id ORDER BY id DESC) FROM (
        SELECT id FROM orders WHERE customer_id=7 ORDER BY id DESC LIMIT 5
      ) s) IS DISTINCT FROM ARRAY[49757,49507,49257,49007,48757] THEN
    RAISE EXCEPTION 'changed result ordering or membership';
  END IF;
END $$;
DO $$ BEGIN
  INSERT INTO orders VALUES (1,999,0);
  RAISE EXCEPTION 'duplicate primary key accepted';
EXCEPTION WHEN unique_violation THEN RAISE NOTICE 'primary key still protects identity';
END $$;
ROLLBACK;

The earlier PostgreSQL 18.6 record reported that the initial plan scanned orders_pkey backward. It returned the requested five rows but removed 1,239 rows by the customer filter first. The primary key supported ordering, yet did not directly locate the desired customer’s range. A plan can therefore be logically correct and still perform avoidable filtering.

After adding (customer_id,id DESC), the plan used orders_customer_recent with the customer equality as an index condition. It reached that customer’s recent entries directly and returned identifiers 49,757, 49,507, 49,257, 49,007, and 48,757 in descending order. The assertion checks exact membership and order, so a faster but differently paginated answer would fail.

The intentional duplicate identifier insert still raised a uniqueness violation. The new access index did not replace the primary key’s integrity role. This check matters because a maintenance decision based only on which index the latest query used could otherwise discard a necessary constraint.

A single-user PostgreSQL 18.6 rerun reproduced these SQL results; the psql client command and transport were not rerun. The plan and removed-row count belong to this fixture and version. Other data distributions or cost settings may select another path. The durable conclusion is the correspondence between equality, ordering, and index key sequence, with result correctness verified separately from performance.

Keep a workload record

For production evaluation, retain representative parameters and compare latency, pages visited, result identity, storage, and write throughput before and after. Include skewed customers and the busiest periods, not just the average key. Large index builds also have operational costs and locking choices that a temporary-table example does not measure. Keep the smallest set supported by demonstrated access and integrity needs, then revisit those needs when the application or data changes.

Sources