Let’s create a garbage table..
CREATE TABLE foo AS SELECT x::text FROM generate_series(1,20e6) AS t(x); Time: 14077.654 ms (00:14.078)
Now, let’s look at boring UPDATE
,
UPDATE foo SET x = 'foo'; Time: 22239.050 ms (00:22.239)
Compared with ALTER
,
ALTER TABLE foo ALTER COLUMN x SET DATA TYPE text USING 'foo'; Time: 9480.027 ms (00:09.480)
It’s approximately twice as fast.