On Thu, Apr 25, 2024 at 4:57 PM Frédéric Yhuel <frederic.yh...@dalibo.com> wrote: > Now I have just read Robert's new message, and I understand his point. > But is there a real problem with triggering analyze after every 500000 > (or more) modifications in the table anyway?
It depends on the situation, but even on a laptop, you can do that number of modifications in one second. You could easily have a moderate large number of tables that hit that threshold every minute, and thus get auto-analyzed every minute when an autovacuum worker is launched in that database. Now, in some situations, that could be a good thing, because I suspect it's not very hard to construct a workload where constantly analyzing all of your busy tables is necessary to maintain query performance. But in general I think what would happen with such a low threshold is that you'd end up with autovacuum spending an awful lot of its available resources on useless analyze operations, which would waste I/O and CPU time, and more importantly, interfere with its ability to get vacuums done. To put it another way, suppose my tables contain 10 million tuples each, which is not particularly large. The analyze scale factor is 10%, so currently I'd analyze after a million table modifications. Your proposal drops that to half a million, so I'm going to start analyzing 20 times more often. If you start doing ANYTHING to a database twenty times more often, it can cause a problem. Twenty times more selects, twenty times more checkpoints, twenty times more vacuuming, whatever. It's just a lot of resources to spend on something if that thing isn't actually necessary. -- Robert Haas EDB: http://www.enterprisedb.com
