How to Control PostgreSQL Query Plans with pg_hint_plan

Why would you force PostgreSQL to use a specific index?

PostgreSQL's query planner is cost-based. It estimates the cost of every possible execution strategy — which index to use, which join algorithm, what order to join tables — and picks the cheapest one. Most of the time, it picks well. Admirably, even. But "most of the time" is not "always," and the gap between those two phrases is where production incidents live.

The planner can choose poorly when statistics are stale, when columns are correlated in ways the planner doesn't know about, when data distribution is heavily skewed, or when the cost model's assumptions don't match your hardware. When that happens, a query that should take 2ms takes 14 seconds. The plan is wrong, and the planner — with the serene confidence of someone who has never checked the actual results — doesn't know it.

In most databases — Oracle, SQL Server, MySQL — you can add hints directly to your SQL to override the optimizer's choices. PostgreSQL deliberately does not offer this. The community's position is that if the planner makes a bad choice, the right fix is better statistics, better indexes, or better cost parameters — not a hint that papers over the problem.

I respect that philosophy. It is, in the main, correct. But philosophy is cold comfort at 3am when a dashboard is timing out. You sometimes need to fix a production query in minutes, not hours. You sometimes need to match an Oracle plan during a migration. You sometimes need to prove that a different plan would be faster before committing to an index change. For all of these situations, there is pg_hint_plan — and I shall walk you through it.

Why doesn't PostgreSQL have built-in hints?

This is a deliberate design choice, not an oversight — and it is one I find largely admirable, if occasionally inconvenient. The PostgreSQL core team has rejected hint syntax proposals multiple times over the project's history. Tom Lane, a PostgreSQL core developer, argued against hints in 2006, stating that attaching hints to individual queries is "completely misguided" — that the correct approach is to fix the statistical or cost estimation problems that cause bad plans in the first place.

The arguments against built-in hints are worth understanding, because they shape how you should think about pg_hint_plan even when you use it:

• Hints go stale. A hint that forces the right plan today may force the wrong plan after a data migration, a schema change, or six months of organic growth. Nobody remembers to revisit hints.
• Hints mask planner bugs. If developers work around bad plans with hints instead of reporting them, the planner never improves. The PostgreSQL project depends on bug reports to identify weak spots in the cost model.
• Hints create false confidence. Developers who learn to "just add a hint" stop learning to diagnose the actual problem — stale statistics, missing indexes, correlated columns, incorrect cost parameters.
• The planner usually wins. Across millions of queries, a cost-based optimizer with accurate statistics outperforms human intuition. Hints encode a human's understanding of one moment in time. The planner adapts continuously.

These are legitimate concerns, every one of them. They are also of limited consolation when production is burning and you know precisely which plan the query needs. pg_hint_plan exists in that gap between philosophy and pragmatism — the fire extinguisher behind the glass that you hope never to use, but are profoundly grateful to find when the moment arrives.

How do you install pg_hint_plan?

pg_hint_plan is not a core contrib module — it must be installed separately. If you'll permit me, I shall attend to each environment in turn.

Self-managed PostgreSQL (Linux)

Install from the PGDG repository (the same repository you use for PostgreSQL itself):

# Install from PGDG repository (Debian/Ubuntu, PostgreSQL 17)
sudo apt-get install postgresql-17-pg-hint-plan

# For PostgreSQL 16:
# sudo apt-get install postgresql-16-pg-hint-plan

# Install from PGDG repository (RHEL/Rocky/Alma, PostgreSQL 17)
sudo dnf install pg_hint_plan_17

Then add it to shared_preload_libraries in postgresql.conf:

# postgresql.conf — add pg_hint_plan to shared_preload_libraries
# A restart is required after changing this parameter.
shared_preload_libraries = 'pg_hint_plan'

# If you already have other libraries loaded:
shared_preload_libraries = 'pg_stat_statements, pg_hint_plan'

