A full scan where pruning was expected, and the wrong join strategy (broadcast vs shuffle/hash), are the two big plan smells.

A plan is the engine confessing its strategy. Smell one: SEQ_SCAN (full scan) feeding a predicate that keeps almost nothing — a missing index/partition or a predicate written unsargably (a function wrapped around the column). Smell two: estimated rows vs actual diverging by orders of magnitude — stale statistics or a correlated predicate, and every join strategy downstream inherits the bad estimate. Read before rewriting.

SELECT
  machine
FROM
  plays
WHERE
  score + 0 > 900
ORDER BY
  machine;

SELECT
  machine
FROM
  plays
WHERE
  score > 900
ORDER BY
  machine;

Both queries return the same machine, but wrapping the column in score + 0 hides it from any prune — the plan shows a SEQ_SCAN reading all five rows to keep one, smell one of a slow query.

EXPLAIN (ANALYZE where available) read bottom-up; smell → cause → then the rewrite.