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

On 1/23/20 8:55 AM, Mike Lissner wrote:
> 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?)*

I going to say it is the exception to the exception, in that in Postgres
varchar and text are essentially the same type.

FYI there is a reindex going on:

test=> set client_min_messages = debug1;
test=> CREATE TABLE t1 (id serial PRIMARY KEY, name character varying(30));
LOG: statement: CREATE TABLE t1 (id serial PRIMARY KEY, name character
varying(30));
DEBUG: CREATE TABLE will create implicit sequence "t1_id_seq" for
serial column "t1.id"
DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey"
for table "t1"
DEBUG: building index "t1_pkey" on table "t1" serially
CREATE TABLE
test=> INSERT INTO t1 (id) SELECT generate_series(1,1000000) i;
LOG: statement: INSERT INTO t1 (id) SELECT generate_series(1,1000000) i;
INSERT 0 1000000
test=> CREATE INDEX ON t1 (name);
LOG: statement: CREATE INDEX ON t1 (name);
DEBUG: building index "t1_name_idx" on table "t1" with request for 1
parallel worker
CREATE INDEX
test=> ALTER TABLE t1 ALTER COLUMN name TYPE text;
LOG: statement: ALTER TABLE t1 ALTER COLUMN name TYPE text;
DEBUG: building index "pg_toast_37609_index" on table "pg_toast_37609"
serially
ALTER TABLE

>
> Thanks!
>
> Mike
> **

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com