8 ms·

PostgreSQL really needs hints. Sometimes I as the developer know better than the optimizer and having to trick the optimizer to get it to do what I want is a ri

PostgreSQL really needs hints. Sometimes I as the developer know better than the optimizer and having to trick the optimizer to get it to do what I want is a ridiculous situation. Also having the optimizer decide to do something bad off hours is not a good situation again hints or plan locking would help here.

baq 5y ago
even plan locking itself would be a welcome feature. we've had multiple instances of plans going haywire (never traced it down, but 99% sure it was after autovacuum analyze) impacting prod and getting back to normal after a manual re-ANALYZE.
- SigmundA 5y ago
  I haven't had much use for plan locking even after MS SQL server got it, even though its a nice feature with being able save load plans from a file etc. 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.
- zmmmmm 5y ago
  if nothing else I would love to have that just so we don't end up debugging heisenbugs that occur and then disappear when you try to find them (or we can't rule it out when we test the same query and it magically starts performing well).
jonatron 5y ago
Having been in a situation where Postgres suddenly decides to use a much slower query plan on a hot query in production, I'd agree.
electroly 5y ago
I really appreciate that in SQL Server we can just use query hints. I don't think I will ever see eye-to-eye with the PostgreSQL team on this. No query planner is perfect. SQL Server's planner certainly isn't. We have had the same sort of "query plan suddenly changes in production and everything breaks" incidents, but they are easily fixed with query hints and/or forced plans, and you can be sure that future statistics updates won't break them again.
- SigmundA 5y ago
  I still have nightmares of SQL servers optimizer taking down production systems at random times due to picking a bad plan, judicious use of hints has made my life much easier and allowed me as a developer to be in control and not have to ask the DB nicely and pray it listens.
  - fennecfoxen 5y ago
    I worked for a payments firm that submitted transfers in bulk every day, but had a monthly surge day, and a late cut-off period to submit or cancel payments. It doesn't matter if you can do everything else in 15 minutes, if the query planner decides to take a 30-second query and turn it into a 4-hour nightmare because different customers are making different numbers of payments today, and it trips over some Postgres heuristic. 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.
    - slt2021 5y ago
      these query issues are most of the times not SQL Server problem, they are rather symptom of poor data model architecture, table design. 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
      - fennecfoxen 5y ago
        Excuse me. You are replying with advice that is fundamentally inappropriate for a batch workload (describing a "hot path query" and suggesting in-memory caches), and it shows that you have not read and comprehended the post I wrote. 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
shock-value 5y ago
It's basically shameful how the maintainers refuse to even entertain the thought of allowing query hints. These kinds of articles and anecdotes come up again and again, yet seemingly no movement whatsoever on the blanket ban on query hints in Postgres. (See https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion) 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.
- yjftsjthsd-h 5y ago
  > These kinds of articles and anecdotes come up again and again 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).
  - erichocean 5y ago
    > 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.
    - adamrt 5y ago
      > We want exactly the functionality that is already present in other systems, pitfalls and all. That's just an excuse to do nothing 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?
pzduniak 5y ago
I recall using https://pghintplan.osdn.jp/pg_hint_plan.html https://pghintplan.osdn.jp/pg_hint_plan.html for this feature, it did what it claimed to be able to do. It's a hack, but it really helps in the worst case scenarios.
- rattray 5y ago
  There's now a github repo for pg_hint_plan: https://github.com/ossc-db/pg_hint_plan https://github.com/ossc-db/pg_hint_plan
quietbritishjim 5y ago
Although absolute sacrilege to many, but I would honestly support a really explicit syntax to compose particular tables and indices. Not even in top of regular SQL query, I mean its own first class language.. E.g. "Take this index, restrict to this subrange; for each row, pick a row using this other index on the foreign key" (but it wouldn't have to be imperative like I've phrased it). 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.
- SigmundA 5y ago
  Yes I would too. Over time I have come to appreciate at least being able to control order of operations, just forcing join order is many times enough of hint to keep things working properly. 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.
