Does converting an indexed varchar to text rewrite its index? Docs say so, tests say no.

From: Mike Lissner <mlissner(at)michaeljaylissner(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Does converting an indexed varchar to text rewrite its index? Docs say so, tests say no.
Date: 2020-01-23 16:55:49
Message-ID: CAMp9=ExXtH0NeF+LTsNrew_oXycAJTNVKbRYnqgoEAT01t=67A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I think the docs say that if you convert a varchar to text, it'll rewrite
the index, but my test doesn't seem to indicate that. Is the test or the
documentation wrong?

If the docs, I'll be happy to make a fix my first contribution to
postgresql. :)

Here are the docs:

(https://www.postgresql.org/docs/10/sql-altertable.html)

> [...] changing the type of an existing column will require the entire
table and its indexes to be rewritten. As an exception when changing the
type of an existing column, if the USING clause does not change the column
contents and the old type is either binary coercible to the new type or an
unconstrained domain over the new type, a table rewrite is not needed; but *any
indexes on the affected columns must still be rebuilt.*

And the test:

postgres=# CREATE TABLE t1 (id serial PRIMARY KEY, name character
varying(30));
CREATE TABLE
Time: 25.927 ms
postgres=# INSERT INTO t1 (id) SELECT generate_series(1,1000000) i;
INSERT 0 1000000
Time: 2080.416 ms (00:02.080)
postgres=# CREATE INDEX ON t1 (name);
CREATE INDEX
Time: 463.373 ms *<-- Index takes ~500ms to build*
postgres=# ALTER TABLE t1 ALTER COLUMN name TYPE text;
ALTER TABLE
Time: 19.698 ms *<-- Alter takes 20ms to run (no rebuild, right?)*

Thanks!

Mike

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron 2020-01-23 16:58:46 Re: Does converting an indexed varchar to text rewrite its index? Docs say so, tests say no.
Previous Message Bruce Momjian 2020-01-23 16:24:22 Re: calculating the MD5 hash of role passwords in C