Activating and Deactivating GPORCA

By default, SynxDB uses GPORCA instead of the Postgres Planner. Server configuration parameters activate or deactivate GPORCA.

Although GPORCA is on by default, you can configure GPORCA usage at the system, database, session, or query level using the optimizer parameter. Refer to one of the following sections if you want to change the default behavior:

Note You can deactivate the ability to activate or deactivate GPORCA with the server configuration parameter optimizer_control. For information about the server configuration parameters, see the SynxDB Reference Guide.

Enabling GPORCA for a System

Set the server configuration parameter optimizer for the SynxDB system.

  1. Log into the SynxDB master host as gpadmin, the SynxDB administrator.

  2. Set the values of the server configuration parameters. These SynxDB gpconfig utility commands sets the value of the parameters to on:

    $ gpconfig -c optimizer -v on --masteronly

  3. Restart SynxDB. This SynxDB gpstop utility command reloads the postgresql.conf files of the master and segments without shutting down SynxDB.

    gpstop -u

Enabling GPORCA for a Database

Set the server configuration parameter optimizer for individual SynxDB 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 SynxDB, 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.