3 ms·

Indeed, hints are super-useful (essential!) for applying quick fixes when something unexpected suddenly happens in the optimizer's magic. Or you just want to in

Indeed, hints are super-useful (essential!) for applying quick fixes when something unexpected suddenly happens in the optimizer's magic. Or you just want to instruct/nudge the optimizer towards doing the right thing, if you know the shape of your data and optimizer can't see it or doesn't act on it correctly for some reason. The downside is that people who don't really know what exactly they want to achieve, will start applying incomplete sets of hints in random locations, based on Internet searches. And sometimes you'd even get lucky, that single index hint makes the problem go away - for a while! And few people tend to remove hints after DB version upgrades, where the optimizer magic (or your table stats) have improved. Now you're limiting optimizer's choices. That's been a problem (by now) for decades in the Oracle world with lots of legacy SQL code full of random hints where some of them aren't even valid anymore, but others still are - and limit optimizer's choices. At least Oracle folks had enough at some point and introduced an "optimizer_ignore_hints" parameter [1], so all legacy hints that were added 20 years ago just get ignored - and the modern optimizer does a much better job getting things right. I do regularly use hints in SQL tuning and troubleshooting experiments, just to verify and prove that a better plan is theoretically and physically possible - and if yes, then go from there. When the (deliberately placed) hints make the query faster, the next step is to check the hinted, faster plan's optimizer cost estimate and drill down from there: Why did optimizer think that the other plan was cheaper or why did the optimizer think that the faster plan was more expensive. Then you end up with measuring row-count misestimates, etc... But yes, plenty of people (including myself) have made legacy Oracle apps run much more efficiently and faster just by globally telling the DB to stop paying attention to all the old random hints lingering on and gathering object stats using the modern settings & defaults (doesn't always work though). [1] https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/OPTIMIZER_IGNORE_HINTS.html#GUID-D62CA6D8-D0D8-4A20-93EA-EEB4B3144347 https://docs.oracle.com/en/database/oracle/oracle-database/1...

davidrowley 3y ago
> At least Oracle folks had enough at some point and introduced an "optimizer_ignore_hints" parameter [1], so all legacy hints that were added 20 years ago just get ignored - and the modern optimizer does a much better job getting things right. I think the general attitude in the Postgres community is been from a purist point of view. When you have a codebase around 40 years old, you do have to think carefully about what you put into it, as it might not be that easy to take it out again. However, yes, I do think hints would be useful for Postgres, providing they're done well. It would be good to at least have something to assist with selectivity estimations. Those are at least not directly forcing the planner into a single choice. New planner/executor smarts, such as something like Memoize added in PG14 could still be considered after upgrading an older pre-PG14 application with such hints, but perhaps not if the hint told the planner that it must nested loop join these two tables.
alexey-salmin 3y ago
If not hints and manual plans then we need a way to "freeze" automatic optimizer plans however optimal they are after each tested release. The optimizer killing your heavily-loaded production because it randomly switched a frequent query to a bad plan is pretty annoying. People mostly put up with this because for a single-instance DB you can easily maintain 4x spare CPU capacity but it's impossible at scale.
- davidrowley 3y ago
  It sounds like it would have to be an opt-in feature which could be applied per query, as otherwise wouldn't it be equally as annoying if the planner didn't adapt to the table data changing? What may be better is if the executor provided feedback to the planner to tell it the new plan was worse than the old one. With that, you might be able to recover much more quickly and less likely to get a midnight phonecall. The tricky part would be when should the planner then retry the new plan again. Also, other factors that influence the plan's execution time such as locking would be a cause of noise for any sub-system that was monitoring this.
  - winrid 3y ago
    > It sounds like it would have to be an opt-in feature which could be applied per query That's called a hint :)
    - alexey-salmin 3y ago
      Not exactly because a hint contains information that may become obsolete. A directive that freezes a known good auto-plan until the next release will not become obsolete as long as you keep releases rolling.
      - masklinn 3y ago
        What I’d like is the ability to hand postgres an execution plan, whether that is a plan I retrieved from Postgres’s own planner, or one I wrote by hand (possibly a mix of the two). Give the API a scary name if you want, but getting 3 Am calls because the planner suddenly decided to go off the rails or seeing pages of convoluted SQL because in (current version) it’s what it takes to get the plan you need is not fun. It’s long past time for database developers to accept that the high level is not always the right solution, it should be that in 95 or 99% of cases, and the better it is the better my life is, but sometimes you got to write exactly what you need. Most language designers are fine with it and will allow either embedding or calling into lower-level language (down to hand-crafted assembly), it would be great if database devs could get on with that program.

davidrowley 3y ago

> At least Oracle folks had enough at some point and introduced an "optimizer_ignore_hints" parameter [1], so all legacy hints that were added 20 years ago just get ignored - and the modern optimizer does a much better job getting things right. I think the general attitude in the Postgres community is been from a purist point of view. When you have a codebase around 40 years old, you do have to think carefully about what you put into it, as it might not be that easy to take it out again. However, yes, I do think hints would be useful for Postgres, providing they're done well. It would be good to at least have something to assist with selectivity estimations. Those are at least not directly forcing the planner into a single choice. New planner/executor smarts, such as something like Memoize added in PG14 could still be considered after upgrading an older pre-PG14 application with such hints, but perhaps not if the hint told the planner that it must nested loop join these two tables.

alexey-salmin 3y ago

If not hints and manual plans then we need a way to "freeze" automatic optimizer plans however optimal they are after each tested release. The optimizer killing your heavily-loaded production because it randomly switched a frequent query to a bad plan is pretty annoying. People mostly put up with this because for a single-instance DB you can easily maintain 4x spare CPU capacity but it's impossible at scale.

davidrowley 3y ago
It sounds like it would have to be an opt-in feature which could be applied per query, as otherwise wouldn't it be equally as annoying if the planner didn't adapt to the table data changing? What may be better is if the executor provided feedback to the planner to tell it the new plan was worse than the old one. With that, you might be able to recover much more quickly and less likely to get a midnight phonecall. The tricky part would be when should the planner then retry the new plan again. Also, other factors that influence the plan's execution time such as locking would be a cause of noise for any sub-system that was monitoring this.
- winrid 3y ago
  > It sounds like it would have to be an opt-in feature which could be applied per query That's called a hint :)
  - alexey-salmin 3y ago
    Not exactly because a hint contains information that may become obsolete. A directive that freezes a known good auto-plan until the next release will not become obsolete as long as you keep releases rolling.
    - masklinn 3y ago
      What I’d like is the ability to hand postgres an execution plan, whether that is a plan I retrieved from Postgres’s own planner, or one I wrote by hand (possibly a mix of the two). Give the API a scary name if you want, but getting 3 Am calls because the planner suddenly decided to go off the rails or seeing pages of convoluted SQL because in (current version) it’s what it takes to get the plan you need is not fun. It’s long past time for database developers to accept that the high level is not always the right solution, it should be that in 95 or 99% of cases, and the better it is the better my life is, but sometimes you got to write exactly what you need. Most language designers are fine with it and will allow either embedding or calling into lower-level language (down to hand-crafted assembly), it would be great if database devs could get on with that program.