query/gporca/query-gporca-enable.html.md.erb

---
title: Enabling GPORCA
---

<span class="shortdesc">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: </span>

-   [Set the <code class="ph codeph">optimizer\_analyze\_root\_partition</code> parameter to <code class="ph codeph">on</code>](#topic_r5d_hv1_kr) to enable statistics collection for the root partition of a partitioned table.
-   Set the `optimizer` parameter to `on` to enable GPORCA. You can set the parameter at these levels:
    -   [A HAWQ system](#topic_byp_lqk_br)
    -   [A specific HAWQ database](#topic_pzr_3db_3r)
    -   [A session or query](#topic_lx4_vqk_br)

**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](../../reference/sql/ANALYZE.html).

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](../../reference/cli/admin_utilities/analyzedb.html#topic1).

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<a id="topic_r5d_hv1_kr"></a>

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
    ```

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.

    ``` shell
    $ hawq stop master -u
    ```

## Enabling GPORCA for a System<a id="topic_byp_lqk_br"></a>

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`:

    ``` shell
    $ 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.

    ``` shell
    $ hawq stop master -u
    ```

## Enabling GPORCA for a Database<a id="topic_pzr_3db_3r"></a>

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

``` sql
> ALTER DATABASE test_db SET OPTIMIZER = ON ;
```

## Enabling GPORCA for a Session or a Query<a id="topic_lx4_vqk_br"></a>

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:

``` sql
> set optimizer = on ;
```

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