# 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 (low_cardinality, high_cardinality) 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.

```text
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:
   ```sql
   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:
   ```sql
   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):
   ```sql
   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 idx_right_order, 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