PostgreSQL 11 comes with a new component in the execution engine, a Just-in-Time (JIT) expression compiler. We recommend to read more about JIT beforehand in case you are not familiar with it or in any doubt regarding to the options available. 

By default, the JIT feature is disabled in PostgreSQL 11. Enabling JIT in Aiven now can be done on a global level or just for a specific database with a simple command and doesn't require any installation. 

Enable JIT on a global level

To enable JIT on a global level you can do so in Aivens Console, within your PostgreSQL service scroll down to the  Advanced Configuration settings and click on the + Add configuration option,  select the pg.jit option and simply turn it on, don't forget to save.

Alternatively you can also use the Aiven command line client and enable it with the following command:

$ avn service update -c pg.jit=true <postgres-service-name>

Enable JIT for a specific database

To start with, you'll need an Aiven PostgreSQL database, in order to create one you can follow the instructions in our Aiven PostgresSQL Getting Started guide.

Most of the simple queries won’t use JIT because when JIT kicks in, the cost is high. We will show you how to enable it and make sure that it is properly configured, but, since this is a quick example, we will have to adjust the parameter value jit_above_cost so that the cost its very low, in that way JIT will kick in.

Launch the psql client in a command shell by pasting the entire service URL as a command-line parameter for it:

$ postgres://

psql (11.5)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.


Once in the database all we have to do is enable JIT with the following command:

alter database defaultdb set jit=on;


alter database defaultdb set jit=true;

The example above is for setting the jit=on by default for a logical database, note that the default will only be applied to new client sessions.

The session can also be set per user:

alter role avnadmin set jit=on;

After starting the new session, you can check it is on with:

defaultdb=> show jit;
(1 row)

Now we can perform a simple query for demonstration. It is not a complex query, just to show that JIT kicks in properly. 

defaultdb=> explain analyze select sum(row) from table;
                                                               QUERY PLAN                                                    
 Finalize Aggregate  (cost=10633.55..10633.56 rows=1 width=8) (actual time=299.417..299.418 rows=1 loops=1)
   ->  Gather  (cost=10633.33..10633.54 rows=2 width=8) (actual time=299.111..307.748 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=9633.33..9633.34 rows=1 width=8) (actual time=178.676..178.676 rows=1 loops=3)
               ->  Parallel Seq Scan on bigone  (cost=0.00..8591.67 rows=416667 width=4) (actual time=0.022..89.465 rows=33333
3 loops=3)
 Planning Time: 0.087 ms
   Functions: 12
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 1.878 ms, Inlining 0.000 ms, Optimization 4.438 ms, Emission 44.926 ms, Total 51.243 ms
 Execution Time: 308.777 ms
(12 rows)

Now we can see in the above example, a separate JIT section comes up after the planning time. Additionally in the last row we get the execution time which could be useful for a benchmark comparison.

Got here by accident? Learn how Aiven simplifies Postgres:

Did this answer your question?