Updated on 2024-05-07 GMT+08:00

Optimizer GUC Parameter Hints

Description

Sets GUC parameters related to query optimization. The settings take effect during the query execution. For details about the application scenarios of hints, see the description of each GUC parameter.

Syntax

1
set( [@queryblock] param value)

Parameters

  • For details about @queryblock, see Hint Specifying the Query Block Where the Hint Is Located. It can be omitted, indicating that the hint takes effect in the current query block. This hint takes effect only when it is specifies the outermost query block.
  • param indicates the parameter name.
  • value indicates the value of a parameter.
  • Currently, the following parameters can be set and take effect by using Hint:
    • Boolean

      enable_bitmapscan, enable_hashagg, enable_hashjoin, enable_indexscan, enable_indexonlyscan, enable_material, enable_mergejoin, enable_nestloop, enable_index_nestloop, enable_seqscan, enable_sort, enable_tidscan, partition_iterator_elimination, partition_page_estimation, enable_functional_dependency, var_eq_const_selectivity, and enable_inner_unique_opt

    • Integer type

      query_dop

    • Floating point

      cost_weight_index, default_limit_rows, seq_page_cost, random_page_cost, cpu_tuple_cost, cpu_index_tuple_cost, cpu_operator_cost, and effective_cache_size

  • If you set a parameter that is not in the whitelist and the parameter value is invalid or the hint syntax is incorrect, the query execution is not affected. Run explain(verbose on). An error message is displayed, indicating that hint parsing fails.
  • The GUC parameter hint takes effect only in the outermost query. That is, the GUC parameter hint in the subquery does not take effect.
  • The GUC parameter hint in the view definition does not take effect.
  • In the CREATE TABLE ... AS ... statement, the outermost GUC parameter hint takes effect.