Re: Unnecessary scan from non-overlapping range predicates

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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
>