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
