Enabling GPORCA

Precompiled versions of HAWQ that include the GPORCA query optimizer enable it by default, no additional configuration is required. To use the GPORCA query optimizer in a HAWQ built from source, your build must include GPORCA. You must also enable specific HAWQ server configuration parameters at or after install time:

Important: If you intend to execute queries on partitioned tables with GPORCA enabled, you must collect statistics on the partitioned table root partition with the ANALYZE ROOTPARTITION command. The command ANALYZE ROOTPARTITION collects statistics on the root partition of a partitioned table without collecting statistics on the leaf partitions. If you specify a list of column names for a partitioned table, the statistics for the columns and the root partition are collected. For information on the ANALYZE command, see ANALYZE.

You can also use the HAWQ utility analyzedb to update table statistics. The HAWQ utility analyzedb can update statistics for multiple tables in parallel. The utility can also check table statistics and update statistics only if the statistics are not current or do not exist. For information about the analyzedb utility, see analyzedb.

As part of routine database maintenance, you should refresh statistics on the root partition when there are significant changes to child leaf partition data.

Setting the optimizer_analyze_root_partition Parameter

When the configuration parameter optimizer_analyze_root_partition is set to on, root partition statistics will be collected when ANALYZE is run on a partitioned table. Root partition statistics are required by GPORCA.

  1. Log into the HAWQ master host as gpadmin, the HAWQ administrator.
  2. Set the values of the server configuration parameters. These HAWQ hawq config utility commands sets the value of the parameters to on:

    shell $ hawq config -c optimizer_analyze_root_partition -v on `

  1. Reload the modified configuration in HAWQ. This HAWQ hawq stop utility command reloads the hawq-site.xml file of the master without shutting down HAWQ.

    $ hawq stop master -u
    

Enabling GPORCA for a System

Set the server configuration parameter optimizer for the HAWQ system.

  1. Log into the HAWQ master host as gpadmin, the HAWQ administrator.
  2. Set the value of the server configuration optimizer parameter. This HAWQ hawq config utility command sets the value to on:

    $ hawq config -c optimizer -v on
    
  3. Reload the modified configuration in HAWQ. This HAWQ hawq stop utility command reloads the hawq-site.xml file of the master without shutting down HAWQ.

    $ hawq stop master -u
    

Enabling GPORCA for a Database

Set the server configuration parameter optimizer for individual HAWQ databases with the ALTER DATABASE command. For example, this command enables GPORCA for the database test_db.

> ALTER DATABASE test_db SET OPTIMIZER = ON ;

Enabling GPORCA for a Session or a Query

You can use the SET command to set optimizer server configuration parameter for a session. For example, after you use the psql utility to connect to HAWQ, this SET command enables GPORCA:

> set optimizer = on ;

To set the parameter for a specific query, include the SET command prior to running the query.