Restart PostgreSQL (a reload is not sufficient — shared_preload_libraries requires a full restart). Then create the extension in each database where you need it:

-- Create the extension in your database
CREATE EXTENSION pg_hint_plan;

-- Verify installation
SELECT * FROM pg_extension WHERE extname = 'pg_hint_plan';

If you want to test pg_hint_plan without modifying shared_preload_libraries or restarting, you can load it for a single session:

-- Alternatively, load pg_hint_plan for the current session only
-- (does not require shared_preload_libraries or a restart)
LOAD 'pg_hint_plan';

The session-level LOAD is useful for a brief acquaintance but does not enable the hint table feature, which requires shared_preload_libraries. For anything beyond evaluation, the full installation is what I would recommend.

AWS RDS and Aurora PostgreSQL

pg_hint_plan is a supported extension on both RDS and Aurora. It has been available since PostgreSQL 9.4 on RDS.

-- AWS RDS / Aurora PostgreSQL
-- pg_hint_plan is available. Add it via Parameter Groups:
-- shared_preload_libraries = 'pg_hint_plan'
-- Then restart the instance and run:
CREATE EXTENSION pg_hint_plan;

Google Cloud SQL

pg_hint_plan is available on Cloud SQL for PostgreSQL. Enable it via the cloudsql.enable_pg_hint_plan database flag.

-- Google Cloud SQL for PostgreSQL
-- Set the database flag: cloudsql.enable_pg_hint_plan = on
-- Then connect and run:
CREATE EXTENSION pg_hint_plan;

-- Or load for the current session (cloudsqlsuperuser role required):
LOAD 'pg_hint_plan';

Azure Database for PostgreSQL

pg_hint_plan is available on Azure Flexible Server.

-- Azure Database for PostgreSQL (Flexible Server)
-- Add pg_hint_plan to shared_preload_libraries via Azure Portal
-- or az CLI, then restart and run:
CREATE EXTENSION pg_hint_plan;

Other managed providers

How does pg_hint_plan hint syntax work?

The syntax is refreshingly direct. Hints are written as block comments with a special prefix: /*+. The comment must appear before the SQL statement (not inside it). There must be no space between /* and +. Inside the comment, each hint is a function-like expression specifying the table (or alias) and the desired behavior.

Scan method hints — forcing or preventing index usage

Scan hints are the most common use of pg_hint_plan, and the ones you will likely reach for first. They tell the planner which method to use when reading a specific table.

-- Without a hint — the planner chooses its own scan method:
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;
-- Result: Index Scan using idx_orders_customer_id (0.04ms)

-- Force a sequential scan instead:
/*+
  SeqScan(orders)
*/
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;
-- Result: Seq Scan on orders (127.45ms)
-- The planner was right. But now you know for certain.

To force a specific index (not just any index scan, but a particular one):

-- Force a specific index when the planner picks the wrong one
/*+
  IndexScan(orders idx_orders_status_created)
*/
SELECT * FROM orders
WHERE status = 'pending'
  AND created_at > '2026-01-01';

You can also prevent a scan method, letting the planner choose among the remaining options:

-- Prevent a specific scan method — let the planner pick anything else
/*+
  NoSeqScan(orders)
*/
SELECT * FROM orders WHERE status = 'pending';

-- Prevent index scan — force bitmap or seq scan
/*+
  NoIndexScan(orders)
*/
SELECT * FROM orders WHERE customer_id = 42;

The full reference of scan hints:

-- Scan method hints (positive — force this method)
SeqScan(table)                -- Sequential scan
IndexScan(table [index])      -- Index scan, optionally naming the index
IndexOnlyScan(table [index])  -- Index-only scan
BitmapScan(table [index])     -- Bitmap scan
TidScan(table)                -- TID scan
TidRangeScan(table)           -- TID range scan (PG 14+)

-- Scan method hints (negative — prevent this method)
NoSeqScan(table)
NoIndexScan(table)
NoIndexOnlyScan(table)
NoBitmapScan(table)
NoTidScan(table)

