6 ms·

Aren't there two arguments for why this is bad? - db will generate new plans as necessary when row counts and values change. Putting in hints makes the plan ri

Aren't there two arguments for why this is bad? - db will generate new plans as necessary when row counts and values change. Putting in hints makes the plan rigid likely leading to headaches down the line. - as new Postgres comes out, it's planner will do a better job. Again forcing specific plan might force the planner into optimization that no longer is optimal. In my experience developers almost never comeback to a query with hints to double check if hints are really needed. Famously oracle has query hints that don't do nothing no more, that are ignored, but oracle can't remove them from query language because that would break too many existing queries. I like Postgres stance that if query planner doesn't do a good job, then dba should first update table/column statistics, and if things are truly bad, submit but to Postgres so the query optimizer can be updated itself. Saying all that, hints support through an extension to Postgres is a good compromise. Postgres developers don't need to bother with hints, its a third party feature. And dba/users, if they really need hints, now they have them.

deniska 3y ago
Why it's good: you won't get a sudden slowdown if postgresql for some reason changes its plan to something much less performant.
- silon42 3y ago
  Yup... parent point 1 is an often a misfeature in production (yes, sometimes the plan is better, but sometimes not).
btown 3y ago
What about when column statistics don’t accurately describe a subset of the table that you know from a business perspective is likely to have different dynamics? Allowing the client to essentially A/B test between different advisements and evaluate performance, with full knowledge of that business context, may be meaningful at certain scales.
- phamilton 3y ago
  That's valuable, but cranking statistics up to 10000 and/or creating custom statistics can go a very long way to helping the planner understand the dataset more fully.
  - justinclift 3y ago
    Sure, but for production environments being able to tell the planner to do it the way you want (aka "fix it for right now") is often the overriding priority. The "make it work work perfectly from just statistics" can come later, when time is more flexible. Probably on an identical non-production copy of the environment, if that shows the same performance characteristics. ;)
  - heavenlyblue 3y ago
    Can you please explain how cranking statistics 10000 and/or creating custom statistics is a more straightfoward way to enforce a certain query plan VS telling the databsee which plan should be used instead?
    - phamilton 3y ago
      The goal isn't to enforce a certain query plan. The goal is to execute queries efficiently. The planner/optimizer is good at its job. In general, better than I am. If I can inform it about a particular shape of data, all queries around that data will improve. If I tell it exactly how to execute one query, only that query improves. Additionally, setting statistics to 10000 is really easy. Easier than tweaking a query. Creating custom statistics is more work and may or may not be worth exploring as a quick fix. But if you know the data is shaped irregularly, why wouldn't you make sure the planner knows about it?
nextaccountic 3y ago
> In my experience developers almost never comeback to a query with hints to double check if hints are really needed. What's needed, then, is a benchmark suite that tests if those hints still give a better performance than a hintless query
ahachete 3y ago
> Aren't there two arguments for why this is bad? > - db will generate new plans as necessary when row counts and values change. Putting in hints makes the plan rigid likely leading to headaches down the line. > - as new Postgres comes out, it's planner will do a better job. Again forcing specific plan might force the planner into optimization that no longer is optimal. It depends what is your priority. Most production environments want to favor predictability over raw performance. I'd rather trade 10% performance degradation in average for a consistent query performance. Even if statistics or new versions could come with 10% better plans, I prefer that my query's performance is predictable and does not experience high p90s or even worse that you risk experiencing plan flips that turn your 0.2s 10K/qps query into a 40s query.
- kevincox 3y ago
  100%, even if my performance slow degrades as my data changes I would much rather have gradual performance and be able to manually switch to a better plan at some point than have the DB suddenly switch a new plan which is probably better, but if not may cause a huge performance swing that takes my application offline. It would be very interesting if there was some sort of system that ensured query plans don't change "randomly" but if it noticed that a new plan was expected to be better on most queries it would surface this in some interface that you could then test and approve. Maybe it could also support gradual rollout, try this on 1% of queries first, then if it does improve performance it can be fully approved. It would be extra cool if this was automatic. New plans could slowly be slowly rolled out by the DB and rolled back if they aren't actually better.
