Greenplum ORCA 优化器的编译安装与使用-阿里云开发者社区

背景

ORCA 是PostgreSQL的下一代优化器，在QUERY的优化上比自带的优化器有长足的进步。

安装ORCA

cmake

wget https://cmake.org/files/v3.5/cmake-3.5.2.tar.gz
tar -zxvf cmake-3.5.2.tar.gz
cd cmake-3.5.2
./configure --prefix=/home/digoal/cmake
make
make install
export PATH=/home/digoal/cmake/bin:$PATH

GPOS

cd ~
git clone https://github.com/greenplum-db/gpos
cd gpos
mkdir build
cd build
cmake -D CMAKE_BUILD_TYPE=RELEASE -D CMAKE_INSTALL_PREFIX=/home/digoal/gpos_home ../
make install

gp-xerces

cd ~
git clone https://github.com/greenplum-db/gp-xerces
cd gp-xerces
mkdir build
cd build
../configure --prefix=/home/digoal/gp-xerces_home
make -j 32
make install

gporca

cd ~
git clone https://github.com/greenplum-db/gporca.git
cd gporca
mkdir build
cd build
cmake -D CMAKE_BUILD_TYPE=RELEASE \
-D CMAKE_INSTALL_PREFIX=/home/digoal/gporca_home \
-D GPOS_INCLUDE_DIR=/home/digoal/gpos_home/include \
-D GPOS_LIBRARY=/home/digoal/gpos_home/lib/libgpos.so \
-D XERCES_INCLUDE_DIR=/home/digoal/gp-xerces_home/include \
-D XERCES_LIBRARY=/home/digoal/gp-xerces_home/lib/libxerces-c.so ../
make -j 32
make install

greenplum

greenplum的源码安装与集群初始化参考
https://yq.aliyun.com/articles/180

源码安装部分修改为

$ git clone https://github.com/greenplum-db/gpdb.git
$ cd gpdb

cp -r ~/gporca_home/include/gpopt ./

$ ./configure --prefix=/home/digoal/gp --enable-orca --with-perl \
--with-python --with-libxml \
--with-includes=/home/digoal/gporca_home/include:/home/digoal/gpos_home/include:/home/digoal/gp-xerces_home/include \
--with-libraries=/home/digoal/gporca_home/lib:/home/digoal/gpos_home/lib:/home/digoal/gp-xerces_home/lib 
$ make -j 32
$ make install
$ ln -s /home/digoal/gp /home/digoal/greenplum-db

$ vi ~/env_gp.sh
GPHOME=/home/digoal/greenplum-db

# Replace with symlink path if it is present and correct
if [ -h ${GPHOME}/../greenplum-db ]; then
    GPHOME_BY_SYMLINK=`(cd ${GPHOME}/../greenplum-db/ && pwd -P)`
    if [ x"${GPHOME_BY_SYMLINK}" = x"${GPHOME}" ]; then
        GPHOME=`(cd ${GPHOME}/../greenplum-db/ && pwd -L)`/.
    fi
    unset GPHOME_BY_SYMLINK
fi

PATH=$GPHOME/bin:$GPHOME/ext/python/bin:$PATH
LD_LIBRARY_PATH=$GPHOME/lib:$GPHOME/ext/python/lib:$LD_LIBRARY_PATH
PYTHONPATH=$GPHOME/lib/python
#PYTHONHOME=$GPHOME/ext/python
OPENSSL_CONF=$GPHOME/etc/openssl.cnf

export GPHOME
export PATH
export LD_LIBRARY_PATH
export PYTHONPATH
#export PYTHONHOME
export OPENSSL_CONF
export MASTER_DATA_DIRECTORY=/data01/digoal/gpdata/gpseg-1
export PGHOST=127.0.0.1
export PGPORT=1921
export PGUSER=digoal
export PGDATABASE=postgres

export LD_LIBRARY_PATH=/home/digoal/gpos_home/lib:/home/digoal/gp-xerces_home/lib:/home/digoal/gporca_home/lib:$LD_LIBRARY_PATH

