PostgreSQL 11 JIT support -preliminary support

[komzpa]

Postgres 11 can compile stuff in JIT, inlining all the functions.

​https://github.com/postgres/postgres/blob/master/src/backend/jit/README

Per Andres Freund: "But basically you'd have to copy what the install_llvm_module rule in src/Makefile.global.in does."

​https://twitter.com/AndresFreundTec/status/979418878919942144

[pramsey]

This is very interesting, but as Andreas points out, we generally have much higher overheads, so the gains we can reap are probably small. What tiny thing do we do very very frequently that could be JIT'ed? Maybe the && operator?

[anarazel]

(Andres here)

This is very interesting, but as Andreas points out, we generally have much higher overheads, so the gains we can reap are probably small. What tiny thing do we do very very frequently that could be JIT'ed? Maybe the && operator?

I've not looked through the code for operators or anything like that. But the JIT feature does have costing logic to see whether function/operator inlining is beneficial, so I'd not worry too much about which functions can be inlinable. I'd suggest just installing the bitcode ;)

I notice that postgis actually does, contrary to my expectation, appears to use PGXS ​https://trac.osgeo.org/postgis/browser/trunk/postgis/Makefile.in so it's possible that the PGXS support does all the necessary things if postgres is compiled with LLVM support. Also possible that some of the bigger complexity, in comparison to the average contrib/ extension, prevents that from working.

Would be good if somebody tried ;). You should see $pkglibdir/postgis-$major-$minor/ being filled with things.

[Raul Marin]

In 16544:

Postgis module: Pass CFLAGS to PGXS

References #4060

[Raul Marin]

In 16545:

Fix travis build (C90 compatibility)

References #4060
Closes ​https://github.com/postgis/postgis/pull/236

[Algunenano]

I've moved the CFLAGS in the postgis module to PG_CPPFLAGS as they were being ignored or overwritten when compiling with JIT support.

Now I have a bunch of files in /usr/lib/postgresql/bitcode/ and it appears to work correctly:

cartodb_dev_user_3e4a6fc6-4137-4c59-bc63-066f80efb90e_db=# SET jit_above_cost = 10;
SET
cartodb_dev_user_3e4a6fc6-4137-4c59-bc63-066f80efb90e_db=# SET jit_inline_above_cost = 10;
SET
cartodb_dev_user_3e4a6fc6-4137-4c59-bc63-066f80efb90e_db=# SET jit_optimize_above_cost = 10;
SET
cartodb_dev_user_3e4a6fc6-4137-4c59-bc63-066f80efb90e_db=# EXPLAIN ANALYZE SELECT ST_AsTWKB(ST_Simplify(ST_RemoveRepeatedPoints("the_geom_webmercator",10000),10000,true),-4) AS geom FROM (SELECT * FROM yellow_1m) as cdbq WHERE "the_geom_webmercator" && ST_SetSRID('BOX3D(-20037508.3 -20037508.3,20037508.3 20037508.3)'::box3d, 3857);                                                                                                                                                                                                                                       
                                                                                                                                                             QUERY PLAN                                                                                                                   
                                                                                                                                                                                                                                                                                          
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------                                                                                                                                                                                                                                                
 Seq Scan on yellow_1m  (cost=0.00..48301.98 rows=999999 width=32) (actual time=104.892..760.779 rows=985785 loops=1)                                                                                                                                                                     
   Filter: (the_geom_webmercator && '01030000A0110F00000100000005000000CDCCCC44F81B73C1CDCCCC44F81B73C10000000000000000CDCCCC44F81B73C1CDCCCC44F81B73410000000000000000CDCCCC44F81B7341CDCCCC44F81B73410000000000000000CDCCCC44F81B7341CDCCCC44F81B73C10000000000000000CDCCCC44F81B73C1CDC
CCC44F81B73C10000000000000000'::geometry)                                                                                                                                                                                                                                                 
   Rows Removed by Filter: 14214                                                                                                                                                                                                                                                          
 Planning Time: 2.969 ms                                                                                                                                                                                                                                                                  
 JIT:                                                                                                                                                                                                                                                                                     
   Functions: 4                                                                                                                                                                                                                                                                           
   Generation Time: 0.507 ms                                                                                                                                                                                                                                                              
   Inlining: true                                                                                                                                                                                                                                                                         
   Inlining Time: 42.497 ms                                                                                                                                                                                                                                                               
   Optimization: true                                                                                                                                                                                                                                                                     
   Optimization Time: 42.008 ms                                                                                                                                                                                                                                                           
   Emission Time: 17.450 ms
 Execution Time: 835.961 ms
(13 rows)

cartodb_dev_user_3e4a6fc6-4137-4c59-bc63-066f80efb90e_db=# 

liblwgeom functions are not included since the library isn't compiled using PGXS and right now I don't know if it'd be useful. I'm leaving this issue open until I can investigate further, but feel free to close it if you think the job is done.

[Raul Marin]

In 16546:

Fix dronie build

References #4060

[robe]

I'm considering this done enough for release. We can revisit with another ticket in 3.0.