Join method hints

Join hints control which algorithm the planner uses to combine two or more tables. These become essential when the planner's row estimate for one side of the join is wrong — causing it to choose a nested loop where a hash join would be orders of magnitude faster. I have seen this particular misjudgement turn a 200ms query into a 30-second ordeal.

-- Force a hash join between orders and customers
/*+
  HashJoin(o c)
*/
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;

-- Force a nested loop join
/*+
  NestLoop(o c)
*/
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;

-- Force a merge join
/*+
  MergeJoin(o c)
*/
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;

The full reference:

-- Join method hints (positive)
NestLoop(t1 t2 [t3 ...])     -- Nested loop join
HashJoin(t1 t2 [t3 ...])     -- Hash join
MergeJoin(t1 t2 [t3 ...])    -- Merge join

-- Join method hints (negative)
NoNestLoop(t1 t2 [t3 ...])
NoHashJoin(t1 t2 [t3 ...])
NoMergeJoin(t1 t2 [t3 ...])

Join ordering with Leading

The Leading hint controls the order in which tables are joined. In a three-table join, the difference between joining A-B first then C, versus A-C first then B, can be dramatic — especially when one pair produces a much smaller intermediate result.

-- Control the join order of three tables
-- Force: join customers and orders first, then join line_items
/*+
  Leading((customers (orders line_items)))
*/
SELECT c.name, o.id, li.product_id
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN line_items li ON li.order_id = o.id
WHERE c.country = 'US';

-- Simpler form: just specify a flat ordering (planner picks nesting)
/*+
  Leading(customers orders line_items)
*/
SELECT c.name, o.id, li.product_id
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN line_items li ON li.order_id = o.id;

The parenthesized form Leading((c (o li))) specifies both order and nesting: join o and li first, then join the result with c. The flat form Leading(c o li) specifies only the order and lets the planner decide the nesting structure.

Row count correction with Rows

The Rows hint is, if I may say so, the most elegant hint in the repertoire. Rather than forcing a specific plan, you correct the input that the planner uses to make its own decision. If the planner underestimates a join's output by 100x, it will choose a nested loop where a hash join is appropriate. Correcting the estimate lets the planner reach the right conclusion on its own. This is the difference between driving manual and adjusting the automatic transmission's map. I have a strong preference for the latter.

-- The planner estimates 100 rows, but you know it will be ~50,000
-- Correct the estimate so the planner picks a better join strategy
/*+
  Rows(orders #50000)
*/
SELECT * FROM orders WHERE status = 'pending';

-- Multiply the planner's estimate by 10
/*+
  Rows(orders customers *10)
*/
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;

-- Rows hint operators:
-- Rows(t1 t2 #n)   Set the join estimate to exactly n
-- Rows(t1 t2 +n)   Add n to the estimate
-- Rows(t1 t2 -n)   Subtract n from the estimate
-- Rows(t1 t2 *n)   Multiply the estimate by n

Parallel execution hints

The Parallel hint controls parallel query execution for a specific table scan. It takes three arguments: the table, the number of workers, and the enforcement level.

-- Force 4 parallel workers on a large table scan
/*+
  Parallel(events 4 hard)
*/
SELECT count(*) FROM events WHERE created_at > '2026-01-01';

-- "soft" only sets max_parallel_workers_per_gather (planner decides the rest)
-- "hard" overrides additional planner parameters to enforce parallelism
-- Parallel(table 0 hard) disables parallelism for a specific scan

/*+
  Parallel(events 0 hard)
*/
SELECT count(*) FROM events;

GUC parameter hints with Set

The Set hint overrides planner-related GUC parameters for the duration of planning a single query. The parameters revert to their session values after planning completes.

-- Override GUC parameters during planning
/*+
  Set(random_page_cost 1.1)
  Set(seq_page_cost 1.0)
*/
SELECT * FROM orders WHERE customer_id = 42;

-- Useful for SSD-based systems where random I/O is nearly as fast as sequential
-- Or to temporarily change work_mem for a specific query's planning phase
/*+
  Set(work_mem "256MB")
*/
SELECT * FROM large_table ORDER BY some_column;

Subquery hints

When hinting queries that contain subqueries, use the table alias if one exists. For IN (SELECT ...) subqueries without an explicit alias, pg_hint_plan uses the implicit name ANY_subquery.

-- Hints apply to table names or aliases used in the query
-- For subqueries, use the alias:
/*+
  HashJoin(o sq)
*/
SELECT o.id
FROM orders o
JOIN (SELECT customer_id FROM customers WHERE country = 'US') sq
  ON sq.customer_id = o.customer_id;

-- For IN (SELECT ...) subqueries, use the implicit name ANY_subquery:
/*+
  NestLoop(orders ANY_subquery)
*/
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'US');

Combining multiple hints

Multiple hints can be combined in a single comment block. This is where pg_hint_plan reveals its full capability — you can specify the scan method, join strategy, join order, row estimates, and planner parameters all at once. A word of caution: the more you specify, the more you are telling the planner "I know better than you about everything." That is a considerable claim. Be certain you can support it.

-- A real-world example: combine multiple hint types
/*+
  IndexScan(o idx_orders_status_created)
  HashJoin(o c)
  Leading((c o))
  Rows(o c *5)
  Set(work_mem "128MB")
*/
EXPLAIN ANALYZE
SELECT o.id, o.total, c.name, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
  AND o.created_at > '2026-01-01';

When should you use pg_hint_plan? Three real-world scenarios

Scenario 1: Emergency plan fix in production

A query regressed after autovacuum ran ANALYZE. The new statistics caused the planner to switch from a hash join to a nested loop. The query went from sub-second to 14 seconds. Dashboards are timing out. Customers are noticing. The on-call engineer's phone is doing that thing phones do at 3am.

You don't have time to investigate whether the new statistics are wrong, whether you need extended statistics for correlated columns, or whether an index change would help. You need the query working now.

-- Scenario: a query regressed after an ANALYZE run.
-- The planner now picks a nested loop where it used to pick a hash join.
-- Production is degraded. You need a fix in minutes, not hours.

-- Step 1: Confirm the bad plan
EXPLAIN ANALYZE
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';
-- Result: Nested Loop (14.8s) — the planner estimates 50 rows, actual 247,000

-- Step 2: Pin the good plan with a hint
/*+
  HashJoin(o c)
*/
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';
-- Result: Hash Join (0.82s) — correct plan, production stabilized

-- Step 3: Investigate the root cause (stale statistics, data skew, etc.)
-- Step 4: Remove the hint once the underlying issue is fixed

The hint is the tourniquet. It stops the bleeding. The root cause investigation — stale statistics, missing extended statistics, data skew — happens after production is stable. The critical discipline is step 4: remove the hint once you've fixed the underlying problem. I cannot stress this sufficiently. A hint that outlives its emergency is technical debt that compounds in silence, and silent debt is the most expensive kind.

Scenario 2: Oracle-to-PostgreSQL migration

I understand how this situation arises, even if I cannot quite bring myself to endorse it. Oracle developers use hints extensively — it is part of the culture. During migration, queries that relied on Oracle hints may produce suboptimal plans on PostgreSQL because the planner's cost model and statistics are different. pg_hint_plan provides a bridge: translate Oracle hints to pg_hint_plan syntax to maintain plan stability during migration, then gradually remove hints as you tune PostgreSQL's statistics and indexes. The word "gradually" is doing important work in that sentence. The goal is to arrive at a PostgreSQL system that stands on its own judgment, not one that has merely traded Oracle's manual transmission for a different make of manual transmission.

-- Oracle hint:
-- SELECT /*+ USE_HASH(o c) INDEX(o ix_orders_status) */ o.id, c.name
-- FROM orders o JOIN customers c ON ...

