A customer recently asked a great question:
- “Is there a session setting in PostgreSQL or YugabyteDB to make query hints ignored?”
Yes, there is.
Both PostgreSQL, when using the pg_hint_plan extension, and YugabyteDB, where pg_hint_plan is pre-configured and enabled by default, use the same setting to control whether optimizer hints are processed.
To make the database ignore hints for your current session, run:
SET pg_hint_plan.enable_hint = off;
That’s it.
Any /*+ ... */ hint blocks in your SQL will be treated like regular SQL comments, and the optimizer will choose the plan using its normal cost-based planning logic.
Why This Is Useful
Query hints can be helpful when you need to guide the planner toward a specific join method, scan type, or access path.
But hints can also become stale over time.
A hint that helped six months ago might not be the right choice after:
- ● table growth
- ● data distribution changes
- ● new indexes
- ● dropped or renamed indexes
- ● schema changes
- ● statistics changes
- ● YugabyteDB version upgrades
- ● optimizer improvements
Being able to temporarily disable hints is useful when you want to compare the hinted plan against the optimizer’s natural plan.
Turning off pg_hint_plan.enable_hint does not change the SQL text. It simply tells the planner to ignore the hint block and plan the query normally.
Understanding the Setting
The pg_hint_plan.enable_hint parameter controls whether hint comments are used by the planner.
on So unless you change it, hints are enabled.
Disable Hints for the Current Session
Use this when you want to test a few queries without hints, but do not want to change behavior for other sessions.
SET pg_hint_plan.enable_hint = off;
To turn hints back on:
SET pg_hint_plan.enable_hint = on;
Disable Hints for a Single Transaction
Use SET LOCAL when you only want hints ignored inside one transaction.
BEGIN;
SET LOCAL pg_hint_plan.enable_hint = off;
-- Queries in this transaction will ignore pg_hint_plan hints
COMMIT;
After the transaction commits or rolls back, the setting returns to its previous value.
Disable Hints for a Specific Role
You can also make this the default behavior for a specific user or role.
ALTER ROLE target_username
SET pg_hint_plan.enable_hint = off;
This affects new sessions for that role.
Existing sessions are not changed.
Disable Hints for a Specific Database
You can also set the default at the database level.
ALTER DATABASE target_dbname
SET pg_hint_plan.enable_hint = off;
This affects new sessions connected to that database.
Checking the Current Value
To confirm whether hints are enabled in your current session, run:
SHOW pg_hint_plan.enable_hint;
Example output:
.pg_hint_plan.enable_hint
--------------------------
on
(1 row)
YugabyteDB Bonus: Handling Bad Hints
YugabyteDB also has another useful setting:
SET pg_hint_plan.yb_bad_hint_mode = replan;
This is different from disabling hints entirely.
Instead of ignoring every hint, yb_bad_hint_mode = replan tells YugabyteDB what to do when a hint is invalid or cannot be applied safely.
For example, a hint may become invalid if it references an index that no longer exists, or if the hinted plan is no longer logically possible.
With replan, YugabyteDB can drop the problematic hint and replan the query without it.
That can be helpful when you want hints to remain available, but also want a safety net if one of them becomes stale or invalid.
Use pg_hint_plan.enable_hint = off when you want to ignore all hints.
Use pg_hint_plan.yb_bad_hint_mode = replan when you still want hints considered, but want YugabyteDB to recover more gracefully from invalid or unusable hints.
Scope Options
Here’s a quick summary of the different ways you can apply the setting.
Final Takeaway
Hints are powerful, but they should not be treated as permanent truth.
As data volumes, indexes, schemas, statistics, and optimizer behavior change, hinted plans should be revisited.
When testing query performance, it is often useful to compare:
- ● the hinted plan
- ● the normal optimizer-chosen plan
- ● the plan after refreshing statistics or adjusting indexes
And when you need to quickly make hints disappear for a test session, the setting is simple:
SET pg_hint_plan.enable_hint = off;
For YugabyteDB, pg_hint_plan.yb_bad_hint_mode = replan can also provide an extra safety net when you want hints enabled, but do not want invalid hints to break planning.
Use pg_hint_plan.enable_hint = off when you want the optimizer to ignore query hints and choose a plan normally. In YugabyteDB, consider pg_hint_plan.yb_bad_hint_mode = replan when you want to keep hints enabled but recover more gracefully from bad or stale hints.
Have Fun!
