DEVOURED
A Hint of Dependence
PostgreSQL 19 adds pg_plan_advice, providing an official, declarative way to override execution plans without embedding brittle hints into SQL queries.
What: PostgreSQL 19 introduces `pg_plan_advice` and `pg_stash_advice` to pin execution plans externally. This mimics Oracle's SQL Plan Management and SQL Server’s Query Store, avoiding the need for query-text hints like `/*+ INDEX(...) */` that cause maintenance issues during upgrades.
Why it matters: This marks a long-standing shift in the PostgreSQL philosophy: treating plan control as a database administration task rather than an application-layer instruction, preserving the independence between physical storage and the relational model.
Takeaway: Stop using `OFFSET 0` or CTE `MATERIALIZED` hacks and look to `pg_plan_advice` for managing execution stability in PostgreSQL 19.
Deep dive
- The Hint Problem: Hard-coded hints make applications brittle by coupling them to specific indexes and data shapes.
- External Plan Control:
pg_plan_adviceallows forcing specific decisions like join orders or scan types externally. - CREATE STATISTICS: The preferred way to fix bad plans by correcting the optimizer's understanding of data correlation (e.g., between two columns) rather than forcing an outcome.
- Philosophy: The relational model Proscription 6 prohibits internal-level constructs in the query language to maintain physical data independence.
Decoder
- Query Plan: The sequence of operations a database engine performs to retrieve data.
- Optimizer: The component that determines the most efficient way to execute a SQL query.
- Selectivity: The estimated proportion of rows returned by a query condition, used by the optimizer to choose an index or join type.
Original article
Full article content is not available for inline reading.
Explore all 58 articles in today's digest
Fresh dev news, AI-digested daily. Never miss a trend at devoured.fyi