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 <t...@sss.pgh.pa.us> wrote: > Ajit Awekar <ajitpostg...@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 >