williamdclt 3y ago
> I like Postgres stance that if query planner doesn't do a good job, then dba should first update table/column statistics, and if things are truly bad, submit but to Postgres so the query optimizer can be updated itself. That’s a very unhelpful stance when I’m having an incident in production because PG decided to use a stupid query plan. Waiting months - years for a bugfix (which might not even be backported to my PG version) is not a solution. I agree that hints are a very dangerous thing, but they’re invaluable as an emergency measure
- phamilton 3y ago
  I like it as an emergency measure, but I often see them used when there's a shallow understanding of operating the db. Before using a hint or rewriting a query to force a specific plan, I try and push the team to do these things: 1. Run `vacuum analyze` and tune the auto vacuum settings. This fixes issues surprisingly often. 2. Increase statistics on the table. 3. Tweak planner settings globally or just for the query. Stuff like `set local join_collapse_limit=1` can fix a bad plan. This is pretty similar to hinting, so not a huge argument that this is better beyond not requiring an extension.
  - franckpachot 3y ago
    All those methods are try and guess. With hints you can have a scientific approach to understand why the bad plan has been chosen and find the right plan. Then, you can address the root cause. join_collapse_limit=1 may set the join order but not the join direction, so that's not enough if cardinality is misestimated. And pg_hint_plan can set this parameter for one statement if that's what you want, better than setting for the transaction
  - Yeroc 3y ago
    In a database supporting several applications or even just a large data model it can be rather difficult to ensure a global setting to the query planner doesn't cause a regression to other queries. A query hint can be a nice way to quickly solve a performance issue without risk of regressing elsewhere. Agree they should be used as a last resort and as a sort term fix while a better, longer term fix is investigated but they are a critical tool to have in your tool box especially as postgres moves into the business critical domains occupied by the commercial database vendors.
perrygeo 3y ago
More philosophically, the query planner is a finely-tuned piece of engineering. If your mental model disagrees with it ("Why isn't it using the index I want?") then it's highly likely your mental model is wrong. Your data model might contain obvious mistakes. Statistics can be out of date too, if e.g. a table was bulk loaded and never analyzed. `ANALYZE tablename` done. Sometimes removing unused indexes can improve things. TLDR; it's always something that you need to tune in your own database. When in doubt, the query planner is right and you are wrong. Good engineering means having the intellectual curiosity to exhaust these possibility before resorting to hints. Hints are an extreme measure. You're basically saying that you know better than the query planner, for now until eternity, and you choose to optimize by hand. That may be the case but it requires detailed knowledge of your table and access patterns. The vast majority of misbehaving query plans just need updated statistics or a better index.
- jupp0r 3y ago
  Queries are complex and the query planner is using heuristics that might just not fit your situation in some cases. The query planner is great for 99.99% of queries but a super small number of edge cases will need tuning at the same time. Finding out what went wrong in the query plan by looking at optimizer traces is a lot of work. I did so recently and the trace alone was 317MB.
- jandrewrogers 3y ago
  This is not a correct assumption with Postgres. Its statistics collection process has fundamental flaws when tables become large such that the statistics it uses for optimization are in no way representative of the actual data, leading to the situation this thread is about. Statistics collection is a weak spot in Postgres and query optimization relies on that information to do its job.
- bsdpufferfish 3y ago
  > you know better than the query planner, for now until eternity, Nope, you just have to know it's fixing a real problem today. Having a query regress in performance below a KPI would be worse than not taking advantage of a further optimization in the future, due to out of date hint. > Good engineering means having the intellectual curiosity to exhaust these possibility before resorting to hints Why is that better? Luckily we don't have to rely on such grandiose claims. Just try it out. If you find a query that you can tune better than the planner for your data set, then it's a better outcome.
