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