Re: [HACKERS] postgres 1 个(共 2 个) can pg 9.6 vacuum freeze skip page on index?

On Thu, Dec 1, 2016 at 1:33 AM, xu jian <jamesxu(at)outlook(dot)com> wrote:
> Hello,
>
> Please execute me if I am using the wrong mailing list, but I ask the
> question in pgsql-admin, looks like no one know the answer.
>
>
> we upgraded our pg db to 9.6, as we know, pg9.6 doesn't need full table scan
> in vacuum freeze.
>
> http://rhaas.blogspot.com/2016/03/no-more-full-table-vacuums.html
>
>
> so we think if we have run vacuum freeze on the table, and there is no
> change on table which has been vacuum freeze before it should finish super
> faster.
>
>
> However, it doesn't look like we expect. the next run of vacuum freeze still
> take long time. Then we run vacuum freeze with verbose. we notice it spends
> long time on scanning index.
>
> it seems even all rows are frozen on the data page, vacuum freeze still
> needs to scan all the index pages. if we drop the index, then vacuum freeze
> finishes immediately.
>
>
> Does anyone know if it is true?

Yeah that's true. The vacuum on each index is required in order to
update index statistics even if no updating on table.

> Btw, our table is large, and has about 40GB index files. is there anyway to
> make the vacuum freeze faster in this case?

I guess that there is no way.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center