-- Equivalent pg_hint_plan hint:
/*+
  HashJoin(o c)
  IndexScan(o ix_orders_status)
*/
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';

-- Key differences from Oracle hints:
-- 1. Hints go BEFORE the SELECT, not inside it
-- 2. Use table aliases, not table names (if aliased in query)
-- 3. /*+ must have no space between /* and +
-- 4. Oracle FULL() = pg SeqScan()
-- 5. Oracle USE_NL() = pg NestLoop()
-- 6. Oracle USE_MERGE() = pg MergeJoin()

The pganalyze migration guide covers the full mapping between Oracle and pg_hint_plan syntax.

Scenario 3: A/B testing query plans

This, I should note, is my favourite use of pg_hint_plan — and the one that earns my genuine approval. You suspect the planner's default plan isn't optimal, but you're not sure. Rather than guessing, use pg_hint_plan to force different strategies and measure them with EXPLAIN (ANALYZE, BUFFERS).

-- A/B testing query plans: which strategy is actually faster?

-- Plan A: Planner's default choice
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total, p.name
FROM orders o
JOIN products p ON p.id = o.product_id
WHERE o.created_at > '2026-01-01';

-- Plan B: Force a merge join
/*+
  MergeJoin(o p)
*/
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total, p.name
FROM orders o
JOIN products p ON p.id = o.product_id
WHERE o.created_at > '2026-01-01';

-- Plan C: Force a nested loop with a specific index
/*+
  NestLoop(o p)
  IndexScan(p products_pkey)
*/
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total, p.name
FROM orders o
JOIN products p ON p.id = o.product_id
WHERE o.created_at > '2026-01-01';

-- Compare: execution time, buffer hits, buffer reads, rows removed
-- The planner usually wins. But now you have evidence instead of opinion.

This is arguably pg_hint_plan's most valuable use case. It turns "I think a hash join would be faster" into "I measured a hash join and it's 3.2x faster." Evidence over intuition. If the planner's choice turns out to be correct — and it often will be — you've avoided an unnecessary index change. If it's wrong, you have the numbers to justify the fix. Either way, you have learned something. That is never wasted.

How does the hints table work for persistent plan control?

Embedding hints in SQL comments works when you control the application code. But what about ORM-generated queries? Third-party applications? Queries where the SQL is assembled by a framework and you cannot so much as insert a comment?

pg_hint_plan addresses this with the hint_plan.hints table — and it is rather clever. You register a normalized query pattern and the hints you want applied. When pg_hint_plan sees an incoming query that matches the pattern, it applies the hints automatically. No SQL modification required.

Setting up the hints table

-- Enable the hint table feature
SET pg_hint_plan.enable_hint_table TO on;

-- The table is created automatically by CREATE EXTENSION
-- Structure: hint_plan.hints (id, norm_query_string, application_name, hints)

-- View the table structure
\d hint_plan.hints

Adding persistent hints

-- Add a persistent hint for a normalized query pattern
INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
VALUES (
  'SELECT * FROM orders WHERE customer_id = $1;',
  '',
  'IndexScan(orders idx_orders_customer_id)'
);

-- The empty application_name means this hint applies to all sessions.
-- To restrict a hint to a specific application:
INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
VALUES (
  'SELECT * FROM orders WHERE status = $1 AND created_at > $2;',
  'web_backend',
  'IndexScan(orders idx_orders_status_created)'
);

The norm_query_string column uses the same normalization as pg_stat_statements: literal values are replaced with positional parameters ($1, $2, etc.). You can find the normalized form of any query by checking pg_stat_statements.query or by running EXPLAIN (VERBOSE) and looking at the query ID.

When hints exist in both the SQL comment and the hints table for the same query, the table hints take precedence.

Managing hints

-- List all registered hints
SELECT id, left(norm_query_string, 60) AS query, application_name, hints
FROM hint_plan.hints
ORDER BY id;

