Horton

Re: JIT compiling with LLVM v12

Andres Freund <andres@anarazel.de>

View thread View on postgr.es
From: Andres Freund <andres@anarazel.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Noah Misch <noah@leadboat.com>, Peter Eisentraut <peter.eisentraut@2ndquadrant.com>, pgsql-hackers@postgresql.org
Date: 2018-08-22T22:43:20Z
Lists: pgsql-hackers 
Hi,

On 2018-08-22 18:15:29 -0400, Tom Lane wrote:
> Andres Freund <andres@anarazel.de> writes:
> > On 2018-08-22 06:20:21 +0000, Noah Misch wrote:
> >> Regardless of the choice of jit={on|off} default, these numbers tell me that
> >> some or all of jit_*_cost defaults are too low.
>
> > I don't think it really shows that. The reason that JITing gets started
> > there is that the tables aren't analyzed and we end up with crazy ass
> > estimates about the cost of the queries. No useful setting of the cost
> > limits will protect against that... :(
>
> I don't buy that line of argument one bit.  No, we generally don't
> analyze most of the regression test tables, but the planner still
> knows that they're not very large.  If JIT is kicking in for those
> queries, the defaults are set wrong.

I looked at the queries that get JITed, I didn't just make that claim up
out of thin air. The first query that's JITed e.g. is:

+explain analyze SELECT '' AS tf_12, BOOLTBL1.*, BOOLTBL2.*
+   FROM BOOLTBL1, BOOLTBL2
+   WHERE BOOLTBL2.f1 <> BOOLTBL1.f1;
+                                                    QUERY PLAN
+------------------------------------------------------------------------------------------------------------------
+ Nested Loop  (cost=0.00..118524.73 rows=3948050 width=34) (actual time=8.376..8.390 rows=12 loops=1)
+   Join Filter: (booltbl2.f1 <> booltbl1.f1)
+   Rows Removed by Join Filter: 4
+   ->  Seq Scan on booltbl1  (cost=0.00..38.10 rows=2810 width=1) (actual time=0.018..0.019 rows=4 loops=1)
+   ->  Materialize  (cost=0.00..52.15 rows=2810 width=1) (actual time=0.004..0.005 rows=4 loops=4)
+         ->  Seq Scan on booltbl2  (cost=0.00..38.10 rows=2810 width=1) (actual time=0.007..0.009 rows=4 loops=1)
+ Planning Time: 0.074 ms
+ JIT:
+   Functions: 6
+   Generation Time: 0.935 ms
+   Inlining: false
+   Inlining Time: 0.000 ms
+   Optimization: false
+   Optimization Time: 0.451 ms
+   Emission Time: 7.716 ms
+ Execution Time: 43.466 ms
+(16 rows)

Now you can say that'd be solved by bumping the cost up, sure. But
obviously the row / cost model is pretty much out of whack here, I don't
see how we can make reasonable decisions in a trivial query that has a
misestimation by five orders of magnitude.

Another subsequent case is:
 set enable_sort = off;  -- try to make it pick a hash setop implementation
 select '(2,5)'::cashrange except select '(5,6)'::cashrange;
which is expensive because a sort is chosen even though sort is disabled
(yes, this might be a bug in the test):
 EXPLAIN  select '(2,5)'::cashrange except select '(5,6)'::cashrange;
┌────────────────────────────────────────────────────────────────────────────────────┐
│                                     QUERY PLAN                                     │
├────────────────────────────────────────────────────────────────────────────────────┤
│ SetOp Except  (cost=10000000000.06..10000000000.07 rows=1 width=36)                │
│   ->  Sort  (cost=10000000000.06..10000000000.06 rows=2 width=36)                  │
│         Sort Key: ('($2.00,$5.00)'::cashrange)                                     │
│         ->  Append  (cost=0.00..0.05 rows=2 width=36)                              │
│               ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..0.02 rows=1 width=36) │
│                     ->  Result  (cost=0.00..0.01 rows=1 width=32)                  │
│               ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..0.02 rows=1 width=36) │
│                     ->  Result  (cost=0.00..0.01 rows=1 width=32)                  │
│ JIT:                                                                               │
│   Functions: 7                                                                     │
│   Inlining: true                                                                   │
│   Optimization: true                                                               │
└────────────────────────────────────────────────────────────────────────────────────┘
(12 rows)

Obviously the high costing here distorts things.  Many of the other
cases here are along similar lines as the two cases before.


> Additional evidence for the
> defaults being wrong is the number of reports we've had of JIT making
> things slower.

Maybe.

Greetings,

Andres Freund

Commits

  1. Improve JIT docs.

  2. Add documentation for the JIT feature.

  3. Add EXPLAIN support for JIT.

  4. Add inlining support to LLVM JIT provider.

  5. JIT tuple deforming in LLVM JIT provider.

  6. Add FIELDNO_* macro designating offset into structs required for JIT.

  7. Add expression compilation support to LLVM JIT provider.

  8. Expand list of synchronized types and functions in LLVM JIT provider.

  9. Add helpers for emitting LLVM IR.

  10. Basic planner and executor integration for JIT.

  11. Debugging and profiling support for LLVM JIT provider.

  12. Support for optimizing and emitting code in LLVM JIT provider.

  13. Add file containing extensions of the LLVM C API.

  14. Basic JIT provider and error handling infrastructure.

  15. Add configure infrastructure (--with-llvm) to enable LLVM support.

  16. Add C++ support to configure.

  17. Add PGAC_PROG_VARCC_VARFLAGS_OPT autoconf macro.

  18. Fix VM buffer pin management in heap_lock_updated_tuple_rec().

  19. Allow tupleslots to have a fixed tupledesc, use in executor nodes.

  20. Expression evaluation based aggregate transition invocation.

  21. Perform slot validity checks in a separate pass over expression.

  22. Rely on executor utils to build targetlist for DML RETURNING.

  23. Refer to OS X as "macOS", except for the port name which is still "darwin".