From: Mark Hills <mark@xwax.org>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Domain check taking place unnecessarily?
Date: Thu, 9 Feb 2023 10:56:35 +0000 (GMT)
Message-ID: <2302091035450.14632@stax.localdomain> (raw)
In-Reply-To: <4b1a0c9e36280b1d4abeb4fb6336832409df6ae0.camel@cybertec.at>
References: <2302081605290.5352@stax.localdomain>
<4b1a0c9e36280b1d4abeb4fb6336832409df6ae0.camel@cybertec.at>
On Wed, 8 Feb 2023, Laurenz Albe wrote:
> On Wed, 2023-02-08 at 18:01 +0000, Mark Hills wrote:
> > I've ruled out waiting on a lock; nothing is reported with
> > log_lock_waits=on. This is a test database with exclusive access (2.5
> > million rows):
> >
> > This is PostgreSQL 14.5 on Alpine Linux. Thanks.
> >
> > CREATE DOMAIN hash AS text
> > CHECK (VALUE ~ E'^[a-zA-Z0-9]{8,32}$');
> >
> > devstats=> ALTER TABLE invite ADD COLUMN test text;
> > ALTER TABLE
> > Time: 8.988 ms
> >
> > devstats=> ALTER TABLE invite ADD COLUMN test hash;
> > ALTER TABLE
> > Time: 30923.380 ms (00:30.923)
> >
> > devstats=> ALTER TABLE invite ADD COLUMN test hash DEFAULT NULL;
> > ALTER TABLE
> > Time: 30344.272 ms (00:30.344)
> >
> > devstats=> ALTER TABLE invite ADD COLUMN test hash DEFAULT '123abc123'::hash;
> > ALTER TABLE
> > Time: 67439.232 ms (01:07.439)
>
> It takes 30 seconds to schan the table and determine that all existing
> rows satisky the constraint.
But there's no existing data (note this is adding column, not constraint)
Existing rows are guaranteed to satisfy the domain check, because the
domain check is guaranteed to be immutable (per [1] in my original mail)
Of course, if it were a table constraint it may involve multiple columns,
requiring it to be evaluated per-row.
But the docs make it clear the domain check is expected to be evaluated on
input, precisely for this purpose.
So I wondered if this was a shortcoming or even a bug.
It seems that adding a column of NULL (or even default) values for a
domain can (should?) be as quick as a basic data type like text or
integer...?
--
Mark
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark@xwax.org, laurenz.albe@cybertec.at, pgsql-performance@lists.postgresql.org
Subject: Re: Domain check taking place unnecessarily?
In-Reply-To: <2302091035450.14632@stax.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox