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

121–130 of 151 posts

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

#121

Post by fakwandi_priv »

SigmundA wrote: post #108
fuy wrote: post #69

Earlier quoted context omitted.

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.

Unlike say MSSQL or Oracle PG does not cache plans at all. I think this is mostly due to its multiprocess architecture vs just sharing in memory plans between threads. In MSSQL a plan can take a while to optimize including jitting if needed but it doesn't matter that much because all plans are cached so when that statement comes in again the plan is ready to go.

https://www.postgresql.org/docs/current/plpgsql-implementati...

Seems like these will get cached at some point.

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

#122

Post by Izkata »

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

There's also "the query plan you want won't do what you expect, so I'm giving you the next best thing until you tell me the right way to store the data": https://news.ycombinator.com/item?id=39311536

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

#123

Post by appplication »

davidrowley wrote: post #67
appplication wrote: post #27

Earlier quoted context omitted.

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.

I see you’re definitely familiar with the space. I think the condition was using ‘or array_contains’ in the join.

This was really the only resource I found acknowledging it. It sounds like it has do with presumption of nulls (e.g. spark can’t assume they won’t be there) but it would be great to be able to say “don’t worry spark I promise there are no nulls/if there are just disregard”) https://kb.databricks.com/sql/disable-broadcast-when-broadca...

The way we go around this feels so brutish. Literally just did two separate joins and then unioned the results. The recommendation to use ‘not exists’ couldn’t be applied as array_contains must be using ‘in’ under the hood and couldn’t be changed.

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

#124

Post by mgsouth »

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?

SELECT x, random() FROM foo ORDER BY x;

SELECT foo.x, bar.y FROM foo WHERE bar.z > random() ORDER BY foo.x;

SELECT x FROM foo ORDER BY x; -- Meanwhile, concurrent updates are happening to foo, so re-running the query gets a different result set.

"We choose to do this thing and the others, not because they are easy, but because we ask 'how hard can it be?'"

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

#126

Post by SigmundA »

fakwandi_priv wrote: post #121
SigmundA wrote: post #108

Earlier quoted context omitted.

Unlike say MSSQL or Oracle PG does not cache plans at all. I think this is mostly due to its multiprocess architecture vs just sharing in memory plans between threads. In MSSQL a plan can take a while to optimize including jitting if needed but it doesn't matter that much because all plans are cached so when that statement comes in again the plan is ready to go.

https://www.postgresql.org/docs/current/plpgsql-implementati... Seems like these will get cached at some point.

Only within a session unless something has changed there, that means no sharing between clients or even from one connection to the next form the same client.

MSSQL caches plans globally and can be used across sessions and connections if the statement text is the same.

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

#127

Post by SigmundA »

nextaccountic wrote: post #115
SigmundA wrote: post #108

Earlier quoted context omitted.

Unlike say MSSQL or Oracle PG does not cache plans at all. I think this is mostly due to its multiprocess architecture vs just sharing in memory plans between threads. In MSSQL a plan can take a while to optimize including jitting if needed but it doesn't matter that much because all plans are cached so when that statement comes in again the plan is ready to go.

> I think this is mostly due to its multiprocess architecture vs just sharing in memory plans between threads You can share stuff with a multiprocess architecture just fine (either through IPC or just plain shared memory + synchronization) It's true that threads share memory by default, but processes can opt into sharing memory if they wish. And it appears that Postgres already makes use of shared memory for some thi…

PG shares data between process not code as far as I know and definetly not any plans. Sharing jitted code is not straight forward as pointers will be different per process.

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

#128

Post by SigmundA »

czl wrote: post #37
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…

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

Only in the same session...

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

#129

Post by darksaints »

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…

What I think could potentially be done is allow threshold-based alternate plans. For a pseudo example, “if subquery A returns 8 records or fewer, use this plan for subquery B, else use that plan.” It’s an explicit admission that the query planner doesn’t have enough information to make a good decision up front, but can easily be made at a later point in time while in the middle of execution.

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

#130

Post by maximegarcia »

davidrowley wrote: post #51
twic wrote: post #43

I'd be interested to know how often these changes have an effect in real queries. The "Use Limit instead of Unique to implement DISTINCT, when possible" change in particular feels like it would only apply to very silly queries. Do the PostgreSQL developers have any source of information about this?

(Author of the blog and that feature here) This one did crop up on the pgsql-hackers mailing list. I very much agree that it's unlikely to apply very often, but the good thing was that detecting when it's possible is as simple as checking if a pointer is NULL. So, it's very simple to detect, most likely does not apply very often, but can provide significant performance increases when it can be applied.

That would be nice to also optimize SELECT DISTINCT foo FROM bar. It is usually very poor on big tables and we have to do recursive CTE. This comes a lot with admin builders for filters ().
Post reply on HN