<MATH> N = ((Z*2)+((Z*3)+0))/1 </MATH>
Apply rules 1,3,4,2 and we get <MATH> N = (2+3)*z </MATH>
Two operations instead of five, no division operator
Same idea works with the Relational Algebra
Filter Early is an algebraic optimization:
Predicate push Down is the same that filter early.
It is valid to push a filter into an input of an inner join if the filter does not reference columns from the other input.
Example from CALCITE-873
Given the following table:
CREATE TABLE T (
K1 VARCHAR,
K2 VARCHAR,
K3 VARCHAR,
CONSTRAINT pk PRIMARY KEY (K1, K2, K3));
In the following queries, no sort is necessary:
SELECT * FROM T WHERE K1='A' ORDER BY K2,K3;
SELECT * FROM T WHERE K2='B' ORDER BY K1,K3;
SELECT * FROM T WHERE K1='A' AND K2='B' ORDER BY K3;