Plan Trees

Scan Filter Project

A scan-filter-project pipeline changes intermediate cardinalities.

highlighted = computed this step

Pipeline cardinalities change

A pipeline lets you read row counts from the leaves up to the root. The scan starts with the base table, the filter keeps matching rows, and project deduplicates under set semantics. Note: the rendered result table is recomputed.

scan filter project\text{scan filter project}

Filter changes row count

The scan row count is 3 and the filter row count is 2. Note: the filter output is an intermediate result, not an authored count.

scan=3,filter=2\text{scan}=3,\quad \text{filter}=2

cardinalities are exact counts on these tiny tables; choosing a plan by cost needs statistics and is deferred - no speed/perf claims; nested-loop vs hash is mechanism, not speed.

Scan filter project project course rows 1 filter course eq DB rows 2 scan Enroll rows 3 result course DB

Project deduplicates

The root project row count is 1. Note: projection follows set semantics here, so duplicate projected rows collapse.

project rows=1\text{project rows}=1

cardinalities are exact counts on these tiny tables; choosing a plan by cost needs statistics and is deferred - no speed/perf claims; nested-loop vs hash is mechanism, not speed.

Scan filter project project course rows 1 filter course eq DB rows 2 scan Enroll rows 3 result course DB

Summary

A pipeline exposes the exact row count after each physical step. Note: choosing by cost needs statistics and is deferred.

pipeline counts\text{pipeline counts}