Thread: dropping and recreating indexes vs. reindexing

dropping and recreating indexes vs. reindexing

From
"Henrik Steffen"
Date:
hello all,

I used to drop and recreate all user indexes once every night.
I noticed that this led to an increase of performance...

Today, using postgres-7.2.1, I wonder if this still has
benefits, or if I should better use "reindex" or if I
don't have to bother about reindexing at all ?

any information appreciated,

--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------


Re: dropping and recreating indexes vs. reindexing

From
"scott.marlowe"
Date:
On Thu, 21 Nov 2002, Henrik Steffen wrote:

>
> hello all,
>
> I used to drop and recreate all user indexes once every night.
> I noticed that this led to an increase of performance...
>
> Today, using postgres-7.2.1, I wonder if this still has
> benefits, or if I should better use "reindex" or if I
> don't have to bother about reindexing at all ?

The problems that cause index growth are still present in pgsql, so if you
are doing a lot of updates / deletes, then yes, reindexing may help.

You should check the size of your indexes on disk to see if they are
larger than they should be.  A quick check is to just to check on the
space your datbase is using, then reindex it and check the space used
again.  If the size drops noticeable, then you probably do have some
problems with index growth.