Comment by fennecfoxen | Hacker News Reader

Comment by fennecfoxen | Hacker News Reader

Parent Full thread

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.

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):

* 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.

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.

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.

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/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.

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.

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.

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.

No, I understand that. I'm asking about how, specifically, Postgres' statistics architecture differs from those other databases.

The statistical models in Postgres are stored as transactional rows in an ordinary table, which puts some pretty hard limits on what you can put into those models because Postgres doesn’t like large rows. There is no theoretical reason you’d want to design things this way, it is just pretty expedient because you don’t have to implement a separate statistics store and you can leverage all of the code for normal tables. IIRC, the fail safe code path puts a hard limit on the amount of statistical data that can be collected no matter how much data you should collect, so that it plays nicely with the Postgres storage model. This threshold is uncomfortably low for proper statistics collection purposes when your database becomes large.

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.

Not really. I had Oracle refuse to use the index written exactly for the query in question (select id from contracts where auth_status = 'U', with index on auth_status) because the planner decided that the index was useless (the statistics were gathered at night when the table contained no unauthorized records). But in Oracle you can use hints to force the planner to use the index, while in Postgres you can't.

I've never heard about it, thanks for writing about this issue. Are there some other resources that cover this problem in more detail?

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.

Reply on news.ycombinator.com