Why does the order I join tables in matter if joins are commutative?

Read the first row. Order B starts by joining the two largest tables with no filter between them and carries 4,100,000 rows into the next step. Order A starts with a filtered accounts relation and carries 1,200 rows. By the third step Order B has applied a filter and is down to 390,000 rows, but it has already carried millions through two joins. Order A never exceeds 31,000 rows. A filter that removes ninety nine per cent of a million-row table leaves 10,000 rows: $1,000,000 \times 0.01 = 10,000$. Applied before the join it saves 990,000 rows from every later step. Applied after three joins it saves none of that work.

The natural guess is that if $A \bowtie B = B \bowtie A$, the database can start anywhere and pay the same. That treats a join as one operation over whole tables. It is not. A filter applied after a join has already paid to read, hash, and pass every row that the filter later discards. A hash join also has a build side and a probe side, so even for one pair the two directions need not cost the same. Commutativity preserves the result; it does not preserve the intermediate cardinalities or the work.

Three tables give $3! = 3 \times 2 \times 1 = 6$ orders, which is trivial. Six tables give $6! = 720$. Ten give $10! = 3,628,800$. Twelve give $12! = 3,628,800 \times 11 \times 12 = 3,628,800 \times 132 = 479,001,600$. Thirteen give $13! = 479,001,600 \times 13 = 6,227,020,800$. That is the count before choosing a join method for each step and before allowing joins between two intermediate results. No hardware improvement catches a factorial.

Planners therefore do not enumerate complete orders. They build upwards by subsets. For each single table they keep the cheapest access path. For each pair they keep the cheapest join of two singles. For each triple they keep the cheapest join of a kept pair to a single. The work is proportional to the number of subsets, roughly $2^n n$, not to $n!$. At ten tables $2^{10} \times 10 = 1,024 \times 10 = 10,240$, against 3,628,800 orders. At twelve tables $2^{12} \times 12 = 4,096 \times 12 = 49,152$, against 479,001,600. That gap is why the method is usable.

The assumption stops holding when the planner gives up on exhaustive search. Above about a dozen tables the subset method itself becomes too expensive to finish inside the planning budget. The planner switches to a sampling search: start from a plausible order, make small changes, keep improvements, stop when time runs out. The plan is then a good order, not the best order. A slow large query may need to be split into smaller queries with a temporary result, so each piece is small enough to be planned exhaustively. A plan on a many-table query can also change without any statistic changing, because the search is not guaranteed to return the same order twice.