0
Accepted answer
Some orders must have more than one active promotion row. The join key isn't unique on the promotions side, so you get a fan-out no matter where the filter sits.
Query joined orders to a promotions table with LEFT JOIN. Worked fine until we added a promotions.status = 'active' filter and now some orders show up twice.
SELECT o.order_id, o.amount, p.code
FROM orders o
LEFT JOIN promotions p ON p.order_id = o.order_id AND p.status = 'active';Row counts jumped about 3%. What did I break?
Accepted answer
Some orders must have more than one active promotion row. The join key isn't unique on the promotions side, so you get a fan-out no matter where the filter sits.
Consider DuckDB or Polars for this size before spinning up a cluster.
Be careful moving that filter into the WHERE clause instead, that quietly turns the LEFT JOIN into an inner join for that predicate.
Yes, the partial index was the win for us too.
Pick one deterministically with a ranked subquery before joining, or aggregate the promo codes into an array if more than one can legitimately apply.
Sign in to reply.
© 2026 Lakebench, operated by Hunnurji Rao. Bengaluru, Karnataka, India.
No cluster. No install. Just the tab.