SQL Plan Hints, Plan Cache & Forcing the Optimizer Across Dialects

sql plan hints are the escape hatch every senior DBA and data engineer eventually reaches for when the cost-based optimiser (CBO) picks a plan that's 100× slower than one the engineer knows would work — the Seq Scan on a 100M-row table when the composite index is right there, the Nested Loop on a join that should be Hash Join, the join order that touches the biggest table first instead of last. Every engineer knows to run ANALYZE when statistics look stale; knowing when to reach for a hint versus when to fix the underlying stats or rewrite the query is what separates a senior operator from a mid-level one. This guide is the honest tour of sql plan hints across the eight engines you'll actually encounter in 2026 — Postgres, SQL Server, Oracle, MySQL, Snowflake, BigQuery, Databricks, DuckDB — and the plan-cache mechanics that determine whether your hint sticks or gets recompiled away.

The tour walks the four "why is my plan wrong" failure modes — stale statistics that produce a bad estimate on the join predicate, correlated columns that fool the independence assumption, data skew that makes the "average" cost model wrong for hot values, and cardinality misestimates on nested subqueries — plus the four levers you can pull to fix them: refresh stats (cheapest), rewrite the query (durable), add a plan hint (fast but fragile), or force a plan via the plan cache (durable but heavy). It walks Postgres's pg_hint_plan extension with its SeqScan(t), IndexScan(t idx), HashJoin(a b), Leading((a b) c) syntax plus the enable_seqscan, enable_nestloop, random_page_cost GUCs that shape the plan without an explicit hint. It walks SQL Server's OPTION (HASH JOIN, LOOP JOIN, MERGE JOIN), OPTION (OPTIMIZE FOR), USE HINT, and the Query Store sp_query_store_force_plan mechanism that pins a specific plan across restarts. It walks Oracle's /*+ ... */ comment-syntax directives, the USE_HASH, LEADING, INDEX, FIRST_ROWS(N) family, and SQL Plan Baselines. It walks MySQL's STRAIGHT_JOIN, USE INDEX, FORCE INDEX, and the newer JOIN_ORDER, JOIN_FIXED_ORDER optimizer hints. It walks Snowflake's MERGE_INTO_UNMATCHED_BY_TARGET_HINT-style directives, BigQuery's JOIN_METHOD and HASH_JOIN hints, Databricks's BROADCAST and SHUFFLE_HASH hints, and DuckDB's PRAGMA disable_optimizer. Every section ships a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown.

When you want hands-on reps immediately after reading, drill the SQL optimization practice library → for hint-first plan tuning, sharpen the SQL practice surface → covering 450+ DE-focused problems, and layer SQL join drills → since most plan hints target join method and join order.

---

On this page

• Why plan hints matter in 2026
• Optimizer failure modes and when to hint
• Postgres pg_hint_plan and planner GUCs
• SQL Server / Oracle / MySQL hints
• Plan cache, forced plans, and dialect matrix
• Cheat sheet — plan hints recipe list
• Frequently asked questions
• Practice on PipeCode

---

1. Why plan hints matter in 2026

The sql plan hints mental model — when the CBO is right, when it's wrong, and why hints are a "last-resort tool but a mandatory tool" for senior operators

The one-sentence invariant: the cost-based optimiser is right 95% of the time on modern engines, spectacularly wrong on the remaining 5%, and the difference between a mid-level DBA and a senior one is (a) knowing which 5% and (b) having the syntax to force the correct plan in a way that survives statistics changes and engine upgrades — the hint is the seatbelt, not the steering wheel. Every engine eventually picks a bad plan on some query somewhere; only teams with a hint-vocabulary in their toolkit can respond in under an hour when it does.

Where plan hints actually show up in production.

• On-call at 3 a.m. A dashboard that ran in 200 ms yesterday is now taking 45 seconds. EXPLAIN shows Nested Loop where Hash Join should be. Statistics are fresh. The immediate fix — hint the plan, buy time to root-cause.
• Prod hotspot after a data load. A big INSERT changed the cardinality distribution; the CBO now underestimates the join. A hint pins the plan while you refresh extended statistics.
• Parameter sniffing on prepared statements. A stored procedure that runs fast for WHERE country='US' (100K rows) crawls for WHERE country='XY' (5M rows) because the first parameter's plan cached. OPTIMIZE FOR hint or plan_cache_mode = force_custom_plan fixes it.
• Analytical query on OLTP. A one-off analytical query that reads 90% of the table. SeqScan is right — but the CBO chose Index Scan because a stale statistic made it look selective. Hint the seq scan.
• Join order on 8-way join. Star-schema query joining a fact to 7 dims. CBO's search space is 8! = 40,320 join orders. Sometimes it picks a bad one. Leading hint or JOIN_ORDER locks the sensible order.
• Warehouse BROADCAST on wrong side. Snowflake / Databricks / BigQuery pick broadcast join based on estimated size; if the "small" side is actually 20M rows, broadcast blows up. NO_BROADCAST hint saves the query.
• Legacy query that regressed after engine upgrade. Postgres 15 → 16 changed a cost constant; a specific query flipped from HashJoin to MergeJoin and got slower. Hint pins the old plan while you migrate.

The four "wrong plan" failure modes.

• Stale statistics. The last ANALYZE was before a big load. Histograms and MCV lists are wrong; every join estimate downstream is off.
• Correlated columns. The CBO assumes independence — WHERE country = 'US' AND state = 'CA' is estimated as sel(country) × sel(state). If state implies country, that's off by an order of magnitude. Fix — extended statistics or a hint.
• Data skew. The average row count on a partition is small; one particular partition has 100× more. Estimated cost is average; actual is much higher. Hint fixes the specific case.
• Cardinality misestimate on nested subqueries. The CBO can't fully unnest complex correlated subqueries; estimates default to guesses that are often way off.

What senior interviewers actually probe.

• Do you know the four failure modes? Stale stats, correlated columns, skew, cardinality misestimate.
• Do you know the four fix levers? ANALYZE, rewrite, hint, force plan.
• Do you know the trade-off? Hints are fast but fragile; rewrites are durable but need testing; forced plans are heavy but survive restart.
• Do you know the plan cache? Parameter sniffing, generic vs custom plan, invalidation triggers.
• Do you know the syntax for at least two engines? Postgres pg_hint_plan + SQL Server OPTION or Oracle /*+ */. Bare minimum.
• Do you know when NOT to hint? When the underlying issue is stats — hint hides the root cause.
• Do you know how to remove a hint safely? Test in staging with fresh stats; verify the CBO picks the right plan on its own before removing.

The four-tier fix hierarchy.