-- Remove a hint by ID
DELETE FROM hint_plan.hints WHERE id = 3;

-- Disable a hint temporarily by setting enable_hint_table off
SET pg_hint_plan.enable_hint_table TO off;

The hints table is a powerful feature, but every row in it is a promise you are making: "I know better than the planner for this specific query, and I will review this periodically." I would encourage you to take that promise seriously. Establish a review cadence — monthly or quarterly — to verify that table-based hints are still earning their keep. A hint that was essential six months ago may now be forcing a plan that's worse than the planner's default. And the planner, unlike the hint, has been paying attention to how the data has changed.

Why isn't my hint taking effect?

Ah. This is the question I am asked most frequently, and it is rarely comfortable. You add a hint, run EXPLAIN ANALYZE, and the plan hasn't changed. The debug output is your diagnostic tool — and I would recommend enabling it the moment you begin working with hints, not after you've spent twenty minutes wondering why nothing happened.

-- Enable debug output to see which hints were applied
SET pg_hint_plan.debug_print TO on;
SET client_min_messages TO log;

/*+
  IndexScan(o idx_orders_customer_id)
  HashJoin(o c)
*/
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.customer_id = 42;

-- Log output shows four categories:
-- LOG: pg_hint_plan:
-- used hint:
--   IndexScan(o idx_orders_customer_id)
--   HashJoin(o c)
-- not used hint:
-- duplication hint:
-- error hint:

When a hint appears under "not used hint," pg_hint_plan recognized the syntax but couldn't apply it:

-- A hint that cannot be satisfied is silently ignored
/*+
  IndexScan(orders idx_that_does_not_exist)
*/
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

-- The planner falls back to its default plan.
-- With debug_print on, the log shows:
-- not used hint:
--   IndexScan(orders idx_that_does_not_exist)

The four most common reasons hints don't work

-- Common reasons hints do not take effect:

-- 1. Wrong table reference — use the alias, not the table name
/*+
  IndexScan(orders idx_orders_status)       -- WRONG if query uses alias "o"
  IndexScan(o idx_orders_status)            -- CORRECT
*/
SELECT * FROM orders o WHERE o.status = 'pending';

-- 2. Missing the + in the comment — must be /*+ with no space
/* IndexScan(orders idx_orders_status) */   -- WRONG: plain comment, not a hint
/*+ IndexScan(orders idx_orders_status) */  -- CORRECT

-- 3. Hint placed after the SELECT keyword
SELECT /*+ SeqScan(orders) */ * FROM orders; -- WRONG: pg_hint_plan reads
                                              -- the comment BEFORE the query
/*+ SeqScan(orders) */
SELECT * FROM orders;                        -- CORRECT

-- 4. Connection pooler stripping comments (statement-mode PgBouncer)
-- Solution: use the hint_plan.hints table instead

A fifth, subtler issue deserves its own mention: the hint is applied, but the plan looks the same. This happens when the hinted plan is physically impossible — for example, hinting IndexOnlyScan on a table where the index doesn't cover all selected columns, or hinting MergeJoin when neither side has a compatible sort order and the planner determines that sorting both sides would be more expensive than any alternative. In these cases, pg_hint_plan tries to honor the hint but the planner's internal constraints override it. The debug output still shows "used hint," which is — I shall be frank — confusing. The hint was heard. It simply could not be obeyed.

When should you NOT use pg_hint_plan?

If I may be candid — and I feel I must — hints are the wrong tool more often than they are the right one. I have just spent considerable effort teaching you how to use pg_hint_plan. Allow me to now spend equal effort explaining when not to. The distinction matters more than the syntax ever will.

-- Before reaching for pg_hint_plan, try these first:

-- 1. Update statistics
ANALYZE orders;

-- 2. Check for stale statistics on related tables
ANALYZE customers;

