41–46 of 46 posts
I don't like the way postgres doesn't have the ability to switch query plans mid query. Frequently a query plan turns out to perform nowhere near as well as the planner expects (for example, because the data distribution is poor, or a key being filtered for doesn't exist). In those cases, flipping the query plan around could turn a 1 hour query into a 1 millisecond query. Yet postgres doesn't have the ability to do t…
Earlier quoted context omitted.
The decisions a database makes aren't semi-arbitrarily: they are completely based on your statistics. Usually wrong decisions are caused by outdated statistics.
The statistics cant accurately represent most data distributions, and errors stack multiplicativly with deep query plans. Typical query plans, even with fresh statistics, are lucky to be within a factor of 10, or even 100 of the true cost of running the query.
Nice. FYI, I once published a similar tool for Oracle, including an explanation of how to read FlameGraphs in SQL execution context. https://blog.tanelpoder.com/posts/visualizing-sql-plan-execu...
Earlier quoted context omitted.
The statistics cant accurately represent most data distributions, and errors stack multiplicativly with deep query plans. Typical query plans, even with fresh statistics, are lucky to be within a factor of 10, or even 100 of the true cost of running the query.
I would be interested to have some academic pointers or actual benchmarks about that claim
We instead generated statistics on a backup copy, then loadtested that the statistics worked acceptably, and then wrote those statistics to the production database. We did that every 3 months, because we found that outdated statistics didn't really have any appreciable performance impact, and performance degradation was gradual rather than a cliff.