Re: Unnecessary scan from non-overlapping range predicates

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