• Tier 1 — Statistics. ANALYZE table. Free, fast, non-invasive. Try first. Fixes ~30% of bad plans.
• Tier 2 — Rewrite. Break correlated subquery to JOIN. Add extended statistics. Rewrite for sargability. Durable, testable, reviewable.
• Tier 3 — Hint. pg_hint_plan, OPTION, /*+ */. Fast but couples the query to a specific plan. Add a comment explaining why.
• Tier 4 — Force plan / plan baseline. SQL Server Query Store sp_query_store_force_plan. Oracle SQL Plan Baseline. Postgres has no built-in equivalent — extension only. Heavy machinery for known-hot queries.

The three anti-patterns to avoid.

• Hinting to hide bad stats. If ANALYZE fixes it, ANALYZE is the fix. Hint is band-aid.
• Hinting the wrong plan. Verify the hinted plan with EXPLAIN ANALYZE; if it's actually slower than what the CBO wanted, remove.
• Global-scope hints. Setting enable_seqscan = off at the DB level to force one query — breaks every other query that legitimately wants seq scan. Use session-scoped or query-scoped only.

Worked example — the CBO picked seq scan; you need index scan

Detailed explanation. A query on a large orders table with a selective filter. The CBO expected the filter to match 20% of rows (should have been seq scan) but the actual selectivity is 0.5% (should have been index scan). Stats are stale; you can hint while you refresh.

Question. Given a orders(id, customer_id, status, created_at) table with 100M rows and an index on (status, created_at), the query WHERE status = 'pending' AND created_at > NOW() - INTERVAL '1 day' should use the index. EXPLAIN shows Seq Scan. Force IndexScan on Postgres.

Input.

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  status TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

EXPLAIN SELECT * FROM orders
WHERE status = 'pending' AND created_at > NOW() - INTERVAL '1 day';
-- Shows: Seq Scan on orders (cost=... rows=20000000)
--   Filter: (status = 'pending' AND created_at > NOW() - '1 day')

Code.

-- Fix 1: refresh stats (try this FIRST)
ANALYZE orders;
-- Re-run EXPLAIN; often the plan flips to IndexScan.

