Sv: Re: Query is over 2x slower with jit=on

[Andreas Joseph Krogh]

På torsdag 23. august 2018 kl. 09:14:56, skrev Andreas Joseph Krogh <
andr...@visena.com >:
På torsdag 23. august 2018 kl. 03:00:42, skrev Jonathan S. Katz <
jk...@postgresql.org >:

 > On Aug 22, 2018, at 7:13 PM, Andres Freund  wrote:
 [snip]
 > For the archives sake: This likely largely is the consequence of
 > building with LLVM's expensive assertions enabled, as confirmed by
 > Jonathan over IM.

 I recompiled with the release version of LLVM. jit=on was still slower,
 but the discrepancy was not as bad as the previously reported result:

 jit = off
 Planning Time: 0.938 ms
 Execution Time: 935.599 ms

 jit = on
 Planning Time: 0.951 ms
 JIT:
   Functions: 184
   Generation Time: 17.605 ms
   Inlining: true
   Inlining Time: 20.522 ms
   Optimization: true
   Optimization Time: 1001.034 ms
   Emission Time: 665.319 ms
 Execution Time: 2491.560 ms

 However, it was still 2x+ slower, so still +1ing for open items.
 
 
I compiled with whatever switches LLVM that comes with Ubuntu 18.04 is built 
with, and without debugging or assertions.
 
With 11b3 as of 825f10fbda7a5d8a48d187b8193160e5e44e4011 I'm repeatedly 
getting these results with jit=on, after 10 runs:
 
 
 
Planning Time: 0.266 ms 
 JIT:
  Functions: 686
  Generation Time: 71.895 ms
  Inlining: false
  Inlining Time: 0.000 ms
  Optimization: false
  Optimization Time: 39.906 ms
  Emission Time: 589.944 ms
 Execution Time: 2198.928 ms
  
 
 
Turning jit=off gives this:
 
Planning Time: 0.180 ms
Execution Time: 938.451 ms
 
I can provide dataset offlist if anyone wants to look into this.
 
--
 Andreas Joseph Krogh

Sv: Re: Query is over 2x slower with jit=on

[Andreas Joseph Krogh]

På onsdag 22. august 2018 kl. 18:51:55, skrev Andres Freund mailto:and...@anarazel.de>>:
On 2018-08-22 18:39:18 +0200, Andreas Joseph Krogh wrote:
 > Just to be clear; The query really runs slower (wall-clock time), it's not
 > just the timing.

 I bet it's not actually running slower, it "just" takes longer to start
 up due to the JITing in each worker. I suspect what we should do is to
 multiple the cost limits by the number of workers, to model that.  But
 without the fixed instrumentation that's harder to see...
 
Well, yes, that might be. By "runs" I meant from me hitting ENTER in psql to 
the time the query finishes...
 
I thought JITing of prepared queries happended once (in "prepare") so it 
didn't have to do the JITing every time the query is executed. Isn't 
the previously generated bytecode usable for subsequent queries?
 
--
 Andreas Joseph Krogh

Sv: Re: Query is over 2x slower with jit=on

[Andreas Joseph Krogh]

På onsdag 22. august 2018 kl. 18:12:41, skrev Andres Freund mailto:and...@anarazel.de>>:
Hi,

 On 2018-04-18 18:37:30 -0400, Robert Haas wrote:
 > On Wed, Apr 18, 2018 at 3:29 PM, Andres Freund  wrote:
 > > Not convinced that that is true - the issue is more likely that JIT work 
in workers is counted as execute time... Gotta add that somehow, not sure what 
the best way would be.
 >
 > Oh, that does seem like something that should be fixed.  If that's
 > what is happening here, it's bound to confuse a lot of people.
 > Probably you need to add some code to
 > ExecParallelRetrieveInstrumentation.

 I had lost track of this, and we unfortunately hadn't added an open item
 back then.  I think we should add it now?

 RMT (with me recused), do you think we should accept the new code fixing
 this would entail? And thus that this should be an open item? It's
 arguably a new feature, although I don't find that a terribly convincing
 position.

 Greetings,

 Andres Freund
 
Just to be clear; The query really runs slower (wall-clock time), it's not 
just the timing.
 
--
 Andreas Joseph Krogh

Sv: Sv: Re: Query is over 2x slower with jit=on

[Andreas Joseph Krogh]

På onsdag 18. april 2018 kl. 18:26:03, skrev Andreas Joseph Krogh <
andr...@visena.com >:
På onsdag 18. april 2018 kl. 17:50:55, skrev Andres Freund >:
On 2018-04-18 17:35:31 +0200, Andreas Joseph Krogh wrote:
 > With jit=on:
 > https://explain.depesz.com/s/vYB
 > Planning Time: 0.336 ms
 >  JIT:
 >   Functions: 716
 >   Generation Time: 78.404 ms
 >   Inlining: false
 >   Inlining Time: 0.000 ms
 >   Optimization: false
 >   Optimization Time: 43.916 ms
 >   Emission Time: 600.031 ms

 Any chance this is a debug LLVM build?


 > What's the deal with jit making it slower?

 JIT has cost, and sometimes it's not beneficial. Here our heuristics
 when to JIT appear to be a bit off. In the parallel world it's worse
 because the JITing is duplicated for parallel workers atm.
 
PostgreSQL is built with "--enable-debug --with-llvm". LLVM is the one which 
comes with Ubuntu-17.10.
 
Some more info;
 
Without --enable-debug (seems this didn't impact performance mutch):
 
First 5 executions (prepared-statement issued from psql)
 Planning Time: 47.634 ms
  JIT:
    Functions: 725
    Generation Time: 74.748 ms
    Inlining: true
    Inlining Time: 90.763 ms
    Optimization: true
    Optimization Time: 5822.516 ms
    Emission Time: 3089.127 ms
  Execution Time: 16375.996 ms
 
 
After 5 executions (prepared-statement issued from psql)
Planning Time: 0.385 ms
 JIT:
   Functions: 716
   Generation Time: 76.382 ms
   Inlining: false
   Inlining Time: 0.000 ms
   Optimization: false
   Optimization Time: 41.709 ms
   Emission Time: 613.074 ms
 Execution Time: 2031.830 ms
 
jit=off:
 
Planning Time: 0.171 ms
 Execution Time: 832.489 ms
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Sv: Re: Query is over 2x slower with jit=on

[Andreas Joseph Krogh]

På onsdag 18. april 2018 kl. 17:50:55, skrev Andres Freund >:
On 2018-04-18 17:35:31 +0200, Andreas Joseph Krogh wrote:
 > With jit=on:
 > https://explain.depesz.com/s/vYB
 > Planning Time: 0.336 ms
 >  JIT:
 >   Functions: 716
 >   Generation Time: 78.404 ms
 >   Inlining: false
 >   Inlining Time: 0.000 ms
 >   Optimization: false
 >   Optimization Time: 43.916 ms
 >   Emission Time: 600.031 ms

 Any chance this is a debug LLVM build?


 > What's the deal with jit making it slower?

 JIT has cost, and sometimes it's not beneficial. Here our heuristics
 when to JIT appear to be a bit off. In the parallel world it's worse
 because the JITing is duplicated for parallel workers atm.
 
PostgreSQL is built with "--enable-debug --with-llvm". LLVM is the one which 
comes with Ubuntu-17.10.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com