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

From Mike Lissner
Subject Re: Does converting an indexed varchar to text rewrite its index?Docs say so, tests say no.
Date
Msg-id CAMp9=EyEMDKswjY5r9F7B0b2_FaeY_oYVbze=aeBTEExuaC7RQ@mail.gmail.com
Whole thread Raw
In response to Re: Does converting an indexed varchar to text rewrite its index?Docs say so, tests say no.  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses Re: Does converting an indexed varchar to text rewrite its index?Docs say so, tests say no.
List pgsql-general
Thanks Adrian. Is there a reason that the index rebuild is nearly instant during the ALTER command as opposed to when you build it from scratch?

Does it have to do with why this is called a "toast" index?

DEBUG:  building index "pg_toast_37609_index" on table "pg_toast_37609"

Thanks for the feedback. I really appreciate it and it's super interesting to learn about.

Mike

On Thu, Jan 23, 2020 at 9:54 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
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.klaver@aklaver.com

pgsql-general by date:

Previous
From: David Steele
Date:
Subject: Re: pgbackrest: ERROR: [029]: unable to convert base 10 string'0000000B' to unsigned int
Next
From: Adrian Klaver
Date:
Subject: Re: Does converting an indexed varchar to text rewrite its index?Docs say so, tests say no.