- derefr 5y ago
  Or, to put that another way: Postgres's query planner should be emitting an explicit bytecode that is then interpreted by a query-execution abstract machine. That same layer should also be exposed directly to clients over the network, allowing pre-planned query bytecode to be submitted directly in place of a textual query. 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.
remus 5y ago
Not that I disagree with your point, but the opposite of > 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.
- lazide 5y ago
  When doing disaster planning, at least something blowing up because of a data change is preventable or mitigateable. Staging environments. Rollbacks, etc. 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.
  - Arnavion 5y ago
    And if you do find your query has turned to treacle, looking for whether you used a wrong query hint is already in your checklist, because it's right there in the query that you're already debugging. You can't forget to double-check it.
- grogers 5y ago
  In practice what happens in this case is that the query slows down over time, often days or weeks after deploying the thing that makes the query worse. It takes time to fill up the data that slows things down. This gives you plenty of time to monitor and take action. If it does happen more quickly than that (e.g. if a table starts getting filled for the first time, statistics can be off) then rolling back is easy. 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.
  - 5y ago
ghusbands 5y ago
Since the PostgreSQL folks are clearly against hints, maybe another random feature suggestion: If a (read-only) query is taking "too long", maybe you could launch a second copy with a different plan. Maybe even a third after twice as long and a fourth after twice as long again. Kill them all once any one returns. Trades some extra load now against waiting forever for a 'bad' query plan. 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.
- lgessler 5y ago
  I'm not a DB expert, but aren't most queries bottlenecked by disk bandwidth? If so, addtl queries might hurt more than help.
  - jandrewrogers 5y ago
    Modern databases on modern server hardware are not limited by disk bandwidth typically, though Postgres is not a modern design and struggles to use the disk bandwidth available. That said, since these queries are accessing roughly the same data, concurrent queries will mostly be hitting cache. Memory bandwidth is often a larger limitation for databases these days, so it would still be wasteful.
- zepearl 5y ago
  On one hand this could theoretically work if the optimizer tracks historical SQLs + the query plans used at that time + their runtime and decides to try to use a different query plan, then aborts the SQL being executed when its runtime exceeds the historical runtime. But in practice it's likely to mess things up because data volume/distribution/machine load (CPU, disks, etc) might differ compared to the past etc... . 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.

baq 5y ago

even plan locking itself would be a welcome feature. we've had multiple instances of plans going haywire (never traced it down, but 99% sure it was after autovacuum analyze) impacting prod and getting back to normal after a manual re-ANALYZE.

SigmundA 5y ago
I haven't had much use for plan locking even after MS SQL server got it, even though its a nice feature with being able save load plans from a file etc. 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.
zmmmmm 5y ago
if nothing else I would love to have that just so we don't end up debugging heisenbugs that occur and then disappear when you try to find them (or we can't rule it out when we test the same query and it magically starts performing well).

jonatron 5y ago

Having been in a situation where Postgres suddenly decides to use a much slower query plan on a hot query in production, I'd agree.

electroly 5y ago

I really appreciate that in SQL Server we can just use query hints. I don't think I will ever see eye-to-eye with the PostgreSQL team on this. No query planner is perfect. SQL Server's planner certainly isn't. We have had the same sort of "query plan suddenly changes in production and everything breaks" incidents, but they are easily fixed with query hints and/or forced plans, and you can be sure that future statistics updates won't break them again.

SigmundA 5y ago
I still have nightmares of SQL servers optimizer taking down production systems at random times due to picking a bad plan, judicious use of hints has made my life much easier and allowed me as a developer to be in control and not have to ask the DB nicely and pray it listens.
- fennecfoxen 5y ago
  I worked for a payments firm that submitted transfers in bulk every day, but had a monthly surge day, and a late cut-off period to submit or cancel payments. It doesn't matter if you can do everything else in 15 minutes, if the query planner decides to take a 30-second query and turn it into a 4-hour nightmare because different customers are making different numbers of payments today, and it trips over some Postgres heuristic. 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.
  - slt2021 5y ago
    these query issues are most of the times not SQL Server problem, they are rather symptom of poor data model architecture, table design. 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
    - fennecfoxen 5y ago
      Excuse me. You are replying with advice that is fundamentally inappropriate for a batch workload (describing a "hot path query" and suggesting in-memory caches), and it shows that you have not read and comprehended the post I wrote. 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

