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

101–110 of 151 posts

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

#101

Post by Sesse__ »

davidrowley wrote: post #93
Sesse__ wrote: post #49

Earlier quoted context omitted.

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 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 would be possible just to add a fixed fuzz to every row estimate, say five rows. It would essentially mean you can never get this issue of a small undercount causing a plan disaster. Overestimating slightly is basically never a big issue as far as I know.

(I should perhaps have considered this when I was actually making a query planner in a previous life, but there were more than enough other things to worry about :-) )

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

#102

Post by mmcgaha »

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…

I have used both for OLTP and data warehousing and both are fine.

I started writing this to say that I would recommend my company move to Postgres if it weren't for vendor provided applications that required SQL Server but then I realized how much work it would be for me to replace the things MS includes like reporting services, integration services, jobs, AD integration, service broker (notify/listen lacks message types). I don't use analysis services any more but when I did that would have been hard to replace too.

This stuff is how they get you. I have no clue how long it would take me to replace all of this but it would not be a good ROI to spend a year replacing what you already have.

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

#103

Post by emmettmoore »

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.

What is the JOB paper? A quick google search didn’t find it, but I’d be interesting to see the recommendations

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

#104

Post by Sesse__ »

emmettmoore wrote: post #103
Sesse__ wrote: post #49

Earlier quoted context omitted.

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.

What is the JOB paper? A quick google search didn’t find it, but I’d be interesting to see the recommendations

https://www.vldb.org/pvldb/vol9/p204-leis.pdf

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

#105

Post by emmettmoore »

Izkata wrote: post #12
tanelpoder wrote: post #8

Earlier quoted context omitted.

One way to look at this is that the most accurate way to "estimate" how fast a certain plan would run, is to actually run it on the full dataset. But that obviously doesn't make sense, as the optimizer is expected to come up with a plan in matter of milliseconds (or less for simple queries) and you don't want your "optimizer stats" to be as big as the whole dataset itself. So optimizer has limited information, by des…

> I don't know much about Postgres optimizer, but I imagine that in addition to table/column stats, is also uses structural info as its inputs, like existence of (enabled & valid) constraints for example. Here's one that surprised me when I found out about it years ago, because I'd never really given it thought: There's a correlation statistic on columns for how well the values in that column match the row order on d…

> Just a nice example of where the obvious "use query hints to make it use the index" would have been the worst option, instead figuring out why postgres didn't want to use it and fixing that resulted in something much better.

I come to the opposite conclusion. Clustering a table results in an access exclusive lock, and due to MVCC the ordering isn’t permanent.

Here, you as the engineer know you’d like to use a sorted index to stream results out even if the overall query end to end is slower due to the I/O cost. In my opinion there should be a way to express this within the query.

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

#106

Post by Izkata »

emmettmoore wrote: post #105
Izkata wrote: post #12

Earlier quoted context omitted.

> I don't know much about Postgres optimizer, but I imagine that in addition to table/column stats, is also uses structural info as its inputs, like existence of (enabled & valid) constraints for example. Here's one that surprised me when I found out about it years ago, because I'd never really given it thought: There's a correlation statistic on columns for how well the values in that column match the row order on d…

> Just a nice example of where the obvious "use query hints to make it use the index" would have been the worst option, instead figuring out why postgres didn't want to use it and fixing that resulted in something much better. I come to the opposite conclusion. Clustering a table results in an access exclusive lock, and due to MVCC the ordering isn’t permanent. Here, you as the engineer know you’d like to use a sorte…

I did test that and like I said, it was strictly worse - went from something like a 2 hour runtime to 5+ hours. Random disk access and not being able to take advantage of the disk cache really is that bad.

Streaming results doesn't mean anything if the total runtime is that much worse, it just means the overall system will take hours longer to complete. And the good version doesn't need to continuously run CLUSTER, the correlation just has to be high enough for it to be the better choice, so we settled on running it once a week - only takes like 2 minutes to run the CLUSTER.

You should be thinking of it the other way around: my final 10 minute result was the ideal situation I wanted, but when circumstances were bad for it, the postgres query planner was smart enough to tell it wouldn't work and switch to the 2-hour plan instead of blindly following the original plan and taking 5 hours.

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

#107

Post by SigmundA »

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…

I develop for MSSQL extensively and PG is missing some things that can be a little surprising.

He might have been referring to the fact that PG doesn't cache query plans or have way to lock them in. PG replans for every statement unless you manually do prepared statements and that only works per connection. MSSQL will cache plans and reuse them and has done this for a very long time. Consequently the planner can take more time planning. Also MSSQL has hints and you can even lock a plan.

PG really needs hints, optimizers are great and all but sometimes I know better and I want to make sure it listens to me.

Also PG has no true clustered indexes all tables are heaps which is something most use all the time in MSSQL, usually your primary key is also set as the clustered index so that the table IS the index and any lookup on the key has no indirection. Interesting SQLite is the opposite tables always have clustered index whether you make one or not, MSSQL give you the choice for heap or index organized tables.

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

#108

Post by SigmundA »

fuy wrote: post #69
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.

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.

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

#109

Post by 946789987649 »

Sesse__ wrote: post #46
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?

The problem is that ORMs have a habit of making very silly queries, and developers insist they cannot write SQL to fix that, because it is somehow impure :-) I doubt this is a very _common_ issue, but I'm not surprised if it shows up every now and then.

> developers insist they cannot write SQL to fix that, because it is somehow impure :-)

Because then they lose many of the benefits of why they used an ORM in the first place. Though I am a big fanboy of JOOQ for exactly this reason.

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

#110

Post by Sesse__ »

davidrowley wrote: post #82
dur-randir wrote: post #75

Earlier quoted context omitted.

If it's not ready for everyone, probably it shouldn't have been made a default, don't you think?

Perhaps, but it might be harsh to say it was the wrong decision when it was made as partitioned tables are far more optimised than when JIT was first worked on. It seems to me, most of the people that have issues with slow JIT times are having these issues with partitioned tables and JIT is slow due to having to compile large numbers of expressions. However, maybe this is the place for me to find out that's not alway…

FWIW, I've seen planning+query times triple with JIT on, with no partitioned tables involved. It just takes forever to JIT sometimes. (This was with Postgres 13 and 14, IIRC.)

Update: I checked some old IRC logs, and found a query that took 1476 ms without JIT and 8754 ms with JIT on. And that is execution time, not planning time!

Post reply on HN