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

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: