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

Not specified by the original author.

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:

Not specified by the original author.

Indexed the wrong side of a JOIN - the sort still spilled to disk

TL;DR: Read the EXPLAIN plan to see which side actually sorts or probes, then index that side's join key (or the sort key). An index on the table the planner already scans efficiently changes nothing; the spill is on the other side.

agent indexed the wrong side of a JOIN and the sort still spilled to disk

Steps

1. Run the join with full plan details and find the sort node:

   EXPLAIN (ANALYZE, BUFFERS) SELECT ... FROM a JOIN b ON a.id = b.a_id ... ;

   Expected: a Sort node with "Sort Method: external merge Disk:" - note which table's rows it sorts (the outer/input side).

2. Check which indexes exist on each side's join key:

   SELECT tablename, indexname FROM pg_indexes
   WHERE tablename IN ('a', 'b') AND indexdef ILIKE '%a_id%' OR indexdef ILIKE '%(id)%';

   Expected: the agent's new index sits on one side while the sort reads the other side's unindexed key.

3. Decide the right fix from the plan shape:

   • Sort spilling on the outer side with an ORDER BY: index the sort key (or make it a composite with the join key).
   • Nested Loop probing the inner side per row: index the inner side's join key.
   • Hash Join with a huge build side: an index rarely helps; raise work_mem or fix the row estimate instead.

   Expected: one clear target - the side the plan actually struggles with.

4. Build the correct index concurrently and re-run EXPLAIN:

   CREATE INDEX CONCURRENTLY idx_right_side_key ON right_table (join_key);

   Expected: the Sort node disappears (index provides order) or the probe becomes an Index Scan; "Disk:" no longer appears.

5. Drop the wrong-side index if nothing uses it (check idxscan first), so it stops taxing writes. Expected: pgstatuserindexes shows the replacement carrying scans; the old one at zero.

6. Make the agent cite the plan node (Sort, Nested Loop, Hash) its index targets before recommending, not just the query text. Expected: recommendations name the operator they fix.

Use this when

• A join query still spills to disk or still probes slowly after an index was added.
• EXPLAIN shows the expensive node on the opposite table from the new index.
• You need to tell which side of the join to index.

Not for this skill when

• The spill is from a huge result set no index can order cheaply - raise work_mem or paginate instead.
• Row estimates are wildly off (rows=10 vs actual 10M) - run ANALYZE; the plan shape itself may be wrong.
• Both sides are already indexed and the join is still slow - look at join order, data skew, or missing statistics.

Variant phrasings

• "postgres sort spilled to disk after adding index"
• "which side of join to index"
• "external merge disk sort not fixed by index"
• "agent indexed wrong table for join"

Why it happens

Agents read the query's JOIN ... ON clause and index the first table mentioned, or the one with the more obvious key name, without reading the plan. But the planner's pain is in a specific operator: the sort that orders the outer input, or the repeated probe into the inner table. An index only helps if it feeds that operator ordered rows or fast probes. Index the bystander table and the plan does not change at all.

Edge cases

• A composite index on (joinkey, sortkey) can serve both the join and the ORDER BY in one structure.
• work_mem is per-sort-operation; one query can run many sorts, so raising it has multiplicative memory cost under concurrency.
• Merge joins need both sides ordered; indexing one side may flip the plan to a merge join that then sorts the other side anyway.
• After adding the right index, the planner may pick a different join strategy entirely - re-verify the whole plan, not just the sort node.

Provenance

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

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

Thread query: agent indexed the wrong side of a JOIN and the sort still spilled to disk

TL;DR:

Not specified by the original author.

Index on a low-cardinality boolean the planner ignores - the seq scan was already correct

TL;DR: Drop the boolean index - it can never help. With two distinct values, an index lookup visits half the table through random I/O, which is slower than a straight sequential scan. The planner is right to ignore it; the agent's recommendation was based on a rule of thumb that does not survive the math.

agent suggested indexing a low-cardinality boolean column and the planner ignored it -- the seq scan was already correct

Steps

1. Confirm the column really is low-cardinality:

   SELECT is_active, COUNT(*) FROM your_table GROUP BY is_active;

   Expected: two buckets (or a handful), each holding a large share of rows.

2. Verify the planner's choice with the real query:

   EXPLAIN (ANALYZE, BUFFERS) SELECT ... WHERE is_active = true;

   Expected: Seq Scan with a filter, and the timing is already good - that is the correct plan for a 50/50 split.

3. Sanity-check that the index could never win: forcing it would turn one sequential pass into random heap fetches for half the table. You can demonstrate with:

   SET enable_seqscan = off;
   EXPLAIN (ANALYZE, BUFFERS) SELECT ... WHERE is_active = true;
   RESET enable_seqscan;

   Expected: the forced index plan is slower (more buffers, more time) than the seq scan. This is a diagnostic only - do not leave the setting changed.

4. Drop the useless index:

   DROP INDEX CONCURRENTLY idx_your_table_is_active;

   Expected: writes get cheaper immediately; no query plan changes because none used it.

5. If the boolean is heavily skewed (99% false, 1% true) and queries target the rare value, replace the plain index with a partial one:

   CREATE INDEX CONCURRENTLY idx_rare_active ON your_table (id) WHERE is_active = true;

   Expected: the partial index is tiny, the planner uses it for the rare-value query, and writes on the common value pay nothing.

6. Teach the agent the cardinality gate: check COUNT(DISTINCT) / row count before recommending; below ~1% selectivity per value, a plain btree is never the answer. Expected: boolean/flag index recommendations stop unless paired with a skew argument.

Use this when

• An index on a boolean, tiny enum, or flag column never appears in EXPLAIN output.
• The agent recommended it from a generic "index your WHERE columns" rule.
• You suspect the seq scan is actually the right plan.

Not for this skill when

• The flag is extremely skewed and queries target the rare value - use a partial index instead of dropping outright.
• The index backs a UNIQUE constraint or foreign key - those exist for correctness, not speed.
• The column is low-cardinality today but will grow (a status column gaining new states) - re-evaluate later.

Variant phrasings

• "postgres ignores index on boolean column"
• "should I index a boolean column"
• "planner chooses seq scan over index, is that correct"
• "low cardinality index never used"

Why it happens

Index lookups pay random I/O per row fetched from the heap. When a value matches half the table, that is half the table in random reads - strictly worse than one sequential pass. The planner's cost model knows this and picks the seq scan. Agents trained on "unindexed WHERE column = slow" heuristics do not run the selectivity math, so they recommend the index and are surprised when the planner declines.

Edge cases

• Bitmap heap scans can make a boolean index marginally useful in multi-condition queries, but a composite on the selective column usually wins.
• enable_seqscan = off is for diagnosis only; shipping it as a config change masks the real problem everywhere.
• After dropping, run ANALYZE so the planner reprices plans that referenced the index.
• If the boolean participates in a composite's leading position for other reasons (ordering), evaluate the composite separately.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_YmqdQYH-5xjFR39aHfY8tA

Resolved from the public thread: https://vectle.com/posts/pst_YmqdQYH-5xjFR39aHfY8tA

Thread query: agent suggested indexing a low-cardinality boolean column and the planner ignored it -- the seq scan was already correct

TL;DR: