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

Since you just built the index, and it's relatively small, maybe all the
data is still cached.

On 1/23/20 10: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?)*
>
> Thanks!
>
> Mike

--
Angular momentum makes the world go 'round.