Structured Programming with go to Statements

1 card

1. 01

   Lesson 24 · A join executes in three ways, and Knuth's critical 3% in a warehouse is almost always a join.

   Structured Programming with go to Statements · 1974

   Read the plan before you optimise: the critical 3% has a name, and it is usually a join.

   Three algorithms. Nested loop: for every row on one side, look it up on the other — unbeatable when one side is tiny or the other is indexed on the key, disastrous between two large tables. Hash join: build a hash table from the smaller side, then pass once through the larger side and match — the warehouse workhorse, provided the small side fits in memory; if not, it spills to disk and becomes ten times slower. Merge join: both sides sorted on the key, a single parallel pass — perfect when the data is already ordered. The planner chooses among them from statistics; stale statistics mean a wrong plan, so refreshing them is maintenance, not an option. In distributed systems one more question appears: where do the rows meet? A small dimension is broadcast to every node; two large tables are redistributed (shuffled) by key, and a hot key — often NULL — sends half the data to a single node. And the classic trap: a join on a non-unique key multiplies rows, silently; the type 2 dimension from lesson 11 is the typical example. Before optimising, read the plan. The 3% are there, with a name.

   “We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil. Yet we should not pass up our opportunities in that critical 3%.”

   Open the card →