The Eternal Clash Over Query Optimization: Why Execution Instructions Belong Outside SQL

By Vik Fearing
Editor and publisher of The Gresql Post, ISO SQL standard editor, and PostgreSQL contributor.

As a member of the ISO SQL standards committee, I often find myself reviewing what developers, database administrators, and software architects believe the relational database language ought to contain. Recently, a widely circulated LinkedIn post by engineer Hussein Nasser reignited a perennial industry debate: "The SQL standard should include advanced hinting, preferably down to the way the index itself is scanned. That is if we want to build scalable database applications."

Nasser is far from alone. Across the enterprise landscape, a bad execution plan is a genuine operational emergency, often waking engineers at 3:00 AM when proper, root-cause troubleshooting is out of the question. When a query planner stumbles, reaching for an execution hint feels like a pragmatic lifeline.

Yet, embedding execution instructions directly inside SQL queries undermines the core architecture of relational databases. By tracing the history of data independence, examining how major database engines handle plan control, and analyzing the emergence of modern solutions like PostgreSQL’s newly introduced plan advice and extended statistics, we can see why execution instructions do not belong in SQL—and what the query planner truly needs instead.

Main Facts: The Core Architecture of Data Independence

To understand why query hints are structurally hazardous, we must return to the foundational principle of the relational model: data independence.

When Edgar F. Codd introduced the relational model in his landmark 1970 paper, his very first sentence established its primary protective goal: "Future users of large data banks must be protected from having to know how the data is organized in the machine."

Codd explicitly identified "indexing dependence" as a critical hazard to be eliminated. An index, he argued, is a purely performance-oriented, informational-redundant component of data representation. As systems evolve, indexes must be created, modified, and destroyed to match shifting workloads. Codd asked a fundamental question: Can application programs and terminal activities remain invariant as indices come and go?

Early navigational database systems, such as IDS, failed this test. They allowed file designers to build indexes into physical file structures as explicit chains. Programs requiring performance optimization had to reference those chains by name, meaning application code broke if the underlying index chains were subsequently altered.

An index hint embedded in a query today is functionally identical to the structural dependencies Codd warned against in 1970. It exposes physical storage choices—which indexes exist, which is selected, and how it is scanned—directly to the application layer. Consequently, these assumptions become hardcoded into application deployments, tightly coupling software code to shifting database infrastructure.

Furthermore, hints blur the operational boundaries of database roles. A query author cares about retrieving business data efficiently; a database administrator (DBA) cares about physical properties, hardware constraints, and retrieval mechanisms. When hints are sprayed across application code, this separation collapses, sparking endless, unproductive arguments over whether database indexing is the responsibility of developers or DBAs.

Chronology: The Evolution of Hint Systems and Engine Resistance

While the ISO SQL standard deliberately omits execution instructions—focusing instead on declarative constraints and leaving performance tuning to external administrative tooling—commercial and open-source vendors diverged sharply over the decades.

1. The Commercial Vendor Approach

Faced with user demands for plan control, major commercial database engines took divergent paths, embedding instructions directly into their grammars or comment syntax:

• Oracle Database (Oracle7 onward): Pioneered hint syntax inside comments (e.g., /*+ INDEX(p parcel_status_ix) */). Ironically, Oracle’s official documentation explicitly warns developers against using hints, steering them instead toward modern tooling like the SQL Tuning Advisor, SQL Plan Management, and SQL Performance Analyzer.
• Microsoft SQL Server: Integrated table hints directly into the T-SQL grammar, such as WITH (INDEX(parcel_status_ix), FORCESEEK). SQL Server’s official manuals similarly caution that hints should be treated only as a last resort for experienced engineers.
• MySQL: Introduced optimizer hints within comment blocks (e.g., /*+ INDEX(...) */) in version 5.7.7, functioning as programmatic mandates rather than gentle suggestions.

2. PostgreSQL and the Long Resistance

PostgreSQL notoriously resisted native query hints for decades. Since a foundational mailing list discussion in April 1999—where core developer Bruce Momjian remarked that "usually hand-tuning joins is a symptom of a bad optimizer"—the community maintained a strict stance against Oracle-style hinting systems.

The PostgreSQL project’s developer wiki outlined the severe long-term costs of hints:

• Upgrade Interference: Helpful performance hints routinely become anti-performance bottlenecks following database engine upgrades or schema changes.
• Scale Mismatches: A hint optimized for a small table actively harms performance once the table grows by orders of magnitude.
• Masking Bugs: Hints allow users to bypass reporting fundamental optimizer deficiencies rather than helping the database engine improve.

Despite core resistance, the high demand for plan control persisted. NTT maintained the popular pg_hint_plan extension, adopted widely across managed cloud services like Amazon RDS, Azure Database for PostgreSQL, and Google Cloud SQL.

