4 ms·

> Poor application code maintainability: hints in queries require massive refactoring. > Interference with upgrades: today's helpful hints become anti-performa

by luhn 4y ago

> Poor application code maintainability: hints in queries require massive refactoring. > Interference with upgrades: today's helpful hints become anti-performance after an upgrade. > Encouraging bad DBA habits slap a hint on instead of figuring out the real issue. Does not scale with data size: the hint that's right when a table is small is likely to be wrong when it gets larger. > Failure to actually improve query performance: most of the time, the optimizer is actually right. > Interfering with improving the query planner: people who use hints seldom report the query problem to the project. https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion

alyandon 4y ago
There is no optimizer that is capable of getting the correct result 100% of the time. Even Oracle with decades of costly engineering behind it requires query tuning (either via explicit hints on the client side sql or via server side plan tuning) at any reasonably large scale. I like Postgresql and use it when I can and I respect the Postgreql core dev team's adherence to principles but in this particular case I believe they are just inventing arguments to justify their unreasonable stance against allowing query hinting.
- _jal 4y ago
  So "most of the time" means "100% of the time" where you live?
  - stormbrew 4y ago
    I mean, I think this is just one of those things where it only matters rarely, but when it does it matters a lot.
    - staticassertion 4y ago
      Maybe postgres isn't the right solution in those cases. If you need tight control over how your queries are executing you may not want an rdbms.
      - kadoban 4y ago
        That's possible I guess, but at least some other relational dbs do allow that control, so it's not like it's impossible.
      - stormbrew 4y ago
        I don't think choices of things like this ever really come down to one thing like that. Not using an RDBMS might mean having to build a whole bunch of features you wouldn't otherwise have to.
jerrysievert 4y ago
> Failure to actually improve query performance: most of the time, the optimizer is actually right. my favorite response to someone complaining about a query doing a sequential scan is to tell them to `set enable_seqscan = false;` and rerun the query. usually, Postgres is right. it's rare that it is wrong, but it's nice to have a workaround for those few times that it's wrong - at least on fairly static data.
- higeorge13 4y ago
  Planner is usually correct given the information it has, the table statistics are usually off and are causing problems.
- dspillett 4y ago
  A common flaw to “just do x and rerun it” is that you are not running both in the same conditions: the data is in RAM second time around. I've seen people think that a change (an index hint, switching between a CTE and a sub-query, …) has made a massive difference because the last couple of times the query ran it timed out or took ages, when in fact the next run works and works faster not because of the change but because the previous run or two have primed the page cache with all the data that is needed, cutting out all the IO. Test from both warm and cold states. Warm because most queries you are optimising are those that run often so usually do have some or all of the required data in RAM, and cold because sometimes they will run cold in production (and some queries just touch so much data, and/or cause spooling to disk of intermediate steps, so that IO will always be involved). Ignore anyone who says always test warm because that is the most common state by far: if your query is reading far more than it really needs to when IO is involved, it will be using much more CPU than it needs to at other times and will harm concurrency potential (directly by eating CPU when other things could use it instead, or through holding more read locks than needed and holding them longer than necessary).
- iracic 4y ago
  You can't check complex cases with that.
  - 4y ago
cameronh90 4y ago
The biggest reason - which doesn't seem to be mentioned - is consistency and stability. I don't like being woken up to debug why the query planner has decided to do something new. Yes, there's always a "good reason" but does it need to communicate that by taking down production at 3am? Even if it's a better plan, I don't need my anomaly detection alerts going off because something is now running in half the time...
- hermanradtke 4y ago
  This is why I prefer to page on features working within SLA instead of lower level things like CPU usage or query time. Having a system send warnings about CPU or query time are great when I am awake. They are hints to a problem, but not always a problem themselves.
infogulch 4y ago
> people who use hints seldom report the query problem to the project This statement is shockingly user-hostile and paints the users overall as selfish and uninterested in the improvement of the postgresql project. Certainly some users will use their hint and go home, but you can't know that this would result in a net decrease in performance reports. Maybe with hints different users would be empowered to submit more reports like "The query planner creates a slow plan for query X, I know because when I use hint Z it's faster". Not implementing hints just to spite selfish users while ignoring the potential additional reports submitted by different caring users is shortsighted and antisocial. Maybe there are a bunch of good reasons why hints should not be implemented, but this is not one of them. My past commentary: https://news.ycombinator.com/item?id=29987139 https://news.ycombinator.com/item?id=29987139

