bad JIT decision

On Sat, 25 Jul 2020 at 08:46, Scott Ribe <scott_ribe@elevated-dev.com> wrote:

Given the magnitude of the miss in using JIT here, I am wondering: is it possible that the planner does not properly take into account the cost of JIT'ing a function for multiple partitions? Or is it that the planner doesn't have enough info about the restrictiveness of conditions, and is therefore anticipating running the functions against a great many rows?

It does not really take into account the cost of jitting. If the total
plan cost is above the jit threshold then jit is enabled. If not, then
it's disabled.

There are various levels of jit and various thresholds that can be tweaked, see:

select name,setting from pg_settings where name like '%jit%';

But as far as each threshold goes, you either reach it or you don't.
Maybe that can be made better by considering jit in a more cost-based
way rather than by threshold, that way it might be possible to
consider jit per plan node rather than on the query as a whole. e.g,
if you have 1000 partitions and 999 of them have 1 row and the final
one has 1 billion rows, then it's likely a waste of time to jit
expressions for the 999 partitions.

However, for now, you might just want to try raising various jit
thresholds so that it only is enabled for more expensive plans.

David

On Jul 24, 2020, at 4:26 PM, David Rowley <dgrowleyml@gmail.com> wrote:

It does not really take into account the cost of jitting.

That is what I was missing.

I read about JIT when 12 was pre-release; in re-reading after my post I see that it does not attempt to estimate JIT cost. And in thinking about it, I realize that would be next to impossible to anticipate how expensive LLVM optimizstion was going to be.

In the case where a set of functions is replicated across partitions, it would be possible to do them once, then project the cost of the copies. Perhaps for PG 14 as better support for the combination of JIT optimization and highly-partitioned data ;-)

David Rowley <dgrowleyml@gmail.com> writes:

However, for now, you might just want to try raising various jit
thresholds so that it only is enabled for more expensive plans.

Yeah. I'm fairly convinced that the v12 defaults are far too low,
because we are constantly seeing complaints of this sort.

regards, tom lane

On Sat, 25 Jul 2020 at 10:37, Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Rowley <dgrowleyml@gmail.com> writes:

However, for now, you might just want to try raising various jit
thresholds so that it only is enabled for more expensive plans.

Yeah. I'm fairly convinced that the v12 defaults are far too low,
because we are constantly seeing complaints of this sort.

I think plan cost overestimation is a common cause of unwanted jit too.

It would be good to see the EXPLAIN ANALYZE so we knew if that was the
case here.

David

On Jul 24, 2020, at 4:37 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Yeah. I'm fairly convinced that the v12 defaults are far too low,
because we are constantly seeing complaints of this sort.

They are certainly too low for our case; not sure if for folks who are not partitioning if they're way too low.

The passive-aggressive approach would really not be good general advice for you, but I'm actually glad that in our case they were low enough to get our attention early ;-)

I think I will disable optimization, because with our partitioning scheme we will commonly see blow ups of optimization time like this one.

The inlining time in this case is still much more than the query, but it is low enough to not be noticed by users, and I think that with different variations of the parameters coming in to the query, that for the slower versions (more partitions requiring actual scans), inlining will help. Slowing down the fastest while speeding up the slower is a trade off we can take.

Hi,

On 2020-07-24 18:37:02 -0400, Tom Lane wrote:

David Rowley <dgrowleyml@gmail.com> writes:

However, for now, you might just want to try raising various jit
thresholds so that it only is enabled for more expensive plans.

Yeah. I'm fairly convinced that the v12 defaults are far too low,
because we are constantly seeing complaints of this sort.

I think the issue is more that we need to take into accoutn that the
overhead of JITing scales ~linearly with the number of JITed
expressions. And that's not done right now. I've had a patch somewhere
that had a prototype implementation of changing the costing to be
#expressions * some_cost, and I think that's a lot more accurate.

Greetings,

Andres Freund

Hi,

On Fri, Jul 24, 2020, at 15:32, Scott Ribe wrote:

On Jul 24, 2020, at 4:26 PM, David Rowley <dgrowleyml@gmail.com> wrote:

It does not really take into account the cost of jitting.

That is what I was missing.

I read about JIT when 12 was pre-release; in re-reading after my post I
see that it does not attempt to estimate JIT cost. And in thinking
about it, I realize that would be next to impossible to anticipate how
expensive LLVM optimizstion was going to be.

We certainly can do better than now.

In the case where a set of functions is replicated across partitions,
it would be possible to do them once, then project the cost of the
copies.

Probably not - JITing functions separately is more expensive than doing them once... The bigger benefit there is to avoid optimizing functions that are likely to be the same.

Perhaps for PG 14 as better support for the combination of JIT
optimization and highly-partitioned data ;-)

If I posted a few patches to test / address some of these issue, could you test them with your schema & querries?

Regards,

Andres

On Sat, 25 Jul 2020 at 10:42, David Rowley <dgrowleyml@gmail.com> wrote:

On Sat, 25 Jul 2020 at 10:37, Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Rowley <dgrowleyml@gmail.com> writes:

