Thomas Munro <thomas.munro@gmail.com> writes:
> I'm wondering if the thing that changed between 9.6 and 13 might be
> the heuristics for when auto vacuum's background ANALYZE is triggered,
> creating the unlucky timing required to get your system to this state
> occasionally.
> For a while now I have been wondering how we could teach the
> planner/stats system about "volatile" tables (as DB2 calls them), that
> is, ones that are frequently empty, which often come up in job queue
> workloads. I've seen problems like this with user queries (I used to
> work on big job queue systems across different relational database
> vendors, which is why I finished up writing the SKIP LOCKED patch for
> 9.5), but this is the first time I've contemplated FK check queries
> being negatively affected by this kind of stats problem. I don't have
> a good concrete idea, though (various dumb ideas: don't let auto
> analyze run on an empty table if it's marked VOLATILE, or ignore
> apparently empty stats on tables marked VOLATILE (and use what?),
> ...).
Hmm. If this complaint were about v14 rather than v13, I'd be
wondering whether 3d351d916 was what made things worse. But
in v13, if the table does go to empty (zero length) and ANALYZE
happens to see that state, we should end up back at the planner's
"minimum ten pages" heuristic, which likely would be enough to
prevent choice of a seqscan. OTOH, if the analyzed state is
"empty but has a couple of pages", it looks like that could
provoke a seqscan.
This is all guesswork though, since we don't know quite what's
happening on Robert's system. It might be worth setting
"log_autovacuum_min_duration = 0" (either globally, or as a
reloption on the relevant tables), and seeing if there seems
to be any correlation between autovacuum/autoanalyze activity
and the occurrences of poor plan choices.
regards, tom lane
