Join Performance, EXPLAIN, and Optimization

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_line_items_order_id ON line_items(order_id);

# Join columns are prime indexing candidates.

EXPLAIN ANALYZE
SELECT c.customer_id, o.order_id
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE c.created_at >= CURRENT_DATE - INTERVAL '30 days';

# Check whether the planner uses indexes, hash joins, merge joins, or nested loops.

WITH recent_orders AS (
  SELECT order_id, customer_id
  FROM orders
  WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT c.customer_id, ro.order_id
FROM customers c
JOIN recent_orders ro ON ro.customer_id = c.customer_id;

# Filter or aggregate early when possible.

SELECT c.customer_id, c.name, o.order_id, o.total_amount
-- not SELECT *

# Return only the columns you need.

1. Are join keys indexed?
2. Are data types identical on both sides?
3. Is a one-to-many join inflating rows?
4. Can filters move earlier?
5. Does EXPLAIN show scans or huge estimates?
6. Can you pre-aggregate or split the query?

# Use this when a join query is unexpectedly slow.