However, for now, you might just want to try raising various jit
thresholds so that it only is enabled for more expensive plans.

Yeah. I'm fairly convinced that the v12 defaults are far too low,
because we are constantly seeing complaints of this sort.

I think plan cost overestimation is a common cause of unwanted jit too.

It would be good to see the EXPLAIN ANALYZE so we knew if that was the
case here.

So Scott did send me the full EXPLAIN ANALYZE for this privately. He
wishes to keep the full output private.

After looking at it, it seems the portion that he pasted above, aka:

-> Index Scan using equities_rds_id on equities e0 (cost=0.42..33.74
rows=1 width=37) (actual time=6751.892..6751.892 rows=0 loops=1)
Index Cond: (rds_id = ANY ('{..., ..., ..., ...}'::uuid[]))
Filter: (security_type = 'ETP'::text)
Rows Removed by Filter: 4

Is nested at the bottom level join, about 6 joins deep. The lack of
any row being found results in upper level joins not having to do
anything, and the majority of the plan is (never executed).

David

David Rowley <dgrowleyml@gmail.com> writes:

On Sat, 25 Jul 2020 at 10:42, David Rowley <dgrowleyml@gmail.com> wrote:

I think plan cost overestimation is a common cause of unwanted jit too.
It would be good to see the EXPLAIN ANALYZE so we knew if that was the
case here.

So Scott did send me the full EXPLAIN ANALYZE for this privately. He
wishes to keep the full output private.

So ... what was the *top* line, ie total cost estimate?

regards, tom lane

Andres Freund <andres@anarazel.de> writes:

On 2020-07-24 18:37:02 -0400, Tom Lane wrote:

Yeah. I'm fairly convinced that the v12 defaults are far too low,
because we are constantly seeing complaints of this sort.

I think the issue is more that we need to take into accoutn that the
overhead of JITing scales ~linearly with the number of JITed
expressions. And that's not done right now. I've had a patch somewhere
that had a prototype implementation of changing the costing to be
#expressions * some_cost, and I think that's a lot more accurate.

Another thing we could try with much less effort is scaling it by the
number of relations in the query. There's already some code in the
plancache that tries to estimate planning effort that way, IIRC.
Such a scaling would be very legitimate for the cost of compiling
tuple-deconstruction code, and for other expressions it'd kind of
amount to an assumption that the expressions-per-table ratio is
roughly constant. If you don't like that, maybe some simple
nonlinear growth rule would work.

regards, tom lane

David Rowley <dgrowleyml@gmail.com> writes:

... nested at the bottom level join, about 6 joins deep. The lack of
any row being found results in upper level joins not having to do
anything, and the majority of the plan is (never executed).

On re-reading this, that last point struck me forcibly. If most of
the plan never gets executed, could we avoid compiling it? That is,
maybe JIT isn't JIT enough, and we should make compilation happen
at first use of an expression not during executor startup.

regards, tom lane

On Sun, 26 Jul 2020 at 02:17, Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Rowley <dgrowleyml@gmail.com> writes:

On Sat, 25 Jul 2020 at 10:42, David Rowley <dgrowleyml@gmail.com> wrote:

I think plan cost overestimation is a common cause of unwanted jit too.
It would be good to see the EXPLAIN ANALYZE so we knew if that was the
case here.

So Scott did send me the full EXPLAIN ANALYZE for this privately. He
wishes to keep the full output private.

So ... what was the *top* line, ie total cost estimate?

Hash Right Join (cost=1200566.17..1461446.31 rows=1651 width=141)
(actual time=5881.944..5881.944 rows=0 loops=1)

So well above the standard jit inline and optimize cost

David

On Sun, 26 Jul 2020 at 02:23, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Andres Freund <andres@anarazel.de> writes:

On 2020-07-24 18:37:02 -0400, Tom Lane wrote:

Yeah. I'm fairly convinced that the v12 defaults are far too low,
because we are constantly seeing complaints of this sort.

I think the issue is more that we need to take into accoutn that the
overhead of JITing scales ~linearly with the number of JITed
expressions. And that's not done right now. I've had a patch somewhere
that had a prototype implementation of changing the costing to be
#expressions * some_cost, and I think that's a lot more accurate.

Another thing we could try with much less effort is scaling it by the
number of relations in the query. There's already some code in the
plancache that tries to estimate planning effort that way, IIRC.
Such a scaling would be very legitimate for the cost of compiling
tuple-deconstruction code, and for other expressions it'd kind of
amount to an assumption that the expressions-per-table ratio is
roughly constant. If you don't like that, maybe some simple
nonlinear growth rule would work.

I had imagined something a bit less all or nothing. I had thought
that the planner could pretty cheaply choose if jit should occur or
not on a per-Expr level. For WHERE clause items we know "norm_selec"
and we know what baserestrictinfos come before this RestrictInfo, so
we could estimate the number of executions per item in the WHERE
clause. For Exprs in the targetlist we have the estimated rows from
the RelOptInfo. HAVING clause Exprs will be evaluated a similar number
of times. The planner could do something along the lines of
assuming, say 1000 * cpu_operator_cost to compile an Expr then assume
that a compiled Expr will be some percentage faster than an evaluated
one and only jit when the Expr is likely to be evaluated enough times
for it to be an overall win. Optimize and inline would just have
higher thresholds.

