Waiting for Postgres 19: Better planner hints with path generation strategies [video]

One of the biggest issues I run into operationally with relational db's is lack of plan stability and inability as a developer to tell it what I know is the right thing to do always in my application.

I use MS SQL Server more than PG currently and if it did not have plan hinting it would have been catastrophic for our customers.

Its still not perfect and over the years I am starting to think that having smart optimizers that use live statistics may be more of a detriment than help due to constantly fighting bad plan generation at typically the worst times (late at night in production). At least with compiler optimization it happens at build time and the results can be reasoned about and is stable. SQL is like some insane runtime JIT system like a javascript engine that de-optimizes but not deterministically based on the data in the system at the time.

Using various tricks and writing the query in slightly different ways while praying to the planner god to pick the correct plan is well infuriating due to lack of control over the system.

I much prefer systems like Linq or Kusto where it's still declarative but the pipeline execution order follows the order as written in the code. One of the most helpful and typical hints that solves issues is simply forcing join order and subquery / where order such that I want it to do this filter first then this other one second, then typically the optimizer picks the obviously correct indexes to use. Bad plans typically try and rewrite the order do much less selective thing first destroying performance. I as the developer normally know the correct order of operations for my applications use and write the query that way.