-- 3. Create extended statistics for correlated columns
CREATE STATISTICS stts_orders_status_type (dependencies)
  ON status, order_type FROM orders;
ANALYZE orders;

-- 4. Adjust planner cost parameters for your hardware
SET random_page_cost = 1.1;  -- SSD storage (default 4.0 is for spinning disks)
SET effective_cache_size = '24GB';  -- Total memory available for caching

-- 5. Check for table bloat
SELECT pg_size_pretty(pg_table_size('orders')) AS table_size,
       n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

Fix statistics first

The single most common cause of bad plans is stale statistics. Not missing indexes. Not planner bugs. Stale statistics. Run ANALYZE on the relevant tables before you do anything else. If autovacuum isn't running ANALYZE frequently enough for your workload, lower the threshold:

ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.02);

The default threshold is 10% of the table's rows. On a 10 million row table, that means 1 million rows must change before autoanalyze triggers. For tables with rapid churn, 2% is a more reasonable threshold.

Fix correlated columns with extended statistics

If two columns in your WHERE clause are correlated (e.g., city and state), the planner assumes independence and underestimates the combined selectivity. This is not a planner deficiency — it is a planner working with incomplete information. Extended statistics supply what's missing:

CREATE STATISTICS stts_orders (dependencies) ON status, order_type FROM orders;

Fix cost parameters for your hardware

The default random_page_cost = 4.0 assumes spinning disks. On SSD storage (which is nearly all cloud infrastructure), random I/O is almost as fast as sequential. Setting random_page_cost = 1.1 makes the planner more willing to use index scans. This one parameter change — five seconds of work, no restart required at the session level — resolves a surprising number of "wrong scan method" complaints. I would go so far as to call it the single most underappreciated tuning knob in PostgreSQL.

Fix the index

If the planner chooses a sequential scan because no suitable index exists, the fix is an index — not a hint that forces a bad index scan or a bitmap scan on an unrelated index. A hint cannot conjure an index into existence. Build the right composite index and the planner will use it without being told. That is always the preferable outcome: a planner that makes the right decision on its own, not one that requires a chaperone for every query.

When hints are genuinely appropriate

After you've worked through the above — and only then — hints are appropriate for:

• Emergency stabilization — pin a plan while investigating the root cause
• Known planner blind spots — complex CTEs, unusual data distributions, or join graphs with 10+ tables where the planner's search space is too large
• Migration bridges — maintaining plan stability during Oracle/SQL Server migrations
• Plan testing — comparing strategies with EXPLAIN ANALYZE before committing to index changes
• Queries you cannot modify — third-party apps with poor plans, using the hints table

How Gold Lapel attends to the same problem

pg_hint_plan is manual plan control. You identify a bad plan, write a hint, deploy it, and then carry the responsibility of remembering it exists, reviewing it periodically, and removing it when it becomes counterproductive. For a handful of queries, this is manageable. For a production system with thousands of query patterns, it does not scale — and I would be doing you a disservice to pretend otherwise.

Gold Lapel addresses the same fundamental problem — the planner sometimes makes poor choices — from the opposite direction. It sits between your application and PostgreSQL as a transparent proxy, observing actual query performance. When it detects a plan regression or an optimization opportunity, it applies the fix automatically: recommending indexes, creating materialized views, or rewriting queries to produce better plans.

The key difference is adaptability. A pg_hint_plan hint is a photograph — it captures one moment in time. Gold Lapel observes continuously. If the planner's default plan improves to the point where the optimization is no longer needed, Gold Lapel backs off. If the data distribution shifts and a new optimization is needed, Gold Lapel detects it. The hint doesn't know the data has changed. Gold Lapel does.

The two tools are not mutually exclusive. Gold Lapel passes hinted queries through unchanged. Some teams use pg_hint_plan for immediate triage — the tourniquet — while Gold Lapel handles the longer-term optimization loop. That is a sensible arrangement, and one that ensures no hint quietly outlives its usefulness.