SQL optimizer

Likely planner behavior and bottlenecks

Postgres will normally rewrite the IN subquery as a semi-join. A plausible plan is:

1. Scan customers to find rows where country = 'TR'.
2. Build a hash table of matching customer IDs.
3. Scan all 20M rows of orders.
4. Hash-join orders against the matching customers.
5. Sort every matched order by created_at DESC.

The planner is not necessarily making a semantic mistake; without suitable indexes, this may be the cheapest available plan. The expensive parts are likely:

• A sequential scan of orders.
• A large final sort, potentially spilling to disk.
• Row-count errors if country is skewed or its statistics are stale.
• SELECT * increases heap I/O and makes a covering index impractical.
• If Turkish customers represent a large fraction of all customers, an index-driven nested loop may be worse than the hash-join plan.

Recommended rewrite

SELECT o.*
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.id
WHERE c.country = 'TR'
ORDER BY o.created_at DESC;

Reasoning per change

• Replace IN with an explicit JOIN:

  • It makes the relationship and intended access path clearer.
  • Postgres often produces the same plan for both forms, so this is primarily clarity rather than a guaranteed optimization.
  • Assuming customers.id is a primary key or otherwise unique, it does not duplicate order rows.

• Start logically from filtered customers:

  • SQL table order does not force join order, but the formulation highlights that country = 'TR' should be applied before looking up orders.

• Keep ORDER BY o.created_at DESC:

  • An index on (customer_id, created_at DESC) orders rows within each customer, not globally across all Turkish customers.
  • Postgres may use incremental sort or merge per-customer streams in some plans, but a final sort is commonly still required.

• Replace o.* with explicit columns if possible:

  • This reduces tuple width, memory used by the sort, he