Date: Wed, 8 Feb 2023 18:01:23 +0000 (GMT)
From: Mark Hills <mark@xwax.org>
To: pgsql-performance@lists.postgresql.org
Subject: Domain check taking place unnecessarily?
Message-ID: <2302081605290.5352@stax.localdomain>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Archived-At: 
 <https://www.postgresql.org/message-id/2302081605290.5352%40stax.localdomain>
Precedence: bulk

I'm used to adding an empty column being instant in most cases, so my 
attention was drawn when it took a long lock.

The timings below imply that each row is running the CHECK?

I've come to expect addition of a NULL column to be fast, and what I'm 
seeing seems to contradict the docs [1]:

> PostgreSQL assumes that CHECK constraints' conditions are immutable, 
> that is, they will always give the same result for the same input value. 
> This assumption is what justifies examining CHECK constraints only when 
> a value is first converted to be of a domain type, and not at other 
> times.

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):

I don't think this is another index or constraint, as removing them does 
not affect performance. Also the "text" case below seems to prove this. 
Results are fully reproducable by repeatedly dropping and adding these 
columns.

Reporting in case something is not as expected. I can't even think of a 
workaround here...

This is PostgreSQL 14.5 on Alpine Linux. Thanks.

[1] https://www.postgresql.org/docs/current/sql-createdomain.html

---

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)

-- 
Mark