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

61–70 of 151 posts

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

#61

Post by londons_explore »

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 rather than 1ms).

You will never have 100% accurate table stats - there is always some odd joint distribution you will not capture.

So instead, allow the query to start, and if progress isn't as fast as the planner expects, feed current progress info back to the planner (pages scanned, tuples matching), and replan with that new data. If the updated plan shows it's quicker to discard current results and restart with a new approach, do that.

Unfortunately, postgres does streaming queries (ie. the first results of a query are sent back to the client before the query is done), which means that significant infrastructural changes would be needed to allow a midway change of plan. Any new plan would need to keep track of which results had already been sent to the client so they aren't resent. Postgres also allows a client to, midway through a query, request that the query reverse direction and re-return previous results in reverse order. That adds a lot of complexity.

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

#62

Post by PartiallyTyped »

andybak wrote: post #57

A friend of mine is a Microsoft DBA for mid-sized companies and was proclaiming how you can't do anything serious with Postgres. He said he was shocked to discover it didn't even have a query planner. Leaving mocking him to one side for a moment - is there any plausibility to his broader claim that MSSQL can handle things at a scale where Postgres would be a poor choice? My gut instinct is that this is nonsense but I…

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

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

#63

Post by williamdclt »

somat wrote: post #34
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 suspect the ideological problem with hints is that if the planner is producing a poor query, then the correct place to fix that is in the planner. While I agree with this viewpoint, The problem is that most people don't want to be a Postgress dev, To actually enable people to fix the planner it would have to be exposed as a runtime service. And unless there was a lot of diligence the planner script would quickly de…

I don't think anybody disagrees that the correct place to fix that is the planner, people just want an escape hatch so that when poor queries happen they can do _something_ instead of waiting for a fix to be written, a release to include it, and upgrading their database. It's a pretty reasonable ask, I think!

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

#64

Post by davidrowley »

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 easier to do something like queue up X tuples and just start sending those out when the queue is full. When the queue is full, we can say that it's too late to replan and we're locked into the current plan. People can set X to what they want to increase the time that the plan can change at the expense of using more memory and higher latency to get the first tuple.

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

#65

Post by Sesse__ »

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…

You may be interested in this paper (and the papers it references): https://arxiv.org/pdf/1902.08291

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

#66

Post by londons_explore »

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…

For many queries, even setting X=1 would probably have big benefits. If it takes far longer than expected to find the first result, it's probably time for a new plan.

Implementing only the X=1 case would also dramatically simplify the design of such a feature. Limiting it to read only queries would also make everything much simpler.

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

#67

Post by davidrowley »

appplication wrote: post #27
viraptor wrote: post #21

Earlier quoted context omitted.

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

There certainly are valid reasons for this. For example, adding a join condition with an OR clause. The only join operator that supports non-equi joins is Nested Loop. If you went from a Hash or Merge join to that, then you'd likely notice some performance degradation. If you have a link to anywhere you've asked for help on this, then I'd be interested to see more details.

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

#68

Post by fuy »

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…

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 Postgres has infrastructure to do that, though, because it doesn't have shared plan cache, for example.

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

#69

Post by fuy »

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.

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

#70

Post by fuy »

PartiallyTyped wrote: post #62
andybak wrote: post #57

A friend of mine is a Microsoft DBA for mid-sized companies and was proclaiming how you can't do anything serious with Postgres. He said he was shocked to discover it didn't even have a query planner. Leaving mocking him to one side for a moment - is there any plausibility to his broader claim that MSSQL can handle things at a scale where Postgres would be a poor choice? My gut instinct is that this is nonsense but I…

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.
Post reply on HN