5 ms·

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 contemp

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.
- rrrrrrrrrrrryan 5y ago
  Is it possible to at least lock-in a query plan with Postgres? I understand the philosophy behind wanting to keep the queries themselves as declarative as possible, but there should be some way to prevent a really important query from randomly going rogue and using a stupid index, just because something completely unrelated changed somewhere else in the database.
- jandrewrogers 5y ago
  Ironically, the underlying cause of the query planner failures is a defect in the statistics collection architecture -- it doesn't scale to large databases in some cases. An argument of "does not scale with data size" applies to the query planner.
zepearl 5y ago
I agree - it's probably not nice/elegant/whatever to use hints, but my opinion is that a query planner cannot probably cover 100% of all possible combinations of layouts/queries/data => not having the possibility to use hints is for me just a huge risk. Additionally in PROD environments you have the "time"-factor about fixing something; often you cannot afford a week of deep analysis trying to understand why the planner does not work as expected respectively what changed about the data that made it think in a different way etc... . Having said this, I just installed PostgreSQL last week, hehe. I did it only after somebody on HN mentioned the extension "pg_hint_plan" ( https://pghintplan.osdn.jp/pg_hint_plan.html https://pghintplan.osdn.jp/pg_hint_plan.html + https://pghintplan.osdn.jp/hint_list.html https://pghintplan.osdn.jp/hint_list.html ) => downloaded & built & installed it on Debian 10 for PostgreSQL 13 => it seems to work (but so far I did only few initial tests). But it's still a bit a risk as it's an external dependency.
jandrewrogers 5y ago
Yes, the Postgres query planner can break in ways that are operationally catastrophic in large systems. Many companies have experienced this. The root cause is that the statistics collection architecture of Postgres that feeds the query optimizer has some deep design flaws. Under some conditions it will produce statistical models of the data that are badly skewed in random ways, which causes the query optimizer which uses those statistics to make erratic and incorrect decisions. Fixing the statistics collection would be a massive undertaking, the issue is architectural in nature. Adding hints would provide a reasonable workaround and is probably easier in terms of development complexity than fixing the statistics collection. It is possible to reduce the probability of these bugs by reorganizing the data. The idea of changing your data model to work around a database bug is pretty horrific but companies do it.
- atombender 5y ago
  Presumably some commercial databases like MS SQL Server and Oracle are better at this? I'd love to know how, and in what way the Postgres architecture is inferior.
  - jandrewrogers 5y ago
    This issue is specific to Postgres. Like I stated, it is design flaw in the statistics collection architecture. I would be surprised if many other databases have a similar defect. Most experienced database engine designers would immediately identify the approach as defective; there is even a comment in the Postgres source code that suggests it. All old database kernels have architectural limitations that emerge because there is no realistic way to anticipate distant future workloads and hardware, and it is nearly impossible to materially modify the architecture in practice.
    - atombender 5y ago
      No, I understand that. I'm asking about how, specifically, Postgres' statistics architecture differs from those other databases.

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.

rrrrrrrrrrrryan 5y ago
Is it possible to at least lock-in a query plan with Postgres? I understand the philosophy behind wanting to keep the queries themselves as declarative as possible, but there should be some way to prevent a really important query from randomly going rogue and using a stupid index, just because something completely unrelated changed somewhere else in the database.
jandrewrogers 5y ago
Ironically, the underlying cause of the query planner failures is a defect in the statistics collection architecture -- it doesn't scale to large databases in some cases. An argument of "does not scale with data size" applies to the query planner.

zepearl 5y ago

I agree - it's probably not nice/elegant/whatever to use hints, but my opinion is that a query planner cannot probably cover 100% of all possible combinations of layouts/queries/data => not having the possibility to use hints is for me just a huge risk. Additionally in PROD environments you have the "time"-factor about fixing something; often you cannot afford a week of deep analysis trying to understand why the planner does not work as expected respectively what changed about the data that made it think in a different way etc... . Having said this, I just installed PostgreSQL last week, hehe. I did it only after somebody on HN mentioned the extension "pg_hint_plan" ( https://pghintplan.osdn.jp/pg_hint_plan.html https://pghintplan.osdn.jp/pg_hint_plan.html + https://pghintplan.osdn.jp/hint_list.html https://pghintplan.osdn.jp/hint_list.html ) => downloaded & built & installed it on Debian 10 for PostgreSQL 13 => it seems to work (but so far I did only few initial tests). But it's still a bit a risk as it's an external dependency.

jandrewrogers 5y ago

Yes, the Postgres query planner can break in ways that are operationally catastrophic in large systems. Many companies have experienced this. The root cause is that the statistics collection architecture of Postgres that feeds the query optimizer has some deep design flaws. Under some conditions it will produce statistical models of the data that are badly skewed in random ways, which causes the query optimizer which uses those statistics to make erratic and incorrect decisions. Fixing the statistics collection would be a massive undertaking, the issue is architectural in nature. Adding hints would provide a reasonable workaround and is probably easier in terms of development complexity than fixing the statistics collection. It is possible to reduce the probability of these bugs by reorganizing the data. The idea of changing your data model to work around a database bug is pretty horrific but companies do it.

atombender 5y ago
Presumably some commercial databases like MS SQL Server and Oracle are better at this? I'd love to know how, and in what way the Postgres architecture is inferior.
- jandrewrogers 5y ago
  This issue is specific to Postgres. Like I stated, it is design flaw in the statistics collection architecture. I would be surprised if many other databases have a similar defect. Most experienced database engine designers would immediately identify the approach as defective; there is even a comment in the Postgres source code that suggests it. All old database kernels have architectural limitations that emerge because there is no realistic way to anticipate distant future workloads and hardware, and it is nearly impossible to materially modify the architecture in practice.
  - atombender 5y ago
    No, I understand that. I'm asking about how, specifically, Postgres' statistics architecture differs from those other databases.