Also having the optimizer decide to do something bad off hours is not a good situation again hints or plan locking would help here.
There are times when I already know how I want the query to execute, and I have to iteratively fiddle with the query to indirectly trick the planner into doing that. There's just no excuse for that arrangement. Even if I've made a mistake and my plan couldn't work efficiently – at least let me execute it inefficiently so I can discover and debug that.
Having used Linq a lot I would actually prefer more of that kind of chained statement approach that is more hands on without having to explicitly loop.
I've heart the rebuttal to this idea before, that even minor DB schema/configuration changes that should be "invisible" to the application (e.g. an index being created — or even refreshed — on the table) would result in the resulting bytecode being different; and so you'd never be able to ship such bytecode as a static artifact in your client.
But: who said anything about a static artifact? I'm more imagining a workflow like this:
1. user submits a query with leading command "PLAN" (e.g. "PLAN SELECT ...")
2. Postgres returns planned bytecode (as a regular single-column bytea rowset)
3. if desired, the user modifies the received bytecode arbitrarily (leaving alone anything the client doesn't understand — much like how optimization passes leave alone generated intrinsics in LLVM IR)
4. user later submits a low-level query-execution command over the binary wire protocol, supplying the (potentially modified) bytecode
5. if the bytecode's assumptions are still valid, the query executes. Otherwise, the query gives a "must re-plan" error. The client is expected to hold onto the original SQL and re-supply it in this case.
In other words, do it similarly to how Redis's EVAL + EVALSHA works; but instead of opaque handles (SHA hashes), the client receives a white-box internal representation.
Or: do it similarly to how compiling a shader on a game-console with unikernel games + unified RAM/VRAM works. The driver's shader compiler spits you back a buffer full of some GPU object code — which, running in kernel-mode, you're free to modify before running.
You -can- use a server side prepared statement to force it to -ahem- plan ahead but that's -usually- not actually worth it.
And using prepared statements only works on the same connection so of limited use.
Luckily PostgreSQL's optimizer is very primitive and so planning doesn't take too much time, as it gets more advanced the lack of plan reuse will become more of an issue. Its already an issue with the LLVM JIT compilation time.
The server is supposed to take the logical intent specified by the queries and do the work to mapping that into concrete retrievals in a stable and performant way, so a query explicitly telling the server to do something in a specific way breaks the model. The more specific, the more broken.
DDL changes for instance ideally would not have to tell the server how to structure things on disk. The server should know how to do it efficiently.
That said, real life intrudes and sometimes (but not usually) it’s important to do this for stability or performance reasons. The more magic involved, the more unpredictable a system can be, and the closer a system runs to redline, the more chaos/downtime that can cause.
And now you've invented Firebird, or even 1980's Interbase. ;)
We also write queries knowing it will use a specific index, or we will create an index because of a specific query. And then we have to have a scheduled task to periodically recalculate statistics just so the DB server doesn't get silly ideas.
Of course it could be abused, but I'm in favor of having ways of letting programmers tell computers exactly what to do. Sometimes we really do know best.
> Also having the optimizer decide to do something bad off hours is not a good situation again hints or plan locking would help here.
Can also happen when you force a particular query plan, only for it to turn to treacle when some assumption you made about the data suddenly breaks.
As with most things there's no free lunch.
The system randomly deciding to drive itself off a cliff for no reason, with no known way to stop it next time is quite concerning.
But if the query planner decides to screw up your query plan it happens instantaneously and there's no possibility of rollback - only emergency deploying code fixes to try to tweak the query. In most OLTP use cases, you almost always know exactly how a query is supposed to execute, so always using index hints is totally reasonable to prevent bad query plans.
Basically, you breaking your own query with bad hints usually breaks things a lot less and at better times than the query planner doing it, and is usually easier to fix too.
Postgres generally is great -- so many great features and optimizations and more added all the time. But its query optimizer still messes up, often. It's absolutely not the engineering marvel some would have you believe.
Don't articles and anecdotes also come up again and again of developers feeding bad happens to the database and cratering their performance?
And that wiki page... Very explicitly isn't a blanket ban? They straight up say that they're willing to consider the idea if somebody wants to say how to do it without the pitfalls of other systems. The only thing they say they're going to ignore outright is people thinking they should have a feature because other databases have it (which seems fair).
Literally NO ONE wants query hinting in Postgres to check some kind of feature box because other databases have it.
We know we want it because…other databases have it, and it's INCREDIBLY USEFUL.
> They straight up say that they're willing to consider the idea if somebody wants to say how to do it without the pitfalls of other systems.
Pitfalls my ass. We want exactly the functionality that is already present in other systems, pitfalls and all. That's just an excuse to do nothing, Apple-style, "because we know better than our own users" while trying to appear reasonable.
It's akin to not adopting SQL until you can do so "while avoiding the pitfalls of SQL." Just utter bullshit.
Are you offering to help with the maintenance or development associated with it? Or are you just demanding features while calling the people that do help with that stuff liars?
Maybe they do know better? Or maybe they know about other hassles that will come with it that you can't fathom. They've been developing and maintaining one of the best open source projects on the planet for nearly 3 decades. Maybe with all that experience, its not their opinion that is BS?
Also given Amazon AWS support it for Aurora, I feel like it's not -that- hobby-ish.
If people use it and can provide evidence that it helps more than it hurts, that might be convincing.
Insulting the core developers for not yet being convinced seems rather less likely to help.
Oh, didn't know that :)
About the rest: sure, I can agree as well about the indirect adverse effect of having them available (e.g. easy to misuse them as I saw in some apps using Oracle DBs), and it's for sure wrong to insult somebody because of this.
Still, personally, I think that the pros would outweight the cons of having that embedded in the app.
On one hand I remember some nights spent in the past trying to make some SQL work, hints were always at least a good temporary workaround.
On the other hand there will always be some SQL which confuse the optimizer (or more special cases about a lot of data changing distribution of values, etc..) and hints would be the only way to cover these cases.
Maybe an interesting question is on which level should hints act? I know mainly only Oracle & MariaDB, therefore I know hints of the type "use that index"/"query tables in this order"/"join these tables with this join type"/etc..., which are probably low-level hints. Maybe already just higher-level hints of the type "most important selectivity criteria comes from inline-view X"/"I want just the first row of the result"/"take into account only plans which select data by using indexes"/etc... would be as well interesting, not sure, just dumping here my thoughts.
Or maybe it's just typical developer hubris of the kind that hits everyone at some point.
There's a difference between "have a feature just because other databases have it" and "this is a very useful feature that would have its own PostgreSQL-isms and also we got this idea because other databases have it". The query planner isn't infallible, so being able to hint queries to not accidentally use a temporary table that just can't fit in ram isn't just copying a feature "just because everyone else has it".
That is basically a blanket ban. Saying you won't implement a widely-implemented feature unless someone comes up with a whole new theory about how to do it better is saying you simply won't implement it. Other databases do well enough with hints, and they do help with some problems.
I'd love to have a way to lock a plan in a temporary "emergency measure" fashion. But of course it's hard/impossible to design this without letting people abuse it and pretend it's just a hint system.
Could this be achieved by extending prepared statements? [1] The dirty option would be to introduce a new keyword like PREPAREFIXED.
The first time such a statement is executed, the execution plan could be stored and then retrieved on subsequent queries. There would be no hints and the changes in code should be minimal.
Once a query runs successfully, the users can be sure that the execution plan won't change.
>But of course it's hard/impossible to design this without letting people abuse it
Is this more important than having predictable execution times?
[1] https://www.postgresql.org/docs/current/sql-prepare.html
To me: no. To the Postgres team: apparently :)
I found the cons section somewhat disappointing given how much respect I have for Postgresql maintainers in general.
Most of the "problems" are essentially manifestations of dysfunction within users or PostgreSQL development itself. People won't report optimizer bugs if they can fix them themselves, etc. (far more likely they won't report the bugs if they have no way to prove their alternative query plan actually performs better, eg: by adding a hint).
Many are just assertions which I highly doubt would hold up if validated ("most of the time the optimizer is actually right", "hints in queries require massive refactoring" ...).
They don't need to run an alternative plan, they just need to report that the main plan did far more work than needed.
In the real world, when users have a problem in their mission-critical system, they ask for a solution, not just give up on the mission.
> Many are just assertions which I highly doubt would hold up if validated ("most of the time the optimizer is actually right", "hints in queries require massive refactoring" ...).
Their 20 years of experience beats your idle speculation.
Perhaps.
But it comes across to me on that page as arrogance and dismissiveness of user needs. Hence why I find it disappointing, even if it is mainly in how it is expressed.
And how do they know that the chosen plan is not already optimal? How many reports do you think you'd get if every time someone encountered a "slow" query they reported it? And what fraction of those would be finally found to be caused by a bad optimizer as opposed to the user's fault like a missing index, outdated statistics, ... 1%? 0.1%? See stack overflow for a sample of that ratio. Be careful what you wish for.
If hints existed performance reports could be "default plan of x is slow, I know because when I use hints w,z then it's fast".
What should they do in the meantime while they're waiting for the pg project to acknowledge the problem, someone to propose a solution, and someone to implement a fix, just sit on their hands and twiddle their thumbs while their database server spews smoke? Hints are an escape hatch that empowers users to take over a when the optimizer veers off course. Not having any way to control the plan is like a tesla autopilot that doesn't allow the driver to take control over the car in an emergency.
Also, distrusting users to report performance issues is a weird attitude and gives a bad taste. Maybe it's true, or maybe you need to make it easier.
It's almost self-evident that this is the case, because if they had, they'd have implemented some more fine-grained way of overriding the query optimizer when it inevitably fails at times.
I have one shameful query where, unable to convince it to execute a subquery that is essentially a constant for most of the rows outside of the hot loop of the main index scan, I pulled it out into a temp table and had the main query select from the temp table instead. Even creating a temp table and indexing it was faster than the plan the Postgres query planner absolutely insisted on. Things like CTEs etc made no difference, it would still come up with the same dumb plan every way I expressed the query.
The worst thing is not even being able to debug or understand what is going on because you can't influence the query plan to try alternative hypotheses out easily.
Hints are a somewhat invasive feature that are hard to tweak once they are integrated into applications. I don't think the half dozen people that are in the best position to consider its evolution have found it the use of their time they wish to expend.
The Postgres query planner was, quite frankly, the enemy. By the time I left we were at the point that we considered it a business risk and were looking at alternatives. If you need queries to run in a predictable amount of time — forget fast, simply predictable — then Postgres is quite simply not fit for purpose.
A lot of times I see in operational DB hot data gets mixed with warm and cold data, and your hot path query with ton of JOINs/subqueries will get rekt.
Proper redesign and rearchitecture helps to provide large buffer against these problems.
Also not everything should be inside SQL server, if you run large query every 5 seconds or something - probably consider using in-memory cache or denormalized model
Besides which, if you need to re-architect and denormalize by moving records in and out of different tables just so you can run what is essentially a monthly report in a predictable (not fast! just predictable) amount of time -- well then, why bother using Postgres in the first place, you might as well bite the bullet and go with something NoSQL at that point, because at least when you read from a given Cassandra partition you know all the results will be right next to each other, 100%; why leave it to chance? you've been burned by Postgres before
And Postgres, again, is materially deficient in its query performance predictability, and its developers are insistent that they have no desire to allow the approaches that would mitigate it. If this matters to your application, then the prudent developer will drop Postgres and use a real database.
I have been working with it since way back in 6.5 and it has made many obviously wrong decisions in the optimizer over the years, its has gotten better but is by no means perfect, good thing is it has hints unlike PG!
Just splitting hairs, again most of the time developers fail to notice the significant data distribution drift, and dont rearchitect their data models then blame the engine for developers omissions and invalid assumptions
I just like good set of hints since they are part of source code and I can put comments and see why they are there in source history later.
The idea obviously needs work and has probably already been suggested and dismissed, somewhere, but I thought I might throw it out there, especially with modern computers having so many cores.
On the other hand, in general there are often too many postential combinations of query plans to try out (hundreds even for a relatively simple SQL) and trying them all out would need hours/days/etc... . The "good plan" might be something that a machine might categorize as "very unlikely to work" so it might end up being the one tested automatically at the very end.
Normal hints would still be a lot easier to handle in the code and for the user.