From: | Cédric Villemain <cedric(dot)villemain(dot)debian(at)gmail(dot)com> |
---|---|
To: | Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com> |
Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Vacuum, visibility maps and SKIP_PAGES_THRESHOLD |
Date: | 2011-05-27 14:07:25 |
Message-ID: | BANLkTinVSKKJdMsn4KFm1zQjPXfE_bGMGw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
2011/5/27 Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com>:
> I wonder if we have tested the reasoning behind having
> SKIP_PAGES_THRESHOLD and the magic number of 32 assigned to it
> currently. While looking at the code after a long time and doing some
> tests, I realized that a manual VACUUM would always scan first 31
> pages of a relation which has not received any write activity since
> the last VACUUM. On closer inspection, I realized that we have
> deliberately put in this hook to ensure that we use visibility maps
> only when we see at least SKIP_PAGES_THRESHOLD worth of all-visible
> sequential pages to take advantage of possible OS seq scan
> optimizations.
>
> My statistical skills are limited, but wouldn't that mean that for a
> fairly well distributed write activity across a large table, if there
> are even 3-4% update/deletes, we would most likely hit a
> not-all-visible page for every 32 pages scanned ? That would mean that
The page skip is still based on VM.
So you wonder what are the chances of a VM not up-to-date when we access it ?
> almost entire relation will be scanned even if the visibility map
> tells us that only 3-4% pages require scanning ? And the probability
> will increase with the increase in the percentage of updated/deleted
> tuples. Given that the likelihood of anyone calling VACUUM (manually
> or through autovac settings) on a table which has less than 3-4%
> updates/deletes is very low, I am worried that might be loosing all
> advantages of visibility maps for a fairly common use case.
>
> Do we have any numbers to prove what we have today is good ? Sorry, I
> may not have followed the discussions very closely in the past and not
> sure if this has been debated/tested already.
>
> Thanks,
> Pavan
>
>
>
>
>
> --
> Pavan Deolasee
> EnterpriseDB http://www.enterprisedb.com
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2011-05-27 14:11:17 | Re: Vacuum, visibility maps and SKIP_PAGES_THRESHOLD |
Previous Message | Tom Lane | 2011-05-27 14:06:10 | Re: Vacuum, visibility maps and SKIP_PAGES_THRESHOLD |