Thread: Unnecessary scan from non-overlapping range predicates
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))
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.
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);
select * from products where (price between 3 and 100) and (price between 900 and 1000);
Thanks
Ajit
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:
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