Postgres Pro
Downloads
Home   >   mailing lists

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=EyRZdxNR1+_gO-odzhqNREiOYPRvA8+XT80U7_Wy=x=ng@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
Tree view
You wrote:

 > Well it did not rebuilt the index("t1_name_idx") you created on name.

OK, so then the docs *are* wrong? They say that:

> any indexes on the affected columns must still be rebuilt.

But that doesn't happen? Sorry to be persistent. I'm just a bit confused here.



On Thu, Jan 23, 2020 at 11:28 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 1/23/20 11:17 AM, Mike Lissner wrote:
> 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?

Well it did not rebuilt the index("t1_name_idx") you created on name.

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

Certain data types(those that have varlena) can have portions of their
data stored in an auxiliary table in a compressed(or not) form. For all
the details see:

https://www.postgresql.org/docs/12/storage-toast.html

The index is the one on this auxiliary table.

>
> 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
>



--
Adrian Klaver
adrian.klaver@aklaver.com

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Does converting an indexed varchar to text rewrite its index?Docs say so, tests say no.
Next
From: Adrian Klaver
Date:
Subject: Re: Does converting an indexed varchar to text rewrite its index?Docs say so, tests say no.