On Thu, May 26, 2011 at 11:25 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > I'm still of the opinion that an incremental estimation process like > the above is a lot saner than what we're doing now, snarky Dilbert > references notwithstanding. The only thing that seems worthy of debate > from here is whether we should trust ANALYZE's estimates a bit more than > VACUUM's estimates, on the grounds that the former are more likely to be > from a random subset of pages. We could implement that by applying a > fudge factor when folding a VACUUM estimate into the moving average (ie, > multiply its reliability by something less than one). I don't have any > principled suggestion for just what the fudge factor ought to be, except > that I don't think "zero" is the best value, which AFAICT is what Robert > is arguing. I think Greg's argument shows that "one" is the right value > when dealing with an ANALYZE estimate, if you believe that ANALYZE saw a > random set of pages ... but using that for VACUUM does seem > overoptimistic.
The problem is that it's quite difficult to predict the relative frequency of full-relation-vacuum, vacuum-with-skips, and ANALYZE operations on the table will be. It matters how fast the table is being inserted into vs. updated/deleted; and it also matters how fast the table is being updated compared with the system's rate of XID consumption. So in general it seems hard to say, well, we know this number might drift off course a little bit, but there will be a freezing vacuum or analyze or something coming along soon enough to fix the problem. There might be, but it's difficult to be sure. My argument isn't so much that using a non-zero value here is guaranteed to have bad effects, but that we really have no idea what will work out well in practice, and therefore it seems dangerous to whack the behavior around ... especially in stable branches. If we changed this in 9.1, and that's the last time we ever get a complaint about it, problem solved. But I would feel bad if we changed this in the back-branches and then found that, while solving this particular problem, we had created others. It also seems likely that the replacement problems would be more subtle and more difficult to diagnose, because they'd depend in a very complicated way on the workload, and having, say, the latest table contents would not necessarily enable us to reproduce the problem. I would feel a lot better about something that is deterministic, like, I dunno, if VACUUM visits more than 25% of the table, we use its estimate. And we always use ANALYZE's estimate. Or something. Another thought: Couldn't relation_needs_vacanalyze() just scale up reltuples by the ratio of the current number of pages in the relation to relpages, just as the query planner does? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers