Enable PQO (Pivotal Query Optimizer) at the database level
Post date: Jan 15, 2016 1:53:5 AM
Step 1: Check the status of the optimizer
gpadmin=# show optimizer;
optimizer
-----------
off
(1 row)
gpadmin=# show optimizer_control;
optimizer_control
-------------------
on
(1 row)
Step 1: Check state of the database (optional)
[gpadmin@gpdb-sandbox ~]$ gpstate
20160114:12:38:42:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-Starting gpstate with args:
20160114:12:38:42:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 4.3.6.1 build 2'
20160114:12:38:42:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 8.2.15 (Greenplum Database 4.3.6.1 build 2) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Oct 1 2015 15:14:22'
20160114:12:38:42:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-Obtaining Segment details from master...
20160114:12:38:42:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-Gathering data from segments...
.
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-Greenplum instance status summary
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-----------------------------------------------------
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:- Master instance = Active
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:- Master standby = No master standby configured
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:- Total segment instance count from metadata = 2
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-----------------------------------------------------
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:- Primary Segment Status
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-----------------------------------------------------
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:- Total primary segments = 2
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:- Total primary segment valid (at master) = 2
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:- Total primary segment failures (at master) = 0
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:- Total number of postmaster.pid files missing = 0
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:- Total number of postmaster.pid files found = 2
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:- Total number of postmaster.pid PIDs missing = 0
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:- Total number of postmaster.pid PIDs found = 2
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:- Total number of /tmp lock files missing = 0
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:- Total number of /tmp lock files found = 2
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:- Total number postmaster processes missing = 0
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:- Total number postmaster processes found = 2
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-----------------------------------------------------
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:- Mirror Segment Status
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-----------------------------------------------------
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:- Mirrors not configured on this array
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-----------------------------------------------------
Step 3: Turn on optimizer_analyze_root_partition
[gpadmin@gpdb-sandbox ~]$ gpconfig -c optimizer_analyze_root_partition -v on --masteronly
20160114:12:39:18:169310 gpconfig:gpdb-sandbox:gpadmin-[INFO]:-completed successfully
Step 4: Turn on the optimizer
[gpadmin@gpdb-sandbox ~]$ gpconfig -c optimizer -v on --masteronly
20160114:12:39:59:169532 gpconfig:gpdb-sandbox:gpadmin-[INFO]:-completed successfully
Step 5: Apply the changes
[gpadmin@gpdb-sandbox ~]$ gpstop -u
20160114:12:40:23:169600 gpstop:gpdb-sandbox:gpadmin-[INFO]:-Starting gpstop with args: -u
20160114:12:40:23:169600 gpstop:gpdb-sandbox:gpadmin-[INFO]:-Gathering information and validating the environment...
20160114:12:40:23:169600 gpstop:gpdb-sandbox:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20160114:12:40:23:169600 gpstop:gpdb-sandbox:gpadmin-[INFO]:-Obtaining Segment details from master...
20160114:12:40:23:169600 gpstop:gpdb-sandbox:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 4.3.6.1 build 2'
20160114:12:40:23:169600 gpstop:gpdb-sandbox:gpadmin-[INFO]:-Signalling all postmaster processes to reload
.
Step 6: Check the status
[gpadmin@gpdb-sandbox ~]$ psql
psql (8.2.15)
Type "help" for help.
gpadmin=# show optimizer;
optimizer
-----------
on
(1 row)
gpadmin=#