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

31–40 of 151 posts

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

#31

Post by fabian2k »

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 a lot of other tables via joins. It runs in a few milliseconds without the JIT, but the JIT spent 1-1.5 seconds doing its thing on top of that and makes it incredibly slow for tiny amounts of data.

I know now to just disable the JIT, but this feature can give a pretty terrible impression to users that don't know enough yet to figure out why it's slow. I like Postgres a lot, but enabling the JIT just seems far too dangerous as a default setting to me.

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

#32

Post by alexey-salmin »

davidrowley wrote: post #23
alexey-salmin wrote: post #19

Earlier quoted context omitted.

If not hints and manual plans then we need a way to "freeze" automatic optimizer plans however optimal they are after each tested release. The optimizer killing your heavily-loaded production because it randomly switched a frequent query to a bad plan is pretty annoying. People mostly put up with this because for a single-instance DB you can easily maintain 4x spare CPU capacity but it's impossible at scale.

It sounds like it would have to be an opt-in feature which could be applied per query, as otherwise wouldn't it be equally as annoying if the planner didn't adapt to the table data changing? What may be better is if the executor provided feedback to the planner to tell it the new plan was worse than the old one. With that, you might be able to recover much more quickly and less likely to get a midnight phonecall. The…

> wouldn't it be equally as annoying if the planner didn't adapt to the table data changing?

Not really, because at scale tables don't change suddenly unless you release something -- and that will re-generate the plans after the perf testing.

Whatever changes slowly accumulate over time will be covered in the next release which normally should be very frequent, daily or more.

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

#33

Post by dagss »

davidrowley wrote: post #3
riku_iki wrote: post #2

Why wouldnt they implement hints..

There is a pg_hint_plan extension. 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. I don't have a link to hand, but last time I recall a discussion on hints there was no general objection to them, providing the implementation could be done in a way that didn't force the planner's hand too strongly and still allowed i…

I feel the best abstraction for hints would be to declare on tables how large you expect them to be -- and even throw errors if query plans with a good scaling cannot be found.

Say I could declare "assume this table will grow very large", "assume this table will be a small enum table".

And then it would use that information instead of actual table size to guide planning AND throw an error for any query doing a full table scan on a declared-to-be-large table -- so that missing indices can be detected instantly, not after running in prod for some days/weeks.

Google Data Store has this property and it is a joy to work with for a backend developer.

What I am usually after is NOT the fastest plan, but the most consistent and robust plan across test and prod environments.

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

#34

Post by somat »

davidrowley wrote: post #3
riku_iki wrote: post #2

Why wouldnt they implement hints..

There is a pg_hint_plan extension. 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. I don't have a link to hand, but last time I recall a discussion on hints there was no general objection to them, providing the implementation could be done in a way that didn't force the planner's hand too strongly and still allowed i…

I suspect the ideological problem with hints is that if the planner is producing a poor query, then the correct place to fix that is in the planner.

While I agree with this viewpoint, The problem is that most people don't want to be a Postgress dev, To actually enable people to fix the planner it would have to be exposed as a runtime service. And unless there was a lot of diligence the planner script would quickly degrade into an unmaintainable mess(low blow: just like most schemas.)

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

#35

Post by alexey-salmin »

winrid wrote: post #28
davidrowley wrote: post #23

Earlier quoted context omitted.

It sounds like it would have to be an opt-in feature which could be applied per query, as otherwise wouldn't it be equally as annoying if the planner didn't adapt to the table data changing? What may be better is if the executor provided feedback to the planner to tell it the new plan was worse than the old one. With that, you might be able to recover much more quickly and less likely to get a midnight phonecall. The…

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

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

#36

Post by aidos »

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…

We hit a curios bug recently on production with the JIT.

I had apt updated a couple of packages and then all of a sudden a bigger query we run every 5 minutes was failing. Or rather, Postgres just started silently hanging up the connection mid query execution with even putting anything in the logs.

Took me a while of running manually in explain to see that the variations of the query that ended up using the JIT broke while those that didn’t were ok. Disabled the JIT and everything was ok again.

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

#37

Post by czl »

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?

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

#38

Post by Semaphor »

dagss wrote: post #33
davidrowley wrote: post #3

Earlier quoted context omitted.

There is a pg_hint_plan extension. 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. I don't have a link to hand, but last time I recall a discussion on hints there was no general objection to them, providing the implementation could be done in a way that didn't force the planner's hand too strongly and still allowed i…

I feel the best abstraction for hints would be to declare on tables how large you expect them to be -- and even throw errors if query plans with a good scaling cannot be found. Say I could declare "assume this table will grow very large", "assume this table will be a small enum table". And then it would use that information instead of actual table size to guide planning AND throw an error for any query doing a full t…

Oh, that sounds really cool. I like declaring expected size, but "throw on certain behaviors" would be something I’d love in MS SQL.

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

#40

Post by fabian2k »

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?

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.
Post reply on HN