On Sun, Feb 9, 2014 at 4:40 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Claudio Freire <klaussfreire@xxxxxxxxx> writes: >>>> I also do routine reindexing to stop index bloat on its tracks, yet >>>> freshly-reindexed indexes get considerably reduced in size with vacuum >>>> full. > > AFAIK there's no reason for vacuum full to produce a different result > from reindex. Did you mean to say that the indexes get smaller than > what they had been after some normal operation? If so it's worth noting > this comment from the btree index building code (nbtsort.c): Smaller than after reindex. It was a surprise to me too. > Also, there are certain usage patterns that can result in btree indexes > having densities much lower than the conventional-wisdom 70%. The main > one I've seen in practice is "decimation", where you delete say 99 out > of every 100 entries in index order. This leaves just a few live entries > in each leaf page --- but our btree code doesn't reclaim an index page > for recycling until it's totally empty. So you can end up with a very > low load factor after doing something like that, and a reindex is the > only good way to fix it. That's exactly the kind of pattern the "archival" step results in, that's why I do routine reindexing. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
