Watch our latest webinar on-demand: Postgres on AWS, What's New & How to Make the Most of It
Logo

Waiting for Postgres 19: Better Planner Hints with Path Generation Strategies

Lukas Fittl
By Lukas Fittl
February 05, 2026

In today’s E121 of “5mins of Postgres” we're talking about the upcoming Postgres 19 release, and how better extensibility of the Postgres planner will enable better plan management and planner hint extensions.

We dive into the recently committed "path generation strategies" change, the proposed pg_plan_advice extension it was developed for, and show how pg_hint_plan can evolve and lose 2,500 lines of code!



Share this episode: Click here to share this episode on LinkedIn. Feel free to sign up for our newsletter and subscribe to our YouTube channel.

Transcript

Welcome back to 5mins of Postgres! Today we talk about the upcoming Postgres 19 release and a patch that was just committed to the development branch that will enable better plan management and planner hint extensions for Postgres.

This commit by Robert Haas, that allows plugin control for path generation strategies is basically a way to give plugins aka extensions, a way to influence the Postgres query planner in its earliest stages.

The role of "paths" in the Postgres planenr

When the Postgres query planner builds a query plan, it first generates what are called paths. And paths might be something like, you know, for this table use a sequential scan, or alternatively, use an index scan on this index or an index scan on this other index.

If you join these two tables, use a hash join, use a merge join and such. And then Postgres basically does a combination of throwing away some of these paths early on because they have worse costs, and in other cases it keeps the paths to then do a full cost comparison and then keeps the best plan basically by its cost.

Now the reason you need a better way to do this is because the challenge right here is, as an extension today, you don't really get away of saying, Hey, Postgres, make sure you keep this plan. Postgres might choose to throw away a plan. And so even though today you can modify these paths, without this patch, you're kind of at a loss in certain cases.

How pg_hint_plan implements hints today

If you're familiar with today's mechanism for influencing query plans, the most popular extension is pg_hint_plan. pg_hint_plan works by basically putting in a query comment in the beginning of your query, it says something like sequential scan on "a" and pg_hint_plan then does some quirky things to make this work.

So the quick gist of how it works is, hint plan basically does a combination of influencing Postgres settings as in, you know, global settings that influence query plans, like "enable_seqscan", and then it also copies a bunch of code. So for example, we can see here "make_join_rel.c" and "core.c", those are files that basically have some of the regular Postgres source code, because hint plan needed to modify some of these things, needed to rerun some of these things, and those were hidden away in internal Postgres files.

We can see an example here, so when you think of, how does hint plan avoid a hash join, if I want a nested loop or merge join, well, it basically goes and it turns off this global setting of enable_hashjoin. And it just does this a lot whilst the planner is running and if the planner doesn't give it enough ways to modify it, it basically just reruns the planner with a little bit of modified code.

And so this works, right? pg_hint_plan is great because it exists. But it also, doesn't work in all cases. Like if you have complex queries, you can't really target the right relations. In some cases it has a little bit of overhead when you apply a hint. And so it's, it's not great, right?

Why don't we have built-in planner hints & the pg_plan_advice proposal

And so with the change that Robert committed to Postgres 19, we basically have better infrastructure to build more reliable ways of doing planner hits. The Postgres community has resisted calls over the years to build something in core, and part of the motivation for improving how you can control the planner with an extension is that Robert is proposing a new extension for contrib in Postgres called "pg_plan_advice".

And the idea behind pg_plan_advice is basically rethinking: What is the problem with planner hints? Why don't we want people to do planner hints? And part of that is, you know, if you frame it as a improve or influence query plans in a very arbitrary way, it's just hard to really think through the implications.

And so with pg_plan_advice, what Robert basically set out was, the way I understand it: The goal is that plan advice for a given query plan, can emit a string of advice, and if you pass that string back to Postgres, you get the same plan.

It's not necessarily about a human coming in and saying "let me go and tweak the plan some way". But rather it's "can I have repeatable plans", so I get the same plan, the next time I run it, even if, statistics changed. I want to have stable query plans, which I think a lot of people care about. And so I think this is a really great design goal.

And so hopefully pg_plan_advice itself makes it into Postgres 19. To be clear, this is still up for discussion, still up for review. If you are a Postgres planner expert, you should be reviewing this patch. That would be great.

The Postgres 19 change for path generation strategies

But, independently of whether plan advice gets in, even if this wouldn't make Postgres 19, a lot of necessary infrastructure was added with this one commit here by Robert. Now let me just give you a really brief version of what this added.

So if we look at the changes here, inside the Postgres planner, basically what was added was a bitmask. The idea is we have this new "pgs_mask" mechanism, and you set this on RelOptInfo, a structure that you have available in various planner hooks.

And so you can now modify this "pgs_mask" to tell Postgres for this particular table, don't use sequential scans. There was basically no way of doing this before. You had to do the quirky stuff that hint plan had to do.

How pg_hint_plan can evolve

What I ended up doing, is basically look at these mechanisms and adapt hint plan to use this. Because even if we get pg_plan_advice in 19, which would be amazing, I think we will be facing the reality that a lot of people have built on pg_hint_plan.

I think pg_hint_plan will continue to exist. And what's really great about the commit to 19 is that it should make the maintenance of hint plan much easier because it avoids hint plan copying any code.

Let me show you the specifics here. So with this proposal here that I put together, we're basically seeing that hint plan can lose 2500 lines of source code.

And to quote one of the pg_hint_plan maintainers, "any change that drops code is a good one". The good news is, here we can drop code and the regression tests still pass. Specifically we lose all these copies of Postgres source code. We also have "pg_hint_plan.c" like the core file has 200 lines less because it's now much simpler because it basically only has to modify this "pgs_mask" versus trying to do all kinds of creative strategies.

And so just to show you one more example, when we look at how hint plan modifies which tables get joined, in which way now hint plan just modifies this mask, and can set things like, if somebody wants a merge join, we'll make sure we get a merge join, there's different ways that Postgres does merge joins. All of this is just a lot simpler and closer to the intent that the user specifies, versus having to do a lot of workarounds to try to get Postgres to do the right thing.

I think this is great. A big thank you to Robert Haas for driving this effort forward, and I hope we see the full pg_plan_advice change in 19. We'll see if that holds true. I'll keep you updated. But even if we don't, I think this will be a core foundation for new plan management extensions and new hint extensions in Postgres for many years to come.

I hope you learned something new from E121 of 5mins of Postgres. Feel free to subscribe to our YouTube channel, sign up for our newsletter or follow us on LinkedIn to get updates about new episodes!

What we have discussed in this episode of 5mins of Postgres

Enjoy blog posts like this?

Get them once a month to your inbox