GUC Parameter Hints

Function

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. @queryblock can be omitted, indicating that the hint takes effect in the current query block. This hint takes effect only when it specifies the outermost queryblock.
• param indicates the parameter name.
• value indicates the value of a parameter.
• Currently, the following parameters can be set and take effect by using hints:
  • Boolean type

    enable_bitmapscan, enable_hashagg, enable_hashjoin, enable_indexscan, enable_indexonlyscan, enable_gsiscan, enable_gsitablescan, enable_material, enable_mergejoin, enable_nestloop, enable_index_nestloop, enable_seqscan, enable_sort, enable_tidscan, enable_stream_operator, enable_stream_recursive, enable_broadcast, enable_fast_query_shipping, enable_trigger_shipping, enable_remotejoin, enable_remotegroup, enable_remotelimit, enable_remotesort, enable_inner_unique_opt, and enable_invisible_indexes

  • Integer type

    best_agg_plan and query_dop

  • Floating point type

    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

  • Character string type

    node_name

    By setting node_name, you can deliver the current SQL statement to the DN corresponding to node_name for execution.

    Example:

    select /*+ set(node_name datanode1) */ from table_name;

    datanode1 indicates the name of the DN queried from the pgxc_node system catalog (without quotation marks), and table_name indicates the table name. This query is directly performed on datanode1.

    

    • node_name can be set only by using the SELECT statement. If it is set by using other statements, it does not take effect.
    • node_name can only be set to the name of a DN and cannot be set to the name of a CN.
    • node_name cannot be modified by using the SET statement and can only be used in plan hints.
    • node_name cannot be modified by using gs_guc.
    • node_name supports only simple query statements and does not support complex query statements (such as UNION and UNION ALL), subqueries, and multi-table associations.
    • This operation can be performed by common users.
    • This operation cannot be performed together with row-level security. If they are performed together, an error will be reported.

• 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.