# vi /etc/ld.so.conf
include ld.so.conf.d/*.conf
/home/digoal/gpos_home/lib
/home/digoal/gp-xerces_home/lib
/home/digoal/gporca_home/lib
/home/digoal/greenplum-db/lib

# ldconfig

你也可以把orca安装到与greenplum一致的目录中，那SO就不需要设置到/etc/ld.so.conf了。

测试ORCA

重启数据库

$ gpstop -M fast -a
$ gpstart -a

开启ORCA测试

$ psql

set client_min_messages='log';
set optimizer=on;
set optimizer_enumerate_plans=on;
set optimizer_minidump=always;
set optimizer_enable_constant_expression_evaluation=off;

create table test(id int, info text, crt_time timestamp);
insert into test select generate_series(1,10000000), md5(random()::text),clock_timestamp();
\timing
insert into test select generate_series(1,10000000), md5(random()::text),clock_timestamp();
create index idx1 on test(info,crt_time);
create index idx2 on test(crt_time,info);

postgres=# explain analyze select * from test where info='abc' and crt_time between '2016-07-16 13:41:06.555882' and '2016-07-16 13:41:06.555882' order by crt_time limit 10;
LOG:  statement: explain analyze select * from test where info='abc' and crt_time between '2016-07-16 13:41:06.555882' and '2016-07-16 13:41:06.555882' order by crt_time limit 10;
LOG:  2016-07-16 17:33:17:175315 CST,THD000,TRACE,"[OPT]: Number of plan alternatives: 8
",
                                                                                 QUERY PLAN                                                                                  
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.38 rows=1 width=45)
   Rows out:  0 rows with 1.141 ms to end, start offset by 0.409 ms.
   ->  Gather Motion 16:1  (slice1; segments: 16)  (cost=0.00..0.38 rows=1 width=45)
         Merge Key: crt_time
         Rows out:  0 rows at destination with 1.139 ms to end, start offset by 0.410 ms.
         ->  Sort  (cost=0.00..0.38 rows=1 width=45)
               Sort Key: crt_time
               Rows out:  0 rows (seg0) with 0.065 ms to end, start offset by 1.099 ms.
               Executor memory:  33K bytes avg, 33K bytes max (seg0).
               Work_mem used:  33K bytes avg, 33K bytes max (seg0). Workfile: (0 spilling)
               ->  Index Scan using idx1 on test  (cost=0.00..0.38 rows=1 width=45)
                     Index Cond: info = 'abc'::text
                     Filter: crt_time >= '2016-07-16 13:41:06.555882'::timestamp without time zone AND crt_time <= '2016-07-16 13:41:06.555882'::timestamp without time zone
                     Rows out:  0 rows (seg0) with 0.045 ms to end, start offset by 1.116 ms.
 Slice statistics:
   (slice0)    Executor memory: 318K bytes.
   (slice1)    Executor memory: 209K bytes avg x 16 workers, 209K bytes max (seg0).  Work_mem: 33K bytes max.
 Statement statistics:
   Memory used: 128000K bytes
 Settings:  optimizer=on
 Optimizer status: PQO version 1.646
 Total runtime: 5.807 ms
(22 rows)

postgres=# set enable_sort=off;
LOG:  statement: set enable_sort=off;
SET
postgres=# explain analyze select * from test where info='abc' and crt_time between '2016-07-16 13:41:06.555882' and '2016-07-16 13:41:06.555882' order by crt_time limit 10;
LOG:  statement: explain analyze select * from test where info='abc' and crt_time between '2016-07-16 13:41:06.555882' and '2016-07-16 13:41:06.555882' order by crt_time limit 10;
LOG:  2016-07-16 17:33:23:623812 CST,THD000,TRACE,"[OPT]: Number of plan alternatives: 8
",
                                                                                 QUERY PLAN                                                                                  
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.38 rows=1 width=45)
   Rows out:  0 rows with 1.019 ms to end, start offset by 0.420 ms.
   ->  Gather Motion 16:1  (slice1; segments: 16)  (cost=0.00..0.38 rows=1 width=45)
         Merge Key: crt_time
         Rows out:  0 rows at destination with 1.017 ms to end, start offset by 0.421 ms.
         ->  Sort  (cost=0.00..0.38 rows=1 width=45)
               Sort Key: crt_time
               Rows out:  0 rows (seg0) with 0.081 ms to end, start offset by 1.047 ms.
               Executor memory:  33K bytes avg, 33K bytes max (seg0).
               Work_mem used:  33K bytes avg, 33K bytes max (seg0). Workfile: (0 spilling)
               ->  Index Scan using idx1 on test  (cost=0.00..0.38 rows=1 width=45)
                     Index Cond: info = 'abc'::text
                     Filter: crt_time >= '2016-07-16 13:41:06.555882'::timestamp without time zone AND crt_time <= '2016-07-16 13:41:06.555882'::timestamp without time zone
                     Rows out:  0 rows (seg0) with 0.060 ms to end, start offset by 1.064 ms.
 Slice statistics:
   (slice0)    Executor memory: 318K bytes.
   (slice1)    Executor memory: 209K bytes avg x 16 workers, 209K bytes max (seg0).  Work_mem: 33K bytes max.
 Statement statistics:
   Memory used: 128000K bytes
 Settings:  enable_sort=off; optimizer=on
 Optimizer status: PQO version 1.646
 Total runtime: 7.194 ms
(22 rows)

ORCA已经整合到阿里云推出的ApsaraDB Greenplum产品中，欢迎使用。
祝大家玩得开心，欢迎随时来阿里云 促膝长谈 业务需求，恭候光临。
阿里云的小伙伴们加油，努力做 最贴地气 的云数据库。