Re:Index bloat reduced in PostgreSQL v14
by janto on Saturday September 11, 2021 @04:58AM
(#28490476)
I wonder if the planner can look at stats of full query execution in addition to the table stats. That way it can fall back to a previous plan (or previous table stats) if things get crazy.
Re:Index bloat reduced in PostgreSQL v14
by pgaddict on Saturday September 11, 2021 @08:09PM
(#28497336)
No, we don't collect any optimizer stats during query execution. It's trickier that it seems, because (a) collecting the stats is actually pretty expensive, and (b) when using indexes, you may actually see just a tiny part of the data.
There have been some discussions about "learning" and correcting some of the estimates, but there was no patch so far.
Even if a full rework or overriding would be hard to implement, saving/loading statistics, or disabling the collector (after it already managed to do a decent run) couldn't be done? Or the nature/representation of the stats don't allow for this? (So they need constant updating, even if badly? But if stats only influence query plans then after good plans are found for the most common queries they could be persisted, right? Am I missing something?)
Re:Index bloat reduced in PostgreSQL v14
by pgaddict on Saturday September 11, 2021 @08:21PM
(#28497418)
I think import/export of stats is pretty doable. Not a tiny amount of work, because of how many stats there may be (regular, extended), but I don't see any obvious major challenges ...
Similarly for disabling autoanalyze. We kinda already allow that for autovacuum (ALTER TABLE ... SET ...), so adding autoanalyze_enabled seems possible too. Or you could just set the threshold/scale_factor very high ...
The worst one, in my experience, is that the statistics collector is architecturally broken for some large tables, which can cause the query planner to go haywire. As a consequence, query performance can be erratic, varying by a couple orders of magnitude each time the table is analyzed. There are no workarounds in many cases, and the architectural defect that causes it is decidedly non-trivial to address (otherwise it would have been fixed years ago).
This is the only defect that consistently rears its head in operational environments in my experience. As data volumes grow, more Postgres instances are experiencing this problem. At a minimum there needs to be a way to easily override the query planner when it goes insane.