Re: Unnecessary scan from non-overlapping range predicates

Ajit Awekar <ajitpostgres(at)gmail(dot)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