On 4/17/19 2:14 AM, Tim Kane wrote:
On Tue, 16 Apr 2019 at 18:04, Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>> 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.____
The table definition and the size of the data set would help with
interpreting the below.
__
__
PSQL:alpha_db/postgres@[local]=# select relfilenode from pg_class where
relname='test';____
relfilenode____
-------------____
20669469 <tel: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 <tel: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 <tel:20671802>____
(1 row)____
__ __
Time: 0.331 ms____
__
Apologies if this formats badly :-/ transcribing between devices not
well suited to email.
Tim
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
