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

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