7 ms·

It's basically shameful how the maintainers refuse to even entertain the thought of allowing query hints. These kinds of articles and anecdotes come up again an

It's basically shameful how the maintainers refuse to even entertain the thought of allowing query hints. These kinds of articles and anecdotes come up again and again, yet seemingly no movement whatsoever on the blanket ban on query hints in Postgres. (See https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion) Postgres generally is great -- so many great features and optimizations and more added all the time. But its query optimizer still messes up, often. It's absolutely not the engineering marvel some would have you believe.

yjftsjthsd-h 5y ago
> These kinds of articles and anecdotes come up again and again Don't articles and anecdotes also come up again and again of developers feeding bad happens to the database and cratering their performance? And that wiki page... Very explicitly isn't a blanket ban? They straight up say that they're willing to consider the idea if somebody wants to say how to do it without the pitfalls of other systems. The only thing they say they're going to ignore outright is people thinking they should have a feature because other databases have it (which seems fair).
- erichocean 5y ago
  > people thinking they should have a feature because other databases have it (which seems fair) Literally NO ONE wants query hinting in Postgres to check some kind of feature box because other databases have it. We know we want it because…other databases have it, and it's INCREDIBLY USEFUL. > They straight up say that they're willing to consider the idea if somebody wants to say how to do it without the pitfalls of other systems. Pitfalls my ass. We want exactly the functionality that is already present in other systems, pitfalls and all. That's just an excuse to do nothing, Apple-style, "because we know better than our own users" while trying to appear reasonable. It's akin to not adopting SQL until you can do so "while avoiding the pitfalls of SQL." Just utter bullshit.
  - adamrt 5y ago
    > We want exactly the functionality that is already present in other systems, pitfalls and all. That's just an excuse to do nothing Are you offering to help with the maintenance or development associated with it? Or are you just demanding features while calling the people that do help with that stuff liars? Maybe they do know better? Or maybe they know about other hassles that will come with it that you can't fathom. They've been developing and maintaining one of the best open source projects on the planet for nearly 3 decades. Maybe with all that experience, its not their opinion that is BS?
    - mst 5y ago
      Given there's already a pg_hint_plan extension I really don't see why people are so angry about the developers not wanting to bless something they consider a footgun as a core feature.
      - zepearl 5y ago
        "risk", because pg_hint is an external module, written by somebody as a hobby, therefore if it stops existing or working after some updates or with newer versions of Postgres then it becomes a huge problem for PROD systems that need it for any reason.
        mst 5y ago
        My point is that "core developers should add an unproven feature they think is a footgun and take on the support overhead of that without convincing evidence it would actually be a net win" is not a convincing argument. Also given Amazon AWS support it for Aurora, I feel like it's not -that- hobby-ish. If people use it and can provide evidence that it helps more than it hurts, that might be convincing. Insulting the core developers for not yet being convinced seems rather less likely to help.
        zepearl 5y ago
        > Also given Amazon AWS support it for Aurora, I feel like it's not -that- hobby-ish. Oh, didn't know that :) About the rest: sure, I can agree as well about the indirect adverse effect of having them available (e.g. easy to misuse them as I saw in some apps using Oracle DBs), and it's for sure wrong to insult somebody because of this. Still, personally, I think that the pros would outweight the cons of having that embedded in the app. On one hand I remember some nights spent in the past trying to make some SQL work, hints were always at least a good temporary workaround. On the other hand there will always be some SQL which confuse the optimizer (or more special cases about a lot of data changing distribution of values, etc..) and hints would be the only way to cover these cases. Maybe an interesting question is on which level should hints act? I know mainly only Oracle & MariaDB, therefore I know hints of the type "use that index"/"query tables in this order"/"join these tables with this join type"/etc..., which are probably low-level hints. Maybe already just higher-level hints of the type "most important selectivity criteria comes from inline-view X"/"I want just the first row of the result"/"take into account only plans which select data by using indexes"/etc... would be as well interesting, not sure, just dumping here my thoughts.
- fragmede 5y ago
  We're software engineers not marketing folk who just want to check a box. There's a difference between "have a feature just because other databases have it" and "this is a very useful feature that would have its own PostgreSQL-isms and also we got this idea because other databases have it". The query planner isn't infallible, so being able to hint queries to not accidentally use a temporary table that just can't fit in ram isn't just copying a feature "just because everyone else has it".
- ghusbands 5y ago
  > they're willing to consider the idea if somebody wants to say how to do it without the pitfalls of other systems That is basically a blanket ban. Saying you won't implement a widely-implemented feature unless someone comes up with a whole new theory about how to do it better is saying you simply won't implement it. Other databases do well enough with hints, and they do help with some problems.
