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

Hi Masahiko,

Thanks for your reply. Is there any reason to update index statistics even if there is no changes on the table?
or is there any way to disable index statistics update during vacuum freeze? thanks

James

________________________________
发件人: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
发送时间: 2016年12月1日 9:06:15
收件人: xu jian
抄送: pgsql-hackers(at)postgresql(dot)org
主题: 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