by luhn 4y ago

alyandon 4y ago

There is no optimizer that is capable of getting the correct result 100% of the time. Even Oracle with decades of costly engineering behind it requires query tuning (either via explicit hints on the client side sql or via server side plan tuning) at any reasonably large scale. I like Postgresql and use it when I can and I respect the Postgreql core dev team's adherence to principles but in this particular case I believe they are just inventing arguments to justify their unreasonable stance against allowing query hinting.

_jal 4y ago
So "most of the time" means "100% of the time" where you live?
- stormbrew 4y ago
  I mean, I think this is just one of those things where it only matters rarely, but when it does it matters a lot.
  - staticassertion 4y ago
    Maybe postgres isn't the right solution in those cases. If you need tight control over how your queries are executing you may not want an rdbms.
    - kadoban 4y ago
      That's possible I guess, but at least some other relational dbs do allow that control, so it's not like it's impossible.
    - stormbrew 4y ago
      I don't think choices of things like this ever really come down to one thing like that. Not using an RDBMS might mean having to build a whole bunch of features you wouldn't otherwise have to.

jerrysievert 4y ago

> Failure to actually improve query performance: most of the time, the optimizer is actually right. my favorite response to someone complaining about a query doing a sequential scan is to tell them to `set enable_seqscan = false;` and rerun the query. usually, Postgres is right. it's rare that it is wrong, but it's nice to have a workaround for those few times that it's wrong - at least on fairly static data.

higeorge13 4y ago
Planner is usually correct given the information it has, the table statistics are usually off and are causing problems.
dspillett 4y ago
A common flaw to “just do x and rerun it” is that you are not running both in the same conditions: the data is in RAM second time around. I've seen people think that a change (an index hint, switching between a CTE and a sub-query, …) has made a massive difference because the last couple of times the query ran it timed out or took ages, when in fact the next run works and works faster not because of the change but because the previous run or two have primed the page cache with all the data that is needed, cutting out all the IO. Test from both warm and cold states. Warm because most queries you are optimising are those that run often so usually do have some or all of the required data in RAM, and cold because sometimes they will run cold in production (and some queries just touch so much data, and/or cause spooling to disk of intermediate steps, so that IO will always be involved). Ignore anyone who says always test warm because that is the most common state by far: if your query is reading far more than it really needs to when IO is involved, it will be using much more CPU than it needs to at other times and will harm concurrency potential (directly by eating CPU when other things could use it instead, or through holding more read locks than needed and holding them longer than necessary).
iracic 4y ago
You can't check complex cases with that.
- 4y ago

cameronh90 4y ago

The biggest reason - which doesn't seem to be mentioned - is consistency and stability. I don't like being woken up to debug why the query planner has decided to do something new. Yes, there's always a "good reason" but does it need to communicate that by taking down production at 3am? Even if it's a better plan, I don't need my anomaly detection alerts going off because something is now running in half the time...

hermanradtke 4y ago
This is why I prefer to page on features working within SLA instead of lower level things like CPU usage or query time. Having a system send warnings about CPU or query time are great when I am awake. They are hints to a problem, but not always a problem themselves.

infogulch 4y ago

> people who use hints seldom report the query problem to the project This statement is shockingly user-hostile and paints the users overall as selfish and uninterested in the improvement of the postgresql project. Certainly some users will use their hint and go home, but you can't know that this would result in a net decrease in performance reports. Maybe with hints different users would be empowered to submit more reports like "The query planner creates a slow plan for query X, I know because when I use hint Z it's faster". Not implementing hints just to spite selfish users while ignoring the potential additional reports submitted by different caring users is shortsighted and antisocial. Maybe there are a bunch of good reasons why hints should not be implemented, but this is not one of them. My past commentary: https://news.ycombinator.com/item?id=29987139 https://news.ycombinator.com/item?id=29987139