Thread: Is there a way to drop and restore an index?

I recall seeing a message by Tom Lane stating that dropping and
re-creating a primary index may speed up db performance. Is there a
SQL command that will do this?

My current method is to use pg_dump -s to dump out the schema. Then I
go through and cut out everything but the CREATE INDEX lines. Then, I
have to add a DROP INDEX line before that. I run this through with the
psql command line program.

Is there a better way?

Thanks.
-Tony

Just off the top of my head,

Couldn't you write a little PL/PGSQL procedure which queries the system
tables and builds statements to execute with the new EXECUTE command for
each record returned that would drop and recreate the indexes? It would
take a little work but would be generic enough to automatically reindex
your entire DB.

Just a thought,

Mike Mascari
mascarm@mascari.com

Tony Reina wrote:
> 
> I recall seeing a message by Tom Lane stating that dropping and
> re-creating a primary index may speed up db performance. Is there a
> SQL command that will do this?
> 
> My current method is to use pg_dump -s to dump out the schema. Then I
> go through and cut out everything but the CREATE INDEX lines. Then, I
> have to add a DROP INDEX line before that. I run this through with the
> psql command line program.
> 
> Is there a better way?
> 
> Thanks.
> -Tony

See REINDEX.
        regards, tom lane

Tom Lane wrote:

> See REINDEX.
>

Thanks.
-Tony