1. Introduction
PostgreSQL can apply Just-in-time(JIT) compilation to some operations to convert general purpose code to native code. So PostgreSQL JIT can acclarate CPU-bound query.
2. What I looked up
(1) How to use PostgreSQL JIT
(2) Effect of PostgreSQL JIT
(3) How to find operators accelarated by PostgreSQL JIT
3. Prerequisites
Version of PostgreSQL: PostgreSQL13.0
OS: CentOS7
PC spec: Ryzen 7 3700X 3.6GHz 8Cores, 128GB memory, SSD M.2 1000GB, shared_buffers = 50GB
4. What I found
4.1 How to use PostgreSQL JIT
4.1.1 Install LLVM and Clang
(1) Install a compiler infrastructure LLVM and compiler front end Clang by executing the following commands
sudo yum install centos-release-scl
sudo yum install llvm-toolset-7-llvm-devel llvm-toolset-7-clang
scl enable llvm-toolset-7 bash
(2) Set environment variables for LLVM and Clang
export LLVM_CONFIG=/opt/rh/llvm-toolset-7/root/usr/bin/llvm-config
export CLANG=/opt/rh/llvm-toolset-7/root/usr/bin/clang
(3) set shared library path for LLVM
echo /opt/rh/llvm-toolset-7/root/usr/lib64 > /etc/ld.so.conf.d/libLLVM-5.0.conf
sudo ldconfig
4.1.2 Build and Install PostgreSQL
Build and Install PostgreSQL source configuring with option”–with–llvm” such as “./configure –with-llvm”. For detail, refer to [2], etc.
4.2 Effect of PostgreSQL JIT
In my environment, I found that the response of some query using PostgreSQL JIT is 1/2 the one without PostgreSQL JIT.
(1) Setup executing the following commands in psql. data.csv contains 100,000,000 rows whose column is generated by Pseudo-random number.
create database testjit;
\c testjit
create table test(col1 float8, col2 float8, col3 float8, col4 float8, col5 float8, col6 float8, col7 float8, col8 float8, col9 float8, col10 float8);
copy test from ‘/tmp/data.csv’ with csv;
(2) Execute the following query(hereinafeter Query1) with “jit = on” and “jit = off”. In my environment, the response is 12 sec to 10 sec setting “jit = on”.
select avg(exp(sign(sqrt(abs(ln(col1 + 1))))) + exp(sign(sqrt(abs(ln(col2 + 1))))) + exp(sign(sqrt(abs(ln(col3 + 1))))) + exp(sign(sqrt(abs(ln(col4 + 1))))) + exp(sign(sqrt(abs(ln(col5 + 1))))) + exp(sign(sqrt(abs(ln(col6 + 1))))) + exp(sign(sqrt(abs(ln(col7 + 1))))) + exp(sign(sqrt(abs(ln(col8 + 1))))) + exp(sign(sqrt(abs(ln(col9 + 1))))) + exp(sign(sqrt(abs(ln(col10 + 1)))))) from test
(3) Execute the following query(hereinafeterQuery2) with “jit = on” and “jit = off”. In my environment, the response is 14 sec to 7 sec setting “jit = on”.
select avg(exp(sign(sqrt(abs(ln(col1 + 1))))) + exp(sign(sqrt(abs(ln(col2 + 1))))) + exp(sign(sqrt(abs(ln(col3 + 1))))) + exp(sign(sqrt(abs(ln(col4 + 1))))) + exp(sign(sqrt(abs(ln(col5 + 1))))) + exp(sign(sqrt(abs(ln(col6 + 1))))) + exp(sign(sqrt(abs(ln(col7 + 1))))) + exp(sign(sqrt(abs(ln(col8 + 1))))) + exp(sign(sqrt(abs(ln(col9 + 1))))) + exp(sign(sqrt(abs(ln(col10 + 1)))))) from test where col1 > 0.5 or col2 > 0.5 or col3 > 0.5 or col4 > 0.5 or col5 > 0.5 or col6 > 0.5 or col7 > 0.5 or col8 > 0.5 or col9 > 0.5 or col10 > 0.5
4.3 How to find operators accelarated by PostgreSQL JIT
You can find operators accelarated by PostgreSQL JIT in llvm_compile_expr() function in the following file.
https://github.com/postgres/postgres/blob/REL_13_STABLE/src/backend/jit/llvm/llvmjit_expr.c
When QUERY2 run, I found the following opcodes are applied by JIT.
- EEOP_SCAN_FETCHSOME
- EEOP_SCAN_VAR
- EEOP_FUNCEXPR_STRICT
- EEOP_BOOL_OR_STEP_FIRST
- EEOP_AGGREF
- EEOP_ASSIGN_TMP_MAKE_RO
By the way, JIT compiled codes are performed in ExecRunCompiledExpr() function.
5. References
[1] PostgreSQL13 document, Chapter 31. Just-in-Time Compilation (JIT)
https://www.postgresql.org/docs/13/jit-reason.html#JIT-ACCELERATED-OPERATIONS
[2] How to do linux debugging PostgreSQL remotely using Visual Studio Code
https://osmanthus.work/?p=97