CuriouslyC 3y ago
Those points are academically correct. In the real world you can end up with the query planner doing very boneheaded stuff that threatens to literally break your business when there's another approach that's fast it just seems to miss. In these cases hints would be a lot easier than the stuff you end up doing.
jandrewrogers 3y ago
That's the theory but it does not work this way in practice. Many of the issues people run into with the Postgres query planner/optimizer are side effects of architectural peculiarities with no straightforward fix. Some of these issues, such as the limitations of Postgres statistics collection, have been unaddressed for decades because they are extremely difficult to address without major architectural surgery that will have its own side effects. In my opinion, Postgres either needs to allow people to disable/bypass the query planner with hints or whatever, or commit to the difficult and unpleasant work of fixing the architecture so that these issues no longer occur. This persistent state of affairs of neither fixing the core problems nor allowing a mechanism to work around them is not user friendly. I like and use Postgres but this aspect of the project is quite broken. The alternative is to put a disclaimer in the Postgres documentation that it should not be used for certain types of common workloads or large data models; many of these issues don't show up in force until databases become quite large. This is a chronic source of pain in large-scale Postgres installations.
- pgaddict 3y ago
  I don't know which "architectural peculiarities" you have in mind, but the issues we have are largely independent of the architecture. So I'm not sure what exactly you think we should be fixing architecture-wise ... The way I see it the issues we have in query planning/optimization are largely independent of the overall architecture. It's simply a consequence of relying on statistics too much. But there's always going to be inaccuracies, because the whole point of stats is that it's a compact/lossy approximation of the data. No matter how much more statistics we might collect there will always be some details missing, until the statistics are way too large to be practical. There are discussions about considering how "risky" a given plan is - some plan shapes may be more problematic, but I don't think anyone submitted any patch so far. But even with that would not be a perfect solution - no approach relying solely on a priori information can be. IMHO the only way forward is to work on making the plans more robust to this kind of mistakes. But that's very hard too.
bsdpufferfish 3y ago
What if I have a performance problem I need to fix now?
RMarcus 3y ago
This is the main motivation behind learned "steering" query optimizers: even if a DBA finds the right hint for a query, it is difficult to track that hint through data changes, future DB versions, and even query changes (e.g., if you add another condition to the WHERE clause, should you keep the hints or drop them?). Turns out, with a little bit of systems work, ML models can do a reasonably good job of selecting a good hint for a particular query plan.
thesnide 3y ago
I see this one as the same level of inline ASM in C code. It is certainly a bad practice, but on very particular occasions it is invaluable.

deniska 3y ago

Why it's good: you won't get a sudden slowdown if postgresql for some reason changes its plan to something much less performant.

silon42 3y ago
Yup... parent point 1 is an often a misfeature in production (yes, sometimes the plan is better, but sometimes not).

btown 3y ago

What about when column statistics don’t accurately describe a subset of the table that you know from a business perspective is likely to have different dynamics? Allowing the client to essentially A/B test between different advisements and evaluate performance, with full knowledge of that business context, may be meaningful at certain scales.

phamilton 3y ago
That's valuable, but cranking statistics up to 10000 and/or creating custom statistics can go a very long way to helping the planner understand the dataset more fully.
- justinclift 3y ago
  Sure, but for production environments being able to tell the planner to do it the way you want (aka "fix it for right now") is often the overriding priority. The "make it work work perfectly from just statistics" can come later, when time is more flexible. Probably on an identical non-production copy of the environment, if that shows the same performance characteristics. ;)
- heavenlyblue 3y ago
  Can you please explain how cranking statistics 10000 and/or creating custom statistics is a more straightfoward way to enforce a certain query plan VS telling the databsee which plan should be used instead?
  - phamilton 3y ago
    The goal isn't to enforce a certain query plan. The goal is to execute queries efficiently. The planner/optimizer is good at its job. In general, better than I am. If I can inform it about a particular shape of data, all queries around that data will improve. If I tell it exactly how to execute one query, only that query improves. Additionally, setting statistics to 10000 is really easy. Easier than tweaking a query. Creating custom statistics is more work and may or may not be worth exploring as a quick fix. But if you know the data is shaped irregularly, why wouldn't you make sure the planner knows about it?

