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

1 Aug 2001 16:33:39 -0700
reina@nsi.edu (Tony Reina)
Is there a way to drop and restore an index?
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

Wed, 01 Aug 2001 20:49:26 -0400
Mike Mascari <mascarm@mascari.com>
Re: Is there a way to drop and restore an index?
See REINDEX.

			regards, tom lane

Wed, 01 Aug 2001 21:35:34 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Is there a way to drop and restore an index? 
Tom Lane wrote:

> See REINDEX.
>

Thanks.
-Tony



Wed, 01 Aug 2001 18:52:54 -0700
"G. Anthony Reina" <reina@nsi.edu>
Re: Is there a way to drop and restore an index?