Design patterns and optimization · Intermediate

Verify predicate pushdown with results and plans

Verify equivalent optimized inner-join forms and a meaning-changing LEFT JOIN rewrite on disposable PostgreSQL.

Ask Logeia about this articleTry the three questionsReview your answers

Updated

On this page
  1. Reduce rows passed to later operators when semantics permit
  2. Check an inner-join condition for equivalence
  3. Execute fixed data in a disposable database
  4. Report the identical observed plans honestly
  5. Use an outer-join counterexample before generalizing
  6. Name the operator behind a selectivity ratio
  7. Solve the NULL and scan-count exercises
  8. Ask Logeia about this articleCheck your understanding

Reduce rows passed to later operators when semantics permit

To aggregate orders of active customers, filtering customers before a join can reduce rows passed into the join. Predicate pushdown moves eligible conditions earlier while preserving meaning. Read relational operations and query plans first. It is not a textual rule that moving WHERE into a subquery always makes SQL faster. An optimizer may already perform the same transformation, and some apparent moves change results.

The disposable PostgreSQL 18.6 example has one thousand customers, one hundred active, and ten thousand orders. Compare ordinary JOIN plus WHERE with joining a filtered customer subquery; both return one thousand orders. A two-row LEFT JOIN counterexample then shows a condition move that changes the answer. Compare answer, plan and semantics together rather than accepting a faster-looking query that no longer performs the requested operation.

Check an inner-join condition for equivalence

The predicate c.active depends only on customer columns. In this inner join, keeping active customers after joining is equivalent to filtering that input before joining. A predicate using both inputs cannot simply be evaluated on one side. NULL, types, functions, aggregation and LIMIT introduce further equivalence questions. Do not assume a side-effecting function can be evaluated a different number of times or in a different order without changing the contract.

Hand-check a small result first, then inspect where the actual plan places the filter. A logical explanation is not necessarily a physical execution sequence. A syntactic subquery also need not materialize a complete intermediate table. Read the optimizer's operator tree rather than treating source nesting as a direct account of temporary storage. Equivalence is a prerequisite to performance interpretation, not something inferred from a familiar SQL shape.

Execute fixed data in a disposable database

Save pushdown.sql and run psql -X -v ON_ERROR_STOP=1 -f pushdown.sql against a verified disposable database. Temporary tables and transaction-local work remain between BEGIN and ROLLBACK. Every tenth customer is active and each customer has ten orders. After ANALYZE, retain both counts and JSON EXPLAIN ANALYZE outputs. No production table or persistent configuration is modified.

Expected count is one hundred customers times ten orders, or one thousand. The primary-key join prevents one order matching several customer rows in this dataset. A changed duplicate-key schema requires new cardinality reasoning. EXPLAIN ANALYZE executes the statement, which matters especially when adapting this technique to effectful SQL. This script inspects SELECT answers and plan structure. Stop-on-error and successful completion through rollback define the recorded run rather than mere appearance of an initial plan.

sql
BEGIN;
CREATE TEMP TABLE phase3_customers(id integer PRIMARY KEY,active boolean);
CREATE TEMP TABLE phase3_orders(id integer, customer_id integer);
INSERT INTO phase3_customers SELECT g,g%10=0 FROM generate_series(1,1000) g;
INSERT INTO phase3_orders SELECT g,(g%1000)+1 FROM generate_series(1,10000) g;
ANALYZE phase3_customers;
ANALYZE phase3_orders;
SELECT count(*) AS expected_1000 FROM phase3_orders o JOIN phase3_customers c ON o.customer_id=c.id WHERE c.active;
EXPLAIN (ANALYZE,BUFFERS,FORMAT JSON) SELECT count(*) FROM phase3_orders o JOIN phase3_customers c ON o.customer_id=c.id WHERE c.active;
SELECT count(*) AS expected_1000 FROM phase3_orders o JOIN (SELECT * FROM phase3_customers WHERE active) c ON o.customer_id=c.id;
EXPLAIN (ANALYZE,BUFFERS,FORMAT JSON) SELECT count(*) FROM phase3_orders o JOIN (SELECT * FROM phase3_customers WHERE active) c ON o.customer_id=c.id;
CREATE TEMP TABLE phase3_left(id integer);
CREATE TEMP TABLE phase3_right(id integer,active boolean);
INSERT INTO phase3_left VALUES(1),(2);
INSERT INTO phase3_right VALUES(1,true),(2,false);
SELECT l.id,r.active FROM phase3_left l LEFT JOIN phase3_right r ON l.id=r.id WHERE r.active ORDER BY l.id;
SELECT l.id,r.active FROM phase3_left l LEFT JOIN phase3_right r ON l.id=r.id AND r.active ORDER BY l.id;
ROLLBACK;

Report the identical observed plans honestly

Both recorded plans scan ten thousand orders and build a hash from one hundred active customer rows, removing nine hundred customer rows by filtering. The ordinary form already applies the predicate before the join. The subquery therefore does not introduce new work reduction in this case. Report that equivalence of optimized plans rather than claiming a speed improvement from the rewrite. Both answers are one thousand.

The customer scan still visits the thousand-row input before producing one hundred rows. Filter pushdown is not an index reading only one hundred rows or partition pruning omitting a relation. Where rows are reduced and which storage is accessed are different observations. Do not turn tiny temporary-table timing differences into a production speed ratio. Further testing needs actual queries, statistics, indexes and storage matching the intended workload.

Use an outer-join counterexample before generalizing

The left table contains IDs one and two. The right contains active true for one and false for two. LEFT JOIN followed by WHERE r.active retains only ID one. Moving the predicate into ON retains ID two as a left-preserved row with NULL right fields. The apparent early filter changes the outer join's meaning. Interpret NULL through SQL's three-valued logic rather than ordinary Boolean assumptions.

Observed output is one true row in the first query and that row plus ID two with an empty psql NULL field in the second. Display formatting is not the SQL value itself. This does not mean no outer-join transformation is ever valid; it shows that equivalence needs additional conditions. Compare results before cost for each proposed move. A more selective plan solving a different query is not a valid optimization of the original requirement.

Name the operator behind a selectivity ratio

One hundred active customers out of one thousand give selectivity 0.1 for this predicate and input. Orders still scan ten thousand rows and the join produces one thousand. The ten-percent figure does not establish ten-percent total I/O or runtime. State the operator's input and output used in the ratio. Different join multiplicity or data correlation can change downstream cardinality, even with equal customer selectivity. Recompute under the real schema rather than transferring this example's ratios into a production estimate. This is another reason to check both answer and plan: a correct local reduction can have a smaller or larger practical effect elsewhere in the operator tree.

Solve the NULL and scan-count exercises

After moving the predicate into ON, ID two remains with NULL right columns. Under WHERE r.active it does not survive because NULL is not true. Explain the difference from the two-row input. A filter output of one hundred customers also does not prove only one hundred were scanned; inspect the scan and nine hundred removed rows.

One shorter subquery timing with an identical plan is not sufficient to recommend that spelling. Preserve samples and interval, considering environmental variation and seeking evidence of changed useful work. The objective is not a preferred textual form. Align predicate, equivalence, plan, accessed work and output, and complete rollback so the disposable resources do not remain.

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