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)
regards, tom lane
