On Mon, Feb 17, 2020 at 8:21 AM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Jeremy Finzel <finzelj@xxxxxxxxx> writes:
> I have a table foo with 100 million rows, and a column:
> - id character varying(20)
> The following command is the one that we expect to execute very quickly (we
> are not seeing any locking), but it is instead taking a very long time:
> - ALTER TABLE foo ALTER COLUMN id TYPE varchar(100);
Hm, the code is supposed to avoid a table rewrite, but I wonder if
there's something else that's not being avoided, such as an index
rebuild or foreign-key verification. Could we see the whole table
definition, eg from psql \d+ ?
regards, tom lane
Based on your feedback, I quickly identified that indeed, the following index is causing the re-type to be slow:
"id_idx" btree ("substring"(id::text, 4, 7))
I'm still not sure why a rebuild of this index would be required, technically speaking. But perhaps in any case the docs should have something to the effect that _expression_ indexes may require rebuild under specific circumstances?
How much faster would "it" be if you dropped the index, ran ALTER and rebuilt the index? Or is it too late?
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
