Thread: PostgreSQL index quesiton for version < 7.4

Hi,

In the HISTORY file for PostgreSQL 7.4, it says:

          Make free space map efficiently reuse empty index pages, and other
          free space management improvements.

          In previous releases, B-tree index pages that were left empty
          because of deleted rows could only be reused by rows with index
          values similar to the rows originally indexed on that page. In
          7.4, "VACUUM" records empty index pages and allows them to be
          reused for any future index rows.

I have a table that is indexed on a sequence field that always increments.
As new rows are inserted, they are given the next ID in the sequence.
Oldre rows are deleted periodically.

For PG < 7.4, does the index grow without the free space being reclaimed?
Would this affect performance substantially?  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.)

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

Regards,

David.

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