pmontra 5y ago
I can understand that people wants to get absolute control on their queries sometimes. I never used hints so I shouldn't even write this note but IMHO that page has a quite balanced view of pros and cons of existing hint systems.
- williamdclt 5y ago
  It does. But when you're watching your database, and therefore your business, crash and burn because it decided to change the query plan for an important query, all these "problems with existing Hint systems" sound very irrelevant. I'd love to have a way to lock a plan in a temporary "emergency measure" fashion. But of course it's hard/impossible to design this without letting people abuse it and pretend it's just a hint system.
  - silon42 5y ago
    Exactly... DB deciding to switch query plans in production is sometimes terrible.
  - suddendescent 5y ago
    >I'd love to have a way to lock a plan in a temporary "emergency measure" fashion. Could this be achieved by extending prepared statements? [1] The dirty option would be to introduce a new keyword like PREPAREFIXED. The first time such a statement is executed, the execution plan could be stored and then retrieved on subsequent queries. There would be no hints and the changes in code should be minimal. Once a query runs successfully, the users can be sure that the execution plan won't change. >But of course it's hard/impossible to design this without letting people abuse it Is this more important than having predictable execution times? [1] https://www.postgresql.org/docs/current/sql-prepare.html https://www.postgresql.org/docs/current/sql-prepare.html
    - williamdclt 5y ago
      > Is this more important than having predictable execution times? To me: no. To the Postgres team: apparently :)
zorgmonkey 5y ago
I've never used it but their is a postgres extension called pg_hint_plan [0] for query hints and I am guessing it is pretty decent because it is even mentioned in the AWS Aurora documentation [0]: https://pghintplan.osdn.jp/pg_hint_plan.html https://pghintplan.osdn.jp/pg_hint_plan.html
zmmmmm 5y ago
Definitely my biggest issue with Postgres. I have one shameful query where, unable to convince it to execute a subquery that is essentially a constant for most of the rows outside of the hot loop of the main index scan, I pulled it out into a temp table and had the main query select from the temp table instead. Even creating a temp table and indexing it was faster than the plan the Postgres query planner absolutely insisted on. Things like CTEs etc made no difference, it would still come up with the same dumb plan every way I expressed the query. The worst thing is not even being able to debug or understand what is going on because you can't influence the query plan to try alternative hypotheses out easily.
fdr 5y ago
Personally, I think they would entertain it, but the implementation has to be fairly good and they have to be up for maintaining whatever that implementation is. There was a lot of hemming and hawing about hot standby & replication for years, but once someone showed up with a credible implementation and track record of maintaining and fixing it, the objections seemed to get a lot quieter. Hints are a somewhat invasive feature that are hard to tweak once they are integrated into applications. I don't think the half dozen people that are in the best position to consider its evolution have found it the use of their time they wish to expend.
potamic 5y ago
Shameful? A bit much for what is essentially a gift to the community? They are building a free product and are entitled to their opinion on how to go about building it, and decisions on taking up scope is very much an integral part of it. In engineering, every decision is a tradeoff that might benefit one set of users while impacting another. Free software is all about choice so that different users can find that thing that is most suitable to them.

yjftsjthsd-h 5y ago

> These kinds of articles and anecdotes come up again and again Don't articles and anecdotes also come up again and again of developers feeding bad happens to the database and cratering their performance? And that wiki page... Very explicitly isn't a blanket ban? They straight up say that they're willing to consider the idea if somebody wants to say how to do it without the pitfalls of other systems. The only thing they say they're going to ignore outright is people thinking they should have a feature because other databases have it (which seems fair).

erichocean 5y ago
> people thinking they should have a feature because other databases have it (which seems fair) Literally NO ONE wants query hinting in Postgres to check some kind of feature box because other databases have it. We know we want it because…other databases have it, and it's INCREDIBLY USEFUL. > They straight up say that they're willing to consider the idea if somebody wants to say how to do it without the pitfalls of other systems. Pitfalls my ass. We want exactly the functionality that is already present in other systems, pitfalls and all. That's just an excuse to do nothing, Apple-style, "because we know better than our own users" while trying to appear reasonable. It's akin to not adopting SQL until you can do so "while avoiding the pitfalls of SQL." Just utter bullshit.
- adamrt 5y ago
  > We want exactly the functionality that is already present in other systems, pitfalls and all. That's just an excuse to do nothing Are you offering to help with the maintenance or development associated with it? Or are you just demanding features while calling the people that do help with that stuff liars? Maybe they do know better? Or maybe they know about other hassles that will come with it that you can't fathom. They've been developing and maintaining one of the best open source projects on the planet for nearly 3 decades. Maybe with all that experience, its not their opinion that is BS?
  - mst 5y ago
    Given there's already a pg_hint_plan extension I really don't see why people are so angry about the developers not wanting to bless something they consider a footgun as a core feature.
    - zepearl 5y ago
      "risk", because pg_hint is an external module, written by somebody as a hobby, therefore if it stops existing or working after some updates or with newer versions of Postgres then it becomes a huge problem for PROD systems that need it for any reason.
      - mst 5y ago
        My point is that "core developers should add an unproven feature they think is a footgun and take on the support overhead of that without convincing evidence it would actually be a net win" is not a convincing argument. Also given Amazon AWS support it for Aurora, I feel like it's not -that- hobby-ish. If people use it and can provide evidence that it helps more than it hurts, that might be convincing. Insulting the core developers for not yet being convinced seems rather less likely to help.
        zepearl 5y ago
        > Also given Amazon AWS support it for Aurora, I feel like it's not -that- hobby-ish. Oh, didn't know that :) About the rest: sure, I can agree as well about the indirect adverse effect of having them available (e.g. easy to misuse them as I saw in some apps using Oracle DBs), and it's for sure wrong to insult somebody because of this. Still, personally, I think that the pros would outweight the cons of having that embedded in the app. On one hand I remember some nights spent in the past trying to make some SQL work, hints were always at least a good temporary workaround. On the other hand there will always be some SQL which confuse the optimizer (or more special cases about a lot of data changing distribution of values, etc..) and hints would be the only way to cover these cases. Maybe an interesting question is on which level should hints act? I know mainly only Oracle & MariaDB, therefore I know hints of the type "use that index"/"query tables in this order"/"join these tables with this join type"/etc..., which are probably low-level hints. Maybe already just higher-level hints of the type "most important selectivity criteria comes from inline-view X"/"I want just the first row of the result"/"take into account only plans which select data by using indexes"/etc... would be as well interesting, not sure, just dumping here my thoughts.
