HN

Comments (23)

• lfittl
  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
• Menu

  crimsonnoodle58
  > 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.
• Menu

  trollbridge
  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).
• Menu

  tschellenbach
  Imagine you have a SAAS app. Microsoft and Apple are customers. You have a table of devices. Type is either windows or osx. How does postgres know how to handle the query devices, where type = OSX properly? How does it know that this matches ~0 or ~100 of rows depending on the customer?This is the main thing the planner doesn't handle well. Postgres was built before SAAS was as big. You have different distributions per customer, and thousands of customers. In most cases the query planner will guess right, but sometimes it will fail and scan millions of rows.
• Menu

  simmschi
  FINALLY!I never understood the issues PG had with hints. Running a non-trivial DB with a non-trivial schema and scale is, well, non-trivial at all. At some point the DB stops being a black box and starts being a tool that you have to know inside and out to avoid performance issues.>The optimizer is usually smarter than you think.Except for when it isn't, and moves heavy calculation inside a nested loop inside a nested loop to avoid an index scan. Nothing is perfect.
• bob1029
  Give the customer what they want, even if it sucks to do so. The alternative it be cast into irrelevance over time. You can run an OSS project however you want, but you can't avoid the consequences of doing so.Principles driven development (we will never or always do X regardless of context) typically comes off as a petty ego trip. The point of the technology is to serve some kind of downstream business. Most people who download Postgres are seeking to solve a real world problem, not to demonstrate their ideological purity.
• robertlagrant
  I'm not an expert in database hints, but the syntax looks very readable and composable. That's great thing to have got right.
• cryptonector
  > 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.
• aeontech
  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!
• Menu

  jbellis
  man, Tom Lane has hated query hints for literally decadesdid he finally come around?
• imJack
  [flagged]
• cgnguyen
  [flagged]
• haeseong
  [dead]
• EvanXue
  [flagged]