[adamzochowski]

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.

[williamdclt]

> 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]

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]

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]

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.

[phamilton]

`set local` allows tuning query planner settings scoped to a transaction, so you don't have to do it globally.

[samus]

There are other ways to achieve that (optimizer features and decisions can be influenced by config settings, or by altering entries in statistics tables), but it's admittedly quite clunky. I agree that a production incident at 3am is definitely not the right time to start grokking these settings. The extension can be a good remedy for such cases.

[tempest_]

I have been burned before when a query on a newer db version was "optimized" in a different way that caused performance to drop but I have not yet had a query on the same version vary so drastically as to cause a problem.

Is this a more common occurrence that I just have not encountered before?

[jandrewrogers]

It is fairly common in e.g. multi-terabyte Postgres databases. Beyond a certain scale, it can become impossible for the statistics collector to build an accurate model for large tables. When this happens, you can end up in the strange place where the query plans change every time you run analyze even though the data model and data distribution hasn't changed. I've seen operational issues at multiple companies due to the query plans randomly changing like this on large tables in Postgres.

[williamdclt]

Not common (if it was anything more than exceptional, PG wouldn’t be a production ready product) but it happens: https://www.postgresql.org/message-id/17540-7aa1855ad5ec18b4...

[ahachete]

> 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]

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.

[jandrewrogers]

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]

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.

[deniska]

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]

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

[CuriouslyC]

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.

[btown]

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]

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]

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]

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]

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?

[RMarcus]

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.

[nextaccountic]

> 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

[thesnide]

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.

[bsdpufferfish]

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

[perrygeo]

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]

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]

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]

> 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.

[justinclift]

> Good engineering means having the intellectual curiosity to exhaust these possibility before resorting to hints.

While it's great that "good engineering" exists, it often requires a bunch more time and effort than people have for the task right then.

Being inflexible and always demanding that time is taken is an extremely poor approach, and often doesn't lead to a good quality result.

Instead it often leads to the inflexible thing being retired and a more flexible alternative being used from then on.

[RaftPeople]

> Hints are an extreme measure. You're basically saying that you know better than the query planner

Absolutely.

A query planner does not analyze the complete and precise solution space, none of them do. The query planner will be extremely wrong sometimes. The only logical solution is to provide a mechanism to guide the planner towards the correct solution.

I've worked with DB2, Oracle and MS SQL Server over the last 3 decades and the story is always the same, the planner is not perfect.

[heavenlyblue]

> You're basically saying that you know better than the query planner, for now until eternity, and you choose to optimize by hand.

Yes I do know better what data is going to be in the database I am managing that some heuristic mechanism.