fragmede 5y ago
We're software engineers not marketing folk who just want to check a box. There's a difference between "have a feature just because other databases have it" and "this is a very useful feature that would have its own PostgreSQL-isms and also we got this idea because other databases have it". The query planner isn't infallible, so being able to hint queries to not accidentally use a temporary table that just can't fit in ram isn't just copying a feature "just because everyone else has it".
ghusbands 5y ago
> they're willing to consider the idea if somebody wants to say how to do it without the pitfalls of other systems That is basically a blanket ban. Saying you won't implement a widely-implemented feature unless someone comes up with a whole new theory about how to do it better is saying you simply won't implement it. Other databases do well enough with hints, and they do help with some problems.

pmontra 5y ago

I can understand that people wants to get absolute control on their queries sometimes. I never used hints so I shouldn't even write this note but IMHO that page has a quite balanced view of pros and cons of existing hint systems.

williamdclt 5y ago
It does. But when you're watching your database, and therefore your business, crash and burn because it decided to change the query plan for an important query, all these "problems with existing Hint systems" sound very irrelevant. I'd love to have a way to lock a plan in a temporary "emergency measure" fashion. But of course it's hard/impossible to design this without letting people abuse it and pretend it's just a hint system.
- silon42 5y ago
  Exactly... DB deciding to switch query plans in production is sometimes terrible.
- suddendescent 5y ago
  >I'd love to have a way to lock a plan in a temporary "emergency measure" fashion. Could this be achieved by extending prepared statements? [1] The dirty option would be to introduce a new keyword like PREPAREFIXED. The first time such a statement is executed, the execution plan could be stored and then retrieved on subsequent queries. There would be no hints and the changes in code should be minimal. Once a query runs successfully, the users can be sure that the execution plan won't change. >But of course it's hard/impossible to design this without letting people abuse it Is this more important than having predictable execution times? [1] https://www.postgresql.org/docs/current/sql-prepare.html https://www.postgresql.org/docs/current/sql-prepare.html
  - williamdclt 5y ago
    > Is this more important than having predictable execution times? To me: no. To the Postgres team: apparently :)

zorgmonkey 5y ago

I've never used it but their is a postgres extension called pg_hint_plan [0] for query hints and I am guessing it is pretty decent because it is even mentioned in the AWS Aurora documentation [0]: https://pghintplan.osdn.jp/pg_hint_plan.html https://pghintplan.osdn.jp/pg_hint_plan.html

zmmmmm 5y ago

Definitely my biggest issue with Postgres. I have one shameful query where, unable to convince it to execute a subquery that is essentially a constant for most of the rows outside of the hot loop of the main index scan, I pulled it out into a temp table and had the main query select from the temp table instead. Even creating a temp table and indexing it was faster than the plan the Postgres query planner absolutely insisted on. Things like CTEs etc made no difference, it would still come up with the same dumb plan every way I expressed the query. The worst thing is not even being able to debug or understand what is going on because you can't influence the query plan to try alternative hypotheses out easily.

fdr 5y ago

Personally, I think they would entertain it, but the implementation has to be fairly good and they have to be up for maintaining whatever that implementation is. There was a lot of hemming and hawing about hot standby & replication for years, but once someone showed up with a credible implementation and track record of maintaining and fixing it, the objections seemed to get a lot quieter. Hints are a somewhat invasive feature that are hard to tweak once they are integrated into applications. I don't think the half dozen people that are in the best position to consider its evolution have found it the use of their time they wish to expend.

potamic 5y ago

Shameful? A bit much for what is essentially a gift to the community? They are building a free product and are entitled to their opinion on how to go about building it, and decisions on taking up scope is very much an integral part of it. In engineering, every decision is a tradeoff that might benefit one set of users while impacting another. Free software is all about choice so that different users can find that thing that is most suitable to them.