-- Fix 2: hint (if ANALYZE didn't help)
LOAD 'pg_hint_plan';
/*+ IndexScan(orders idx_orders_status_created) */
EXPLAIN SELECT * FROM orders
WHERE status = 'pending' AND created_at > NOW() - INTERVAL '1 day';

-- Fix 3: session-scoped GUC (heavy hand)
SET enable_seqscan = off;
EXPLAIN SELECT * FROM orders
WHERE status = 'pending' AND created_at > NOW() - INTERVAL '1 day';
SET enable_seqscan = on;

-- Fix 4: rewrite for sargability (durable)
EXPLAIN SELECT * FROM orders
WHERE status = 'pending'
  AND created_at > (SELECT NOW() - INTERVAL '1 day');

Step-by-step explanation.

1. ANALYZE orders refreshes histograms and MCV lists. The CBO recomputes selectivity; if the fresh stats show status='pending' matches 0.5% of rows, the plan flips to Index Scan automatically.
2. If stats are already fresh (rare) or ANALYZE didn't fix it, pg_hint_plan provides the /*+ IndexScan(t idx) */ comment syntax. The hint is parsed on execution; the CBO respects it if the index is valid.
3. SET enable_seqscan = off is a heavy hand — it disables seq scan for the entire session, not just this query. Use only in a self-contained session.
4. Rewriting the predicate to use a scalar subquery for NOW() sometimes changes cardinality estimation because the CBO doesn't peek into the subquery. Fragile — depends on engine version.
5. Always verify the fix with EXPLAIN ANALYZE; compare actual time before and after.

Output.

Fix	Plan	Wall clock	Durability
Baseline (Seq Scan)	Seq Scan	8 s	—
ANALYZE refresh	Index Scan	15 ms	Durable
pg_hint_plan hint	Index Scan (forced)	15 ms	Fragile (couples query to index)
SET enable_seqscan = off	Index Scan (all queries)	15 ms	Very fragile (breaks other queries)
Rewrite	Depends	Depends	Testable

Rule of thumb. Always try ANALYZE first. Hint only when stats aren't the cause.

Worked example — parameter sniffing on a stored procedure

Detailed explanation. SQL Server compiles a plan for a stored procedure on first execution using the parameters passed. If those parameters were atypical (very selective or very unselective), the cached plan is wrong for subsequent executions.

Question. Given a GetOrdersByCountry(@country VARCHAR) procedure that's fast for 'US' but crawls for 'XY' (a small country), diagnose and fix.

Code.

-- Diagnose: check the cached plan
SELECT p.query_id, p.query_text_id, qs.execution_type_desc,
       qs.count_executions, qs.avg_duration
FROM sys.query_store_query qs
JOIN sys.query_store_query_text p ON p.query_text_id = qs.query_text_id
WHERE p.query_sql_text LIKE '%orders WHERE country%';

-- Fix 1: OPTIMIZE FOR — pin the "typical" parameter value
CREATE PROCEDURE dbo.GetOrdersByCountry @country VARCHAR(10)
AS
SELECT * FROM orders WHERE country = @country
OPTION (OPTIMIZE FOR (@country = 'US'));

-- Fix 2: OPTIMIZE FOR UNKNOWN — use average distribution
OPTION (OPTIMIZE FOR UNKNOWN);

-- Fix 3: RECOMPILE — build a fresh plan every execution
OPTION (RECOMPILE);

Step-by-step explanation.

1. Query Store reveals which parameter value produced the cached plan; if avg_duration varies wildly by execution, parameter sniffing is likely.
2. OPTIMIZE FOR (@country = 'US') tells the CBO to use 'US' as the reference for cardinality; the plan is optimized for the common case.
3. OPTIMIZE FOR UNKNOWN uses the average distribution — good when parameters vary widely.
4. OPTION (RECOMPILE) re-plans every execution — expensive but always right. Use for procedures called infrequently.
5. Alternative — dynamic SQL or separate procedures per country tier.

Output.

Fix	Cost per call	Plan stability	Best for
Baseline (parameter sniffing bug)	Wildly variable	Bad plan sticks	—
OPTIMIZE FOR ('US')	Fast for 'US', OK for others	Good	Skewed distribution with known dominant value
OPTIMIZE FOR UNKNOWN	Consistent average	Good	Variable parameter distribution
OPTION (RECOMPILE)	Higher per-call	Fresh plan	Infrequent, high-variability queries

Rule of thumb. Parameter sniffing is common; OPTIMIZE FOR is your first hint. RECOMPILE is last resort.

Worked example — join order hint on an 8-way star join

Detailed explanation. A fact table joined to 7 dims. The CBO's search space is 8! = 40,320. It picks a bad order (fact joined to dim1 first) when the right order is to filter dims first, then join to fact. Force the join order.

Question. Force LEADING (dim1 dim2 dim3 dim4 dim5 dim6 dim7 fact) on Oracle.

Code.

SELECT /*+ LEADING(d1 d2 d3 d4 d5 d6 d7 f) USE_HASH(f) */
       f.id, f.total, d1.name, d2.name, d3.name, d4.name, d5.name, d6.name, d7.name
FROM fact f
JOIN dim1 d1 ON d1.id = f.d1_id
JOIN dim2 d2 ON d2.id = f.d2_id
JOIN dim3 d3 ON d3.id = f.d3_id
JOIN dim4 d4 ON d4.id = f.d4_id
JOIN dim5 d5 ON d5.id = f.d5_id
JOIN dim6 d6 ON d6.id = f.d6_id
JOIN dim7 d7 ON d7.id = f.d7_id
WHERE d1.filter = 'X' AND d2.filter = 'Y';

Step-by-step explanation.

1. /*+ LEADING(...) */ tells Oracle: start with d1, then join d2, d3, ..., d7, then finally join fact.
2. USE_HASH(f) tells Oracle to use hash join for the fact.
3. This is the classic "filter dims first, hash-join fact last" star-schema pattern.
4. Without the hint, Oracle might join fact to d1 first, materialise a huge intermediate, then filter — 10× slower.
5. Verify with EXPLAIN PLAN FOR and DBMS_XPLAN.DISPLAY.

Output.

Order	Wall clock	Reason
CBO auto (fact first)	45 s	Huge intermediate
Hinted (dims first, fact last)	3 s	Filter reduces before join

Rule of thumb. For star schemas with many dims, force the join order via LEADING.

Common beginner mistakes

• Hinting to hide stats problems.
• Setting SET enable_seqscan = off globally.
• Adding hints without a comment explaining why.
• Not verifying the hinted plan is actually faster.
• Adding hints in ORM-generated queries without version discipline.
• Assuming hints survive engine upgrades (they don't always).

sql plan hints interview question on when to hint vs when to fix stats

A senior interviewer often opens with: "You get paged: dashboard query went from 200 ms to 45 seconds. Walk me through your triage tree — first three things you check, and where hints fit."

Solution Using the 4-tier fix hierarchy applied to a real triage

-- Step 1: EXPLAIN ANALYZE to see the current plan
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <the query>;

-- Step 2: Check statistics freshness
SELECT last_analyze, last_autoanalyze FROM pg_stat_all_tables
WHERE relname IN (<tables involved>);

-- Step 3: If stats > 1 hour old and rows changed materially, refresh
ANALYZE <tables>;
-- Re-run EXPLAIN. If plan flips, this was the fix. Done.

-- Step 4: If ANALYZE didn't help, check estimate vs actual gap in EXPLAIN ANALYZE
--    rows=100 vs actual rows=100000 → 1000× off → likely correlated columns
CREATE STATISTICS <name> ON col1, col2 FROM <table>;
ANALYZE <table>;

-- Step 5: If stats problem is confirmed but restart takes hours, hint
LOAD 'pg_hint_plan';
/*+ HashJoin(a b) IndexScan(a idx) */
<the query>

-- Step 6: Root-cause post-hoc — was it a data-shape change? New query?
--         Add extended stats or rewrite for durability; remove hint after.

Step-by-step trace.

Step	Check	Fix if problem found
1	EXPLAIN ANALYZE plan	Diagnose which node is slow
2	Stats freshness	ANALYZE
3	Estimate vs actual gap	Extended statistics
4	Correlated columns	CREATE STATISTICS (dependencies, ndistinct)
5	Real crisis, need immediate fix	Hint with pg_hint_plan
6	Root-cause post-hoc	Rewrite or extended stats; remove hint

Output:

Signal	Root cause	Fix priority
Stats > 1 hour, changed rows > 20%	Stale stats	ANALYZE (Tier 1)
Estimate off by 10× on correlated cols	Independence assumption	Extended statistics (Tier 2)
Plan flip after engine upgrade	Cost constant changed	Hint (Tier 3), rewrite plan long-term
Parameter sniffing	Prepared statement plan cached	OPTIMIZE FOR / RECOMPILE (Tier 3)
Storage layout changed (SSD → NVMe)	Cost model out of date	Update random_page_cost

Why this works — concept by concept:

• Stats first is not "always right" but "always cheap" — ANALYZE is O(sample rows), takes seconds, and fixes ~30% of bad plans. Even when it doesn't, you rule it out.
• Estimate-vs-actual gap in EXPLAIN ANALYZE — the smoking gun for stats issues. 10× off means bad stats; 100× off means correlated columns or a serious estimation failure.
• Extended statistics for correlation — Postgres CREATE STATISTICS (dependencies, ndistinct) ON a, b FROM t. Teaches the CBO that a and b are correlated.
• Hint as emergency stop — when you need the plan pinned right now and root-causing later.
• Cost — Tier 1 (ANALYZE) is free. Tier 2 (extended stats + rewrite) is engineer-hours. Tier 3 (hint) is minutes. Tier 4 (forced plan) is heavy — reserved for hot queries.

SQL
Topic — optimization
SQL optimization drills

Practice →

SQL Topic — SQL SQL practice library

Practice →

---

2. Optimizer failure modes and when to hint

The four "wrong plan" failure modes — stale stats, correlated columns, skew, cardinality misestimate — and the reading discipline that tells you which one you have

The mental model in one line: every bad plan traces back to a bad estimate at some node in the plan tree; find the node where rows (estimated) diverges from actual rows by more than 10× and you've found the root cause — the fix is dictated by which of the four failure modes produced the divergence, and hints are only the right lever for the failure modes that stats or rewrites can't fix.

Slot 1 — stale statistics.

• Symptom. Estimate off by 10-100×. Fresh data since last ANALYZE.
• Signal. pg_stat_all_tables.last_autoanalyze old; n_mod_since_analyze high.
• Root cause. Big INSERT / UPDATE changed cardinality distribution; autovacuum hasn't caught up.
• Fix. ANALYZE table explicitly. On Postgres, tune autovacuum_analyze_scale_factor per table for heavy-write tables.
• When to hint instead. When you can't wait for ANALYZE — hot query in prod, need immediate fix. Add hint, then run ANALYZE, then remove hint.

Slot 2 — correlated columns.

• Symptom. Multi-column WHERE clause estimates way off. Individual column selectivities correct; combined selectivity multiplied assuming independence.
• Signal. WHERE country='US' AND state='CA' — estimate = sel(country) × sel(state) = 20% × 2% = 0.4%. Actual = 2% (because state implies country). 5× off.
• Root cause. CBO assumes column independence when they're actually correlated.
• Fix. Extended statistics: CREATE STATISTICS name (dependencies, ndistinct) ON a, b FROM t. Then ANALYZE.
• When to hint instead. As a stopgap. But extended stats is the durable fix.

Slot 3 — data skew.

• Symptom. Query fast for common values, slow for rare (or vice versa).
• Signal. Parameter sniffing — cached plan optimal for one param value, terrible for another.
• Root cause. CBO uses average distribution; actual is bimodal or skewed.
• Fix. OPTIMIZE FOR (SQL Server), OPTIMIZE FOR UNKNOWN, or RECOMPILE. On Postgres, plan_cache_mode = force_custom_plan for prepared statements.
• When to hint. Immediately. Skew is a fundamental limitation; hints are the tool.

Slot 4 — cardinality misestimate on subqueries.

• Symptom. Correlated subquery with complex predicate. CBO can't estimate accurately; defaults to guess.
• Signal. Nested Loop where Hash Join would be right; huge intermediate.
• Root cause. Optimiser has no visibility into what the subquery selects.
• Fix. Rewrite subquery to JOIN. Or add hint forcing hash join.
• When to hint. When rewrite isn't feasible (dynamic SQL, ORM-generated).

Slot 5 — plan regression after engine upgrade.

• Symptom. Same query, slower after upgrade. Different plan.
• Signal. Compare EXPLAIN plans before and after.
• Root cause. CBO cost constants or heuristics changed.
• Fix. Update cost constants (random_page_cost, etc.) to match your storage. Or hint the query.
• When to hint. As bridge; long-term revisit cost constants or query.

Slot 6 — the estimate-vs-actual ratio reading discipline.

• 1:1 to 2:1. Fine. CBO's estimate is close to reality.
• 2:1 to 10:1. Moderate. Worth checking stats but plan may still be OK.
• 10:1 to 100:1. Serious. Almost always causes a bad plan somewhere upstream.
• >100:1. Very serious. Hint or rewrite immediately; then fix stats.
• Applied per node. Read each node's ratio; the largest ratio is the root cause.

Slot 7 — the "when to reach for a hint" decision tree.

• Q1: Is the plan actually wrong? Verify by running EXPLAIN ANALYZE on both current plan and a hypothetical better plan (via SET enable_* GUCs). If the "better" plan is actually slower, don't hint.
• Q2: Are stats fresh? SELECT last_autoanalyze FROM pg_stat_all_tables. If old, ANALYZE first.
• Q3: Is there a correlation issue? Look for multi-column WHERE where estimates should be independent but aren't. Extended stats.
• Q4: Is it parameter sniffing? Check plan cache for parameter-specific plans. OPTIMIZE FOR or RECOMPILE.
• Q5: Is it a data-shape issue? Skewed column, hot key. Hint or rewrite.
• Q6: Are you certain the hinted plan is right? Test in staging. Verify.
• Q7: Add hint with justifying comment. Explain why the CBO is wrong.
• Q8: Add a "revisit" note for the next engine upgrade. Hints don't survive upgrades reliably.

Slot 8 — hints as a debugging tool.

• SET enable_seqscan = off for one session — force index scan to compare cost. Don't leave enabled.
• Compare plans with/without hint — the diff reveals the CBO's mistake.
• Use hints to isolate the "which node" question — force nested loop to prove hash join is right.

Slot 9 — hints as documentation.

• Comment above hint. -- CBO underestimates by 10x due to correlation; extended stats not enabled per DBA decision. Force hash join.
• Alert on hint removal. Some teams tag hinted queries in a table; monthly review.
• Version-control hint decisions. Hints in migration files with dated context.

Slot 10 — hints and A/B testing.

• Split traffic hinted vs unhinted — measure real performance in prod.
• Feature flag toggle — apply hint conditionally.
• A/B test decision — hint stays only if measurably better.

Common beginner mistakes

• Hinting without checking ANALYZE first.
• Assuming the "obvious" plan is faster without measuring.
• Leaving global GUCs at non-default values.
• Adding hints in ORM raw SQL without a code path to remove them.
• Not testing hinted queries against multiple parameter values.

Worked example — the correlated columns bug and extended stats fix

Detailed explanation. A query filtering by country and state on a global customer table. CBO assumes independence; estimate off by 5×.

Code.

-- Diagnose: EXPLAIN ANALYZE shows the gap
EXPLAIN ANALYZE SELECT * FROM customers WHERE country = 'US' AND state = 'CA';
-- Estimate: rows=8000
-- Actual:   rows=45000  (5.6x off)

-- Fix: Postgres extended statistics
CREATE STATISTICS customers_geo_stats (dependencies, ndistinct)
ON country, state FROM customers;
ANALYZE customers;

-- Re-verify
EXPLAIN ANALYZE SELECT * FROM customers WHERE country = 'US' AND state = 'CA';
-- Estimate: rows=45000 (correct)

Rule of thumb. Multi-column WHERE with correlated columns → extended statistics.

Worked example — the parameter sniffing incident

Code.

-- SQL Server: parameter sniffing on stored proc
CREATE PROCEDURE GetSales @country VARCHAR(10) AS
SELECT * FROM sales WHERE country = @country;

-- First execution: EXEC GetSales 'US' → optimal for common value
-- Later execution: EXEC GetSales 'AS' (American Samoa, rare) → bad cached plan
-- Fix: OPTIMIZE FOR UNKNOWN to use average distribution
CREATE PROCEDURE GetSales @country VARCHAR(10) AS
SELECT * FROM sales WHERE country = @country
OPTION (OPTIMIZE FOR UNKNOWN);

Rule of thumb. Prepared statements with variable-selectivity parameters → OPTIMIZE FOR UNKNOWN or RECOMPILE.

Worked example — the storage cost constant update

Detailed explanation. Postgres default random_page_cost = 4.0 assumes spinning disks. On NVMe, closer to 1.1 is realistic. Wrong constant → CBO prefers seq scan when index scan is fine.

Code.

-- Check current setting
SHOW random_page_cost;
-- 4.0

-- Update for NVMe / SSD (in postgresql.conf or ALTER SYSTEM)
ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();

-- Verify plan changes
EXPLAIN <same query>;

Rule of thumb. Update cost constants when storage tier changes. Common mistake — leaving defaults on modern hardware.

sql plan hints interview question on plan reading + hint decision

A senior interviewer asks: "Show me an EXPLAIN ANALYZE plan and walk through your decision — is this a stats problem, a correlation problem, a parameter sniffing problem, or a data-shape problem? What's your fix?"

Solution Using the estimate-vs-actual ratio + failure mode matrix

Node-by-node reading:
1. Find leaf nodes (Seq Scan, Index Scan, ...)
2. For each: compare rows= (estimate) to actual rows= (real)
3. Ratio > 10x → suspect. Root cause:
   - Stale stats? Check pg_stat_all_tables.last_autoanalyze
   - Correlated cols? Multi-col WHERE with independence gone wrong
   - Skew? Parameter-dependent selectivity
   - Subquery? Complex predicate CBO can't estimate
4. Identify the failure mode; apply matched fix from the 4-tier hierarchy.

Output: Decision tree for plan diagnosis.

Why this works — the ratio locates the wrong node; the failure mode dictates the fix.

SQL
Topic — optimization
SQL optimization drills

Practice →

---

3. Postgres pg_hint_plan and planner GUCs

pg_hint_plan — the extension every senior Postgres DBA installs, plus the enable_* GUCs that let you shape the plan without extension

The mental model in one line: Postgres ships with no built-in hint syntax (a philosophy choice); the pg_hint_plan extension provides comment-based hints (/*+ SeqScan(t) */, /*+ IndexScan(t idx) */, /*+ HashJoin(a b) */, /*+ Leading((a b) c) */), while the enable_* GUCs (enable_seqscan, enable_nestloop, enable_hashjoin, enable_mergejoin, enable_indexscan) let you disable specific plan strategies for a session — the two together cover almost every "force this plan" scenario.

Slot 1 — installing pg_hint_plan.

• From source or package. CREATE EXTENSION pg_hint_plan; (must be installed at server level; may need superuser).
• Loading. SHARED_PRELOAD_LIBRARIES = 'pg_hint_plan' in postgresql.conf, or LOAD 'pg_hint_plan'; per session.
• Verify. SHOW shared_preload_libraries; should list it.
• Cloud managed — many managed Postgres services (Aurora, Cloud SQL) don't include pg_hint_plan; check your provider.

Slot 2 — scan method hints.

• SeqScan(t) — force sequential scan on table t.
• IndexScan(t idx_name) — force index scan using specific index.
• IndexOnlyScan(t idx_name) — force index-only scan.
• BitmapScan(t idx_name) — force bitmap heap scan.
• NoSeqScan(t) — disable seq scan for this table (CBO picks another).
• NoIndexScan(t) — disable index scan.

Slot 3 — join method hints.

• HashJoin(a b) — use hash join between a and b.
• MergeJoin(a b) — use merge join.
• NestLoop(a b) — use nested loop.
• NoHashJoin(a b) — disable hash join for this pair.
• NoMergeJoin(a b) / NoNestLoop(a b) — same idea.

Slot 4 — join order hints.

• Leading((a b)) — a first, then join b.
• Leading((a b c)) — a first, then b, then c.
• Leading((a b) c) — join a and b first, then join to c.
• Parenthesised nesting — explicitly controls join tree shape.

Slot 5 — parallelism hints.

• Parallel(t 4) — use 4 parallel workers for scanning t.
• Parallel(t 0) — disable parallelism for t.
• Set(parallel_setup_cost 1000) — adjust cost of setting up parallelism.

Slot 6 — configuration hints.

• Set(random_page_cost 1.1) — set GUC just for this query.
• Set(work_mem '128MB') — bigger sort/hash memory.
• Set(enable_seqscan off) — disable seq scan for this query only.

Slot 7 — the enable_* GUCs.

• enable_seqscan — off disables sequential scans (CBO picks index or bitmap).
• enable_nestloop — off disables nested loops (CBO picks hash or merge).
• enable_hashjoin — off disables hash joins.
• enable_mergejoin — off disables merge joins.
• enable_indexscan — off disables index scans (rare use).
• enable_bitmapscan — off disables bitmap scans.
• enable_material — off disables materialisation of subquery results.

Slot 8 — cost-constant tuning.

• random_page_cost — default 4.0 (spinning disk); NVMe/SSD 1.1-1.5.
• seq_page_cost — default 1.0.
• cpu_tuple_cost — default 0.01.
• work_mem — default 4 MB. Bigger = fewer sort spills.
• Tune these per storage tier, not per query.

Slot 9 — combining hints.

/*+
  IndexScan(o idx_orders_status)
  HashJoin(o c)
  Leading((o c))
  Set(work_mem '256MB')
*/
SELECT ...

Multi-hint blocks — parsed left-to-right.

Slot 10 — how to verify hint applied.

-- Verify with EXPLAIN
LOAD 'pg_hint_plan';
SET pg_hint_plan.debug_print = 'verbose';
EXPLAIN /*+ IndexScan(t idx) */ SELECT * FROM t WHERE ...;
-- pg_hint_plan logs which hints applied and which were rejected

Check for warnings like "hint syntax error" or "hint not applicable" — silent no-op is a common bug.

Worked example — forcing hash join over nested loop

Code.

LOAD 'pg_hint_plan';

-- CBO chose Nested Loop, want Hash Join
/*+ HashJoin(o c) */
EXPLAIN ANALYZE
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'shipped';

Rule of thumb. Force hash join for large-large joins where CBO underestimated.

Worked example — the join order for star schema

Code.

/*+ Leading((d1 d2) (d3 f)) HashJoin(d1 d2) HashJoin(d3 f) */
SELECT ...
FROM fact f
JOIN dim1 d1 ON d1.id = f.d1_id
JOIN dim2 d2 ON d2.id = f.d2_id
JOIN dim3 d3 ON d3.id = f.d3_id
WHERE d1.filter = 'X';

Rule of thumb. Nest parentheses to describe the join tree shape.

Worked example — per-query work_mem bump

Code.

/*+ Set(work_mem '256MB') */
EXPLAIN ANALYZE
SELECT customer_id, ARRAY_AGG(order_id ORDER BY created_at)
FROM orders
GROUP BY customer_id;

Rule — never ALTER SYSTEM SET work_mem = '256MB' globally; only per-query.

pg_hint_plan interview question on production incident

A senior interviewer asks: "You're on-call. A batch job is stuck on a nested loop over 50M × 100K rows. You need to unstick it right now. Walk through your fix."

Solution Using immediate hint + follow-up ANALYZE

-- 1. Immediate: kill the running query
SELECT pg_terminate_backend(<pid>);

-- 2. Investigate: EXPLAIN the query
EXPLAIN <query>;
-- Nested Loop plan visible; estimates suspect.

-- 3. Verify hash join would be faster
SET enable_nestloop = off;
EXPLAIN ANALYZE <query>;
-- Hash Join plan: 30 seconds vs Nested Loop's projected 30 minutes.

-- 4. Add hint to production query
/*+ HashJoin(a b) */ <query>

-- 5. Post-hoc: run ANALYZE and check for correlated columns
ANALYZE big_table;
-- Confirm stats fresh; consider extended stats.

-- 6. Test removal of hint after stats fix; keep if CBO still wrong.

Output: Immediate mitigation + root-cause fix.

Why this works — hint stops the bleeding; stats fix addresses root cause.

SQL
Topic — SQL
SQL practice library

Practice →

SQL Topic — joins SQL join drills

Practice →

---

4. SQL Server / Oracle / MySQL hints

sql server query hint, oracle hints, and MySQL optimizer hints — the syntax that lets you force plans on the big-three RDBMSs

The mental model in one line: SQL Server, Oracle, and MySQL all have built-in hint syntax (unlike Postgres); the syntax differs — SQL Server OPTION (HASH JOIN, LOOP JOIN, MAXDOP N) and USE HINT, Oracle /*+ HINT */ comment directives, MySQL SELECT STRAIGHT_JOIN, USE INDEX, FORCE INDEX, and the newer /*+ */ optimizer hints — but the mental model is the same: point the CBO at a specific plan shape when its default is wrong.

Slot 1 — SQL Server OPTION hints (query-level).

• OPTION (HASH JOIN) — force hash join for all joins in query.
• OPTION (LOOP JOIN) — force nested loop.
• OPTION (MERGE JOIN) — force merge join.
• OPTION (MAXDOP 4) — limit parallelism to 4 threads.
• OPTION (RECOMPILE) — force fresh plan every execution.
• OPTION (OPTIMIZE FOR (@p = 'US')) — pin cardinality estimate to parameter value.
• OPTION (OPTIMIZE FOR UNKNOWN) — use average distribution.
• OPTION (USE HINT ('HINT_NAME')) — modern extensible hint mechanism (2016+).

Slot 2 — SQL Server join hints (per-join).

• FROM a INNER HASH JOIN b ON ... — force hash join on this specific join.
• FROM a INNER LOOP JOIN b ON ... — nested loop.
• FROM a INNER MERGE JOIN b ON ... — merge join.
• FROM a INNER REMOTE JOIN b ON ... — for cross-server joins.

Slot 3 — SQL Server table hints.

• WITH (NOLOCK) — dirty read (READ UNCOMMITTED); dangerous.
• WITH (INDEX = idx_name) — force specific index.
• WITH (FORCESEEK) — force index seek not scan.
• WITH (READPAST) — skip rows locked by other txs.

Slot 4 — SQL Server USE HINT (modern).

• USE HINT ('ENABLE_QUERY_OPTIMIZER_HOTFIXES') — enable planner fixes.
• USE HINT ('DISABLE_OPTIMIZER_ROWGOAL') — disable row-goal optimization.
• USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION') — use pre-2014 CE model.
• Extensible. New hint names added per release.

Slot 5 — Oracle hint syntax.

• SELECT /*+ HINT_NAME(args) */ — comment directive after SELECT.
• Multiple. /*+ USE_HASH(a b) LEADING(a b) */
• USE_HASH(t) / USE_MERGE(t) / USE_NL(t) — join method for table.
• INDEX(t idx_name) — force index.
• FULL(t) — force full table scan.
• LEADING(a b c) — join order.
• FIRST_ROWS(N) — optimize for first N rows (LIMIT-friendly).
• ALL_ROWS — optimize for all rows (default).
• PARALLEL(t 8) — parallel degree.

Slot 6 — Oracle common hints.

• APPEND — direct-path insert (bypasses buffer cache).
• NO_MERGE — prevent view merging.
• PUSH_PRED — push predicate into view.
• GATHER_PLAN_STATISTICS — collect stats for this run.
• OPT_PARAM('_optimizer_ignore_hints' 'FALSE') — force hint respect.

Slot 7 — MySQL optimizer hints.

• SELECT STRAIGHT_JOIN — force join order to match table list.
• SELECT /*+ JOIN_ORDER(a, b, c) */ — MySQL 8.0.14+.
• SELECT /*+ JOIN_FIXED_ORDER() */ — lock join order.
• SELECT /*+ USE_INDEX(t idx) */ — force index.
• SELECT /*+ NO_INDEX(t idx) */ — disable index.
• SELECT /*+ SET_VAR(work_mem = ...) */ — set variable.

Slot 8 — MySQL table hints (per-table).

• SELECT * FROM t USE INDEX (idx) — hint use this index.
• SELECT * FROM t FORCE INDEX (idx) — force use (stronger than USE).
• SELECT * FROM t IGNORE INDEX (idx) — don't use this index.

Slot 9 — Snowflake hints.

• Limited hint support. Snowflake's philosophy is "the optimiser knows best."
• /*+ NO_BROADCAST(t) */ — prevent broadcast of table t.
• /*+ BROADCAST(t) */ — force broadcast (rarely used; small dim).
• MERGE_INTO_UNMATCHED_BY_TARGET_HINT — specific to MERGE.
• Cluster keys and materialised views are the primary tuning tools.

Slot 10 — BigQuery / Databricks hints.

• BigQuery @@JOIN_METHOD — session-level join preference.
• BigQuery /*+ HASH_JOIN */ per query.
• Databricks /*+ BROADCAST(t) */ and /*+ SHUFFLE_HASH(t) */.
• Databricks /*+ COALESCE(N) */ — reduce partitions.
• Databricks /*+ REPARTITION(N) */ — increase partitions.

Common beginner mistakes

• Using SQL Server WITH (NOLOCK) for perf without understanding dirty reads.
• Oracle hint typo — comment-based syntax means typos are silently ignored.
• MySQL STRAIGHT_JOIN when subquery unnesting is what you actually need.
• Snowflake global hints — session-level BROADCAST breaks everything.
• Not verifying MySQL 8+ hint syntax vs legacy (/*! */).

Worked example — SQL Server force hash join

Code.

SELECT o.id, c.name
FROM orders o
INNER HASH JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'shipped';
-- OR
SELECT ... FROM orders o JOIN customers c ON ... OPTION (HASH JOIN);

Rule of thumb. Per-join hint is more targeted; OPTION applies to all joins.

Worked example — Oracle join order

Code.

SELECT /*+ LEADING(d1 d2 f) USE_HASH(f) INDEX(d1 idx_d1_filter) */
       f.*, d1.name, d2.category
FROM fact f
JOIN dim1 d1 ON d1.id = f.d1_id
JOIN dim2 d2 ON d2.id = f.d2_id
WHERE d1.filter = 'X';

Rule of thumb. Combine LEADING (join order), USE_HASH (join method), INDEX (scan method).

Worked example — MySQL force index

Code.

SELECT * FROM orders FORCE INDEX (idx_orders_customer)
WHERE customer_id = 42 AND status = 'shipped';

Rule of thumb. FORCE INDEX is stronger than USE INDEX; use when the CBO stubbornly ignores.

sql server query hint interview question

A senior interviewer asks: "You have a report query on SQL Server that's fast for common tenants but slow for outlier tenants. Show me the hint you'd use."

Solution Using OPTION (OPTIMIZE FOR UNKNOWN) + RECOMPILE hybrid

CREATE PROCEDURE dbo.TenantReport @tenant_id INT
AS
SELECT ...
FROM sales
WHERE tenant_id = @tenant_id
OPTION (OPTIMIZE FOR UNKNOWN);
-- Uses average distribution; consistent for all tenants.

-- OR for frequently-recompiled small queries:
OPTION (RECOMPILE);
-- Fresh plan every call.

Output: Consistent performance across tenants.

Why this works — parameter sniffing is defeated by using distribution average or recompiling.

---

5. Plan cache, forced plans, and dialect matrix

plan cache semantics, plan stability, and the 8-engine dialect matrix for plan pinning

The mental model in one line: the plan cache stores compiled plans keyed by query text (or parameterized form), reusing plans across executions to avoid replan cost; parameter sniffing bugs happen when a plan optimal for one parameter value is cached and reused for a value with different selectivity; **forced plans (SQL Server Query Store, Oracle SQL Plan Baselines) pin a specific plan across restarts and stat changes, providing the strongest form of plan stability at the cost of losing CBO improvements**.

Slot 1 — plan cache basics.

• Purpose. Avoid re-planning identical queries.
• Key. Query text (or parameterized fingerprint).
• Invalidation. Schema change, stats update, GUC change, connection reset.
• Postgres. pg_prepared_statements, plan_cache_mode (auto / force_custom_plan / force_generic_plan).
• SQL Server. Plan cache in memory; Query Store persists history.
• Oracle. Shared pool caches parsed plans; dbms_shared_pool.purge() to evict.
• MySQL. Prepared statement cache; simpler than Postgres/SQL Server.

Slot 2 — parameter sniffing.

• Cause. First call's parameters shape the plan; subsequent calls use it.
• Symptom. Wildly variable execution time by parameter.
• Detection. Query Store execution stats show high stddev.
• Fix (SQL Server). OPTIMIZE FOR, OPTIMIZE FOR UNKNOWN, RECOMPILE.
• Fix (Postgres). plan_cache_mode = force_custom_plan per session.

Slot 3 — SQL Server Query Store forced plans.

• Enable Query Store. ALTER DATABASE SET QUERY_STORE = ON.
• Identify problematic query. sp_query_store_reset_exec_stats if needed; then observe.
• Force plan. sp_query_store_force_plan @query_id = X, @plan_id = Y.
• Un-force. sp_query_store_unforce_plan @query_id = X.
• Auto tuning (2017+). ALTER DATABASE SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON) — SQL Server auto-forces plans that regress.

Slot 4 — Oracle SQL Plan Baselines.

• DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE — capture current plans.
• DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE — test alternative plans.
• DBMS_SPM.ALTER_SQL_PLAN_BASELINE — enable/disable/fix specific plan.
• Result. Query is pinned to captured plan across restarts, stats changes, engine upgrades.

Slot 5 — Postgres has no built-in equivalent.

• Extension. pg_stat_plans (deprecated); pg_hint_plan for query-level hints.
• Trick. Store hinted query text; retrieve at runtime.
• AWS Aurora — has query plan management extension.

Slot 6 — MySQL plan stability.

• SELECT ... FOR UPDATE and other locks don't affect plan cache.
• Prepared statements — plan cached per prepared statement.
• FLUSH TABLES or RESTART — clears cache.
• No forced plan mechanism — hints are the tool.

Slot 7 — the 8-engine dialect matrix.

Engine	Hint syntax	Plan cache	Forced plan
Postgres	pg_hint_plan comment	prepared statements	pg_hint_plan hint (fragile)
SQL Server	OPTION / USE HINT / table hint	Yes (in-memory + Query Store)	Query Store sp_query_store_force_plan
Oracle	/*+ HINT */ comment	Shared pool	SQL Plan Baseline
MySQL	/*+ HINT */ or STRAIGHT_JOIN / USE INDEX	Prepared stmt cache	Optimizer hints only
Snowflake	Limited (BROADCAST)	Result cache + micro-partition	Not applicable
BigQuery	JOIN_METHOD, HASH_JOIN	Query result cache	Not applicable
Databricks	BROADCAST, SHUFFLE_HASH	Delta cache	Not applicable
DuckDB	PRAGMA	Query planner cache	Not applicable

Slot 8 — plan cache invalidation.

• Statistics update. ANALYZE triggers plan re-evaluation next time.
• Schema change. DROP index / ALTER TABLE invalidates related plans.
• GUC change. Session variables can force replan.
• DISCARD PLANS (Postgres). Session-level.
• DBCC FREEPROCCACHE (SQL Server). Server-wide (dangerous).

Slot 9 — when to force a plan.

• Query performance-critical and stable data shape. Forcing prevents random regression.
• After identifying a good plan you don't want the CBO to abandon.
• NOT for exploratory queries or ad-hoc analytics.
• NOT for queries whose optimal plan varies by parameter.

Slot 10 — hints vs forced plans.

• Hints. Fragile; live in query text; visible in code review.
• Forced plans. Durable; managed at DB level; invisible in code (need audit).
• Best practice. Hint first for prototyping; move to forced plan for hot production queries.

Common beginner mistakes

• Forgetting Postgres has no forced plan built-in.
• Forcing a plan then never revisiting it.
• Not documenting why a plan was forced.
• Assuming Query Store retains forced plans across major upgrades (verify).
• Using DBCC FREEPROCCACHE in prod without alternatives.

Worked example — SQL Server Query Store forced plan

Code.

-- Enable Query Store
ALTER DATABASE MyDB SET QUERY_STORE = ON;
ALTER DATABASE MyDB SET QUERY_STORE (
  OPERATION_MODE = READ_WRITE,
  CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30)
);

