From: | Tim Kane <tim(dot)kane(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | Ron <ronljohnsonjr(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Alter domain type / avoiding table rewrite |
Date: | 2019-04-17 15:34:02 |
Message-ID: | CADVWZZKLGc5xovmeyKmXVA76vLNLw5quHy=yDy_Onxc-ahco7A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, 17 Apr 2019 at 15:23, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:
>
>
> The table definition and the size of the data set would help with
> interpreting the below.
The below example shows the issue on a table with just a single field. I
can demonstrate the problem wether there are 100 records or a million
records.
In every case:
Altering the type from a domain of varchar(9) to a raw varchar(9) results
in a full table rewrite (as identified by relfilenode).
Altering the type from a raw varchar(9) to a domain of varchar(9) occurs
for free, with no change to relfilenode.
The timing of each ALTER operation appears to back this up.
postgres(at)[local]=# create domain old_type as varchar(9);
CREATE DOMAIN
postgres(at)[local]=# create table test (values old_type);
CREATE TABLE
postgres(at)[local]=# with data as (select generate_series(1,1000000),
md5(random()::text))
postgres(at)[local]-# insert into test select substring(md5, 1, 9) from data;
INSERT 0 1000000
Time: 4097.162 ms
postgres(at)[local]=# \d test
Table "alpha_core.test"
Column | Type | Modifiers
--------+----------+-----------
values | old_type |
postgres(at)[local]=# \dD old_type
List of domains
Schema | Name | Type | Modifier | Check
------------+----------+----------------------+----------+-------
alpha_core | old_type | character varying(9) | |
(1 row)
postgres(at)[local]=# select count(*) from test;
count
---------
1000000
(1 row)
postgres(at)[local]=# select relfilenode from pg_class where relname='test';
relfilenode
-------------
20689856
(1 row)
postgres(at)[local]=# alter table test alter COLUMN values set data type
varchar(9);
ALTER TABLE
Time: 993.271 ms
postgres(at)[local]=# select relfilenode from pg_class where relname='test';
relfilenode
-------------
20691283
(1 row)
postgres(at)[local]=# alter table test alter COLUMN values set data type
old_type;
ALTER TABLE
Time: 21.569 ms
postgres(at)[local]=# select relfilenode from pg_class where relname='test';
relfilenode
-------------
20691283
(1 row)
postgres(at)[local]=# drop table test;
DROP TABLE
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2019-04-17 16:15:25 | Re: Alter domain type / avoiding table rewrite |
Previous Message | Tom Lane | 2019-04-17 14:48:19 | Re: PostgreSQL ping/pong to client |