nextaccountic 3y ago

> In my experience developers almost never comeback to a query with hints to double check if hints are really needed. What's needed, then, is a benchmark suite that tests if those hints still give a better performance than a hintless query

ahachete 3y ago

> Aren't there two arguments for why this is bad? > - db will generate new plans as necessary when row counts and values change. Putting in hints makes the plan rigid likely leading to headaches down the line. > - as new Postgres comes out, it's planner will do a better job. Again forcing specific plan might force the planner into optimization that no longer is optimal. It depends what is your priority. Most production environments want to favor predictability over raw performance. I'd rather trade 10% performance degradation in average for a consistent query performance. Even if statistics or new versions could come with 10% better plans, I prefer that my query's performance is predictable and does not experience high p90s or even worse that you risk experiencing plan flips that turn your 0.2s 10K/qps query into a 40s query.

kevincox 3y ago
100%, even if my performance slow degrades as my data changes I would much rather have gradual performance and be able to manually switch to a better plan at some point than have the DB suddenly switch a new plan which is probably better, but if not may cause a huge performance swing that takes my application offline. It would be very interesting if there was some sort of system that ensured query plans don't change "randomly" but if it noticed that a new plan was expected to be better on most queries it would surface this in some interface that you could then test and approve. Maybe it could also support gradual rollout, try this on 1% of queries first, then if it does improve performance it can be fully approved. It would be extra cool if this was automatic. New plans could slowly be slowly rolled out by the DB and rolled back if they aren't actually better.

williamdclt 3y ago

> I like Postgres stance that if query planner doesn't do a good job, then dba should first update table/column statistics, and if things are truly bad, submit but to Postgres so the query optimizer can be updated itself. That’s a very unhelpful stance when I’m having an incident in production because PG decided to use a stupid query plan. Waiting months - years for a bugfix (which might not even be backported to my PG version) is not a solution. I agree that hints are a very dangerous thing, but they’re invaluable as an emergency measure

phamilton 3y ago
I like it as an emergency measure, but I often see them used when there's a shallow understanding of operating the db. Before using a hint or rewriting a query to force a specific plan, I try and push the team to do these things: 1. Run `vacuum analyze` and tune the auto vacuum settings. This fixes issues surprisingly often. 2. Increase statistics on the table. 3. Tweak planner settings globally or just for the query. Stuff like `set local join_collapse_limit=1` can fix a bad plan. This is pretty similar to hinting, so not a huge argument that this is better beyond not requiring an extension.
- franckpachot 3y ago
  All those methods are try and guess. With hints you can have a scientific approach to understand why the bad plan has been chosen and find the right plan. Then, you can address the root cause. join_collapse_limit=1 may set the join order but not the join direction, so that's not enough if cardinality is misestimated. And pg_hint_plan can set this parameter for one statement if that's what you want, better than setting for the transaction
- Yeroc 3y ago
  In a database supporting several applications or even just a large data model it can be rather difficult to ensure a global setting to the query planner doesn't cause a regression to other queries. A query hint can be a nice way to quickly solve a performance issue without risk of regressing elsewhere. Agree they should be used as a last resort and as a sort term fix while a better, longer term fix is investigated but they are a critical tool to have in your tool box especially as postgres moves into the business critical domains occupied by the commercial database vendors.

perrygeo 3y ago

More philosophically, the query planner is a finely-tuned piece of engineering. If your mental model disagrees with it ("Why isn't it using the index I want?") then it's highly likely your mental model is wrong. Your data model might contain obvious mistakes. Statistics can be out of date too, if e.g. a table was bulk loaded and never analyzed. `ANALYZE tablename` done. Sometimes removing unused indexes can improve things. TLDR; it's always something that you need to tune in your own database. When in doubt, the query planner is right and you are wrong. Good engineering means having the intellectual curiosity to exhaust these possibility before resorting to hints. Hints are an extreme measure. You're basically saying that you know better than the query planner, for now until eternity, and you choose to optimize by hand. That may be the case but it requires detailed knowledge of your table and access patterns. The vast majority of misbehaving query plans just need updated statistics or a better index.