-- Find the query
SELECT q.query_id, qt.query_sql_text, p.plan_id, rs.avg_duration
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
WHERE qt.query_sql_text LIKE '%dashboard%'
ORDER BY rs.avg_duration DESC;

-- Force the good plan
EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 100;

-- Un-force later
EXEC sp_query_store_unforce_plan @query_id = 42;

Rule of thumb. Query Store forced plans are the SQL Server "plan hint" for hot queries.

Worked example — Postgres plan_cache_mode

Code.

-- Session-level
SET plan_cache_mode = force_custom_plan;
-- Prepared statements now re-plan each execution.

-- Per-query (deallocate + re-prepare)
DEALLOCATE stmt;
PREPARE stmt AS SELECT ...;

Rule of thumb. force_custom_plan defeats generic-plan cache when parameter distribution matters.

Worked example — Oracle SQL Plan Baseline

Code.

-- Capture plans automatically
ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
-- Or manually
DECLARE
  ret NUMBER;
BEGIN
  ret := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => 'abc123',
    plan_hash_value => 456789
  );
END;
/

-- Fix a specific plan
DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
  sql_handle => 'SQL_...',
  plan_name => 'SQL_PLAN_...',
  attribute_name => 'fixed',
  attribute_value => 'YES'
);

Rule of thumb. Oracle SPM is the enterprise-grade forced plan mechanism.

