Experiments and implementations · Intermediate

Comparing join plans across selectivities

Compare joins with narrow and broad predicates while distinguishing estimated rows, actual rows and repeated execution counts.

Ask Logeia about this articleTry the three questionsReview your answers

Updated

On this page
  1. Examine the rows reaching the join
  2. Fix data generation and index conditions
  3. Execute narrow and broad predicates
  4. Align the units of rows and loops
  5. Avoid ranking join names universally
  6. Extend the experiment with explicit distribution changes
  7. Ask Logeia about this articleCheck your understanding

Examine the rows reaching the join

Joining the same two tables can have very different costs when one input is narrowed to one row versus when most rows survive. Create customer and order tables and compare a single-customer predicate with a broad region predicate. The goal is to explain how many rows are processed and how often, not memorize one join-method name. This is a PostgreSQL 18 reproduction procedure, not an observed plan transcript. Run it in a disposable local database. These two queries intentionally ask different questions, so their elapsed times must not be presented as an optimization that preserves an identical result set.

Fix data generation and index conditions

Use ten thousand customers, two hundred thousand orders and an index on the order customer identifier. The formulas deliberately generate a simple artificial distribution rather than a realistic concentration of orders among customers. Run ANALYZE before collecting plans so a missing-statistics case is not accidentally mixed into the comparison. This still does not force one join method: version, configuration, row width and cost settings can affect planning, while caching affects execution. Record which conditions are fixed and which predicate is changed. A controlled dataset is useful for understanding a mechanism without claiming to represent every production workload.

Execute narrow and broad predicates

The first EXPLAIN ANALYZE query selects customer forty-two, while the second selects regions below ninety. Both aggregates return one final row, but many orders may be processed before aggregation. One output row therefore does not mean that the join handled one row. Read the plan from its scan inputs upward and distinguish rows surviving scans, rows returned by the join and rows consumed by the aggregate. Compare plain EXPLAIN when useful to identify which fields are planning estimates and which require execution. Keep the actual query text with each plan rather than associating a displayed node name with an unspecified workload.

PostgreSQL 18 SQL — procedure
-- PostgreSQL 18, one disposable local session.
CREATE TEMP TABLE lab_customers(id integer PRIMARY KEY, region integer);
CREATE TEMP TABLE lab_orders(id integer, customer_id integer, amount integer);
INSERT INTO lab_customers SELECT i, i%100 FROM generate_series(1, 10000) AS i;
INSERT INTO lab_orders SELECT i, 1+(i%10000), i%200 FROM generate_series(1, 200000) AS i;
CREATE INDEX lab_orders_customer ON lab_orders(customer_id);
ANALYZE lab_customers;
ANALYZE lab_orders;
EXPLAIN (ANALYZE, BUFFERS)
SELECT sum(o.amount) FROM lab_customers c JOIN lab_orders o ON o.customer_id=c.id WHERE c.id=42;
EXPLAIN (ANALYZE, BUFFERS)
SELECT sum(o.amount) FROM lab_customers c JOIN lab_orders o ON o.customer_id=c.id WHERE c.region<90;

Align the units of rows and loops

For repeatedly executed nodes, displayed actual rows represent an average per execution. A node showing twenty rows and one hundred loops consequently represents roughly two thousand row results when considering total work, with care for rounding and node semantics. Parent timing can include child work, so summing every displayed duration double-counts work. To locate an estimate error, compare estimated and actual quantities with compatible units. Do not compare the aggregate’s single output row with a scan estimate and call it a cardinality failure. The interpretation depends on the node and on how often it is executed.

Avoid ranking join names universally

Nested loops can be attractive with a small outer input and efficient indexed inner lookups. Hash joins can match many rows against a built structure but introduce memory and possible spill costs. Merge joins depend in part on ordering of inputs. None is universally best. If the method does not change between these queries, that is not automatically an experimental failure; inspect estimates and other conditions explaining the chosen cost. Prefer explaining the selected path from row counts and work over forcing a preferred method name through settings. An attractive label does not establish a faster or more suitable plan.

Extend the experiment with explicit distribution changes

Possible follow-ups concentrate orders among a few customers, correlate columns, or increase row width. Treat each as a new condition and record when statistics were collected and what each query returned. Additional statistics do not automatically repair every join estimate; inspect which relationships a supported statistics object can represent. Close the temporary-table session after testing. When connecting the observation to optimization, distinguish a semantics-preserving rewrite from a comparison that deliberately changes requirements. The procedure helps explain planner choices under different selectivities, but a production recommendation needs representative data and equivalent-answer validation in addition to these artificial examples.

Reading and practice are recorded separately in this browser, for each language.

Learn this in order

Understand the systems behind the web · STEP 12

Choose a learning path