Enabling The New Greenplum Query Optimizer

Starting with Greenplum Database, there is a new Query Optimizer that is now available that is faster than the legacy optimizer.

Greenplum Database is a fork of PostgreSQL 8.2. This fork was done to optimize SQL queries for a distributed cluster and it has done very well over the years. Often times Greenplum faster that competing MPP databases because of this Query Optimizer. But, because it has many years of development on top of PostgreSQL, it has become difficult to manage and enhance.

Orca was the code name of the new Query Optimizer that is a refactor of the legacy Query Optimizer that also includes enhancements for performance and maintainability. This post is about how to enable the new Query Optimizer.

Note: You must be using Greenplum Database or newer.

1. Log into the Master server with ssh as gpadmin.
2. Make sure the database is running and healthy.


3. Execute gpconfig:

gpconfig -c optimizer_analyze_root_partition -v on --masteronly

4. Enable the new optimizer for the entire system.

gpconfig -c optimizer -v on --masteronly

5. Apply the changes with gpstop.

gpstop -u

6. Disable the new Query Optimizer for the gpperfmon database.


7. Verify the new Query Optimizer is on.

[gpadmin@mdw ~]$ psql
psql (8.2.15)
Type "help" for help.

gpdb=# explain select version();
                   QUERY PLAN                   
 Result  (cost=0.00..0.00 rows=1 width=8)
   ->  Result  (cost=0.00..0.00 rows=1 width=1)
 Settings:  optimizer=on
(3 rows)

8. Using psql, change to the gpperfmon database and make sure the new Query Optimizer is off.

gpdb=# \c gpperfmon
You are now connected to database "gpperfmon" as user "gpadmin".
gpperfmon=# explain select version();
                QUERY PLAN                
 Result  (cost=0.00..0.01 rows=1 width=0)
 Settings:  optimizer=off
(2 rows)


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.