Cheat sheet — plan hints recipe list

• Try ANALYZE first. Free, fast, fixes ~30% of bad plans.
• Then check estimate-vs-actual ratio. 10× off = suspect.
• Extended statistics for correlated columns. CREATE STATISTICS.
• Then rewrite — sargable predicates, avoid EXTRACT wrap.
• Then hint. Comment with justification.
• Then force plan. Query Store / SPM for hot queries.
• Postgres — pg_hint_plan extension. LOAD 'pg_hint_plan' + /*+ SeqScan(t) */.
• Postgres GUCs. enable_seqscan, enable_nestloop, random_page_cost.
• SQL Server OPTION. HASH JOIN, LOOP JOIN, MAXDOP N, RECOMPILE.
• SQL Server per-join. INNER HASH JOIN, INNER LOOP JOIN.
• SQL Server table hints. WITH (INDEX = idx), WITH (FORCESEEK).
• SQL Server USE HINT. Modern extensible mechanism.
• SQL Server parameter sniffing. OPTIMIZE FOR, OPTIMIZE FOR UNKNOWN, RECOMPILE.
• SQL Server Query Store. Force plan for hot queries; auto-tuning.
• Oracle /*+ USE_HASH(t) */. Force hash join.
• Oracle LEADING(a b c). Force join order.
• Oracle INDEX(t idx) / FULL(t). Scan method.
• Oracle SPM (SQL Plan Baseline). Enterprise-grade forced plans.
• MySQL STRAIGHT_JOIN. Force join order.
• MySQL USE INDEX / FORCE INDEX. Force index.
• MySQL 8+ /*+ JOIN_ORDER(a, b) */. Modern hint syntax.
• Snowflake /*+ NO_BROADCAST(t) */. Prevent broadcast.
• BigQuery /*+ HASH_JOIN */. Force hash join.
• Databricks /*+ BROADCAST(t) */. Force broadcast.
• Never hint globally. Session or query only.
• Comment every hint. Explain why.
• Test hinted plan. Verify actually faster.
• Revisit after engine upgrade. Hints may become obsolete.
• Prefer stats/rewrite over hint when possible.

