Ajit Awekar <ajitpostgres@gmail.com> writes: > 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.
This is not done by default because it would be a waste of planner cycles for well-written queries. However, if you have a lot of poorly-written queries ...
regression=# create table products (price numeric); CREATE TABLE regression=# explain select * from products where price < 100 AND price > 300; QUERY PLAN ------------------------------------------------------------------- Seq Scan on products (cost=0.00..30.40 rows=7 width=32) Filter: ((price < '100'::numeric) AND (price > '300'::numeric)) (2 rows)
regression=# set constraint_exclusion to on; SET regression=# explain select * from products where price < 100 AND price > 300; QUERY PLAN ------------------------------------------ Result (cost=0.00..0.00 rows=0 width=0) One-Time Filter: false (2 rows)
