|
|
|
|
|
|
by jandrewrogers
820 days ago
|
|
|
That's the theory but it does not work this way in practice. Many of the issues people run into with the Postgres query planner/optimizer are side effects of architectural peculiarities with no straightforward fix. Some of these issues, such as the limitations of Postgres statistics collection, have been unaddressed for decades because they are extremely difficult to address without major architectural surgery that will have its own side effects. In my opinion, Postgres either needs to allow people to disable/bypass the query planner with hints or whatever, or commit to the difficult and unpleasant work of fixing the architecture so that these issues no longer occur. This persistent state of affairs of neither fixing the core problems nor allowing a mechanism to work around them is not user friendly. I like and use Postgres but this aspect of the project is quite broken. The alternative is to put a disclaimer in the Postgres documentation that it should not be used for certain types of common workloads or large data models; many of these issues don't show up in force until databases become quite large. This is a chronic source of pain in large-scale Postgres installations. |
|
|
The way I see it the issues we have in query planning/optimization are largely independent of the overall architecture. It's simply a consequence of relying on statistics too much. But there's always going to be inaccuracies, because the whole point of stats is that it's a compact/lossy approximation of the data. No matter how much more statistics we might collect there will always be some details missing, until the statistics are way too large to be practical.
There are discussions about considering how "risky" a given plan is - some plan shapes may be more problematic, but I don't think anyone submitted any patch so far. But even with that would not be a perfect solution - no approach relying solely on a priori information can be.
IMHO the only way forward is to work on making the plans more robust to this kind of mistakes. But that's very hard too.