6 ms·

And yet Postgres still refuses to add hints. I'd rather deal with a nonexistent query optimizer, like the one in Clickhouse, than with an insufficiently smart

And yet Postgres still refuses to add hints. I'd rather deal with a nonexistent query optimizer, like the one in Clickhouse, than with an insufficiently smart one that I can't control.

bmurphy1976 5y ago
I haven't done much database work in recent years, but this was always one of my frustrations. I'd take predictability over max performance any day.
mike_hock 5y ago
A hint won't make the query planner use an index column it doesn't know it can use, though.
- lazide 5y ago
  It does tell the query planner it can use it though.
  - deleted 5y ago
    [deleted]
  - SpicyLemonZest 5y ago
    "Doesn't know it can use" in this context means the system probably doesn't know how to use it, even if a hint suggests it should.
    - lazide 5y ago
      Many times it's not that, or specifically that type of behavior doesn't cause the weird 'something randomly blows up' type problems. It's that it is using index size vs on disk size or something for specific query values and deciding to do something kind of dumb, like a full range scan, because it isn't taking into account the disk data is on... well disk, and the index is already loaded into memory because of other queries. Semi-locking it to known good indexes or whatever, or at least overriding it's attempt to be smart, helps usually in cases like this as it cuts out the long tail behavior and makes the whole system more predictable.
      - mike_hock 5y ago
        Yes. But in this case it isn't that.
- jeff-davis 5y ago
  Right, hints can only tell the database to use features it already has, they don't add new features on the fly. I think the hints discussion is interesting, but not relevant to this particular article.
fennecfoxen 5y ago
Just about when I was leaving [financial-service company] our team was considering that the Postgres query planner was a significant source of risk, and contemplating mitigations and alternatives. We'd optimize things so that on our busy day each month, query A would take 2 minutes, and query-set B would take 30 or so in total, and we'd have nice graphs to track trends over time. But every so often, the query planner would change its mind about how to do some part of the operation. You'd be looking at query A suddenly taking 2+ hours, while query-set B hadn't even started yet. In the worst cases, someone would have to get on the phone with our banking partner, and ask for an extended deadline tonight. Business-critical? The job in question was the business, literally the operation customers were paying for (in combination with a quick template-fill and SFTP, anyway). It was particularly hard to nail down because the plans would depend on the specific customers in question, and the transactions they were doing that day.
- baq 5y ago
  we're running 'ANALYZE table' with default_statistics_target set in the connection to the max reasonable value for the dataset size every 4 hours to avoid just that. also, the default checklist item for sudden slowness of postgres is a manual ANALYZE with a bumped statistics target.
- shock-value 5y ago
  It's so odd because Postgres generally does everything else so well and is generally very customizable and modular otherwise. Yet the maintainers totally scoff at the idea of query hints even in the face of so many examples of planning failures. Their arguments are asinine too (from https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion): * 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. Literally all of these are expressions of condescension and distrust of their own users. Anyone who would use hinting would be using it to solve a real issue caused by the shortcomings of the planner -- that much should be self-evident.

And yet Postgres still refuses to add hints. I'd rather deal with a nonexistent query optimizer, like the one in Clickhouse, than with an insufficiently smart one that I can't control.

bmurphy1976 5y ago

I haven't done much database work in recent years, but this was always one of my frustrations. I'd take predictability over max performance any day.

mike_hock 5y ago

A hint won't make the query planner use an index column it doesn't know it can use, though.

lazide 5y ago
It does tell the query planner it can use it though.
- deleted 5y ago
  [deleted]
- SpicyLemonZest 5y ago
  "Doesn't know it can use" in this context means the system probably doesn't know how to use it, even if a hint suggests it should.
  - lazide 5y ago
    Many times it's not that, or specifically that type of behavior doesn't cause the weird 'something randomly blows up' type problems. It's that it is using index size vs on disk size or something for specific query values and deciding to do something kind of dumb, like a full range scan, because it isn't taking into account the disk data is on... well disk, and the index is already loaded into memory because of other queries. Semi-locking it to known good indexes or whatever, or at least overriding it's attempt to be smart, helps usually in cases like this as it cuts out the long tail behavior and makes the whole system more predictable.
    - mike_hock 5y ago
      Yes. But in this case it isn't that.
jeff-davis 5y ago
Right, hints can only tell the database to use features it already has, they don't add new features on the fly. I think the hints discussion is interesting, but not relevant to this particular article.

fennecfoxen 5y ago

Just about when I was leaving [financial-service company] our team was considering that the Postgres query planner was a significant source of risk, and contemplating mitigations and alternatives. We'd optimize things so that on our busy day each month, query A would take 2 minutes, and query-set B would take 30 or so in total, and we'd have nice graphs to track trends over time. But every so often, the query planner would change its mind about how to do some part of the operation. You'd be looking at query A suddenly taking 2+ hours, while query-set B hadn't even started yet. In the worst cases, someone would have to get on the phone with our banking partner, and ask for an extended deadline tonight. Business-critical? The job in question was the business, literally the operation customers were paying for (in combination with a quick template-fill and SFTP, anyway). It was particularly hard to nail down because the plans would depend on the specific customers in question, and the transactions they were doing that day.

baq 5y ago
we're running 'ANALYZE table' with default_statistics_target set in the connection to the max reasonable value for the dataset size every 4 hours to avoid just that. also, the default checklist item for sudden slowness of postgres is a manual ANALYZE with a bumped statistics target.
shock-value 5y ago
It's so odd because Postgres generally does everything else so well and is generally very customizable and modular otherwise. Yet the maintainers totally scoff at the idea of query hints even in the face of so many examples of planning failures. Their arguments are asinine too (from https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion): * 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. Literally all of these are expressions of condescension and distrust of their own users. Anyone who would use hinting would be using it to solve a real issue caused by the shortcomings of the planner -- that much should be self-evident.