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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly