Thread: Constraints elimination during runtime
I am not sure if this list is the most appropriate, but I figured I’d share it here…
If a column has a check constraint, such as CHECK (length(value) < 10) or even something like a VARCHAR(10) shouldn’t a query like this become a no-op/false instantly?
create table test_constraint (
value varchar(10) // could be a CHECK constraint also
); insert into test_constraint values (‘small’);
-- shouldn’t this qual always evaluate to false?
select * from test_constraint where value = ‘way too big to fit anyway’;
On Wed, 2025-04-16 at 11:16 +0000, Weck, Luis wrote:
> I am not sure if this list is the most appropriate, but I figured I’d share it here…
>
> If a column has a check constraint, such as CHECK (length(value) < 10) or even
> something like a VARCHAR(10) shouldn’t a query like this become a no-op/false instantly?
>
> create table test_constraint (
> value varchar(10) // could be a CHECK constraint also
> );
>
> insert into test_constraint values (‘small’);
>
> -- shouldn’t this qual always evaluate to false?
> select * from test_constraint where value = ‘way too big to fit anyway’;
I am sure that it could be done, but I doubt it would be a good idea.
These extra checks would slow down query planning for most queries a
bit, and only very few queries would benefit from it.
If you are writing a query that uses a user-defined constant, you
could write the query as
SELECT ...
FROM test_constraint
WHERE value = $1
AND length($1) <= 10;
If the query planner knows the value of the constant, it will evaluate
the second condition when it plans the query and replace it with a
"One-Time Filter: false", which would do exactly what you want.
Yours,
Laurenz Albe