Changed Behavior with GPORCA

There are changes to Greenplum Database behavior with the GPORCA optimizer enabled (the default) as compared to the Postgres-based planner.

• UPDATE operations on distribution keys are allowed.

• UPDATE operations on partitioned keys are allowed.

• Queries against uniform partitioned tables are supported.

• The command CREATE TABLE AS distributes table data randomly if the DISTRIBUTED BY clause is not specified and no primary or unique keys are specified.

• Non-deterministic updates not allowed. The following UPDATE command returns an error.

  update r set b =  r.b  + 1 from s where  r.a  in (select a from s);
  

• Statistics are required on the root table of a partitioned table. The ANALYZE command generates statistics on both the root partitioned table and individual leaf partitions. See the ROOTPARTITION clause for ANALYZE command.

• Additional Result nodes in the query plan:

  • Query plan Assert operator.
  • Query plan Partition selector operator.
  • Query plan Split operator.

• When running EXPLAIN, the query plan generated by GPORCA is different than the plan generated by the Postgres-based planner.

• Greenplum Database adds the log file message Planner produced plan when GPORCA is enabled and Greenplum Database falls back to the Postgres-based planner to generate the query plan.

• Greenplum Database issues a warning when statistics are missing from one or more table columns. When running an SQL command with GPORCA, Greenplum Database issues a warning if the command performance could be improved by collecting statistics on a column or set of columns referenced by the command. The warning is issued on the command line and information is added to the Greenplum Database log file. For information about collecting statistics on table columns, see the ANALYZE command in the Greenplum Database Reference Guide.

Parent topic: About GPORCA