shock-value 5y ago

It's basically shameful how the maintainers refuse to even entertain the thought of allowing query hints. These kinds of articles and anecdotes come up again and again, yet seemingly no movement whatsoever on the blanket ban on query hints in Postgres. (See https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion) 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.

yjftsjthsd-h 5y ago
> These kinds of articles and anecdotes come up again and again 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).
- erichocean 5y ago
  > 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.
  - adamrt 5y ago
    > We want exactly the functionality that is already present in other systems, pitfalls and all. That's just an excuse to do nothing 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?

pzduniak 5y ago

I recall using https://pghintplan.osdn.jp/pg_hint_plan.html https://pghintplan.osdn.jp/pg_hint_plan.html for this feature, it did what it claimed to be able to do. It's a hack, but it really helps in the worst case scenarios.

rattray 5y ago
There's now a github repo for pg_hint_plan: https://github.com/ossc-db/pg_hint_plan https://github.com/ossc-db/pg_hint_plan

quietbritishjim 5y ago

Although absolute sacrilege to many, but I would honestly support a really explicit syntax to compose particular tables and indices. Not even in top of regular SQL query, I mean its own first class language.. E.g. "Take this index, restrict to this subrange; for each row, pick a row using this other index on the foreign key" (but it wouldn't have to be imperative like I've phrased it). 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.

SigmundA 5y ago
Yes I would too. Over time I have come to appreciate at least being able to control order of operations, just forcing join order is many times enough of hint to keep things working properly. 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.
derefr 5y ago
Or, to put that another way: Postgres's query planner should be emitting an explicit bytecode that is then interpreted by a query-execution abstract machine. That same layer should also be exposed directly to clients over the network, allowing pre-planned query bytecode to be submitted directly in place of a textual query. 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.

remus 5y ago

Not that I disagree with your point, but the opposite of > 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.

lazide 5y ago
When doing disaster planning, at least something blowing up because of a data change is preventable or mitigateable. Staging environments. Rollbacks, etc. 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.
- Arnavion 5y ago
  And if you do find your query has turned to treacle, looking for whether you used a wrong query hint is already in your checklist, because it's right there in the query that you're already debugging. You can't forget to double-check it.
grogers 5y ago
In practice what happens in this case is that the query slows down over time, often days or weeks after deploying the thing that makes the query worse. It takes time to fill up the data that slows things down. This gives you plenty of time to monitor and take action. If it does happen more quickly than that (e.g. if a table starts getting filled for the first time, statistics can be off) then rolling back is easy. 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.
- 5y ago

ghusbands 5y ago

Since the PostgreSQL folks are clearly against hints, maybe another random feature suggestion: If a (read-only) query is taking "too long", maybe you could launch a second copy with a different plan. Maybe even a third after twice as long and a fourth after twice as long again. Kill them all once any one returns. Trades some extra load now against waiting forever for a 'bad' query plan. 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.

lgessler 5y ago
I'm not a DB expert, but aren't most queries bottlenecked by disk bandwidth? If so, addtl queries might hurt more than help.
- jandrewrogers 5y ago
  Modern databases on modern server hardware are not limited by disk bandwidth typically, though Postgres is not a modern design and struggles to use the disk bandwidth available. That said, since these queries are accessing roughly the same data, concurrent queries will mostly be hitting cache. Memory bandwidth is often a larger limitation for databases these days, so it would still be wasteful.
zepearl 5y ago
On one hand this could theoretically work if the optimizer tracks historical SQLs + the query plans used at that time + their runtime and decides to try to use a different query plan, then aborts the SQL being executed when its runtime exceeds the historical runtime. But in practice it's likely to mess things up because data volume/distribution/machine load (CPU, disks, etc) might differ compared to the past etc... . 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.