PostgreSQL 19's pg_plan_advice: Finally, We Can Tweak the Planner

PostgreSQL 19 came out yesterday. There’s one feature that’s making the rounds in DBA circles - pg_plan_advice. I’ve been wanting something like this for ages.

Look, PostgreSQL’s query planner is generally solid. But there are times when it drives you up the wall. You twiddle with random_page_cost, you mess with enable_seqscan, and yet the planner does whatever it wants anyway. Oracle has hints. SQL Server has hints. PostgreSQL? Nah, we don’t do that here. Or at least, we didn’t.

Now we do.

What’s This Thing Actually?

pg_plan_advice is a contrib module that ships with 19. You’ll need to install it separately, but that’s no big deal. The really cool part is how it works: you can either let it analyze a plan you like and generate advice from that, or you can hand-write your own. Both approaches are valid.

Just don’t come crying to me if you write terrible advice and your queries start crawling. The documentation is upfront about this - garbage in, garbage out. Play nice with it.

Installation

1
CREATE EXTENSION pg_plan_advice;

Simple enough. Let’s look at what you can actually do with it.

Making the Planner Listen

Here’s a scenario: you’ve got a query that’s running perfectly. The plan is beautiful. You want to make sure it stays that way, even when statistics change and the planner gets Ideas. Here’s the trick:

1
SELECT * FROM pg_plan_advice('SELECT * FROM orders WHERE customer_id = 123');

What comes back looks something like this:

1
SEQ_SCAN(orders) INDEX_SCAN(orders orders_cust_idx)

Plain English: do a sequential scan on orders, but grab the index for that customer lookup.

Now the fun part. Take that output and use it:

1
2
SET pg_plan_advice TO 'SEQ_SCAN(orders) INDEX_SCAN(orders orders_cust_idx)';
SELECT * FROM orders WHERE customer_id = 456;

Boom. The planner now has to follow your rules. Not its own judgment. Yours.

The Syntax - What Can You Control?

Let me walk through the main things you can tweak.

Scans

1
SET pg_plan_advice TO 'INDEX_SCAN(products prod_name_idx)';

This forces an index scan on prod_name_idx when querying products. You can also do INDEX_ONLY_SCAN, BITMAP_HEAP_SCAN, or just SEQ_SCAN if you’re feeling contrarian.

Joins

1
SET pg_plan_advice TO 'JOIN_ORDER(customers orders line_items)';

That example means: join customers to orders first, then bring in line_items. You can also specify join methods directly:

1
SET pg_plan_advice TO 'HASH_JOIN(orders)';

Want a nested loop instead? Use NESTED_LOOP_PLAIN. Merge join? MERGE_JOIN. Pick your poison.

Parallel and Partitioning

1
2
SET pg_plan_advice TO 'GATHER(orders)';
SET pg_plan_advice TO 'NO_GATHER(orders)';

GATHER forces parallel execution. NO_GATHER disables it. For partitioned tables, there’s PARTITIONWISE to control whether partition-wise joins happen.

A Practical Example

Picture this: you’ve got a reporting query that’s important but slow.

1
2
3
4
5
6
SELECT o.*, c.name, SUM(l.quantity * l.price) as total
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN line_items l ON o.id = l.order_id
WHERE o.created_at > '2025-01-01'
GROUP BY o.id, c.name;

You ran EXPLAIN. The planner keeps choosing nested loops when you know - you just know - a hash join would crush it. You’ve tried everything else.

Try this:

1
2
SET pg_plan_advice TO 'HASH_JOIN(orders) HASH_JOIN(customers line_items)';
-- run your query

It should listen now.

There’s a Catch

Of course there’s a catch. The docs are straightforward about limitations:

Aggregation methods? Can’t control those. Hash aggregate vs sort? No dice.
Cost estimates? You’re stuck with what the planner gives you.
GEQO? This doesn’t always play nice with the genetic optimizer.
DDL changes? Drop an index your advice references and everything breaks.

Also, this is PostgreSQL 19 only. Older versions need to wait.

When Does This Make Sense?

Honestly? Most of the time, you don’t need this. The planner is smart. Really smart. But every now and then, you run into a situation where:

A critical query keeps flipping plans because statistics changed overnight
You’re migrating from Oracle and you really, really need that hint syntax
You’ve exhaustively tested it and the planner is simply wrong, and you have the benchmarks to prove it

Those are the times to reach for this.

My Two Cents

PostgreSQL has historically resisted hinting. The philosophy was: if you need to hint, something is wrong upstream. Schema design, statistics, whatever. And honestly, that’s usually correct.

But the community came around. pg_hint_plan existed as an extension for years. This is better - it’s built-in, it’s official, and it has that round-trip guarantee where advice generated from a plan will work the same way when fed back.

Is it dangerous? Sure, if you use it carelessly. But so is a chainsaw. Sometimes you just need the right tool.

Go experiment. Just maybe not on Monday morning.