Query Processing & Optimization

A correlated subquery references a column from the enclosing query, so its result depends on the current outer row and logically must be re-evaluated for each one. An uncorrelated subquery is self-contained: computed once, reused for every row.

Pushdown moves a filter as close to the data source as possible - below joins and into scans - so fewer rows travel up the plan. Projection pushdown does the same for columns: read and carry only the columns actually needed. Both cut I/O, memory and CPU.

It produces a semi-join: for each outer row it checks whether at least one matching inner row exists, then emits the outer row once and stops probing. An inner join emits one output row per matching pair, so it can duplicate outer rows and it exposes inner columns.

When a predicate applied after the join rejects NULLs on the null-supplying side, the NULL-extended rows cannot survive anyway, so the outer join is equivalent to an inner join. Converting is worth it because inner joins can be reordered freely and get more access-path and pushdown options.

A logical plan says what result is wanted - relational operators like scan, filter, join, aggregate - with no algorithms. A physical plan says how: chosen access methods, join algorithms and order, with costs. Binding and rewriting produce the logical plan; the cost-based optimizer produces the physical one.

A rule-based optimizer applies a fixed priority list of heuristics regardless of the data. A cost-based optimizer enumerates alternative plans, estimates the cost of each from statistics about the data, and picks the cheapest. Cost-based wins because the right plan depends on data distribution, which rules cannot see.

They are summaries of the data — row counts, distinct values per column, null fractions, common values, value distributions — that the query planner reads to guess how many rows each step will produce. Missing or stale statistics make those guesses wrong, so the planner picks a bad access path or join method and the query runs far slower.

The optimizer estimates what fraction of rows the predicate will match using stored statistics, converts that fraction into an estimated cost for each candidate path, and picks the cheapest. Low estimated selectivity favours the index; high favours the full scan.

Selectivity is the fraction of rows a predicate keeps (0 to 1); cardinality is the resulting row count — input rows times selectivity. Every cost term scales with cardinality, and estimates feed into the next operator, so an early error multiplies up the plan tree and produces structurally wrong join and access choices.

Join order never changes the result, but it changes the size of the intermediate results carried between operators, so cost can differ by orders of magnitude. The number of orderings grows factorially: eight tables give 40,320 left-deep orders and roughly 17 million bushy ones.

A fully pipelined plan passes each row up to the client as it is produced, so the first row arrives early. If the plan contains a blocking operator — a sort, a hash build, a grouping step — nothing can be emitted until that operator has read all its input, so results appear only at the end.

For each row of the outer input, scan the entire inner input and emit the pairs that satisfy the join predicate. Comparisons are O(n*m), and the naive form re-reads the inner input once per outer row, so I/O is the real problem.

Each sort or grouping operator gets a limited memory budget. If the rows it must hold exceed that budget, the engine writes partial results into temporary files and reads them back later. Spilling replaces memory work with disk I/O, so latency jumps sharply.

Phase one (build): read the smaller input fully and hash its join-key values into an in-memory hash table. Phase two (probe): stream the larger input, hash each row's key, look it up, and emit matches. It is chosen for equality joins over large inputs with no useful index.

Each operator call returns a batch — commonly around 1024 rows held column-wise — instead of one row. Per-call dispatch is amortized over the batch, and the inner loops become tight, branch-free, cache-resident and SIMD-friendly, so far more cycles go into real work.

A plan is the tree of physical operators the engine will run - scans, joins, sorts - with estimated rows and cost per node. A predicted plan shows estimates only; an execution-time plan really runs the query and adds actual rows, loop counts and timings.

The cache stores compiled physical plans so repeated statements skip parsing, binding and optimization. Entries are keyed on the statement text (plus session context like schema search order and some settings), and the key is essentially exact-match, so different whitespace or inlined literals produce different keys and separate entries.

Read innermost/deepest nodes first: leaves produce rows, parents consume them, results flow upward to the root. A node showing 8 rows over 40,000 executions was started 40,000 times and produced about 8 rows each time - roughly 320,000 rows in total, so multiply before judging cost.

The engine compiled the plan using the first parameter values it saw and cached it. If those values were unrepresentative - very selective or very unselective compared with typical ones - every later execution reuses a plan tuned for the wrong case. Recompiling with different values silently swaps which case suffers.

It is a cardinality misestimate: the optimizer planned for a tiny input and got a huge one, so it likely chose the wrong join method, join order and memory grant. Causes are stale or missing statistics, correlated predicates, and predicates the optimizer cannot see through. Find the lowest node where the gap starts.