Thread

1. Unnecessary scan from non-overlapping range predicates

   Ajit Awekar <ajitpostgres@gmail.com> — 2025-06-25T15:46:38Z

   Hi Team,
   
   Please find below an observation regarding query planning  that leads to
   unnecessary table scan when the WHERE clause contains non-overlapping
   conditions.
   
   
   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.
   
   Similarly below non-overlapping ranges in query also lead to unnecessary
   scan without any result.
   
   select * from products where (price between 3 and 100) and (price between
   900 and 1000);
   
   
   Thanks
   Ajit
   

2. Re: Unnecessary scan from non-overlapping range predicates

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-06-25T16:28:02Z

   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
   
   
   
   

3. Re: Unnecessary scan from non-overlapping range predicates

   Ajit Awekar <ajitpostgres@gmail.com> — 2025-06-26T04:15:31Z

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