Check operators for cost and correctness

The decision

You are reviewing a query or its plan, before it ships or after it surprised you. Two different questions need two separate passes: what will this cost, and does it return the right answer? Wrong answers here are silent: no error, just a number that is off. What should you check?

You will end with where the query's cost comes from, and whether it contains any of the common silent wrong answers.

For cost

Find where the row count peaks

Multiply each operator's effect on row count along the plan. The peak, not the input size, sets the cost. A many-to-many join that fans out, or unnesting an array before aggregating, is the usual cause.

Classify each blocking operator

An operator can block in two ways. Latency-blocking: it emits nothing until its input ends (a sort); these set the time to the first row. State-blocking: it holds state that grows with its input (a hash table); these set the memory ceiling. Some do both.

Name what each operator holds in memory

All rows (N), one entry per distinct key, one entry per key and distinct value, the top k, or one window frame. If you cannot name it: you cannot predict the operator's cost.

Count the orderings and partitionings required

Count the distinct sort orders and partitionings the whole query needs. Each one is a sort or shuffle the planner must insert, and these usually are the query's cost.

Ask which properties each operator destroys

A hash aggregation destroys order. A union destroys both order and partitioning. A broadcast join keeps the large side's partitioning. Every destroyed property needed later is rebuilt at full price.

For correctness

Look for these seven silent wrong answers

A join that multiplies rows before an aggregate, inflating sums.
A LEFT JOIN with a condition on the right table in WHERE, which turns it into an inner join.
NOT IN against a subquery that can return NULL, which returns no rows at all.
COUNT(x) where COUNT(*) was meant: it skips NULLs.
SUM over an empty group, which returns NULL, not 0.
A NULL in a GROUPING SETS or ROLLUP result that could mean "all values" or a real NULL.
A RANGE window frame where ROWS was meant, which includes ties.