3 ms·

I think this very much depends on what exactly is the problem - if it's with how costing for LIMIT works (https://news.ycombinator.com/item?id=39728826 https://

I think this very much depends on what exactly is the problem - if it's with how costing for LIMIT works (https://news.ycombinator.com/item?id=39728826 https://news.ycombinator.com/item?id=39728826), then yeah, this did not change since 9.x in a substantial way, so 16 has the same behavior. On a technical level, this is hard because the optimizer has a fairly limited amount of information (optimizer stats) - in particular, it does not know if the columns are correlated. If they are not, then this costing is perfect, and the plan is the correct one. And this assumption of independence is what most databases do by default, so we do that. But if the columns are correlated, it blows up, unfortunately. That is, it's not a very robust plan :-( But I think the really hard problem is the impact the change might have on existing systems. As I said, the problem is we have very little information to inform the decision. We could penalize this "risky" plan somehow (we don't really have any concept of "risk" separately from the cost), but inevitably that will make the query much worse for some of the systems where that plan is correct. I understand the frustration, though. (FWIW I love sqlite, it's an amazing piece of software, but claiming that it has a better optimizer based on a single query may be a bit ... premature.)

riku_iki 3y ago
So, why PG devs are resistant against introducing hints, and allowing users optimize base on their knowledge about data nature and have predictable results?..
- pgaddict 3y ago
  I don't think there's a uniform agreement to not have hints, and even for devs opposing the idea of hints is there is not a single universal reason and it's more like every single dev has his own reason(s) to not like them. For example: 1) The dev may be working on something else entirely, not caring about hints at all. I'd bet most devs are actually in this group. 2) PG does the right decisions for the use cases the dev needs/supports, so there's not much motivation to make this complex, or feeling hints are needed. Everyone scratches their own itch in the first place. 3) There's a feeling we shouldn't be fixing planning issues by hints but by fixing the optimizer. I agree it's rather idealistic and perhaps not very practical (how does it help that in 2 years there might be a fix, when you have the problem on PROD now?) or supported by history (there's a bunch of cases that we know are a problem for years, not much progress was made). 4) Often bad past experience with applications overusing hints to force plans that the "smart" developer thought are great, but then it grew to 100x the size over years, and now the plans are insane. But also the hints are part of the application code and that can't be changed and it's obviously he fault of the DBA to "make it work". If I had a $1 for every such application I had to deal with ... 5) There issues are fairly rare (depending on the application/schema/,...) are there are other ways to force the database to use a different plan. You can do CTEs, set different cost parameters, disable some plan nodes, ... Not as explicit as hints, but good enough for no one to spend enough time on hints. 6) Belief this can/should be solved out of core. The fact that pg_hint_plan exists suggests maybe it's not a bad idea. 7) Concerns about complexity - the optimizer is quite complicated piece of code already, hints would make it even more complex (both the code and testing). If the community is not ready to accept this extra complexity and maintain it forever, it won't be added. 8) There are proprietary/commercial forks that actually do support hints - for example EDB Postgres Advanced Server supports this. (disclosure: I work for EDB, although not on EPAS.) 9) I don't think there ever was a formal patch adding this capability. And without a patch it's all just a theoretical discussion. 10) ... probably many more reasons ... I'm not saying any of this is a good reason to (not) support hints, it's merely my observation of what devs say when asked about hints.
  - riku_iki 3y ago
    imho, lack of hints makes PG just dangerous to use in prod, since DB can stuck in poorly optimized query any moment, and shutdown whole site/system. It would be priority #1 if I would be product manager of this project, and all your bullet points would be naturally resolved.

riku_iki 3y ago

So, why PG devs are resistant against introducing hints, and allowing users optimize base on their knowledge about data nature and have predictable results?..

pgaddict 3y ago
I don't think there's a uniform agreement to not have hints, and even for devs opposing the idea of hints is there is not a single universal reason and it's more like every single dev has his own reason(s) to not like them. For example: 1) The dev may be working on something else entirely, not caring about hints at all. I'd bet most devs are actually in this group. 2) PG does the right decisions for the use cases the dev needs/supports, so there's not much motivation to make this complex, or feeling hints are needed. Everyone scratches their own itch in the first place. 3) There's a feeling we shouldn't be fixing planning issues by hints but by fixing the optimizer. I agree it's rather idealistic and perhaps not very practical (how does it help that in 2 years there might be a fix, when you have the problem on PROD now?) or supported by history (there's a bunch of cases that we know are a problem for years, not much progress was made). 4) Often bad past experience with applications overusing hints to force plans that the "smart" developer thought are great, but then it grew to 100x the size over years, and now the plans are insane. But also the hints are part of the application code and that can't be changed and it's obviously he fault of the DBA to "make it work". If I had a $1 for every such application I had to deal with ... 5) There issues are fairly rare (depending on the application/schema/,...) are there are other ways to force the database to use a different plan. You can do CTEs, set different cost parameters, disable some plan nodes, ... Not as explicit as hints, but good enough for no one to spend enough time on hints. 6) Belief this can/should be solved out of core. The fact that pg_hint_plan exists suggests maybe it's not a bad idea. 7) Concerns about complexity - the optimizer is quite complicated piece of code already, hints would make it even more complex (both the code and testing). If the community is not ready to accept this extra complexity and maintain it forever, it won't be added. 8) There are proprietary/commercial forks that actually do support hints - for example EDB Postgres Advanced Server supports this. (disclosure: I work for EDB, although not on EPAS.) 9) I don't think there ever was a formal patch adding this capability. And without a patch it's all just a theoretical discussion. 10) ... probably many more reasons ... I'm not saying any of this is a good reason to (not) support hints, it's merely my observation of what devs say when asked about hints.
- riku_iki 3y ago
  imho, lack of hints makes PG just dangerous to use in prod, since DB can stuck in poorly optimized query any moment, and shutdown whole site/system. It would be priority #1 if I would be product manager of this project, and all your bullet points would be naturally resolved.