Frequently asked questions

When should I hint a query vs refresh statistics?

Refresh stats first — always. ANALYZE is free, fast (seconds), and non-invasive; it fixes about 30% of bad plans on its own by giving the CBO fresh selectivity estimates. Only reach for a hint when: (a) stats are already fresh but the plan is still wrong (indicates correlated columns or a CBO estimation bug), (b) you can't wait for ANALYZE (production incident), or (c) the underlying cause is a fundamental CBO limitation (skew, complex subquery). Hints are a coupling — the query text now depends on a specific plan shape that may not survive engine upgrades or data-shape changes. Every hint should be accompanied by a comment explaining the CBO's mistake and a plan to revisit.

Why does my Postgres plan flip after upgrade?

Because the CBO's cost constants and heuristics evolve between major versions. Postgres 15 → 16 changed how nested loop costs are estimated for parallel scans; 14 → 15 tuned hash join thresholds. Same query, same data — different plan. Fix strategies: (1) update random_page_cost, seq_page_cost per your storage tier; (2) add extended statistics; (3) as bridge, hint via pg_hint_plan; (4) test critical queries in staging on the target version before upgrading production. Never assume plans survive upgrades.

What's parameter sniffing and how do I fix it?

SQL Server compiles a plan for a stored procedure the first time it's executed, using the actual parameter values passed. That plan is cached and reused for subsequent executions, even when parameters have very different selectivity. Consequence — a plan optimal for WHERE country='US' (100K rows) is disastrous for WHERE country='XY' (5M rows). Fixes: OPTION (OPTIMIZE FOR (@p = 'US')) pins to a representative value; OPTION (OPTIMIZE FOR UNKNOWN) uses average distribution; OPTION (RECOMPILE) builds fresh plan every call (expensive, use for low-frequency procs). For Postgres, SET plan_cache_mode = force_custom_plan per session forces prepared statements to replan.

