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

81–90 of 151 posts

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

#81

Post by s08148692 »

sa46 wrote: post #29
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, I…

> when Postgres picks a stupid plan and can't be convinced to do something reasonable.

In my experience it can always be convinced to make a reasonable plan, but it's not always trivial. Sometimes it's just adding an index, sometimes it's entirely rewriting a query

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

#82

Post by davidrowley »

dur-randir wrote: post #75
davidrowley wrote: post #55

Earlier quoted context omitted.

> One part of this I found rather frustrating is the JIT in newer Postgres versions. The heuristics on when to use appear not robust at all to me. (Author of the blog here and Postgres committer). I very much agree that the code to decide if JIT should be used or not needs work. For PG16, it only takes into account the estimated total cost of the plan and does not take into account how many expressions need to be com…

If it's not ready for everyone, probably it shouldn't have been made a default, don't you think?

Perhaps, but it might be harsh to say it was the wrong decision when it was made as partitioned tables are far more optimised than when JIT was first worked on. It seems to me, most of the people that have issues with slow JIT times are having these issues with partitioned tables and JIT is slow due to having to compile large numbers of expressions. However, maybe this is the place for me to find out that's not always the case. The JIT costing is likely to get an overhaul soon, and if all goes to plan there JIT will be considered per plan node rather than per plan.

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

#83

Post by davidrowley »

londons_explore wrote: post #71
fuy wrote: post #68

Earlier quoted context omitted.

Or it could work in a way that the Planner has access to data about previous runs of each query, and it can use this data to change plans that were proven bad during execution. This way, the first execution would be slow, but Planner could self-learn and better next time. SQL Server has a bunch of similar features in its query optimizer https://learn.microsoft.com/en-us/sql/relational-databases/p... . I'm not sure Po…

Also, many queries might be so slow they never complete, and therefore never populate the cache. (think those queries run by a data scientist with 50 JOIN's)

You'd still need analyze to gather table statistics to have the planner produce plans prior to getting any feedback from the executor. So, before getting feedback, the quality of the plans needn't be worse than they are today.

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

#84

Post by davidrowley »

sega_sai wrote: post #74
londons_explore wrote: post #61

I really wish the postgres query planner would gain the ability to replan a query mid way through execution... Frequently the most pathological queries (ie. the dreadfully slow ones) are because the query planner didn't have some knowledge required of the data distribution and couldn't accurately estimate the cost of some approach to planning the query. This can easily have a 1000x impact on execution time (ie. 1s ra…

I think another way to think about it is to allow 'long planning' queries. I.e. where it is allowed to spend a second, or maybe a few seconds choosing the best plan. That may involve collecting more statistics or running a query for a little bit.

I've considered things like this before but not had time to take it much beyond that. The idea was that the planner could run with all expensive optimisations disabled on first pass, then re-run if the estimated total cost of the plan was above some threshold with more expensive optimisations enabled. It does seem pretty silly to worry about producing a plan in a millisecond for say, an OLAP query that's going to take 6 hours to complete. On the other hand, we don't want to slow down the planner too much for a query that executes in 0.1 milliseconds.

There'd be a few hurdles to get over before we could get such a feature. The planner currently has a habit of making changes to the parsed query, so we'd either need to not do that, or make a copy of it before modifying it. The former would be best.

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

#85

Post by magicalhippo »

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…

We don't use PostgresSQL (yet), but have had issues with random production outages due to planner suddenly deciding to do dumb stuff like full table scans instead of using an index.

To avoid having to sprinkle hints all over, we've added a background job that forces recalculation of statistics once a week or so...

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

#86

Post by CubsFan1060 »

fuy wrote: post #70
PartiallyTyped wrote: post #62

Earlier quoted context omitted.

AWS' Aurora seems to be handling things pretty well tbh and is meant as a drop-in replacement for Postgresql and MySQL.

Aurora is using native Postgres planner, I believe, probably with some minor enhancements.

There is this extension which gives you some flexibility: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide...

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

#87

Post by jhoechtl »

Sesse__ wrote: post #47
jhoechtl wrote: post #39

Why is this released by citusdata instead on postgresql.org? Is this a paid feature only or an open source addition?

Because the poster (who also wrote some of the optimizations in question) works for Citus Data.

There was no subtone in my question, I just wanted to know if this is a paid only feature.

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

#88

Post by jhoechtl »

davidrowley wrote: post #53
Sesse__ wrote: post #47

Earlier quoted context omitted.

Because the poster (who also wrote some of the optimizations in question) works for Citus Data.

Just to clarify. I'm the author of the blog. I work for Microsoft in the Postgres open-source team. All the work mentioned in the blog is in PostgreSQL 16, which is open-source.

Answers it nicely, thank you!

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

#89

Post by CubsFan1060 »

s08148692 wrote: post #81
sa46 wrote: post #29

Earlier quoted context omitted.

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, I…

> when Postgres picks a stupid plan and can't be convinced to do something reasonable. In my experience it can always be convinced to make a reasonable plan, but it's not always trivial. Sometimes it's just adding an index, sometimes it's entirely rewriting a query

Agreed. I've found this setting to be difficult: https://postgresqlco.nf/doc/en/param/default_statistics_targ...

Too low == bad query plan. Oddly, too high == bad query plan.

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

#90

Post by davidrowley »

fuy wrote: post #69
fabian2k wrote: post #31

Query planner improvements are always welcome, it's a very important part of the DB. Though of course most of the time you notice it is when it's not doing what you want ;-). One part of this I found rather frustrating is the JIT in newer Postgres versions. The heuristics on when to use appear not robust at all to me. I've seen this for a rather typical ORM-generated query that is pretty straightforward, but pulls in…

One other thing about JIT that I feel is pretty crazy is that the generated code is not cached. I mean it's the most expensive part of the query execution a lot of the time, how come it's not cached? I couldn't find good reasons for this looking through Postgres mailing lists discussion around JIT. Disabling JIT is the way to go for OLTP workloads.

There's some information about why that does not happen in https://www.postgresql.org/message-id/20211104234742.ao2qzqf...

In particular:

> The immediate goal is to be able to generate JITed code/LLVM-IR that doesn't > contain any absolute pointer values. If the generated code doesn't change > regardless of any of the other contents of ExprEvalStep, we can still cache > the JIT optimization / code emission steps - which are the expensive bits.

A colleague is working on getting this patch into shape. So we might see some caching work get done after the relative pointer work is in.

Post reply on HN