Message-ID: <3ee9caa4d50df99916922693b987df551a02d991.camel@cybertec.at>
Subject: Re: Constraints elimination during runtime
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: "Weck, Luis" <luis.weck@pismo.io>,
 "pgsql-performance@lists.postgresql.org"
	 <pgsql-performance@lists.postgresql.org>
Date: Wed, 16 Apr 2025 14:38:10 +0200
In-Reply-To: 
 <IA3PR10MB81133D5DD8051D7018DECD338DBD2@IA3PR10MB8113.namprd10.prod.outlook.com>
References: 
	<IA3PR10MB81133D5DD8051D7018DECD338DBD2@IA3PR10MB8113.namprd10.prod.outlook.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/3ee9caa4d50df99916922693b987df551a02d991.camel%40cybertec.at>
Precedence: bulk

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=E2=80=
=99d share it here=E2=80=A6
>=20
> If a column has a check constraint, such as CHECK (length(value) < 10) or=
 even
> something like a VARCHAR(10) shouldn=E2=80=99t a query like this become a=
 no-op/false instantly?
>=20
> create table test_constraint (
> =C2=A0 =C2=A0 =C2=A0value varchar(10) // could be a CHECK constraint also
> =C2=A0 );
>=20
> insert into=C2=A0test_constraint values (=E2=80=98small=E2=80=99);
>=20
> -- shouldn=E2=80=99t this qual always evaluate to false?=C2=A0
> select * from test_constraint where value =3D =E2=80=98way too big to fit=
 anyway=E2=80=99;

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 =3D $1
    AND length($1) <=3D 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