List several operations before choosing an index
Fix two order queries: Q1 returns the newest five rows for customer42; Q2 returns the newest five open rows for that customer. Include status updates as another workload operation. An index chosen for one SELECT may harm or fail to help another query or a write. Compare how two column orders support the same declared workload.
The ten-thousand-row table has id, customer, created, status and payload. Created is an integer ordering key, not a complete timestamp or pagination design. A product needing stable order among equal timestamps needs an additional tie rule. Record filters, ordering, returned columns, frequency and updated fields before inspecting plans. A favorable node or isolated latency is not enough to justify a workload-wide index decision.
Follow equality prefixes and remaining order
Candidate A is customer followed by created descending. Q1 fixes one customer and can use the remaining creation order for its first rows. Q2 may scan that customer's recent rows and filter status afterward, potentially inspecting more than the five returned rows. Read where filters apply and how many rows they remove, not merely whether any index appeared.
Candidate B inserts status between customer and created. Q2 fixes both leading fields and can exploit the remaining order. Q1 leaves status unconstrained, so separate creation orders within status groups do not automatically provide one global creation order for that customer. A Sort may remain. More columns do not universally improve every query. Connect each query's equality prefix to its actual required output ordering.
Treat partial and covering indexes as separate choices
A partial index on open rows is another candidate, but the planner must recognize that the query implies its predicate. Q1 asks for all statuses and may not benefit the same way. Adding returned columns for covering access likewise does not guarantee every heap access disappears; visibility rules and implementation conditions matter. Use the existing partial-index and index-only experiments for those distinct questions.
Creating every candidate also adds storage and maintenance. Decide whether similar indexes justify their write and capacity costs under query frequency and deadlines. The example drops A before creating B so the planner does not choose between both simultaneously. Dropping an index on a disposable temporary table is not an instruction to apply the same removal to production without a workload and dependency review.
Observe plans, sizes and updates on a disposable table
Run the SQL through psql in a disposable PostgreSQL instance. Inside BEGIN, create a temporary table, generate ten thousand deterministic rows and ANALYZE it. Record Q1 and Q2 before extra indexes, with A and then with B using JSON EXPLAIN ANALYZE and BUFFERS. Plans can vary with sampled statistics and environment, so one node name is not a universal cross-platform acceptance condition.
Each candidate also reports size and a status-update plan. Roll back A's update to a savepoint before B so logical rows are restored, but recognize that rollback does not reset every page or cache condition. These sequential timings are not an independently controlled universal speed ranking. Final ROLLBACK removes the temporary experiment. Use no production connection information, and keep the complete SQL and outputs with the recorded engine version.
\timing on
BEGIN;
CREATE TEMP TABLE orders_lab(id int PRIMARY KEY,customer int,created int,status text,payload text);
INSERT INTO orders_lab SELECT i,i%100,i,CASE WHEN (i/100)%10=0 THEN 'open' ELSE 'done' END,repeat('x',100) FROM generate_series(1,10000) i;
ANALYZE orders_lab;
EXPLAIN(ANALYZE,BUFFERS,FORMAT JSON) SELECT id FROM orders_lab WHERE customer=42 ORDER BY created DESC LIMIT 5;
EXPLAIN(ANALYZE,BUFFERS,FORMAT JSON) SELECT id FROM orders_lab WHERE customer=42 AND status='open' ORDER BY created DESC LIMIT 5;
CREATE INDEX candidate_a ON orders_lab(customer,created DESC);
EXPLAIN(ANALYZE,BUFFERS,FORMAT JSON) SELECT id FROM orders_lab WHERE customer=42 ORDER BY created DESC LIMIT 5;
EXPLAIN(ANALYZE,BUFFERS,FORMAT JSON) SELECT id FROM orders_lab WHERE customer=42 AND status='open' ORDER BY created DESC LIMIT 5;
SELECT pg_relation_size('candidate_a') AS a_bytes;
SAVEPOINT before_update_a;
EXPLAIN(ANALYZE,BUFFERS,FORMAT JSON) UPDATE orders_lab SET status='done' WHERE id<=1000;
ROLLBACK TO before_update_a;
DROP INDEX candidate_a;
CREATE INDEX candidate_b ON orders_lab(customer,status,created DESC);
EXPLAIN(ANALYZE,BUFFERS,FORMAT JSON) SELECT id FROM orders_lab WHERE customer=42 ORDER BY created DESC LIMIT 5;
EXPLAIN(ANALYZE,BUFFERS,FORMAT JSON) SELECT id FROM orders_lab WHERE customer=42 AND status='open' ORDER BY created DESC LIMIT 5;
SELECT pg_relation_size('candidate_b') AS b_bytes;
EXPLAIN(ANALYZE,BUFFERS,FORMAT JSON) UPDATE orders_lab SET status='done' WHERE id<=1000;
ROLLBACK;
Interpret the recorded plans within their scope
On the recorded PostgreSQL18.6 instance, both baseline queries used sequential scanning and sorting. A produced index scans. With B, Q2 used an index scan while Q1 included bitmap scanning and sorting, matching the distinction between fixing and not fixing status. These are observations for this dataset, not guarantees for every customer or distribution.
Preserve complete outputs from five executions of the full SQL. Read changes in scans, filtering, sorting, size and update work before interpreting individual milliseconds. Small datasets may legitimately favor no index. Forcing an index is not itself the definition of a successful optimization. A stronger performance conclusion needs a workload resembling the target application and independently controlled repeated measurements in addition to the plan evidence.
| Condition | Q1: customer, newest first | Q2: customer, status, newest first |
|---|---|---|
| No index | Seq Scan + Sort | Seq Scan + Sort |
| Candidate A | Index Scan | Index Scan with status Filter |
| Candidate B | Bitmap scan + Sort | Index Scan |
Inspect write maintenance without a simplistic multiplier
Including status as an index key makes status updates relevant to index maintenance. Equal old/new values, page space and database-specific update paths also matter; one added index does not imply a universal fixed write multiplier. EXPLAIN ANALYZE on UPDATE really executes the write, unlike plain EXPLAIN. Keep it inside the disposable table and transaction scope.
Index size depends on types, distribution, free space and subsequent changes. pg_relation_size reports this candidate at this point, not total table storage, resident cache or backup size. Capacity planning also includes other indexes, logs and later growth. Put read latency and long-term maintenance beside each other in the design comparison, with accurate measurement scope. The fastest isolated read is only one component of that decision.
Change the workload before changing the conclusion
A service dominated by Q2 can value B differently from one dominated by Q1. Multiplying operation frequency by observed cost can guide an initial comparison, but contention and caching can invalidate a simple additive prediction. Record read/write proportions, result sizes and deadlines, then inspect the plans for the important requests.
Low observed index-use counts alone do not prove safe removal. The observation interval may omit important monthly, recovery or rare administrative queries. This lesson deliberately studies two SELECTs and one UPDATE. Production adoption needs a broader inventory of missing operations and constraints, result-equivalence checks and visibility into regressions as well as improvements. A workload decision must describe the workloads it actually considered.
Include writes in the workload budget
One thousand daily history reads and ten updates present a different tradeoff from continuous high-rate status changes. The same index can help both reads while consuming very different amounts of write headroom. Frequency multiplied by per-operation cost is a useful starting calculation, but it does not capture every concurrency effect or the distribution of waiting time. Retain those observations instead of collapsing everything into one mean.
The ten-thousand-row example fixes its distribution and queries. Extend it with customers owning many recent orders, periods with skewed status values and a larger retained history. When the plan changes, revisit estimated versus actual rows, sorting and the scanned range rather than trying to restore a preferred index name. Removing an index later also needs evidence of both usage and maintenance cost. A measured winner under one synthetic workload remains a conditional choice, which should have a recorded reason and a condition for reconsideration.
Exercise: predict where sorting can remain
With index customer/status/created-descending, fixing only customer does not necessarily provide global created order across all status groups. Q2 fixes status as well and has a different remaining-order property. Explain equality prefixes and ordering rather than inferring behavior from an index name.
A partial index containing only open rows also cannot alone answer Q1's requirement for all statuses. Missing rows are part of the requested result. Finally distinguish this temporary-table experiment from executing its UPDATE EXPLAIN ANALYZE against production: that statement performs real writes. Combine result equivalence, plan structure, size, update work and execution scope when making the decision. Those dimensions are what turn a plan-reading exercise into an index-design argument.