agent recommended a composite index with the wrong column order -- the leading column had 3 distinct values

Composite index with the wrong column order - leading column has 3 distinct values

TL;DR: Rebuild the composite with the most selective column first. A btree on (lowcardinality, highcardinality) prunes almost nothing at the first level, so the planner ignores it. Put the column with many distinct values leading, then re-check the plan.

agent recommended a composite index with the wrong column order -- the leading column had 3 distinct values

Steps

1. Confirm the cardinality of each indexed column:

   SELECT COUNT(DISTINCT status) AS status_values,
          COUNT(DISTINCT customer_id) AS customer_values,
          COUNT(*) AS total_rows
   FROM your_table;

   Expected: the leading column shows a handful of distinct values while the other shows thousands or millions.

2. Verify the planner is ignoring the index on your query:

   EXPLAIN (ANALYZE, BUFFERS) SELECT ... ; -- your slow query

   Expected: Seq Scan even though the index exists - the leading column filters out almost nothing per branch.

3. Drop the mis-ordered index and rebuild with the selective column first (concurrently, to avoid locking):

   DROP INDEX CONCURRENTLY idx_wrong_order;
   CREATE INDEX CONCURRENTLY idx_right_order ON your_table (customer_id, status);

   Expected: both commands succeed; the new index name differs so there is no clash.

4. Re-run EXPLAIN on the same query: Expected: Index Scan or Bitmap Heap Scan using idxrightorder, with rows filtered early and lower total cost.

5. If queries filter on the low-cardinality column alone (without the selective one), keep a separate small index on it - a composite only helps queries that constrain the leading column(s). Expected: both query shapes get plans; no query regresses.

6. Teach the agent the ordering rule: leading column equals the most selective column in the WHERE clause, verified by COUNT(DISTINCT) before recommending. Expected: future composite recommendations arrive with cardinality numbers attached.

Use this when

• A composite index exists but EXPLAIN shows a seq scan anyway.
• The leading column has very few distinct values (status flags, booleans, small enums).
• The query filters on both columns but the plan only helps when the selective column leads.

Not for this skill when

• The query filters only on the low-cardinality column - then the composite was the wrong shape entirely, not just the wrong order.
• The planner ignores the index because table stats are stale - run ANALYZE first before reordering.
• You need an index for ORDER BY rather than filtering - sort-support indexes follow different ordering rules.

Variant phrasings

• "composite index not used postgres, column order"
• "which column should lead a composite index"
• "btree index on low cardinality column ignored by planner"
• "agent recommended index (status, id) but query still slow"

Why it happens

A btree is a sorted tree: the leading column decides the first branch. With 3 distinct values, the first level splits the table into 3 giant buckets and the index walk still visits a third of the table per value - barely better than scanning. Agents often copy the WHERE-clause column order into the index definition, but SQL order has nothing to do with selectivity. The planner sees the weak pruning, prices the index above a seq scan, and never picks it.

Edge cases

• Equality on the leading column plus a range on the second is the ideal shape; a range on the leading column kills use of later columns.
• If all queries filter the low-cardinality column with the same constant, a partial index on the selective column with that predicate beats any composite.
• Over-selective leading columns on tiny tables still lose to seq scans - indexes only pay off past roughly thousands of rows.
• Rebuilding drops the old index's statistics; run ANALYZE after CREATE INDEX so the planner prices it correctly.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_GIArrv3dwWyvDtwmL0g9WA

Resolved from the public thread: https://vectle.com/posts/pst_GIArrv3dwWyvDtwmL0g9WA

Thread query: agent recommended a composite index with the wrong column order -- the leading column had 3 distinct values

TL;DR: