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.
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.
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.
Their arguments are asinine too (from 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.
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.
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/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.
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.
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.
The saving grace is that for some common data distributions, the degraded statistical models are still pretty close to representative of the data.
However, some data distributions badly expose the fact that the statistical models were improperly constructed to fit resource limits. In these cases, the statistical model is unpredictable and erratic, and will change every time the statistics collection is run even if the data does not change.
We are not talking about an extraordinary amount of statistics data. I would expect databases to have a special structure optimized for this purpose rather than using an OLTP row. The Postgres approach to statistics collection was reasonable a couple decades ago, but modern workloads expose it more frequently now.
FWIW, I still use Postgres a fair amount because it is very solid within its limits. It is the reason I am familiar with its sharp edges.
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.
But in this case it isn't that.
I think the hints discussion is interesting, but not relevant to this particular article.