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
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
