Waiting for 9.2 – Avoiding reindexing on alter type of indexed column

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.

4 thoughts on “Waiting for 9.2 – Avoiding reindexing on alter type of indexed column”

  1. A $ sign as a psql prompt? Are you trying to mess with someone’s mind? 🙂

  2. @Joe:
    my full prompt looks like this:

    (depesz@[local]:5910) 16:36:00 [depesz] 
    $ 

    “$” is at the end because I’m using superuser for tests and my PROMPT1 is:

    '(%n@%M:%>) %`date +%H:%M:%S` [%/] \n%x$ '
  3. 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.

  4. @Joe:

    Hmm … not sure how you got default =>

    When I run psql without my .psqlrc, prompt is:

    =$ psql
    psql (9.2devel)
    Type "help" for help.
     
    depesz=# \set
    AUTOCOMMIT = 'on'
    PROMPT1 = '%/%R%# '
    PROMPT2 = '%/%R%# '
    PROMPT3 = '>> '
    VERBOSITY = 'default'
    VERSION = 'PostgreSQL 9.2devel on x86_64-unknown-linux-gnu, compiled by gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 64-bit'
    DBNAME = 'depesz'
    USER = 'depesz'
    PORT = '5910'
    ENCODING = 'UTF8'
    depesz=#

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.