At the same time, users discovered and weaponized "optimization fences"—undocumented or semi-documented constructs that forced the planner’s hand. Writing OFFSET 0 inside a subquery forced PostgreSQL to treat it as a black box, blocking optimizations. Similarly, Common Table Expressions (CTEs) introduced in PostgreSQL 8.4 acted as implicit optimization fences because they were evaluated only once per execution.

Recognizing that users were intentionally exploiting these behaviors, PostgreSQL 12 officially introduced the MATERIALIZED and NOT MATERIALIZED keywords for CTEs. As core developer Tom Lane noted, users had to essentially lie to the planner or rely on hacks to force stability; providing explicit syntax acknowledged a reality long resisted.

Supporting Data: Bytecode, Plans, and the SQLite Reality

When an engineering team demands advanced hinting—specifying index scans, join orders, and parallel processing degrees—they are no longer writing declarative SQL. They are writing an execution plan.

To see what this looks like under the hood, we can examine SQLite, which translates SQL statements into explicit bytecode executed by a virtual machine. Running an EXPLAIN on a simple filtered query reveals the exact machine-level instructions executed by the engine:

EXPLAIN SELECT reference, posted_at FROM parcel p WHERE status = 'held';

The output reveals a rigid procedural program: OpenRead initializes cursors on the table and index; String8 loads literals; SeekGE positions the index cursor; and Next loops through matching records.

When a database user demands the ability to manually select index scans or join algorithms, they are effectively asking to bypass the query planner entirely in favor of manual bytecode manipulation. However, a query language built on explicit execution instructions loses its most vital superpower: adaptability.

As database workloads scale from thousands of rows to tens of millions, a static execution plan frozen into application code becomes a liability. While statistics gathered via commands like ANALYZE allow a dynamic planner to continuously adapt to changing data distributions, a rigid query hint preserves yesterday’s suboptimal decision indefinitely.

Official Responses: PostgreSQL 19 and the Rise of Plan Advice

After nearly 25 years of philosophical resistance, PostgreSQL achieved a breakthrough. In October 2025, core developer Robert Haas recalibrated the project’s stance on user-controlled optimization:

"[A]ny form of user control over the planner tends to be a lightning rod for criticism around here. I’ve come to believe that’s the wrong way of thinking about it: we can want to improve the planner over the long term and also want to have tools available to work around problems with it in the short term."

Committing in March 2026 for PostgreSQL 19, the core team introduced two groundbreaking contrib modules: pg_plan_advice and pg_stash_advice.

Instead of polluting SQL query text with brittle hints, pg_plan_advice translates a verified execution plan into modular, declarative instructions:

EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM join_fact f JOIN join_dim d ON f.dim_id = d.id;

-- Generated Plan Advice:
--   JOIN_ORDER(f d)
--   HASH_JOIN(d)
--   SEQ_SCAN(f d)
--   NO_GATHER(f d)

Critically, this advice does not travel inside the query text. It can be stored separately by pg_stash_advice, keyed to a unique query identifier. The query source code remains completely pristine and untouched.

This mirrors enterprise solutions found elsewhere in the industry: Oracle’s SQL Plan Management and Microsoft SQL Server’s Query Store. Furthermore, the design strictly limits advice to structural outcomes (such as enforcing a sequential scan) rather than allowing developers to inject false statistical guesses into the engine.

Implications: Fixing the Planner’s Mind, Not Its Code

Why do engineers reach for hints in the first place? Almost invariably, it is because the query planner’s statistical estimates were wrong.

For example, when a query features multiple conditions in a WHERE clause, standard optimizers naively assume column independence and multiply their selectivities. When columns are heavily correlated (such as a customer’s city and postal code, or a product’s status and shipping carrier), this independence assumption fails catastrophically, throwing off cost estimations by orders of magnitude.

Rather than forcing a specific execution plan via a hint, the correct relational remedy is to provide the planner with accurate facts about the data. In PostgreSQL, this is accomplished via CREATE STATISTICS:

CREATE STATISTICS stts (DEPENDENCIES) ON a, b FROM t;
ANALYZE t;

By registering functional dependencies or multivariate statistics in the data catalog via DDL, the database administrator corrects the planner’s foundational knowledge rather than overriding its operational choices.

• A hint corrects one specific query plan at a single point in time, masking deeper issues.
• A statistics object corrects row estimates for every query touching those columns, including applications yet to be written.

Conclusion

The relational model pioneered by Codd, Date, and Darwen established a brilliant division of labor: application developers declare what data they need, while the database engine determines how to retrieve it efficiently.

While operational emergencies on production systems will always tempt engineers to seek quick fixes, polluting application queries with physical execution instructions creates long-term fragility, upgrade friction, and tight architectural coupling.

Whether through Oracle’s Plan Management, SQL Server’s Query Store, or PostgreSQL’s innovative pg_plan_advice and extended statistics modules, the modern database industry converges on a singular truth: Plan control and performance tuning belong beside the query, never inside it.