Are hints portable across engines?

No. Hint syntax is fundamentally engine-specific. Postgres uses pg_hint_plan comment syntax; SQL Server uses OPTION clauses; Oracle uses /*+ */ directives; MySQL has its own /*+ */ optimizer hints plus older forms like STRAIGHT_JOIN. Even conceptually similar hints have different syntax. Best practice — keep hints in engine-specific migration files; write portable queries first, hint only when necessary; document each hint with the engine and reason. Use ORM abstractions that support per-dialect hints where portability matters.

What's the difference between hints and forced plans?

Hints live in query text — comment-based directives added to the SQL statement itself, visible in code review, applied on every execution. Forced plans are managed at the DB level — SQL Server Query Store sp_query_store_force_plan, Oracle SQL Plan Baseline — pinning a specific compiled plan by ID; the query text is unchanged. Trade-offs: hints are more visible and version-controllable but couple query text to plan; forced plans are more durable and survive query text changes but are less discoverable. For hot production queries where you need strong plan stability, forced plans; for ad-hoc or dev, hints.

How do I know if the CBO's plan is actually wrong?

Compare EXPLAIN ANALYZE output against a hypothetical better plan. Force the alternative via SET enable_* GUCs or a hint, re-run, measure. If the "better" plan is actually slower, the CBO was right — remove the hint. Common trap: engineers think they know the "right" plan but haven't measured. Every hint decision should be backed by two EXPLAIN ANALYZE outputs showing the current and hinted plans, with wall-clock comparison. If wall clock is close (within 20%), skip the hint — CBO's plan may improve with future upgrades. Only hint when the delta is significant (2x+) and stable across parameter values.

