From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Mark Hills <mark(at)xwax(dot)org>, pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Domain check taking place unnecessarily? |
Date: | 2023-02-08 19:47:07 |
Message-ID: | 4b1a0c9e36280b1d4abeb4fb6336832409df6ae0.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
The last example is slower, because there is actually a non-NULL value to check.
If that were not a domain, but a normal check constraint, you could first add
the constraint as NOT VALID and later run ALTER TABLE ... VALIDATE CONSTRAINT ...,
which takes a while too, but does not lock the table quite that much.
But I don't think there is a way to do that with a domain.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2023-02-08 22:35:46 | Re: Window Functions & Table Partitions |
Previous Message | Benjamin Tingle | 2023-02-08 19:45:09 | Window Functions & Table Partitions |