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

41–50 of 151 posts

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

#41

Post by Sesse__ »

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…

I believe the JIT is pretty much a failure, yes. It was well-meant, but LLVM just isn't the right tool for this. I've turned it off globally. (I don't use any ORMs, so it's not simply about strange query patterns.)

Query parallelization, on the other hand, can actually be useful—and most importantly, rarely hurts.

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

#42

Post by trollied »

fabian2k wrote: post #40
czl wrote: post #37

Earlier quoted context omitted.

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

As far as I understand prepared statements don't help here as the JIT output is not saved but generated for each execution. In this case I'm also using an ORM (EF Core) which doesn't expose the ability to prepare statements.

Most optimisers cache the execution plan based on a hash of the query, hence reuse when using prepared statements vs not.

Oracle has an option to detect literals (CURSOR_SHARING) and essentially replace them with binds internally, in order to increase performance and stop the query pool getting filled up with the same statement(s).

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

#43

Post by twic »

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?

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

#44

Post by joachimma »

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?

Where I used to work we allowed duplicate email addresses in the user table for legacy reasons, but we did not want any new entered in the db, so we ran a "select distinct email from users where email = ?" query before creation of new users. I don't think we had more than a 100 rows with the same email though. Most of the duplicates were test users which could have been removed, but I digress.

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

#45

Post by cnity »

davidrowley wrote: post #14
klysm wrote: post #11

Earlier quoted context omitted.

The tool is great and I use it, but I don't really have a deep enough understanding to know how to fix issues in my approach from what looks bad in the plan.

It's pretty hard to tell if a plan is good or bad from EXPLAIN without using the ANALYZE option. With EXPLAIN ANALYZE you can see where the time is being spent, so can you get an idea of which part of the plan you should focus on. To know if it's a bad plan, it does take quite a bit of knowledge as you need to know what alternative plans could have been used instead. It takes quite a bit of time to learn that stuff.…

To be honest, you can get quite a lot of mileage out of EXPLAIN by focusing on which indices the plan selected (or did not select). For this reason it's really worth understanding your indices and what value they add (or remove!).

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

#46

Post by Sesse__ »

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.

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

#48

Post by davidrowley »

trollied wrote: post #42
fabian2k wrote: post #40

Earlier quoted context omitted.

As far as I understand prepared statements don't help here as the JIT output is not saved but generated for each execution. In this case I'm also using an ORM (EF Core) which doesn't expose the ability to prepare statements.

Most optimisers cache the execution plan based on a hash of the query, hence reuse when using prepared statements vs not. Oracle has an option to detect literals (CURSOR_SHARING) and essentially replace them with binds internally, in order to increase performance and stop the query pool getting filled up with the same statement(s).

Postgres does not currently reuse JIT-compiled code. JIT will run each execution of the query. This may change in the next few years, likely starting with tuple deforming, as that's fairly reusable, per table for any query.

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

#49

Post by Sesse__ »

davidrowley wrote: post #5
riku_iki wrote: post #4

Earlier quoted context omitted.

> 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. they will work in prod in the way engineer is expecting. Current planner also can change its mood in unpredictable way and often generates sub-optimal plans for complex queries, because can't reason about what specific subquery will return exactly, and you learn about…

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

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

#50

Post by masklinn »

alexey-salmin wrote: post #35
winrid wrote: post #28

Earlier quoted context omitted.

> It sounds like it would have to be an opt-in feature which could be applied per query That's called a hint :)

Not exactly because a hint contains information that may become obsolete. A directive that freezes a known good auto-plan until the next release will not become obsolete as long as you keep releases rolling.

What I’d like is the ability to hand postgres an execution plan, whether that is a plan I retrieved from Postgres’s own planner, or one I wrote by hand (possibly a mix of the two).

Give the API a scary name if you want, but getting 3 Am calls because the planner suddenly decided to go off the rails or seeing pages of convoluted SQL because in (current version) it’s what it takes to get the plan you need is not fun.

It’s long past time for database developers to accept that the high level is not always the right solution, it should be that in 95 or 99% of cases, and the better it is the better my life is, but sometimes you got to write exactly what you need.

Most language designers are fine with it and will allow either embedding or calling into lower-level language (down to hand-crafted assembly), it would be great if database devs could get on with that program.

Post reply on HN