OPT_PARAM is a query hint for PolarDB for PostgreSQL (Compatible with Oracle) that overrides optimizer parameters at the individual SQL statement level. Unlike database-level, session-level, or user-level parameter settings, values set through OPT_PARAM apply only to the statement that contains the hint.
Use OPT_PARAM when a specific query performs poorly due to a suboptimal execution plan and you need to test a different plan strategy without changing the global or session configuration.
The enable_* parameters controlled by OPT_PARAM are a quick way to influence query plans, not a long-term tuning solution. If a query consistently produces poor plans, investigate root causes such as stale statistics (run ANALYZE), inaccurate cost constants, or insufficient per-column statistics targets.
Syntax
SELECT /*+ opt_param("parameter_name" "value") */ ...;
Multiple hints can be combined in a single comment block:
SELECT /*+ opt_param("enable_hashjoin" "off"),
opt_param("enable_mergejoin" "off") */ ...;
Supported parameters
All parameters accept "on" or "off" as values.
Scan methods
| Parameter |
Description |
enable_bitmapscan |
Controls whether the query planner considers bitmap scan plans. |
enable_indexscan |
Controls whether the query planner considers index scan plans. |
enable_indexonlyscan |
Controls whether the query planner considers index-only scan plans. |
enable_seqscan |
Controls whether the query planner considers sequential scan plans. Sequential scans cannot be fully disabled; setting this to off discourages the planner from using them when other options are available. |
enable_tidscan |
Controls whether the query planner considers TID scan plans. |
Join methods
| Parameter |
Description |
enable_hashjoin |
Controls whether the query planner considers hash join plans. |
enable_mergejoin |
Controls whether the query planner considers merge join plans. |
enable_nestloop |
Controls whether the query planner considers nested loop join plans. Nested loop joins cannot be fully disabled; setting this to off discourages the planner from using them when other join methods are available. |
Aggregation
| Parameter |
Description |
enable_hashagg |
Controls whether the query planner considers hash aggregate plans. |
Sorting and materialization
| Parameter |
Description |
enable_sort |
Controls whether the query planner uses explicit sort steps. Explicit sorts cannot be fully disabled; setting this to off discourages the planner from using them when other sort strategies are available. |
enable_material |
Controls whether the query planner inserts materialization nodes. Materialization cannot be fully disabled; setting this to off prevents the planner from inserting materialization nodes. |
Parallel query
| Parameter |
Description |
enable_gathermerge |
Controls whether the query planner considers gather merge plans. |
enable_parallel_append |
Controls whether the query planner considers parallel append plans. |
enable_parallel_hash |
Controls whether the query planner uses parallel hash for hash join plans. Has no effect if enable_hashjoin is off. |
Partition optimization
| Parameter |
Description |
enable_partition_pruning |
Controls whether the query planner eliminates partitions from query plans and whether the query executor removes (ignores) partitions at runtime. |
enable_partitionwise_aggregate |
Controls whether the query planner performs grouping or aggregation separately for each partition. If the GROUP BY clause does not include all partition keys, only partial aggregation runs per partition and finalization occurs afterward. |
enable_partitionwise_join |
Controls whether the query planner performs joins between partitioned tables by joining matching partitions. Applies only when the join condition includes all partition keys, the partition keys have the same data type, and child partitions have a one-to-one correspondence. |
Examples
Disable bitmap scan for a single query
EXPLAIN (COSTS OFF) SELECT /*+ opt_param("enable_bitmapscan" "off") */ * FROM sampletable WHERE x < 423;
Disable merge join for a join query
EXPLAIN (COSTS OFF) SELECT /*+ opt_param("enable_mergejoin" "off") */ tt1.*, tt2.* FROM tt1 JOIN tt2 ON tt1.joincol = tt2.joincol;
Enable hash join for a join query
EXPLAIN (COSTS OFF) SELECT /*+ opt_param("enable_hashjoin" "on") */ tt1.*, tt2.* FROM tt1 JOIN tt2 ON tt1.joincol = tt2.joincol;
Combine multiple hints
Disable both hash join and merge join in a single statement:
EXPLAIN (COSTS OFF) SELECT /*+ opt_param("enable_hashjoin" "off"),
opt_param("enable_mergejoin" "off") */ tt1.*, tt2.* FROM tt1 JOIN tt2 ON tt1.joincol = tt2.joincol;