Opened 7 months ago

Closed 4 months ago

#4060 closed defect (fixed)

PostgreSQL 11 JIT support -preliminary support

Reported by: komzpa Owned by: pramsey
Priority: medium Milestone: PostGIS 2.5.0
Component: postgis Version: trunk
Keywords: Cc: anarazel

Description

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

Change History (9)

comment:1 Changed 7 months ago by anarazel

Cc: anarazel added

comment:2 Changed 7 months ago by robe

Milestone: PostGIS 2.4.4PostGIS 2.5.0

comment:3 Changed 7 months ago by 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?

comment:4 Changed 7 months ago by 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.

comment:5 Changed 6 months ago by Raul Marin

In 16544:

Postgis module: Pass CFLAGS to PGXS

References #4060

comment:6 Changed 6 months ago by Raul Marin

In 16545:

Fix travis build (C90 compatibility)

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

comment:7 Changed 6 months ago by 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.

comment:8 Changed 6 months ago by Raul Marin

In 16546:

Fix dronie build

References #4060

comment:9 Changed 4 months ago by robe

Resolution: fixed
Status: newclosed
Summary: PostgreSQL 11 JIT supportPostgreSQL 11 JIT support -preliminary support

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

Note: See TracTickets for help on using tickets.