How to find operators accelarated by JIT in PostgreSQL

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

Published by ktke109

I love open souce database management systems.

Leave a comment

Your email address will not be published. Required fields are marked *