This topic describes the OPT_PARAM hint of PolarDB for PostgreSQL(Compatible with Oracle).
You can specify the values of specific parameters at the database level, session level, or user level. This method is traditional. When you execute a SQL statement, you cannot control the values of specific parameters for the statement. In this case, you can use the OPT_PARAM hint to specify the values of specific parameters for a SQL statement. Such values are valid only in the SQL statement.
Syntax
SELECT /*+ opt_param("enable_hashjoin" "off"),
opt_param("enable_mergejoin" "off") */ ...;Parameters
The OPT_PARAM hint allows you to specify the values of the following parameters:
| Parameter | Description |
|---|---|
| enable_hashjoin | Specifies whether to enable the query planner to use hash-join plan types. |
| enable_mergejoin | Specifies whether to enable the query planner to use merge-join plan types. |
| enable_nestloop | Specifies whether to enable the query planner to use nested-loop join plans. Nested-loop joins cannot be completely prohibited. However, if you disable this parameter, the planner prioritizes other methods. |
| enable_bitmapscan | Specifies whether to enable the query planner to use bitmap-scan plan types. |
| enable_indexonlyscan | Specifies whether to enable the query planner to use index-only-scan plan types. |
| enable_indexscan | Specifies whether to enable the query planner to use index-scan plan types. |
| enable_material | Specifies whether to enable the query planner to use materialization. Materialization cannot be completely prohibited. However, if you disable this parameter, the planner cannot insert materialization nodes. |
| enable_seqscan | Specifies whether to enable the query planner to use sequential scan plan types. Sequential scans cannot be completely prohibited. However, if you disable this parameter, the planner prioritizes other methods. |
| enable_sort | Specifies whether to enable the query planner to use explicit sort steps. Explicit sorts cannot be completely prohibited. However, if you disable this parameter, the planner prioritizes other methods. |
| enable_tidscan | Specifies whether to enable the query planner to use TID scan plan types. |
| enable_gathermerge | Specifies whether to enable the query planner to use gather-merge plan types. |
| enable_hashagg | Specifies whether to enable the query planner to use hashed aggregation plan types. |
| enable_parallel_append | Specifies whether to enable the query planner to use parallel-aware append plan types. |
| enable_parallel_hash | Specifies whether to enable the query planner to use hash-join plan types for parallel hashes. If hash-join plans are not enabled, this parameter does not take effect. |
| enable_partition_pruning | Specifies whether to enable the query planner to eliminate a partitioned table's partitions from query plans. It also controls the query planner's ability to generate query plans which allow the query executor to remove (ignore) partitions during query execution. |
| enable_partitionwise_aggregate | Specifies whether to enable the query planner to use partitionwise grouping or aggregation, which allows grouping or aggregation on a partitioned tables performed separately for each partition. If the GROUP BY clause does not include the partition keys, only partial aggregation can be performed on a per-partition basis, and finalization must be performed later. |
| enable_partitionwise_join | Specifies whether to enable the query planner to use partitionwise join, which allows a join between partitioned tables to be performed by joining the matching partitions. Partitionwise join currently applies only when the join conditions include all the partition keys, which must be of the same data type and have one-to-one matching sets of child partitions. |
Example
Do not use the OPT_PARAM hint:
EXPLAIN (COSTS OFF) SELECT * FROM sampletable WHERE x < 423;Use the OPT_PARAM hint and set the enable_bitmapscan parameter to off to disable the query planner to use bitmap-scan plan types:
EXPLAIN (COSTS OFF) SELECT /*+ opt_param("enable_bitmapscan" "off") */ * FROM sampletable WHERE x < 423;Use the OPT_PARAM hint and set the enable_mergejoin parameter to off to disable the query planner to use merge-join plan types:
EXPLAIN (COSTS OFF) SELECT /*+ opt_param("enable_mergejoin" "off") */ tt1.*, tt2.* FROM tt1 JOIN tt2 on tt1.joincol = tt2.joincol;Use the OPT_PARAM hint and set the enable_hashjoin parameter to on to enable the query planner to use hash-join plan types:
EXPLAIN (COSTS OFF) SELECT/*+ opt_param("enable_hashjoin" "on") */ tt1.*, tt2.* FROM tt1 JOIN tt2 on tt1.joincol = tt2.joincol;