On 18th of July, Robert Haas committed patch:
Avoid index rebuild for no-rewrite ALTER TABLE .. ALTER TYPE. Noah Misch. Review and minor cosmetic changes by me.
Some time ago, I wrote about new feature in PostgreSQL 9.1 – changing datatypes without rewrite of table.
This worked pretty good, but I didn't test, and forgot about – cases when changed column is indexed.
Apparently, even when the datatype changes in a way that can skip table rewrite (i.e. varchar(x) -> text), index still had to be reindexed. It looks like this:
$ CREATE TABLE test ( x VARCHAR(50) ); CREATE TABLE TIME: 5.820 ms $ copy test (x) FROM '/usr/share/dict/polish'; COPY 3495123 TIME: 9295.989 ms $ CREATE INDEX test_idx ON test (x); CREATE INDEX TIME: 31446.456 ms $ SELECT * FROM pg_class WHERE relname = 'test_idx'; -[ RECORD 1 ]--+------------ relname | test_idx relnamespace | 2200 reltype | 0 reloftype | 0 relowner | 16384 relam | 403 relfilenode | 22116 reltablespace | 0 relpages | 14088 reltuples | 3.49512e+06 reltoastrelid | 0 reltoastidxid | 0 relhasindex | f relisshared | f relpersistence | p relkind | i relnatts | 1 relchecks | 0 relhasoids | f relhaspkey | f relhasrules | f relhastriggers | f relhassubclass | f relfrozenxid | 0 relacl | [NULL] reloptions | [NULL] TIME: 0.509 ms $ ALTER TABLE test ALTER COLUMN x TYPE TEXT; ALTER TABLE TIME: 29837.654 ms $ SELECT * FROM pg_class WHERE relname = 'test_idx'; -[ RECORD 1 ]--+------------ relname | test_idx relnamespace | 2200 reltype | 0 reloftype | 0 relowner | 16384 relam | 403 relfilenode | 22117 reltablespace | 0 relpages | 14088 reltuples | 3.49512e+06 reltoastrelid | 0 reltoastidxid | 0 relhasindex | f relisshared | f relpersistence | p relkind | i relnatts | 1 relchecks | 0 relhasoids | f relhaspkey | f relhasrules | f relhastriggers | f relhassubclass | f relfrozenxid | 0 relacl | [NULL] reloptions | [NULL] TIME: 4.025 ms
Please note that changing the datatype in ALTER TABLE took 29s, and afterwards relfilenode of index has changed from 22116 to 22117 – which tells us that the index got recreated.
But now, with this new patch, it looks differently:
$ SELECT * FROM pg_class WHERE relname = 'test_idx'; -[ RECORD 1 ]--+------------ relname | test_idx relnamespace | 2200 reltype | 0 reloftype | 0 relowner | 16384 relam | 403 relfilenode | 16395 reltablespace | 0 relpages | 14088 reltuples | 3.49512e+06 reltoastrelid | 0 reltoastidxid | 0 relhasindex | f relisshared | f relpersistence | p relkind | i relnatts | 1 relchecks | 0 relhasoids | f relhaspkey | f relhasrules | f relhastriggers | f relhassubclass | f relfrozenxid | 0 relacl | [NULL] reloptions | [NULL] TIME: 0.679 ms $ ALTER TABLE test ALTER COLUMN x TYPE TEXT; ALTER TABLE TIME: 26.952 ms $ SELECT * FROM pg_class WHERE relname = 'test_idx'; -[ RECORD 1 ]--+--------- relname | test_idx relnamespace | 2200 reltype | 0 reloftype | 0 relowner | 16384 relam | 403 relfilenode | 16395 reltablespace | 0 relpages | 0 reltuples | 0 reltoastrelid | 0 reltoastidxid | 0 relhasindex | f relisshared | f relpersistence | p relkind | i relnatts | 1 relchecks | 0 relhasoids | f relhaspkey | f relhasrules | f relhastriggers | f relhassubclass | f relfrozenxid | 0 relacl | [NULL] reloptions | [NULL] TIME: 5.955 ms
Relfilenode didn't change (still 16395), and the time for alter table is visibly shorter!
I tested, and it works the same way also for unique indexes, including the ones used by primary key. Nice.
A $ sign as a psql prompt? Are you trying to mess with someone’s mind? 🙂
@Joe:
my full prompt looks like this:
“$” is at the end because I’m using superuser for tests and my PROMPT1 is:
Interesting choices. The $ is traditionally associated with a Unix/Linux/*BSD/VMS shell command line prompt and a # with a Unix/Linux/*BSD root/superuser shell prompt (also psql superuser prompt), and of course psql’s default is =>. Well, de gustibus non est disputandum.
@Joe:
Hmm … not sure how you got default =>
When I run psql without my .psqlrc, prompt is: