From: Mark Hills <mark@xwax.org>
To: pgsql-performance@lists.postgresql.org
Subject: Domain check taking place unnecessarily?
Date: Wed, 8 Feb 2023 18:01:23 +0000 (GMT)
Message-ID: <2302081605290.5352@stax.localdomain> (raw)
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
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, pgsql-performance@lists.postgresql.org
Subject: Re: Domain check taking place unnecessarily?
In-Reply-To: <2302081605290.5352@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