Looking Forward to Postgres 19: Query Hints
pgedge.com
pgedge.com
Also, I'll add my perspective: I think "EXPLAIN (PLAN_ADVICE)" is a key piece to making this a plan stability feature, not (just) a hinting feature. The extensibility/framework pg_plan_advice adds is a foundation, that over time will over time address the age-old "Postgres doesn't have hints" problem, even if the initial release doesn't check all the boxes yet, e.g. no way to use advice for adjusting row/join estimates.
To give an example on extensibility: Some people that I've spoken to are asking "but why is it not a comment-style hint". There are reasons why Postgres didn't go that way for this release (comment parsing in core is non-existent today, and comments don't work correctly e.g. for functions), but its easy to write an extension that sets up an advisor hook to parse comments: https://github.com/pganalyze/pg_advice_comment
Some quick stories about databases in general and type hints in particular:
1. Some of the joins involved 10+ tables AND had query hints
That sounds cool until you realize that the queries were designed for the planner from 3 versions ago
2. Was in multiple conversations with head of the DBA team and application owners that went something like this:
Developer: "This query hasn't changed in YEARS! Why is it not performing well now??"
Me: "Have you considered that maybe the data cardinality has changed?" and then I or the lead DBA would explain how database indices worked.
I mention the above only as a warning to folks newer to DBA land that some of these features can seem great but can also be a crutch. What should have happened is that the schema, indices or application code should have been reviewed by the app teams (with the DBAs) periodically to ensure assumptions were correct.
(This was several years ago so I'm curious what kind of impact the LLMs have had on this)
I've only tried Claude Opus but it does a pretty good job interpreting the plan. It's also really powerful being able to bring in telemetry context and code to help make a more balanced tradeoff with usage metrics (much easier to callout anomolies, edge cases, and non performance sensitive contexts)
That said fixing the queries which usually originate from Django ORM is a bit hit or miss. Been multiple times the LLM wants to rewrite to something similar but not identical or wants to make large, structural changes instead of focusing on low hanging surgical improvements
Once it was even able to pull PG mailing lists to figure out I was hitting a planner predicate comparison limitation where switching from "bool = false" to "not bool" or maybe the other way around led to a massive 100x+ improvement (the planner was skipping using an index because the predicate in the query didn't "match" the predicate in the partial index despite the conditions being logically equivalent)
Would have taken days or weeks for me to figure that out on my own
It's not perfect and still requires thought and understanding (it'll recommend removing unused indexes on the primary keys, for example), but SOMETHING needs to continuously monitor anything but the smallest, simplest relational DBs.
So far I've had the best luck with pg_stat_statements + auto explain + Claude Code to go digging through those.
I’m a SWE and at my previous job it fell to me to optimize some queries that used EF Core ORM. I gave opus a local db with a small export of anonymized prod data, had it generate a billion rows with similar cardinality.
Then I told opus how to get the raw SQL and told it to write unit tests with various optimizations.
It got the query down from 5+ minutes to a few seconds. I verified the final SQL and ORM structure.
All it needed was to modify some indices and fix up the ORM to properly generate the lateral join.
However, to get the join working properly required a full restructure of the ORM query.
I have and have come to the conclusion it's too risky. On too many attempts it hallucinates correctness and subtly breaks edge cases
>large structural changes are sometimes needed for small query text changes due to how ORMs generate the SQL
Sometimes that's true but a lot of times you can get 90% the benefit with significantly lower risk with a much smaller targeted changes.
It can check correctness against real data but I've still hit problems where you can just look at the rewrite and identify behavior differences that don't necessarily show up in 1 off execution comparison unless you brute force your way through tons of test data.
A lot of the cases I'm running into are somewhat older code (5+ years) with poor test coverage that few people understand or remember. Some of these probably have legitimate bugs but it's risky assuming and safer to ensure an optimization is identical.
enable_nestloop = off here.
For us, joining many complex views quickly trips the planner up, so I'm really glad to see this.
> They break on upgrades.
The irony is so does the planner. I've seen queries working perfectly fine in older PG's suddenly run away in newer versions. So hints will actually bring stability.
Every time Claude tries to tell me to try these, it made it worse.
Not once has it made it better for me. I’m doing materialized view refreshes with a billion rows, which is small enough maybe that this doesn’t come into play…but so far the planner knows best.
If the database can’t make it fast with just smart joins and filtering then it’s the architecture that’s a problem, not the database.
Usually the only thing I need to do is increase work_mem.
If hints are what they say they are, they cannot guarantee anything.
And they indeed are hints. FTA: “The documentation is explicit: advice "can only produce plans the core planner considers viable." Advice only nudges the planner toward one it already considered.”
Most devs have problems writing decent queries (in some situations), now you want to introduce writing the query execution plan into the mix ?
The worst thing that could possibly happen is that you give it bad advice leading to slow queries, and then the obvious first step to fixing that is to drop the manual advice and see whether the automatic planner handles it better.
It baffles me that PostgreSQL, which is so deeply customisable in almost every other way, resisted this form of customisability for so long. This is great news.
Our productivity is proportional to our ability to recruit abstractions. The more we deal with pure concepts like relational algebra and data-driven development, the more bang we get for our buck.
If we get lost in the weeds having to worry about doing the planner's job, it's like we're paying a tax that doesn't need to exist.
This is why the syntactic sugar of Ruby, the async design patterns of JavaScript, the footgun avoidance best practices of C++, even the impure workarounds of functional languages, (all meant to improve developer and/or execution performance) don't really do it for me. They hint at avoidance of deeper understanding. Once we learn higher abstractions like copy-on-write, compare-and-swap, higher-order methods, etc, we start to see that languages pass the cost of their externalities on to us.
I'd prefer that Postgres move the opposite direction. For example, databases need a universal index that turns as many operations as possible into O(1) at the cost of memory, since resource prices tend to always fall on a long enough timescale. Stuff that works more like a content-addressable memory for ludicrous scaling. In other words, whatever it takes to make planner hints obsolete, is what Postgres maintainers should be putting their efforts into.
I guess a stopgap might be to have an automated way to profile an app during testing and generate planner hints for the main use cases. Or maybe be able to cache them to avoid cold start latency. But if my work ever requires me to deal with them directly, I'll be treating that as a code smell.
-
After writing this out, I realized that performance is an orthogonal concern to conceptual correctness. So a more appropriate phrasing might be that the planner is none of SQL's business. So technically, anyone's opinion on it is valid. In which case, we should choose the path of kindness. If allowing access to the planner saves someone's bacon, than we should allow it. But work to alleviate whatever pain necessitated its use in the first place.
FINALLY!
I like this design.
And yes, the community resisted this for way too long.
I never understood the issues PG had with hints. Running a non-trivial DB with a non-trivial schema and scale is, well, non-trivial at all. At some point the DB stops being a black box and starts being a tool that you have to know inside and out to avoid performance issues.
>The optimizer is usually smarter than you think.
Except for when it isn't, and moves heavy calculation inside a nested loop inside a nested loop to avoid an index scan. Nothing is perfect.
> Except for when it isn't, and moves heavy calculation inside a nested loop inside a nested loop to avoid an index scan. Nothing is perfect.
Yeah that's also been my experience. It's true that Postgres is usually smarter than I think, when I try to figure out why it's not using a better query plan I eventually find out that it wouldn't be better at all. But from time to time, it genuinely is taking a bad decision and having no power over that at all is a problem
Haven't seen pg_plan_advice before, TIL!
did he finally come around?
I do know that one of the important criteria for getting this in was that a bad advice can't cause the planner to fail, and that's something that was explicitly included in the design of pg_plan_advice.
This is the main thing the planner doesn't handle well. Postgres was built before SAAS was as big. You have different distributions per customer, and thousands of customers. In most cases the query planner will guess right, but sometimes it will fail and scan millions of rows.
https://www.postgresql.org/docs/current/planner-stats.html#P...
Principles driven development (we will never or always do X regardless of context) typically comes off as a petty ego trip. The point of the technology is to serve some kind of downstream business. Most people who download Postgres are seeking to solve a real world problem, not to demonstrate their ideological purity.
> The Postgres community's position on query hints has been, shall we say, firm. The official wiki page on the subject states it plainly: "We are not interested in implementing hints in the exact ways they are commonly implemented on other databases. Proposals based on "because they've got them" will not be welcomed."
Are we reading the same article?