can pg 9.6 vacuum freeze skip page on index?

From: xu jian <jamesxu(at)outlook(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: can pg 9.6 vacuum freeze skip page on index?
Date: 2016-11-29 15:01:38
Message-ID: MWHPR20MB14212E13FFD233DF28A68CDEA18D0@MWHPR20MB1421.namprd20.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello,

we upgraded our postgresql 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 after vacuum freeze, and we run vacuum freeze again, it should finish super faster.

However, it doesn't 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.

looks like 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?

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

Thanks,

James

Browse pgsql-admin by date

  From Date Subject
Next Message Albe Laurenz 2016-11-29 15:06:04 Re: How to query by month?
Previous Message taizi 2016-11-29 14:08:50 How to query by month?