Practice on PipeCode

• Drill the SQL optimization practice library → for hint decisions and plan-reading exercises.
• Sharpen SQL practice library → — 450+ DE-focused questions covering plan hints and CBO internals.
• Layer SQL join drills → — most plan hints target join method or order.
• Warm up with aggregation drills → for hash aggregate cost model reads.
• Layer window function drills → — window nodes benefit from Parallel and Set(work_mem) hints.
• For the broader SQL interview surface, take the SQL for Data Engineering course →.

Pipecode.ai is Leetcode for Data Engineering — every `sql plan hints` recipe above ships with hands-on practice rooms where you type `LOAD 'pg_hint_plan'; /*+ HashJoin(o c) */` on a live Postgres, force `OPTION (OPTIMIZE FOR UNKNOWN)` on a SQL Server stored proc to defeat parameter sniffing, apply `/*+ USE_HASH(f) LEADING(d1 d2 f) */` on an Oracle star-schema query, and finally pin a hot query via SQL Server Query Store `sp_query_store_force_plan` — the exact plan-hinting fluency that senior DE and DBA interviews probe. PipeCode pairs every hint concept with 450+ DE-focused problems and a real-time scoring engine, so you never have to wonder whether your `pg_hint_plan` / `OPTION` / `LEADING` answer holds up under a senior interviewer's depth probes.

Practice optimization now →
SQL library →