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, 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.