Early in the morning every day a pgAgent job refreshes the contents of table A from table B on my PostgreSQL 8.4 database. Table A contains around 140k records across 91 columns and has two indexes - one as part of the PRIMARY KEY and the other a GIST index on a POINT PostGIS geometry column.
To make the process go a little faster the job drops the index on the geometry column, before deleting the records in the table A and inserting the records from table B, then the index is recreated. This all being done the autovacuum daemon gets to work when it feels like it (after ten minutes or so from comparing the job stats and table stats for the job completion time and autovacuum run time).
Upon checking on the table this morning after all this had happened the table stats told me the table size was 272MB, the TOAST table size was 8192bytes, and the index size was 23MB. This seemed quite large so I issued a REINDEX command on the table and the index size came down to 9832kB.
My question(s) is this:
Why does the REINDEX apparently reduce the size of the indexes so much when the indexes (or at least the geometry column index) have been built anew from scratch? Should I make sure that the table has been vacuumed/analyzed before the indexes are built? Is not dropping the index on the primary key a factor in this? What am I missing?