David

On Sun, 26 Jul 2020 at 02:54, Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Rowley <dgrowleyml@gmail.com> writes:

... nested at the bottom level join, about 6 joins deep. The lack of
any row being found results in upper level joins not having to do
anything, and the majority of the plan is (never executed).

On re-reading this, that last point struck me forcibly. If most of
the plan never gets executed, could we avoid compiling it? That is,
maybe JIT isn't JIT enough, and we should make compilation happen
at first use of an expression not during executor startup.

That's interesting. But it would introduce an additional per
evaluation cost of checking if we're doing the first execution.

David

On 2020-Jul-24, Andres Freund wrote:

I think the issue is more that we need to take into accoutn that the
overhead of JITing scales ~linearly with the number of JITed
expressions. And that's not done right now. I've had a patch somewhere
that had a prototype implementation of changing the costing to be
#expressions * some_cost, and I think that's a lot more accurate.

I don't quite understand why is it that a table with 1000 partitions
means that JIT compiles the thing 1000 times. Sure, it is possible that
some partitions have a different column layout, but it seems an easy bet
that most cases are going to have identical column layout, and so tuple
deforming can be shared. (I'm less sure about sharing a compile of an
expression, since the varno would vary. But presumably there's a way to
take the varno as an input value for the compiled expr too?) Now I
don't actually know how this works so please correct if I misunderstand
it.

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Jul 27, 2020, at 4:00 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:

I don't quite understand why is it that a table with 1000 partitions
means that JIT compiles the thing 1000 times. Sure, it is possible that
some partitions have a different column layout, but it seems an easy bet
that most cases are going to have identical column layout, and so tuple
deforming can be shared. (I'm less sure about sharing a compile of an
expression, since the varno would vary. But presumably there's a way to
take the varno as an input value for the compiled expr too?) Now I
don't actually know how this works so please correct if I misunderstand
it.

I'm guessing it's because of inlining. You could optimize a function that takes parameters, no problem. But what's happening is inlining, with parameters, then optimizing.

Hi,

On 2020-07-25 10:54:18 -0400, Tom Lane wrote:

David Rowley <dgrowleyml@gmail.com> writes:

... nested at the bottom level join, about 6 joins deep. The lack of
any row being found results in upper level joins not having to do
anything, and the majority of the plan is (never executed).

On re-reading this, that last point struck me forcibly. If most of
the plan never gets executed, could we avoid compiling it? That is,
maybe JIT isn't JIT enough, and we should make compilation happen
at first use of an expression not during executor startup.

That unfortunately has its own downsides, in that there's significant
overhead of emitting code multiple times. I suspect that taking the
cost of all the JIT emissions together into account is the more
promising approach.

Greetings,

Andres Freund

On 2020-Jul-27, Scott Ribe wrote:

On Jul 27, 2020, at 4:00 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:

I don't quite understand why is it that a table with 1000 partitions
means that JIT compiles the thing 1000 times. Sure, it is possible that
some partitions have a different column layout, but it seems an easy bet
that most cases are going to have identical column layout, and so tuple
deforming can be shared. (I'm less sure about sharing a compile of an
expression, since the varno would vary. But presumably there's a way to
take the varno as an input value for the compiled expr too?) Now I
don't actually know how this works so please correct if I misunderstand
it.

I'm guessing it's because of inlining. You could optimize a function
that takes parameters, no problem. But what's happening is inlining,
with parameters, then optimizing.

Are you saying that if you crank jit_inline_above_cost beyond this
query's total cost, the problem goes away?

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Tue, 28 Jul 2020 at 11:00, Andres Freund <andres@anarazel.de> wrote:

On 2020-07-25 10:54:18 -0400, Tom Lane wrote:

David Rowley <dgrowleyml@gmail.com> writes:

... nested at the bottom level join, about 6 joins deep. The lack of
any row being found results in upper level joins not having to do
anything, and the majority of the plan is (never executed).

On re-reading this, that last point struck me forcibly. If most of
the plan never gets executed, could we avoid compiling it? That is,
maybe JIT isn't JIT enough, and we should make compilation happen
at first use of an expression not during executor startup.

That unfortunately has its own downsides, in that there's significant
overhead of emitting code multiple times. I suspect that taking the
cost of all the JIT emissions together into account is the more
promising approach.

Is there some reason that we can't consider jitting on a more granular
basis? To me, it seems wrong to have a jit cost per expression and
demand that the plan cost > #nexprs * jit_expr_cost before we do jit
on anything. It'll make it pretty hard to predict when jit will occur
and doing things like adding new partitions could suddenly cause jit
to not enable for some query any more.

ISTM a more granular approach would be better. For example, for the
expression we expect to evaluate once, there's likely little point in
jitting it, but for the one on some other relation that has more rows,
where we expect to evaluate it 1 billion times, there's likely good
reason to jit that. Wouldn't it be better to consider it at the
RangeTblEntry level?

David