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

51–60 of 151 posts

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

#51

Post by davidrowley »

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.

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

#52

Post by yxhuvud »

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?

I expect it will have effect fairly often - DISTINCT is something less experienced developers often add to fix their bad queries, and generally the first thing I do when I start to improve performance is to rewrite it to not have to do that. So if these improvements to DISTINCT make it more robust to bad queries, then a lot is gained.

It probably won't fix all issues, but any improvements are welcome.

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

#53

Post by davidrowley »

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.

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.

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

#54

Post by [deleted] »

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.

[deleted]

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

#55

Post by davidrowley »

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 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 compiled. It's quite fast to compile a few expressions, but if you're querying a partitioned table with hundreds of partitions and the plan contains all those partitions, then the JIT compiler has a lot of work to do. The number of expressions is not considered. A colleague and I do have some code to improve this. Unsure if it'll make PG17 at this stage.

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

#57

Post by andybak »

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'm not a DBA by a long stretch.

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

#58

Post by fabian2k »

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…

Postgres has a query planner, I mean this entire post is about improvements to it. So I think there either was some miscommunication or your friend doesn't know anything about Postgres.

There are very large Postges databases that seem to work fine, so Postgres can certainly scale. But SQL Server also has some features that Postgres doesn't, and if those are important for you it might work better for your use case. They are in the end different databases with different strengths and weaknesses.

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

#59

Post by danpalmer »

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…

Yes there is. If what you need is a database that will do pretty much anything well enough, then MSSQL and Oracle are going to manage it. They solve this by throwing money and hardware (more money) at the problem until it works. There's some clever stuff happening in there of course, but fundamentally they've just had much more engineering work over a long time. They can scale out more than Postgres can reasonably do.

That said, Postgres is catching up, and arguably MySQL/MariaDB has always had a good story here. Scale-out options are improving all the time. The landscape has also changed in other ways too, now you can easily have a multi-terabyte Postgres cluster on a small number of machines serving large traffic volumes, and then put your "big data" into a more specialist database. The old world of shoving everything on MSSQL/Oracle may be a bit old-school.

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

#60

Post by williamdclt »

uudecoded wrote: post #18
wiradikusuma wrote: post #7

I use this tool to visualize my queries: https://explain.dalibo.com/ (there's also https://www.pgexplain.dev/ , last time the output was less nice, but now both look the same)

I read your profile and see that you are a CTO of a fintech. Given that, by what method do you navigate that tool's [explain.dalibo.com] assertion of "It is recommended not to send any critical or sensitive information"? Is there an explain plan sanitizer that is helpful for this situation?

Whatever the domain, a query isn't necessarily critical or sensitive. It only is if it contains personal information (eg querying by a bank account number or a name), or if the query itself is part of your competitive advantage (unlikely)
Post reply on HN