jupp0r 3y ago
Queries are complex and the query planner is using heuristics that might just not fit your situation in some cases. The query planner is great for 99.99% of queries but a super small number of edge cases will need tuning at the same time. Finding out what went wrong in the query plan by looking at optimizer traces is a lot of work. I did so recently and the trace alone was 317MB.
jandrewrogers 3y ago
This is not a correct assumption with Postgres. Its statistics collection process has fundamental flaws when tables become large such that the statistics it uses for optimization are in no way representative of the actual data, leading to the situation this thread is about. Statistics collection is a weak spot in Postgres and query optimization relies on that information to do its job.
bsdpufferfish 3y ago
> you know better than the query planner, for now until eternity, Nope, you just have to know it's fixing a real problem today. Having a query regress in performance below a KPI would be worse than not taking advantage of a further optimization in the future, due to out of date hint. > Good engineering means having the intellectual curiosity to exhaust these possibility before resorting to hints Why is that better? Luckily we don't have to rely on such grandiose claims. Just try it out. If you find a query that you can tune better than the planner for your data set, then it's a better outcome.

CuriouslyC 3y ago

Those points are academically correct. In the real world you can end up with the query planner doing very boneheaded stuff that threatens to literally break your business when there's another approach that's fast it just seems to miss. In these cases hints would be a lot easier than the stuff you end up doing.

jandrewrogers 3y ago

That's the theory but it does not work this way in practice. Many of the issues people run into with the Postgres query planner/optimizer are side effects of architectural peculiarities with no straightforward fix. Some of these issues, such as the limitations of Postgres statistics collection, have been unaddressed for decades because they are extremely difficult to address without major architectural surgery that will have its own side effects. In my opinion, Postgres either needs to allow people to disable/bypass the query planner with hints or whatever, or commit to the difficult and unpleasant work of fixing the architecture so that these issues no longer occur. This persistent state of affairs of neither fixing the core problems nor allowing a mechanism to work around them is not user friendly. I like and use Postgres but this aspect of the project is quite broken. The alternative is to put a disclaimer in the Postgres documentation that it should not be used for certain types of common workloads or large data models; many of these issues don't show up in force until databases become quite large. This is a chronic source of pain in large-scale Postgres installations.

pgaddict 3y ago
I don't know which "architectural peculiarities" you have in mind, but the issues we have are largely independent of the architecture. So I'm not sure what exactly you think we should be fixing architecture-wise ... The way I see it the issues we have in query planning/optimization are largely independent of the overall architecture. It's simply a consequence of relying on statistics too much. But there's always going to be inaccuracies, because the whole point of stats is that it's a compact/lossy approximation of the data. No matter how much more statistics we might collect there will always be some details missing, until the statistics are way too large to be practical. There are discussions about considering how "risky" a given plan is - some plan shapes may be more problematic, but I don't think anyone submitted any patch so far. But even with that would not be a perfect solution - no approach relying solely on a priori information can be. IMHO the only way forward is to work on making the plans more robust to this kind of mistakes. But that's very hard too.

bsdpufferfish 3y ago

What if I have a performance problem I need to fix now?

RMarcus 3y ago

This is the main motivation behind learned "steering" query optimizers: even if a DBA finds the right hint for a query, it is difficult to track that hint through data changes, future DB versions, and even query changes (e.g., if you add another condition to the WHERE clause, should you keep the hints or drop them?). Turns out, with a little bit of systems work, ML models can do a reasonably good job of selecting a good hint for a particular query plan.

thesnide 3y ago

I see this one as the same level of inline ASM in C code. It is certainly a bad practice, but on very particular occasions it is invaluable.