On Tue, 16 Apr 2019 at 18:04, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:
>
>
> Where are you seeing the rewrite in your case?
I’m unfamiliar with ctid (which I now plan to read up on)… I’ve been
looking at *relfilenode*
I’ve observed that relfilenode changes when altering from *old_type *
*à varchar(9) *and the operation takes 6 seconds on this data set.
PSQL:alpha_db/postgres@[local]=# select relfilenode from pg_class where
relname='test';
relfilenode
-------------
20669469
(1 row)
PSQL:alpha_db/postgres@[local]=# alter table test alter COLUMN exec_id set
data type varchar(9);
ALTER TABLE
Time: 6605.454 ms
PSQL:alpha_db/postgres@[local]=# select relfilenode from pg_class where
relname='test';
relfilenode
-------------
20671802
(1 row)
And then the other way… from *varchar(9) **à old_type*
refilenode does not change, and the operation takes 0.3ms
PSQL:alpha_db/postgres@[local]=# alter table test alter COLUMN exec_id set
data type execid_t;
ALTER TABLE
Time: 1.360 ms
PSQL:alpha_db/postgres@[local]=# select relfilenode from pg_class where
relname='test';
relfilenode
-------------
20671802
(1 row)
Time: 0.331 ms
Apologies if this formats badly :-/ transcribing between devices not well
suited to email.
Tim
>
