public inbox for [email protected]
help / color / mirror / Atom feedConstraints elimination during runtime
2+ messages / 2 participants
[nested] [flat]
* Constraints elimination during runtime
@ 2025-04-16 11:16 Weck, Luis <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Weck, Luis @ 2025-04-16 11:16 UTC (permalink / raw)
To: [email protected] <[email protected]>
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’;
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Constraints elimination during runtime
@ 2025-04-16 12:38 Laurenz Albe <[email protected]>
parent: Weck, Luis <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Laurenz Albe @ 2025-04-16 12:38 UTC (permalink / raw)
To: Weck, Luis <[email protected]>; [email protected] <[email protected]>
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-04-16 12:38 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-04-16 11:16 Constraints elimination during runtime Weck, Luis <[email protected]>
2025-04-16 12:38 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox