Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

What's new in the Postgres 16 query planner

citusdata.com

Post reply on HN
Print view View original

21–30 of 151 posts

Re: What's new in the Postgres 16 query planner

#21

Post by viraptor »

davidrowley wrote: post #3
riku_iki wrote: post #2

Why wouldnt they implement hints..

There is a pg_hint_plan extension. I think the danger with hints is that they might only be correct when written. If the table sizes or data skew changes, they might make things worse. I don't have a link to hand, but last time I recall a discussion on hints there was no general objection to them, providing the implementation could be done in a way that didn't force the planner's hand too strongly and still allowed i…

>I think the danger with hints is that they might only be correct when written.

Not "correct when written", but "scaling as written". That means if you force the execution that scales linearly or quadratically, that's what you get all the time. If the row number increases, you know what will happen. You can monitor that ahead of time and plan for the increase.

On the other hand without the hints, you don't know when and how the plan will change without testing. At some random point postgres can decide to do something terribly stupid and at that point you get to figure out what happened and how to fix that in an emergency mode. Do you know how to adjust the right statistics? Do you need to change the indexes? Do you know how long that will take?

Re: What's new in the Postgres 16 query planner

#22

Post by uudecoded »

Defman wrote: post #20
uudecoded wrote: post #18

Earlier quoted context omitted.

I read your profile and see that you are a CTO of a fintech. Given that, by what method do you navigate that tool's [explain.dalibo.com] assertion of "It is recommended not to send any critical or sensitive information"? Is there an explain plan sanitizer that is helpful for this situation?

You can download the whole visualizer as a simple html file and use it this way. No need to obfuscate or sanitize anything at all. https://github.com/dalibo/pev2

Thank you for sharing - I misunderstood - this is a javascript visualizer, and has no additional analytic capability beyond visualization, or did I miss something?

Re: What's new in the Postgres 16 query planner

#23

Post by davidrowley »

alexey-salmin wrote: post #19
tanelpoder wrote: post #6

Earlier quoted context omitted.

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 st…

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.

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.

Re: What's new in the Postgres 16 query planner

#24

Post by dewey »

klysm wrote: post #11
wiradikusuma wrote: post #7

I use this tool to visualize my queries: https://explain.dalibo.com/ (there's also https://www.pgexplain.dev/ , last time the output was less nice, but now both look the same)

The tool is great and I use it, but I don't really have a deep enough understanding to know how to fix issues in my approach from what looks bad in the plan.

There's also https://www.pgmustard.com, which gives you a bit more hints and information on the possible optimizations.

Re: What's new in the Postgres 16 query planner

#25

Post by Defman »

uudecoded wrote: post #22
Defman wrote: post #20

Earlier quoted context omitted.

You can download the whole visualizer as a simple html file and use it this way. No need to obfuscate or sanitize anything at all. https://github.com/dalibo/pev2

Thank you for sharing - I misunderstood - this is a javascript visualizer, and has no additional analytic capability beyond visualization, or did I miss something?

Yeah, you’re right. This is the visualizer that’s linked in the parent comment. I just wanted to point out that you can use it locally without sending your explain plan to anyone. Sorry for the confusion, I’ll edit my original comment.

Re: What's new in the Postgres 16 query planner

#26

Post by hans_castorp »

davidrowley wrote: post #5
riku_iki wrote: post #4

Earlier quoted context omitted.

> I think the danger with hints is that they might only be correct when written. If the table sizes or data skew changes, they might make things worse. they will work in prod in the way engineer is expecting. Current planner also can change its mood in unpredictable way and often generates sub-optimal plans for complex queries, because can't reason about what specific subquery will return exactly, and you learn about…

(Postgres committer and blog author here) Personally, I don't have any objection to hints. The resolution of any statistics is never going to be high enough to always be accurate enough for all cases. I think it would be good to give DBAs a better way to coax the planner into making or not making a certain decision. It would also be nice if the planner was a little more risk-averse. Currently, it's happy to do things…

I think Oracle style hints are not a good thing to have - especially because you have to change the query itself which sometimes isn't possible in a production environment. Additionally, for me they quite frequently made things worse after minor Oracle upgrades.

I would prefer having "externally attached" hints for a query (e.g. identified by it's queryid) like Oracle's stored outlines.

Re: What's new in the Postgres 16 query planner

#27

Post by appplication »

viraptor wrote: post #21
davidrowley wrote: post #3

Earlier quoted context omitted.

There is a pg_hint_plan extension. I think the danger with hints is that they might only be correct when written. If the table sizes or data skew changes, they might make things worse. I don't have a link to hand, but last time I recall a discussion on hints there was no general objection to them, providing the implementation could be done in a way that didn't force the planner's hand too strongly and still allowed i…

>I think the danger with hints is that they might only be correct when written. Not "correct when written", but "scaling as written". That means if you force the execution that scales linearly or quadratically, that's what you get all the time. If the row number increases, you know what will happen. You can monitor that ahead of time and plan for the increase. On the other hand without the hints, you don't know when…

I had this happen for the first time to some prod jobs the other day in spark. We made a pretty normal update to a join with an additional condition, our integration tests which run local Spark succeeded. But something about it running on the cluster… it was generating a completely different query plan than it ran locally. We eventually had to rewrite the whole query to work around it because it was trying to broadcast a 3TB table and couldn’t be talked out of it.

Re: What's new in the Postgres 16 query planner

#28

Post by winrid »

davidrowley wrote: post #23
alexey-salmin wrote: post #19

Earlier quoted context omitted.

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.

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…

> It sounds like it would have to be an opt-in feature which could be applied per query

That's called a hint :)

Re: What's new in the Postgres 16 query planner

#29

Post by sa46 »

riku_iki wrote: post #2

Why wouldnt they implement hints..

Related discussion

Why PostgreSQL doesn't have query hints

https://news.ycombinator.com/item?id=2179433 (60 comments, 2011)

The official stance from the Postgres wiki https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion:

> We are not interested in implementing hints in the exact ways they are commonly implemented on other databases.

> Problems with existing Hint systems: Poor application code maintainability, Interference with upgrades, Encouraging bad DBA habits, Does not scale with data size

I don't fault their stance, but it's frustrating when Postgres picks a stupid plan and can't be convinced to do something reasonable.

Re: What's new in the Postgres 16 query planner

#30

Post by pmontra »

davidrowley wrote: post #3
riku_iki wrote: post #2

Why wouldnt they implement hints..

There is a pg_hint_plan extension. I think the danger with hints is that they might only be correct when written. If the table sizes or data skew changes, they might make things worse. I don't have a link to hand, but last time I recall a discussion on hints there was no general objection to them, providing the implementation could be done in a way that didn't force the planner's hand too strongly and still allowed i…

I guess that the solution to this problem can be automated. The DB or an extension to the DB or application code can run the query without hints sometimes and compare the result with the version with hints. If the hinted version is still faster, good. If it is slower, it's time to tell the DBA. Or switch to the unhinted query automatically if it's faster for a large enough number of times.
Post reply on HN