Re: [GENERAL] Vacuum time degrading - Mailing list pgsql-hackers

From Tom Lane
Subject Re: [GENERAL] Vacuum time degrading
Date
Msg-id 14630.1112720384@sss.pgh.pa.us
Whole thread Raw
List pgsql-hackers
Wes <> 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

pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Should we still require RETURN in plpgsql?
Next
From: "Joshua D. Drake"
Date:
Subject: REINDEX ALL