2011/5/27 Cédric Villemain <cedric.villemain.deb...@gmail.com>: > 2011/5/27 Pavan Deolasee <pavan.deola...@gmail.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 ?
re-reading the mails and answers, I misunderstood the case you exposed. > >> 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@postgresql.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 > -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
