News for hackers. Stuff that matters.
Re:Working around a case where the Postgres planner is “not ... by orthoxerox on Monday August 02, 2021 @03:26PM (#28041459)
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.

Re:Working around a case where the Postgres planner is “not ... by fennecfoxen on Monday August 02, 2021 @04:05PM (#28041918)
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.

Re:Working around a case where the Postgres planner is “not ... by shock-value on Monday August 02, 2021 @04:25PM (#28042159)
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.

Re:Working around a case where the Postgres planner is “not ... by jandrewrogers on Monday August 02, 2021 @05:57PM (#28043214)
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.
Re:Working around a case where the Postgres planner is “not ... by rrrrrrrrrrrryan on Monday August 02, 2021 @05:17PM (#28042783)
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.

Re:Working around a case where the Postgres planner is “not ... by ollien on Monday August 02, 2021 @05:52PM (#28043161)
Not AFAIK. I know MSSQL has a way, but cursory googling suggests that Postgres does not.
Re:Working around a case where the Postgres planner is “not ... by zepearl on Monday August 02, 2021 @04:49PM (#28042438)
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.

Re:Working around a case where the Postgres planner is “not ... by jandrewrogers on Monday August 02, 2021 @05:48PM (#28043121)
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.

Re:Working around a case where the Postgres planner is “not ... by atombender on Monday August 02, 2021 @06:30PM (#28043492)
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.
Re:Working around a case where the Postgres planner is “not ... by jandrewrogers on Monday August 02, 2021 @07:06PM (#28043813)
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.

Re:Working around a case where the Postgres planner is “not ... by atombender on Tuesday August 03, 2021 @04:09AM (#28046790)
No, I understand that. I'm asking about how, specifically, Postgres' statistics architecture differs from those other databases.
Re:Working around a case where the Postgres planner is “not ... by jandrewrogers on Tuesday August 03, 2021 @11:58PM (#28057108)
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.

Re:Working around a case where the Postgres planner is “not ... by orthoxerox on Monday August 09, 2021 @02:40PM (#28120572)
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.
Re:Working around a case where the Postgres planner is “not ... by golergka on Tuesday August 03, 2021 @07:02AM (#28047782)
I've never heard about it, thanks for writing about this issue. Are there some other resources that cover this problem in more detail?
Re:Working around a case where the Postgres planner is “not ... by baq on Monday August 02, 2021 @04:14PM (#28042009)
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.
Re:Working around a case where the Postgres planner is “not ... by bmurphy1976 on Monday August 02, 2021 @03:46PM (#28041693)
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.
Re:Working around a case where the Postgres planner is “not ... by mike_hock on Monday August 02, 2021 @04:04PM (#28041906)
A hint won't make the query planner use an index column it doesn't know it can use, though.
Re:Working around a case where the Postgres planner is “not ... by lazide on Monday August 02, 2021 @04:13PM (#28041993)
It does tell the query planner it can use it though.
Re:Working around a case where the Postgres planner is “not ... by SpicyLemonZest on Monday August 02, 2021 @04:57PM (#28042528)
"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.
Re:Working around a case where the Postgres planner is “not ... by lazide on Tuesday August 03, 2021 @12:26AM (#28045707)
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.

Re:Working around a case where the Postgres planner is “not ... by mike_hock on Tuesday August 03, 2021 @01:48PM (#28052019)
Yes.

But in this case it isn't that.

Re:Working around a case where the Postgres planner is “not ... by jeff-davis on Monday August 02, 2021 @05:41PM (#28043057)
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.