Optimize a query that performs poorly due to suboptimal join operations.
Instruction: In PostgreSQL, customers(id PRIMARY KEY), orders(id PRIMARY KEY, customer_id, status) and order_items(order_id, quantity) are queried with SELECT DISTINCT c.id FROM customers c JOIN orders o ON o.customer_id=c.id JOIN order_items i ON i.order_id=o.id WHERE o.status='paid' AND i.quantity>0. The goal is one ID per customer having at least one paid order with a positive-quantity item. Propose an equivalent rewrite and explain what plan and workload evidence is needed before claiming an optimization.
Updated
Official answer
The requested result is customer IDs, not a row for each qualifying item. A semijoin-style formulation makes that intent explicit:
SELECT c.id
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.id
WHERE o.customer_id = c.id
AND o.status = 'paid'
AND i.quantity > 0
)
ORDER BY c.id;
With unique customer IDs, this produces one row per qualifying customer, even when several orders or items qualify. The original DISTINCT join can be logically correct too; the rewrite is a candidate to measure, not proof of a faster plan.
Compare result sets on cases with duplicate matches, unpaid orders, no items and NULL/nonpositive quantities. Then inspect the original and candidate plans using representative data. Look for incorrect cardinality estimates, expensive duplicate elimination, repeated scans and spills.
Consider supporting indexes on the relevant order/customer and item/order predicates based on selectivity and write cost. Do not index every joined column automatically, force a join algorithm without evidence, or change an outer join to an inner join unless the required result is preserved. Report actual latency and resource measurements before claiming improvement.