Hacker News new | ask | show | jobs
by davidrowley 859 days 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.
2 comments
alexey-salmin 859 days ago
> wouldn't it be equally as annoying if the planner didn't adapt to the table data changing?

Not really, because at scale tables don't change suddenly unless you release something -- and that will re-generate the plans after the perf testing.

Whatever changes slowly accumulate over time will be covered in the next release which normally should be very frequent, daily or more.

link
winrid 859 days ago
> It sounds like it would have to be an opt-in feature which could be applied per query

That's called a hint :)

link
alexey-salmin 859 days 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.
link
masklinn 859 days 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.

link
winrid 858 days ago
Store hints and index definitions in same place and read from it for migrations. Problem solved in most practical scenarios.
link