Richard Guo <guofengli...@gmail.com> writes: > > The detection of self-inconsistent restrictions already exists in > planner. > > # set constraint_exclusion to on; > SET > # explain (costs off) select * from a where a > 3 and a is null; > QUERY PLAN > -------------------------- > Result > One-Time Filter: false > (2 rows)
It has a different scope and cost from what I suggested. I'd suggest to detect the notnull constraint only with lower cost and it can be used in another user case. the constaint_exclusion can covers more user cases but more expensivly and default off. Apart from the abve topic, I'm thinking if we should think about the case like this: create table t1(a int); create table t2(a int); explain (costs off) select * from t1 join t2 using(a) where a is NULL; QUERY PLAN ----------------------------------- Hash Join Hash Cond: (t2.a = t1.a) -> Seq Scan on t2 -> Hash -> Seq Scan on t1 Filter: (a IS NULL) Here a is nullable at the base relation side, but we know that the query would not return anything at last. IIUC, there is no good place to handle this in our current infrastructure, I still raise this up in case I missed anything. -- Best Regards Andy Fan