Re: PostgreSQL index quesiton for version < 7.4

David F. Skoll wrote:
> For PG < 7.4, does the index grow without the free space being
> reclaimed?

I believe so.

> Would this affect performance substantially?

Yes, it could.

> I have had reports from customers who said our applications speed
> improved tremendously after a database dump/drop/recreate/restore
> recycle.  (We do run VACUUM ANALYZE regularly, so it's not the lack
> of that that would cause it to slow down.)

As a less severe measure, you could also use REINDEX. See:
http://www.postgresql.org/docs/7.3/static/sql-reindex.html

Also, there is a contrib/reindexdb, which I believe assists you in
reindexing all tables in a database.

>
> Will PG 7.4 improve the performance if this is indeed a problem?

I should think so. You need to be sure you freespace map settings are up
to snuff.

Here's the 7.4 doc for REINDEX:
http://www.postgresql.org/docs/current/static/routine-reindex.html
which basically says the need to reindex has been reduced.

Here's the freespace map reference:
http://www.postgresql.org/docs/current/static/runtime-config.html

16.4.2.2. Free Space Map
max_fsm_pages (integer)
     Sets the maximum number of disk pages for which free space will be
tracked in the shared free-space map. Six bytes of shared memory are
consumed for each page slot. This setting must be more than 16 *
max_fsm_relations. The default is 20000. This option can only be set at
server start.

max_fsm_relations (integer)
     Sets the maximum number of relations (tables and indexes) for which
free space will be tracked in the shared free-space map. Roughly fifty
bytes of shared memory are consumed for each slot. The default is 1000.
This option can only be set at server start.

HTH,

Joe