PostgreSQL 9.3 – Will This SQL Snippet REINDEX GIN Index?

disk-spaceindexindex-tuningpostgresqlpostgresql-9.3

I have a large PostgreSQL 9.3 database. One index on one table is quite large and appears to be bloated. I am using pgcompact from pgtoolkit to reduce database bloat without heavy table locks. However it would not reindex that large index, since it's a GIN index. It suggested this SQL snippet as a way to reindex this index. (mytable is the table, myindex is the index. myindex is just a GIN index on the nodes (bigint) column of mytable).

CREATE INDEX CONCURRENTLY pgcompact_index_17791 ON mytable USING gin (nodes) WITH (fastupdate=off);
BEGIN;
SET LOCAL statement_timeout TO 1000;
ALTER INDEX public.myindex RENAME TO pgcompact_temp_index_17791;
ALTER INDEX public.pgcompact_index_17791 RENAME TO myindex;
END;
DROP INDEX CONCURRENTLY public.pgcompact_temp_index_17791;

Will this snippet actually reindex the myindex index and reduce table index bloat?

What is the point of renaming the old index (myindex) to the name of the temporary index (pgcompact_temp_index_17791), and then renaming it back again? Won't that result in overwriting the currently being created temp index and then just renaming it back, meaning that myindex isn't rebuilt at all? The author of pgtoolkit appears quite knowledgeable, but I don't understand how this works, and I don't want to run SQL on my database without understanding it.

If this approach works, what's the difference between this and doing REINDEX INDEX myindex;?

Best Answer

Look at the names more carefully. pgcompact_temp_index_17791 and pgcompact_index_17791 are not the same thing.

The reason to rename the old index is that you can't do "drop index concurrently" inside of a transaction.

Related Question