Pivotal Knowledge Base


ERROR: cannot change the value of "optimizer"


Pivotal Greenplum Database (GPDB) 4.3.5 and above


While setting "optimizer" to "on" at the session level, the below error has occurred:

gpadmin=#SET optimizer=on ;
ERROR:  cannot change the value of "optimizer"


From GPDB 4.3.5.x, there is a new Global User Configuration (GUC) "optimizer_control" that has been added to control the usage of the new optimizer. This GUC can only be set by the Superuser.


If a non-Superuser needs to set "optimizer" at the session level, either of these options need to be executed by the Superuser: 

  • For specific database:
Connect to the DB 
ALTER DATABASE <dbname> SET optimizer_control=on ;
Reconnect to take the changes into affect.
  • For all databases:
gpconfig -c optimizer_control -v on
gpstop -u


Powered by Zendesk