Re: [PROPOSAL] VACUUM Progress Checker.

Hello,

>Autovacuum knows what % of a table needs to be cleaned - that is how it is triggered.
>When a vacuum runs we should calculate how many TIDs we will collect and therefore how many trips to the indexes we need for given memory.
>We can use the VM to find out how many blocks we'll need to scan in the table. So overall, we know how many blocks we need to scan.

Total heap pages to be scanned can be obtained from VM as mentioned. To figure out number of index scans we need an estimate of dead tuples.

IIUC, autovacuum acquires information that a table has to be cleaned by looking at pgstat entry for the table. i.e n_dead_tuples.
Hence,initial estimate of dead tuple TIDs can be made using n_dead_tuples in pgstat.
n_dead_tuples in pgstat table entry is the value updated by last analyze and may not be up to date.
In cases where pgstat entry for table is NULL, number of dead tuples TIDs cannot be estimated.
In such cases where TIDs cannot be estimated , we can start with an initial estimate of 1 index scan and later go on adding number of index pages to the total count of pages(heap+index) if count of index scan exceeds.

Thank you,
Rahila Syed.

______________________________________________________________________
Disclaimer: This email and any attachments are sent in strictest confidence
for the sole use of the addressee and may contain legally privileged,
confidential, and proprietary data. If you are not the intended recipient,
please advise the sender by replying promptly to this email and then delete
and destroy this email and any attachments without any further use, copying
or forwarding.