pg.ddx.io pgsql-hackers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Sv: Re: Query is over 2x slower with jit=on 4+ messages / 1 participants [nested] [flat]
* Sv: Re: Query is over 2x slower with jit=on @ 2018-04-18 16:26 Andreas Joseph Krogh <andreas@visena.com> 0 siblings, 0 replies; 4+ messages in thread From: Andreas Joseph Krogh @ 2018-04-18 16:26 UTC (permalink / raw) To: pgsql-hackers@lists.postgresql.org På onsdag 18. april 2018 kl. 17:50:55, skrev Andres Freund <andres@anarazel.de <mailto:andres@anarazel.de>>: 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 andreas@visena.com <mailto:andreas@visena.com> www.visena.com <https://www.visena.com; <https://www.visena.com; ^ permalink raw reply [nested|flat] 4+ messages in thread
* Sv: Re: Query is over 2x slower with jit=on @ 2018-08-22 16:39 Andreas Joseph Krogh <andreas@visena.com> 0 siblings, 0 replies; 4+ messages in thread From: Andreas Joseph Krogh @ 2018-08-22 16:39 UTC (permalink / raw) To: Andres Freund <andres@anarazel.de>; +Cc: Robert Haas <robertmhaas@gmail.com>; Alvaro Herrera <alvherre@2ndquadrant.com>; Jonathan S. Katz <jkatz@postgresql.org>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org> På onsdag 22. august 2018 kl. 18:12:41, skrev Andres Freund <andres@anarazel.de <mailto:andres@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 <andres@anarazel.de> 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Sv: Re: Query is over 2x slower with jit=on @ 2018-08-22 17:51 Andreas Joseph Krogh <andreas@visena.com> 0 siblings, 0 replies; 4+ messages in thread From: Andreas Joseph Krogh @ 2018-08-22 17:51 UTC (permalink / raw) To: Andres Freund <andres@anarazel.de>; +Cc: Robert Haas <robertmhaas@gmail.com>; Alvaro Herrera <alvherre@2ndquadrant.com>; Jonathan S. Katz <jkatz@postgresql.org>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org> På onsdag 22. august 2018 kl. 18:51:55, skrev Andres Freund <andres@anarazel.de <mailto:andres@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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Sv: Re: Query is over 2x slower with jit=on @ 2018-09-08 10:44 Andreas Joseph Krogh <andreas@visena.com> 0 siblings, 0 replies; 4+ messages in thread From: Andreas Joseph Krogh @ 2018-09-08 10:44 UTC (permalink / raw) To: pgsql-hackers@lists.postgresql.org På torsdag 23. august 2018 kl. 09:14:56, skrev Andreas Joseph Krogh < andreas@visena.com <mailto:andreas@visena.com>>: På torsdag 23. august 2018 kl. 03:00:42, skrev Jonathan S. Katz < jkatz@postgresql.org <mailto:jkatz@postgresql.org>>: > On Aug 22, 2018, at 7:13 PM, Andres Freund <andres@anarazel.de> 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2018-09-08 10:44 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2018-04-18 16:26 Sv: Re: Query is over 2x slower with jit=on Andreas Joseph Krogh <andreas@visena.com> 2018-08-22 16:39 Sv: Re: Query is over 2x slower with jit=on Andreas Joseph Krogh <andreas@visena.com> 2018-08-22 17:51 Sv: Re: Query is over 2x slower with jit=on Andreas Joseph Krogh <andreas@visena.com> 2018-09-08 10:44 Sv: Re: Query is over 2x slower with jit=on Andreas Joseph Krogh <andreas@visena.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox