What's new in the Postgres 16 query planner
citusdata.com
What's new in the Postgres 16 query planner
1–10 of 151 posts
Re: What's new in the Postgres 16 query planner
#2Why wouldnt they implement hints..
Re: What's new in the Postgres 16 query planner
#3Why 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 it to adapt to the underlying data changing. For example, indicating there's a correlation between two columns, rather than specifying a given predicate matches 10 rows.
Re: What's new in the Postgres 16 query planner
#4Why 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. 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 it when queries start work very slow in production in the middle of the night.
Re: What's new in the Postgres 16 query planner
#5Earlier 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. 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 like Nested Loop join because it thinks some complex WHERE clause will only match 1 row. Nested Loop works best for that, but if there are 2 rows, then generally, any other join type is better, especially so when the inner side of the join is expensive.
Re: What's new in the Postgres 16 query planner
#6Earlier 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. 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…
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/1...
Re: What's new in the Postgres 16 query planner
#7I 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)
Re: What's new in the Postgres 16 query planner
#8Earlier 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…
One way to look at this is that the most accurate way to "estimate" how fast a certain plan would run, is to actually run it on the full dataset. But that obviously doesn't make sense, as the optimizer is expected to come up with a plan in matter of milliseconds (or less for simple queries) and you don't want your "optimizer stats" to be as big as the whole dataset itself. So optimizer has limited information, by design, and it has to come up with _something_ in a very short amount of time.
I don't know much about Postgres optimizer, but I imagine that in addition to table/column stats, is also uses structural info as its inputs, like existence of (enabled & valid) constraints for example. If the optimizer knows that some column never has NULLs or is guaranteed to be unique, all kinds of transformation & shortcuts become possible.
(There are plenty of large big-vendor ERP/CRM/etc apps out there that do not use DB constraints for the sake of "portability"... not fun to work with these).
Re: What's new in the Postgres 16 query planner
#9Earlier 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…
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…
> 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.
Re: What's new in the Postgres 16 query planner
#10Earlier quoted context omitted.
(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…
One way to look at this is that the most accurate way to "estimate" how fast a certain plan would run, is to actually run it on the full dataset. But that obviously doesn't make sense, as the optimizer is expected to come up with a plan in matter of milliseconds (or less for simple queries) and you don't want your "optimizer stats" to be as big as the whole dataset itself. So optimizer has limited information, by des…
> you don't want your "optimizer stats" to be as big as the whole dataset itself. So optimizer has limited information, by design, and it has to come up with _something_ in a very short amount of time.
This is very true. PostgreSQL does not do any proactive plan caching, so it's important that the planner remains fast. It is possible to adjust the number of stats targets to control the size of the histograms and most common values list. Upping that can be useful for OLAP-type workloads.
> I imagine that in addition to table/column stats, is also uses structural info as its inputs, like existence of (enabled & valid) constraints for example.
Yes. Foreign key constraints are used to assist with join selectivity estimations. PG17 (when released) should be able to make more use of NOT NULL constraints to improve plans.