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

Hi Tom,

Thanks a lot for sharing.

The GUC constraint_exclusion setting is helpful, especially for handling
poorly written queries.

Thanks & Best Regards,
Ajit

On Wed, 25 Jun 2025 at 21:58, Tom Lane <tgl@sss.pgh.pa.us> wrote: