Hi and happy new year. The lazy vacuum calls lazy_cleanup_index to update statistics of indexes on a table such as relpages, reltuples at the end of the lazy_scan_heap. In all type of indexes the lazy_cleanup_index scans all index pages. It happens even if table has not been updated at all since previous vacuum invoked. Freeze map reduces the execution time and cost of table vacuuming much if almost table has been frozen. But it doesn't work for cleaning up indexes. If a very large static table has index then because the cleaning up index is called and it always scans all index pages, it takes time to scan all pages of index as reported[1].
Attached patch introduces new GUC parameter parameter vacuum_cleanup_index_scale_factor which specifies the fraction of the table pages containing dead tuple needed to trigger a cleaning up indexes. The default is 0.0, which means that the cleanup index is not invoked if no update on table. In other word, if table is completely frozen then lazy vacuum can skip the index scans as well. Increasing this value could reduce total time of lazy vacuum but the statistics and the free space map of index are not updated. [1] https://www.postgresql.org/message-id/MWHPR20MB142177B86D893C946FAFC9A4A18C0%40MWHPR20MB1421.namprd20.prod.outlook.com Regards, -- Masahiko Sawada NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
cleanup_indexes_threshold_v1.patch
Description: binary/octet-stream
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers