AnalyticDB for PostgreSQL V7.0 supports the Just-In-Time (JIT) compilation feature. This feature converts a form of interpreted program into a native program that the CPU can execute. This accelerates computation.

Background information

JIT compilation is an effective method to improve CPU-bound query capabilities for online analytical processing (OLAP) databases. AnalyticDB for PostgreSQL V7.0 can use JIT compilation to accelerate computation of expressions (such as WHERE clauses, lists, aggregations, and projections) and unpacking of tuples.

If a table contains a large amount of data, you can use JIT compilation to improve performance by up to 20% for single-column aggregation scenarios. JIT compilation can improve the performance of CPU-bound queries. The larger the amount of data, the better the effect. However, if a table contains a small amount of data, JIT compilation degrades performance due to additional JIT overheads and extended amount of time for compilation. Use JIT compilation based on your business scenarios.

Examples

This example compares the query execution duration before and after JIT compilation is enabled.

  1. Create a test table and insert data into the table. 
    CREATE TABLE jit_test (id serial);
INSERT INTO jit_test (id) select * from generate_series(1, 100000000);
ANALYZE jit_test;
  2. Query the execution duration in the execution plan while JIT compilation is not enabled. 
    EXPLAIN ANALYZE SELECT avg(id) FROM jit_test;
    The following execution plan is returned. The execution duration is 13,445.055 milliseconds. 
                                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=453326.89..453326.90 rows=1 width=32) (actual time=13444.050..13444.050 rows=1 loops=1)
   ->  Gather Motion 3:1  (slice1; segments: 3)  (cost=453326.83..453326.88 rows=3 width=32) (actual time=13064.601..13444.006 rows=3 loops=1)
         ->  Partial Aggregate  (cost=453326.83..453326.84 rows=1 width=32) (actual time=13443.451..13443.452 rows=1 loops=1)
               ->  Seq Scan on jit_test  (cost=0.00..369995.67 rows=33332467 width=4) (actual time=0.191..7151.999 rows=33335145 loops=1)
 Optimizer: Postgres query optimizer
 Planning Time: 0.447 ms
   (slice0)    Executor memory: 40K bytes.
   (slice1)    Executor memory: 38K bytes avg x 3 workers, 38K bytes max (seg0).
 Memory used:  128000kB
 Execution Time: 13445.055 ms
(10 rows)
  3. Enable JIT compilation and query the execution duration in the execution plan. 
    SET jit to on;
SET optimizer to off;
    EXPLAIN ANALYZE SELECT avg(id) FROM jit_test;
    The following execution plan is returned. After JIT compilation is enabled, the execution duration is reduced by 1747.94 milliseconds, which is significantly shorter. 
                                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=453326.89..453326.90 rows=1 width=32) (actual time=11695.112..11695.113 rows=1 loops=1)
   ->  Gather Motion 3:1  (slice1; segments: 3)  (cost=453326.83..453326.88 rows=3 width=32) (actual time=10926.392..11690.101 rows=3 loops=1)
         ->  Partial Aggregate  (cost=453326.83..453326.84 rows=1 width=32) (actual time=11693.964..11693.965 rows=1 loops=1)
               ->  Seq Scan on jit_test  (cost=0.00..369995.67 rows=33332467 width=4) (actual time=0.213..6529.483 rows=33335145 loops=1)
 Optimizer: Postgres query optimizer
 Planning Time: 0.492 ms
   (slice0)    Executor memory: 40K bytes.
   (slice1)    Executor memory: 38K bytes avg x 3 workers, 38K bytes max (seg0).
 Memory used:  128000kB
 JIT:
   Functions: 4
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 0.870 ms, Inlining 0.000 ms, Optimization 0.295 ms, Emission 4.462 ms, Total 5.627 ms
 Execution Time: 11697.065 ms
(14 rows)