[HN Gopher] Looking Forward to Postgres 19: Query Hints
___________________________________________________________________
Looking Forward to Postgres 19: Query Hints
Author : jjgreen
Score : 109 points
Date : 2026-06-05 15:15 UTC (3 days ago)
(HTM) web link (www.pgedge.com)
(TXT) w3m dump (www.pgedge.com)
| crimsonnoodle58 wrote:
| > How many of us have toggled enable_seqscan to off to force an
| index scan? Or thrown an OFFSET 0 into a subquery to prevent the
| planner from flattening it?
|
| enable_nestloop = off here.
|
| For us, joining many complex views quickly trips the planner up,
| so I'm really glad to see this.
|
| > They break on upgrades.
|
| The irony is so does the planner. I've seen queries working
| perfectly fine in older PG's suddenly run away in newer versions.
| So hints will actually bring stability.
| da_chicken wrote:
| The planner breaking on updates is common for almost all
| RDBMSs. They introduce optimizations that work great for 95% of
| customers, and some will just have queries that now act like
| cardinality is way off or covering indexes are missing.
| mpyne wrote:
| This issue was one of AWS's listed reasons for tending to
| prefer NoSQL style databases over "more performant" RDBMS,
| because of the more consistent worst-case performance, even
| if the result is worse average-case performance, which was
| important in their assumptions for scalability planning.
| throwatdem12311 wrote:
| Every single time I've thought I've needed to try these it made
| it worse.
|
| Every time Claude tries to tell me to try these, it made it
| worse.
|
| Not once has it made it better for me. I'm doing materialized
| view refreshes with a billion rows, which is small enough maybe
| that this doesn't come into play...but so far the planner knows
| best.
|
| If the database can't make it fast with just smart joins and
| filtering then it's the architecture that's a problem, not the
| database.
|
| Usually the only thing I need to do is increase work_mem.
| silon42 wrote:
| The hints are needed when you have a web scale / online
| transaction processing application, where you want to
| guarantee no table scan ever. Ideally, I'd want to rrun
| without statistics on the tables in that case (ie, no smart
| joins).
| robertlagrant wrote:
| I'm not an expert in database hints, but the syntax looks very
| readable and composable. That's great thing to have got right.
| lfittl wrote:
| Its also worth reading the original post by Robert Haas (the
| author of pg_plan_advice) on motivation/design:
| https://rhaas.blogspot.com/2026/03/pgplanadvice-plan-stabili...
|
| Also, I'll add my perspective: I think "EXPLAIN (PLAN_ADVICE)" is
| a key piece to making this a plan stability feature, not (just) a
| hinting feature. The extensibility/framework pg_plan_advice adds
| is a foundation, that over time will over time address the age-
| old "Postgres doesn't have hints" problem, even if the initial
| release doesn't check all the boxes yet, e.g. no way to use
| advice for adjusting row/join estimates.
|
| To give an example on extensibility: Some people that I've spoken
| to are asking "but why is it not a comment-style hint". There are
| reasons why Postgres didn't go that way for this release (comment
| parsing in core is non-existent today, and comments don't work
| correctly e.g. for functions), but its easy to write an extension
| that sets up an advisor hook to parse comments:
| https://github.com/pganalyze/pg_advice_comment
| jbellis wrote:
| man, Tom Lane has hated query hints for literally decades
|
| did he finally come around?
| lfittl wrote:
| I don't think Tom's perspective has necessarily changed (and
| there is certainly concern from others that this could cause
| less reports on planner bugs), but Tom is pretty good about not
| standing in the way of others (i.e. Robert Haas in this case)
| trying to make things work, and being open to new perspectives.
|
| I do know that one of the important criteria for getting this
| in was that a bad advice can't cause the planner to fail, and
| that's something that was explicitly included in the design of
| pg_plan_advice.
| trollbridge wrote:
| Shudder. Flashbacks to having to write optimiser hints in Oracle
| (and the resulting fun times when you'd upgrade the database,
| something would change, and your hints would make a query
| _slower_ ).
| jeffbee wrote:
| I can't believe we're still doing this. You should be able to
| dictate the query execution without involving a planner.
| timacles wrote:
| I really don't think you're considering all the edge cases in
| such a scenario.
|
| Most devs have problems writing decent queries (in some
| situations), now you want to introduce writing the query
| execution plan into the mix ?
| akoboldfrying wrote:
| No one is taking away the existing automatic planning that
| works well 95% of the time. You're welcome to continue
| using that.
|
| The worst thing that could possibly happen is that you give
| it bad advice leading to slow queries, and then the obvious
| first step to fixing that is to drop the manual advice and
| see whether the automatic planner handles it better.
|
| It baffles me that PostgreSQL, which is so deeply
| customisable in almost every other way, resisted this form
| of customisability for so long. This is great news.
| winrid wrote:
| As opposed to not upgrading and the planner picking a plan at
| 3am that is slower?
| aeontech wrote:
| Very interesting - I just installed pg_hint_plan [0] extension a
| few months ago to get around a query that was confusing the
| planner too much. Edge case, but when you need it you really need
| it.
|
| Haven't seen pg_plan_advice before, TIL!
| cryptonector wrote:
| > The advice language is surprisingly expressive for something
| the community resisted for decades.
|
| FINALLY!
|
| I like this design.
|
| And yes, the community resisted this for way too long.
___________________________________________________________________
(page generated 2026-06-09 06:00 UTC)