Composite index column order: diagnose slow PostgreSQL queries with EXPLAIN

October 25, 2026 · 10 min read

An index exists, yet the query is slow. Follow an order-history API to choose column order from WHERE and ORDER BY, recognize expressions that prevent selective lookups, replace deep OFFSETs, and check foreign-key indexes.

What is a composite index? Start with one query you need to improve

A composite index indexes multiple columns together. This example uses PostgreSQL 18 B-trees for an endpoint returning the newest 50 orders for one tenant within a date range. Assume id is the primary key, tenant_id and created_at are NOT NULL, and created_at is timestamptz. The SQL is illustrative; bind application values to placeholders such as $1.

Order-history query and a candidate index shaped around it

SELECT id, created_at, total_minor
FROM orders
WHERE tenant_id = $1
  AND created_at >= $2
  AND created_at < $3
ORDER BY created_at DESC, id DESC
LIMIT 50;

CREATE INDEX orders_tenant_created_id_idx
ON orders (tenant_id, created_at DESC, id DESC);

Fix tenant_id, then locate a created_at range within that tenant. Use id to order equal timestamps deterministically. For this query, tenant first followed by the timestamp and ID in the required order is a useful starting point. Reordering predicates in the WHERE text is different from reordering index columns.

Why put equality conditions before the range and ordering columns?

An index on (tenant_id, created_at) groups entries by tenant, then timestamp. Reversing those columns groups timestamps first. Fixing the leading column can make the desired range contiguous. If several columns have equality predicates, also consider which predicates other important queries omit when choosing their order.

Candidate	How it relates to this query	What to check
(tenant_id, created_at DESC, id DESC)	Can locate one tenant’s range in the requested order	Actual rows, additional filters, and heap access
(created_at, tenant_id)	A date-first range scan can include other tenants	Alternative scan strategies and any sorting
Separate tenant_id and created_at indexes	Index-combination plans are possible	Whether sorting is still needed for order history

Estimate candidate sets: returning 50 rows is different from finding them

Assume 100 equal tenants, one million total orders per day, and 365 days of history. One tenant’s day contains 1,000,000 ÷ 100 = 10,000 rows. Filtering only by tenant leaves 10,000 × 365 = 3.65 million candidates. A date-only range covers one million orders across all tenants.

Hypothetical candidate sets: 100 equal tenants, 1M total orders/day, 365 days. These are not measured scan counts or timings

One tenant, one day: 10,000 rows
All tenants, one day: 1M rows
One tenant, 365 days: 3.65M rows

These are logical candidate-set sizes. A matching index order and LIMIT can stop a scan early, so the database need not visit every candidate. Sorting or discarding many rows under other filters can require substantially more work to return 50. A hundredfold smaller candidate set does not guarantee a hundredfold speedup.

Why an index may not help: expressions such as DATE on the indexed column

Converting a timestamp to a business date produces an expression different from the indexed created_at column. The example rewrites the same Tokyo business day as a half-open interval, including the start and excluding the end. Calculate boundaries in the intended time zone; do not assume every local day in a daylight-saving region lasts 24 hours.

Equivalent conditions for one day in Asia/Tokyo

-- Business day in Asia/Tokyo; created_at is timestamptz.
WHERE tenant_id = $1
  AND (created_at AT TIME ZONE 'Asia/Tokyo')::date = DATE '2026-10-24'

-- Equivalent interval for this date, without transforming the column.
WHERE tenant_id = $1
  AND created_at >= TIMESTAMPTZ '2026-10-24 00:00:00+09'
  AND created_at <  TIMESTAMPTZ '2026-10-25 00:00:00+09'

Failing to perform a selective lookup in a plain B-tree is different from never using an index. A matching expression index or a full index scan may still be chosen. Case-insensitive email lookup can use a suitable lower(email) expression index. Prefix LIKE also depends on collation and operator class, so inspect string-search plans too.

Reading EXPLAIN: an Index Scan label is not enough

Start with EXPLAIN, then compare actual execution with EXPLAIN (ANALYZE, BUFFERS) in an environment where running the query is appropriate. ANALYZE really executes the statement, including write side effects. It is not an observation-only command for production writes.

Output	What to examine
Estimated rows versus actual rows	Large errors suggest statistics or skew to investigate. Actual rows normally describes node output, not every index entry examined
loops	Number of executions. Displayed actual rows and times are per-execution averages; distinguish them from total work
Index Cond / Filter	Conditions supplied to the index versus filtering afterward. An Index Cond alone does not prove a narrow scan
Buffers	Compare hits and reads. Parent counts include child work, so do not simply sum every node
Sort / Execution Time	Inspect sorting and overall time. A sequential scan plus sort can be appropriate when retrieving much of a table

A Seq Scan is not automatically a defect. Small tables and queries returning many rows can justify it. Compare buffers, waiting, and endpoint latency under the same conditions. Likewise, adding every node’s time does not give the query’s overall duration.

Deep pages still slow? Inspect OFFSET

LIMIT 50 OFFSET 100000 skips 100,000 qualifying rows before returning 50. A straightforward ordered scan with enough matches still produces 100,050 rows along that path. If the UI only needs a next-page operation, use the last created_at and id as a cursor and continue through the index.

Next page: bind the previous page’s final timestamp and ID to $4 and $5

SELECT id, created_at, total_minor
FROM orders
WHERE tenant_id = $1
  AND created_at >= $2 AND created_at < $3
  AND (created_at, id) < ($4, $5)
ORDER BY created_at DESC, id DESC
LIMIT 50;

This example assumes non-null, unique, immutable ordering keys and a suitable index. A timestamp alone can lose tied rows, so include the primary key id. A NULL in a deciding pair can make the row comparison unknown; validate cursor timestamps and IDs as required, non-null values too. Bind the cursor to the tenant and filters, and authorize every page. Arbitrary page jumps or a point-in-time list across updates require additional design.

Slow parent deletion? Check the referencing side of foreign keys

Declaring a PostgreSQL foreign key does not automatically index its referencing columns. Deleting a customer requires finding orders.customer_id references. A tenant-first index may not adequately support that lookup; consider the actual foreign key and lookup predicates separately.

Candidate when orders.customer_id references customers.id

CREATE INDEX orders_customer_id_idx ON orders (customer_id);

An existing multicolumn index beginning with customer_id may already serve this lookup. Inspect it before adding a duplicate. Indexing does not remove the cost of deleting many child rows through CASCADE or waiting for locks.

Five checks before adding another index

Identify the query with problematic frequency and latency, its parameter types, WHERE, ORDER BY, and LIMIT.
Reproduce tenant skew, date ranges, and NULL behavior on representative data.
Check whether expressions or deep OFFSETs can be rewritten.
Compare existing and candidate indexes using plans, buffers, and API latency under equivalent conditions.
Account for index storage, write costs, creation locks, and operational procedures.

FAQ: should the most selective column always come first?

Distinct-value count alone does not determine column order. This endpoint fixes a tenant and needs 50 rows in timestamp order, so choose a layout that supports that access pattern. Important date-only reporting queries may benefit from another order or another index. Balance the query mix against write overhead.