Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

Optimizer Method Configuration

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

These configuration parameters provide a crude method of influencing the query plans chosen by the query optimizer. If the default plan chosen by the optimizer for a particular query is not optimal, a temporary solution is to use one of these configuration parameters to force the optimizer to choose a different plan. Better ways include adjusting the optimizer cost constants, manually running ANALYZE, increasing the value of the default_statistics_target parameter, and increasing the amount of the statistics collected in specific columns using ALTER TABLE SET STATISTICS.

enable_broadcast

Parameter description: Controls whether the query optimizer uses the broadcast distribution method when it evaluates the cost of stream.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

  • on: enabled.
  • off: disabled.

Default value: on

NOTE:

This parameter does not take effect in the current version.

enable_bitmapscan

Parameter description: Specifies the query optimizer's use of bitmap-scan plan types.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

  • on: enabled.
  • off: disabled.

Default value: on

force_bitmapand

Parameter description: Specifies the query optimizer's use of BitmapAnd plan types.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

  • on: enabled.
  • off: disabled.

Default value: off

enable_hashagg

Parameter description: Specifies the query optimizer's use of Hash aggregation plan types.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

  • on: enabled.
  • off: disabled.

Default value: on

enable_hashjoin

Parameter description: Specifies the query optimizer's use of hash-join plan types.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

  • on: enabled.
  • off: disabled.

Default value: on

enable_indexscan

Parameter description: Specifies the query optimizer's use of index-scan plan types.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

  • on: enabled.
  • off: disabled.

Default value: on

enable_indexonlyscan

Parameter description: Specifies the query optimizer's use of index-only-scan plan types.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

  • on: enabled.
  • off: disabled.

Default value: on

enable_material

Parameter description: Specifies the query optimizer's use of materialization. It is impossible to suppress materialization entirely, but setting this variable to off prevents the optimizer from inserting materialized nodes.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

  • on: enabled.
  • off: disabled.

Default value: on

enable_mergejoin

Parameter description: Specifies the query optimizer's use of merge-join plan types.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

  • on: enabled.
  • off: disabled.

Default value: off

enable_nestloop

Parameter description: Specifies whether the query optimizer uses the nested-loop join plan type to fully scan internal tables. It is impossible to suppress nested-loop joins entirely, but setting this variable to off encourages the optimizer to choose other methods if available.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

  • on: enabled.
  • off: disabled.

Default value: off

enable_index_nestloop

Parameter description: Specifies whether the query optimizer uses the nested-loop join plan type to scan the parameterized indexes of internal tables.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

  • on: enabled.
  • off: disabled.

Default value: on

enable_seqscan

Parameter description: Specifies the query optimizer's use of sequential scan plan types. It is impossible to suppress sequential scans entirely, but setting this variable to off encourages the optimizer to choose other methods if available.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

  • on: enabled.
  • off: disabled.

Default value: on

enable_sort

Parameter description: Specifies the query optimizer's choice of sort methods. It is impossible to suppress explicit sorts entirely, but setting this variable to off encourages the optimizer to choose other methods if available.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

  • on: enabled.
  • off: disabled.

Default value: on

enable_tidscan

Parameter description: Specifies the query optimizer's use of Tuple ID (TID) scan plan types.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

  • on: enabled.
  • off: disabled.

Default value: on

enable_kill_query

Parameter description: In CASCADE mode, when a user is deleted, all the objects belonging to the user are deleted. This parameter specifies whether the queries of the objects belonging to the user can be unlocked when the user is deleted.

This parameter is a SUSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

  • on indicates that the unlocking is allowed.
  • off indicates that the unlocking is not allowed.

Default value: off

enforce_a_behavior

Parameter description: Specifies the rule matching modes of regular expressions.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

  • on indicates that the A matching rule is used.
  • off indicates that the POSIX matching rule is used.

Default value: on

max_recursive_times

