Wes <wespvp@syntegra.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