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

91–100 of 151 posts

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

#91

Post by hyperpape »

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)

I'm sure there are reasons the implementation might not be easy, but conceptually this seems fixable. You just need a lower bound of "how bad can this plan be?" and that doesn't require completing the query, just observing that it's been running for a long time and/or used unreasonable amounts of resources.

Also, is the problem with the 50 join query that the planner screws it up, or that it's fundamentally doing too much?

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

#92

Post by codeflo »

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…

If the sort order isn't fully determined by a query, can the query plan influence the result order? If so, what you're suggesting might be nearly impossible. The new query wouldn't be able to just skip the first N results, it would have to match each individual row against a dictionary of previously sent ones.

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

#93

Post by davidrowley »

Sesse__ wrote: post #49
davidrowley wrote: post #5

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

I do wonder if one could eventually just turn off nestloops in such a case (e.g. inner side contains a seqscan), like the JOB paper recommended. Yes, it will have marginally higher estimated cost, but the upside is _much_ safer query plans when the statistics are off.

That could be useful if there was a way to just disable non-parameterized nested loop, however enable_nestloop=0 also disables parameterized nested loops. Parameterized nested loops are useful to avoid sorting or hashing some large relation when only a small subset of that relation is likely to have a join partner. This is even more true when you consider that since PG14, Memoize exists to act as a cache between Nested Loop and its inner subnode to cache previously looked-up values.

It's also important to consider that with enable_nestloop=0, when Nested Loop must be used (e.g for a CROSS JOIN) that the cost penalty that's added to reduce the chances of Nested Loop being used can dilute the costs so much that the query planner can then go on to make poor subsequent choices later in planning due to the costs for each method of implementing the subsequent operation being so relatively close to each other than the slightly cheaper one might not even be considered. See add_path() and STD_FUZZ_FACTOR. So, running enable_nestloop=0 in production is not without risk.

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

#94

Post by mkl »

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…

> Postgres also allows a client to, midway through a query, request that the query reverse direction and re-return previous results in reverse order.

What is that useful for?

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

#95

Post by sgift »

davidrowley wrote: post #64
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…

(blog author and Postgres committer here) I personally think this would be nice to have. However, I think the part about sending of tuples to the client is even more tricky than you've implied above. It's worse because a new plan does not even guarantee that the same tuples are returned. e.g. if you wrote: SELECT * FROM table LIMIT 10, there's no ORDER BY so which tuples are returned is non-deterministic. It may be e…

I have no knowledge how common queries with ORDER BY vs no ORDER BY are, but that sounds like a first implementation which only works if ORDER BY is present would be easier and still useful? Or do you think that's not common enough to justify the effort?

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

#97

Post by mike_hock »

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…

Alternatively, associate some confidence value with the statistics and make conservative choices when confidence is low and/or the expected difference is small.

Sometimes a sequential scan is faster than index lookups for each item. But the sequential scan is a risky gamble, whereas the index lookups have robust performance characteristics. It's not always clear which choice is the conservative one, but often it is.

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

#98

Post by davidrowley »

sgift wrote: post #95
davidrowley wrote: post #64

Earlier quoted context omitted.

(blog author and Postgres committer here) I personally think this would be nice to have. However, I think the part about sending of tuples to the client is even more tricky than you've implied above. It's worse because a new plan does not even guarantee that the same tuples are returned. e.g. if you wrote: SELECT * FROM table LIMIT 10, there's no ORDER BY so which tuples are returned is non-deterministic. It may be e…

I have no knowledge how common queries with ORDER BY vs no ORDER BY are, but that sounds like a first implementation which only works if ORDER BY is present would be easier and still useful? Or do you think that's not common enough to justify the effort?

You have to remember that because the query has an ORDER BY, it does not mean the rows come out in a deterministic order. There'd need to be at least an ORDER BY column that provably contains unique values. Of course, you could check for that, but then I don't think that's the end of the complexity. Things like SKIP LOCKED skip over rows which we can't immediately lock. If the first time we couldn't lock the lowest order row and output the 2nd, then aborted, replanned, then next time the 1st row wasn't locked, we'd then output the rows in the wrong order. It's probably possible to figure out all these cases and not do it when there's some hazard, but it sounds very tricky and bug-prone to me.

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

#99

Post by jasonjayr »

mkl wrote: post #94
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…

> Postgres also allows a client to, midway through a query, request that the query reverse direction and re-return previous results in reverse order. What is that useful for?

Paginating with an open cursor, perhaps?

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

#100

Post by jeltz »

dur-randir wrote: post #76
czl wrote: post #37

Earlier quoted context omitted.

Did you try using prepared statement so the compilation is done once and compiled results are reused each time that query is run?

There's a separate can of worms with prepared statements. Two main are: - parameters are opaque to planner, so it prefers (or is even forced to?) to choose generic vs specific plans - it doesn't play nice with pg_bouncer in transaction mode

Pgbouncer supports prepared statements these days.
Post reply on HN