Parameter description: Specifies the maximum number of WITH RECURSIVE iterations.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: an integer ranging from 0 to INT_MAX

Default value: 200

enable_vector_engine

Parameter description: Specifies the query optimizer's use of vectorized executor.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

  • on: enabled.
  • off: disabled.

Default value: on

enable_change_hjcost

Parameter description: Specifies whether the optimizer excludes internal table running costs when selecting the Hash Join cost path. If it is set to on, tables with a few records and high running costs are more possible to be selected.

This parameter is a SUSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

  • on: enabled.
  • off: disabled.

Default value: off

enable_absolute_tablespace

Parameter description: Specifies whether the tablespace can use the absolute path.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

  • on indicates that an absolute path can be used.
  • off indicates that an absolute path cannot be used.

Default value: on

enable_valuepartition_pruning

Parameter description: Specifies whether the DFS partitioned table is dynamically or statically optimized.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

  • on indicates that the DFS partitioned table is dynamically or statically optimized.
  • off indicates that the DFS partitioned table is not dynamically or statically optimized.

Default value: on

qrw_inlist2join_optmode

Parameter description: Specifies whether to enable inlist-to-join (inlist2join) query rewriting.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: a string

  • disable indicates that the inlist2join query rewriting is disabled.
  • cost_base indicates that the cost-based inlist2join query rewriting is enabled.
  • rule_base indicates that the forcible rule-based inlist2join query rewriting is enabled.
  • A positive integer indicates the threshold of inlist2join query rewriting. If the number of elements in the list is greater than the threshold, the rewriting is performed.

Default value: cost_base

skew_option

Parameter description: Specifies whether an optimization policy is used.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: a string

  • off indicates that the policy is disabled.
  • normal indicates that a radical policy is used. All possible skews are optimized.
  • lazy indicates that a conservative policy is used. Uncertain skews are ignored.

Default value: normal

default_limit_rows

Parameter description: Specifies the default estimated number of limit rows for generating genericplan. If this parameter is set to a positive value, the positive value is used as the estimated number of limit rows. If this parameter is set to a negative value, the negative value is converted to a percentage and used as default estimated value, that is, -5 indicates 5%.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: a floating point number ranging from –100 to DBL_MAX

Default value: –10

check_implicit_conversions

Parameter description: Specifies whether to check candidate index paths generated for index columns that have implicit type conversions in a query.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

  • on indicates that a check will be performed for candidate index paths generated for index columns that have implicit type conversion in a query.
  • off indicates that a check will not be performed.

Default value: off

cost_weight_index

Parameter description: Specifies the cost weight of index_scan.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: a floating point number ranging from 1e-10 to 1e+10.

Default value: 1

enable_default_index_deduplication

Parameter description: Specifies whether to deduplicate and compress tuples with duplicate key values for a B-tree index by default. The deduplication and compression functions do not take effect for primary key indexes and unique indexes. When there are a large number of indexes with duplicate key values, the deduplication and compression function can effectively reduce the space occupied by indexes. In scenarios where non-unique indexes are used and index key values are seldom repeated or unique, the deduplication and compression function slightly deteriorates the index insertion performance. If the WITH (deduplication set to on/off) syntax is used during index creation, the deduplication parameter is preferentially used to determine whether to use deduplication and compression for the index.

This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

  • off: indicates that the deduplication and compression function is disabled for B-tree indexes. This is the default value.
  • on: indicates that the deduplication and compression function is enabled for B-tree indexes.

Default value: off

enable_expr_fusion

Parameter description: Specifies whether to enable the SRF, expression flattening, centralized Seq Scan projection cancellation, transition status of shared aggregate functions, and step number optimization features.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

  • off: indicates that this function is disabled. This is the default value.
  • on: indicates that the SRF, expression flattening, centralized Seq Scan projection cancellation, transition status of shared aggregate functions, and step number optimization features are enabled.

Default value: off

NOTE:

It is supported only in the scenario where query_dop is set to 1.

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback