Hi Team,
Please find below an observation regarding query planning that leads to unnecessary table scan when the WHERE clause contains non-overlapping conditions.
EXPLAIN (costs off)
select * from products where price < 100 AND price > 300;
Seq Scan on products
Filter: ((price < '100'::numeric) AND (price > '300'::numeric))
Since this condition is false and result will always be empty. Despite this, we still perform unnecessary sequential scan over the table.
Can we detect such contradictory predicates during planning and optimize them away using a Result node with One-Time Filter: false. This would avoid scanning large tables unnecessarily and improve performance.
Similarly below non-overlapping ranges in query also lead to unnecessary scan without any result.
select * from products where (price between 3 and 100) and (price between 900 and 1000);
Thanks
Ajit
