Re: Domain check taking place unnecessarily? - Mailing list pgsql-performance

From Mark Hills
Subject Re: Domain check taking place unnecessarily?
Date
Msg-id 2302091035450.14632@stax.localdomain
Whole thread Raw
In response to Re: Domain check taking place unnecessarily?  (Laurenz Albe <laurenz.albe@cybertec.at>)
Responses Re: Domain check taking place unnecessarily?
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Andres Freund
Date:
Subject: Re: max_wal_senders
Next
From: Mark Hills
Date:
Subject: Re: Domain check taking place unnecessarily?