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

111–120 of 151 posts

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

#111

Post by davidrowley »

Sesse__ wrote: post #101
davidrowley wrote: post #93

Earlier quoted context omitted.

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

Yeah, nestloop with a cheap inner path (e.g. a lookup into a unique index) should be just fine, so I don't think nestloops as a whole should be banned. (Also, I believe Postgres is pretty much the only place I've seen the concept of a parameterized path described; it's not talked much about in academia, although it is probably really hard to make an index-aware System R planner without it.) I wondered whether it woul…

It's a bit complex to explain here, but I describe an idea I've been considering in https://www.postgresql.org/message-id/CAApHDvo2sMPF9m=i+YPPU...

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

#112

Post by londons_explore »

jasonjayr wrote: post #99
mkl wrote: post #94

Earlier quoted context omitted.

> 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?

Exactly. Web services of the 90's would let a user run a query which returns say 1 million rows, but view them in pages of perhaps 10 rows with a next page/previous page link. The query runs only once, and the million row result set is never created in full - a bit more of it is generated each time the user clicks next page. And if the user clicks previous page, the query needs to be reversible to regenerate previous results.

Very few people use that functionality today to my knowledge. Keeping a query 'open' uses hundreds of megabytes of RAM on the server, so most applications want the query done and ended right away.

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

#113

Post by evntdrvn »

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.

thanks for the work that your team does!

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

#114

Post by nextaccountic »

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…

> 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.

Maybe warrant a new SQL syntax, like

    select * from table limit 10 queue X

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

#115

Post by nextaccountic »

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.

> 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 things

https://www.instaclustr.com/blog/postgresql-docker-and-share...

https://stackoverflow.com/questions/32930787/understanding-p...

(random links from Google just to illustrate the point)

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

#116

Post by thaumasiotes »

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…

> He said he was shocked to discover it didn't even have a query planner.

How did he discover that?

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

#117

Post by devit »

I think it would be really nice to have a "strict mode" (for app testing), where PostgreSQL returns an error if an index would improve the query asymptotically and it doesn't exist (only based on the query itself, not statistics).

And a "CREATE INDICES FOR " command to create the indices (for app upgrades), plus an automatic index creation mode (for interactive and development use).

In general, the system should be architected so that asymptotically suboptimal execution never happens.

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

#118

Post by TylerE »

davidrowley wrote: post #84
sega_sai wrote: post #74

Earlier quoted context omitted.

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

I don't think it's sop muhch a matter of "expensive" as "unknowable".

For instance, if you have a left join (and let's say it can't use an index for whatever reason) - the optimal plan will probably be different if almost every row has a matching row(s) in the join table than if only a few do.

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

#119

Post by rst »

davidrowley wrote: post #98
sgift wrote: post #95

Earlier quoted context omitted.

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

However, at least some ORDER BY queries will have a final sorting step before the client sees the first row. (If you're sorting on a column with an index, it may be able to produce the data in sorted order; if not, though, the final sort seems unavoidable.)

Which means that the capacity to buffer up data until it's all produced is present. But it might still be awkward to make it the default.

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

#120

Post by Sesse__ »

davidrowley wrote: post #111
Sesse__ wrote: post #101

Earlier quoted context omitted.

Yeah, nestloop with a cheap inner path (e.g. a lookup into a unique index) should be just fine, so I don't think nestloops as a whole should be banned. (Also, I believe Postgres is pretty much the only place I've seen the concept of a parameterized path described; it's not talked much about in academia, although it is probably really hard to make an index-aware System R planner without it.) I wondered whether it woul…

It's a bit complex to explain here, but I describe an idea I've been considering in https://www.postgresql.org/message-id/CAApHDvo2sMPF9m=i+YPPU...

Yes, a lot of this seems interesting to go into. I really hope that at some point, someone would find the resources to just try a lot of dumb stuff and see what works in practice. I mean, what we have right now (multiply selectivities together as if they were independent) is also pretty dumb, and there's no good reason why it should be preferred over everything else.

Another avenue is of course trying to avoid the issue to begin with, e.g. through the recent “translation grids” of Müller and Moerkotte for better join selectivities. But I doubt anyone is going to be finding a silver bullet for this anytime soon, so reducing plan risk somehow seems very worthwhile.

Post reply on HN