Re: Vacuum time degrading

Wes <wespvp(at)syntegra(dot)com> writes:
> Ok, now I follow. Taking the biggest indexes:

> The weekend before:
> INFO: index "message_recipients_i_recip_date" now contains 393961361 row
> versions in 2435100 pages
> INFO: index "message_recipients_i_message" now contains 393934394 row
> versions in 1499853 pages

> After reindex:
> INFO: index "message_recipients_i_recip_date" now contains 401798357 row
> versions in 1765613 pages
> INFO: index "message_recipients_i_message" now contains 401787237 row
> versions in 1322974 pages

OK, that's certainly not a factor-of-four difference in size, so I'm
now convinced you're right: bringing the index into physical order is
having a big impact on the runtime.

From a development standpoint, that suggests a couple of TODO items:
* Look harder at whether VACUUM can scan the index in physical instead
of logical order.
* See whether ordinary btree maintenance (ie page splits) can do
anything to maintain/improve the physical ordering of the index.

regards, tom lane