在CentOS7编译postgresql11使用JIT功能

1.查看centos版本
[root@localhost ~]# cat /etc/centos-release
CentOS Linux release 7.5.1804 (Core)

2.查看LLVM版本。

[root@localhost ~]# yum search llvm
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirrors.huaweicloud.com
* extras: mirrors.huaweicloud.com
* updates: mirrors.huaweicloud.com
=========================================================== N/S matched: llvm ============================================================
llvm-devel.x86_64 : Libraries and header files for LLVM
llvm-doc.noarch : Documentation for LLVM
llvm-libs.x86_64 : LLVM shared libraries
llvm-ocaml.x86_64 : OCaml binding for LLVM
llvm-ocaml-devel.x86_64 : Development files for llvm-ocaml
llvm-ocaml-doc.noarch : Documentation for LLVM’s OCaml binding
llvm-private.i686 : llvm engine for Mesa
llvm-private.x86_64 : llvm engine for Mesa
llvm-private-devel.i686 : Libraries and header files for LLVM
llvm-private-devel.x86_64 : Libraries and header files for LLVM
llvm-static.x86_64 : LLVM static libraries
mesa-private-llvm.i686 : llvm engine for Mesa
mesa-private-llvm.x86_64 : llvm engine for Mesa
mesa-private-llvm-devel.i686 : Libraries and header files for LLVM
mesa-private-llvm-devel.x86_64 : Libraries and header files for LLVM
clang.x86_64 : A C language family front-end for LLVM
llvm.x86_64 : The Low Level Virtual Machine

Name and summary matches only, use “search all” for everything.
[root@localhost ~]# yum info llvm
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirrors.huaweicloud.com
* extras: mirrors.huaweicloud.com
* updates: mirrors.huaweicloud.com
Available Packages
Name : llvm
Arch : x86_64
Version : 3.4.2
Release : 8.el7
Size : 1.3 M
Repo : extras/7/x86_64
Summary : The Low Level Virtual Machine
URL : http://llvm.org/
License : NCSA
Description : LLVM is a compiler infrastructure designed for compile-time,
: link-time, runtime, and idle-time optimization of programs from
: arbitrary programming languages. The compiler infrastructure includes
: mirror sets of programming tools as well as libraries with equivalent
: functionality.

1. PostgreSQL documentation 说明LLVM最低版本需要3.9。修改yum源，安装LLVM5.0
   [root@localhost ~]# wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
   [root@localhost ~]# yum localinstall epel-release-latest-7.noarch.rpm
   [root@localhost ~]# yum install llvm5.0 llvm5.0-devel clang

4.下载postgresql11Beta2
wget https://ftp.postgresql.org/pub/source/v11beta2/postgresql-11beta2.tar.gz

5.解压编译

tar -zxvf postgresql-11beta2.tar.gz
./configure –with-llvm LLVM_CONFIG=’/usr/lib64/llvm5.0/bin/llvm-config’
make
发生以下错误：
clang: error: unknown argument: ‘-flto=thin’
make[2]: * [localtime.bc] Error 1
make[2]: Leaving directory /root/postgresql-11beta2/src/timezone'
make[1]: *** [all-timezone-recurse] Error 2
make[1]: Leaving directory/root/postgresql-11beta2/src’
make: * [all-src-recurse] Error 2
COMP

修改vi src/Makefile.global.in
COMPILE.c.bc = $(CLANG) -Wno-ignored-attributes$(BITCODE_CFLAGS) $(CPPFLAGS) -flto=thin -emit-llvm -c COMPILE.c.bc =$(CLANG) -Wno-ignored-attributes $(BITCODE_CFLAGS)$(CPPFLAGS) -emit-llvm -c
重新编译
make clean
./configure –with-llvm LLVM_CONFIG=’/usr/lib64/llvm5.0/bin/llvm-config’
发生以下错误：
make[2]: g++: Command not found
make[2]: * [llvmjit_error.o] Error 127
make[2]: Leaving directory /root/postgresql-11beta2/src/backend/jit/llvm'
make[1]: *** [all-backend/jit/llvm-recurse] Error 2
make[1]: Leaving directory/root/postgresql-11beta2/src’
make: * [all-src-recurse] Error 2

安装g++
yum install -y gcc-c++
继续安装
make
make install
6.JIT默认有效，查看相关参数

[postgres@localhost bin]$ ./psql
psql (11beta2)
Type “help” for help.

postgres=# select name,setting from pg_settings where name like ‘jit%’;
name | setting
————————-+———
jit | on
jit_above_cost | 100000
jit_debugging_support | off
jit_dump_bitcode | off
jit_expressions | on
jit_inline_above_cost | 500000
jit_optimize_above_cost | 500000
jit_profiling_support | off
jit_provider | llvmjit
jit_tuple_deforming | on
(10 rows)

postgres=#

7.测试
postgres=# create table ttt (a int, b text, c date );
CREATE TABLE
postgres=# insert into ttt (a,b,c)
postgres-# select aa.*, md5(aa::text), now()
postgres-# from generate_series(1,1000000) aa;
INSERT 0 1000000
postgres=# set jit_above_cost=5;
SET
postgres=# set jit_optimize_above_cost=5;
SET
postgres=# set jit_inline_above_cost=5;
SET
postgres=# explain select sum(a) from ttt;

QUERY PLAN

Finalize Aggregate (cost=15554.55..15554.56 rows=1 width=8)
-> Gather (cost=15554.33..15554.54 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=14554.33..14554.34 rows=1 width=8)
-> Parallel Seq Scan on ttt (cost=0.00..13512.67 rows=416667 width=4)
**JIT:
Functions: 6
Inlining: true
Optimization: true
(9** rows)

postgres=#

参考：
https://www.postgresql.org/docs/11/static/jit.html
https://blog.dbi-services.com/how-to-compile-postgresql-11-with-support-for-jit-compilation-on-rhelcentos-7/