Re: Why overhead of SPI is so large?

On 13.09.2019 10:16, Pavel Stehule wrote:
>
>
> pá 13. 9. 2019 v 9:09 odesílatel Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru <mailto:k(dot)knizhnik(at)postgrespro(dot)ru>> napsal:
>
>
>
> On 24.08.2019 19:13, Pavel Stehule wrote:
>>
>>
>> so 24. 8. 2019 v 18:01 odesílatel David Fetter <david(at)fetter(dot)org
>> <mailto:david(at)fetter(dot)org>> napsal:
>>
>> On Fri, Aug 23, 2019 at 11:10:28AM +0200, Pavel Stehule wrote:
>> > pá 23. 8. 2019 v 11:05 odesílatel Konstantin Knizhnik <
>> > k(dot)knizhnik(at)postgrespro(dot)ru
>> <mailto:k(dot)knizhnik(at)postgrespro(dot)ru>> napsal:
>> >
>> > >
>> > >
>> > > On 22.08.2019 18:56, Pavel Stehule wrote:
>> > >
>> > >
>> > >
>> > > čt 22. 8. 2019 v 17:51 odesílatel Konstantin Knizhnik <
>> > > k(dot)knizhnik(at)postgrespro(dot)ru
>> <mailto:k(dot)knizhnik(at)postgrespro(dot)ru>> napsal:
>> > >
>> > >> Some more information...
>> > >> First of all I found out that marking PL/pgSQL function
>> as immutable
>> > >> significantly increase speed of its execution:
>> > >> 19808 ms vs. 27594. It happens because
>> exec_eval_simple_expr is taken
>> > >> snapshot if function is volatile (default).
>> > >> I wonder if PL/pgSQL compiler can detect that evaluated
>> expression itself
>> > >> is actually immutable  and there is no need to take snapshot
>> > >> for each invocation of this function. Also I have tried
>> yet another PL
>> > >> language - JavaScript, which is now new outsider,
>> despite to the fact that
>> > >> v8 JIT compiler is very good.
>> > >>
>> > >
>> > > I have a plan to do some work in this direction. Snapshot
>> is not necessary
>> > > for almost buildin functions. If expr calls only buildin
>> functions, then
>> > > probably can be called without snapshot and without any
>> work with plan
>> > > cache.
>> > >
>> > >
>> > > I wonder if the following simple patch is correct?
>> > >
>> >
>> > You cannot to believe to user defined functions so
>> immutable flag is
>> > correct. Only buildin functions are 100% correct.
>> >
>> > CREATE OR REPLACE FUNCTION foo()
>> > RETURNS int AS $$
>> > SELECT count(*) FROM pg_class;
>> > $$ LANGUAGE sql IMMUTABLE;
>> >
>> > is working.
>>
>> No, it's lying to the RDBMS, so it's pilot error. The problem of
>> determining from the function itself whether it is in fact
>> immutable
>> is, in general, equivalent to the Halting Problem, so no, we
>> can't
>> figure it out. We do need to trust our users not to lie to
>> us, and we
>> do not need to protect them from the consequences when they do.
>>
>>
>> I have not any problem with fixing this behave when there will be
>> any alternative.
>>
>> I can imagine new special flag that can be used for STABLE
>> functions, that enforce one shot plans and can be optimized
>> similar like IMMUTABLE functions now - using result in planning time.
>>
>> The users lie because they must - there is not a alternative.
>> There is not any other solution - and estimation errors related
>> to a joins are fundamental issue.
>>
>
> Pavel, I wonder if I can put my patch (with fix which performs
> this optimization only for built-in functions) to commitfest or
> you prefer to do it yourself in some other way and propose your
> own solution?
>
>
> I think so your patch is good enough for commitfest.
>
> It doesn't remove all overhead - I think so there is lot of overhead
> related to plan cache, but it in good direction.
>
> Probably for these expressions is our final target using a cached JIT
> - but nobody knows when it will be. I'll not have to time for my
> experiments before October.
>

This is profile of execution of PL/pgSQL function with my patch:

   5.39%  postgres  plpgsql.so         [.] exec_assign_value
   5.10%  postgres  postgres           [.] ExecInterpExpr
   4.70%  postgres  postgres           [.] tts_buffer_heap_getsomeattrs
   4.56%  postgres  plpgsql.so         [.] exec_move_row_from_fields
   3.87%  postgres  postgres           [.] ExecScan
   3.74%  postgres  plpgsql.so         [.] exec_eval_expr
   3.64%  postgres  postgres           [.] heap_form_tuple
   3.13%  postgres  postgres           [.] heap_fill_tuple
   3.07%  postgres  postgres           [.] heapgettup_pagemode
   2.95%  postgres  postgres           [.] heap_deform_tuple
   2.92%  postgres  plpgsql.so         [.] plpgsql_param_eval_var
   2.64%  postgres  postgres           [.] HeapTupleSatisfiesVisibility
   2.61%  postgres  postgres           [.] AcquirePlannerLocks
   2.58%  postgres  postgres           [.] AcquireExecutorLocks
   2.43%  postgres  postgres           [.] GetCachedPlan
   2.26%  postgres  plpgsql.so         [.] exec_stmt
   2.23%  postgres  plpgsql.so         [.] exec_cast_value
   1.89%  postgres  postgres           [.] AllocSetAlloc
   1.75%  postgres  postgres           [.] palloc0
   1.73%  postgres  plpgsql.so         [.] exec_move_row
   1.73%  postgres  postgres           [.] OverrideSearchPathMatchesCurrent
   1.69%  postgres  plpgsql.so         [.] assign_simple_var
   1.63%  postgres  postgres           [.] heap_getnextslot
   1.60%  postgres  postgres           [.] SPI_plan_get_cached_plan
   1.55%  postgres  postgres           [.] heapgetpage
   1.47%  postgres  postgres           [.] heap_compute_data_size
   1.46%  postgres  postgres           [.] spi_printtup
   1.43%  postgres  postgres           [.] float8mul
   1.37%  postgres  postgres           [.] RevalidateCachedQuery
   1.36%  postgres  postgres           [.] standard_ExecutorRun
   1.35%  postgres  postgres           [.] recomputeNamespacePath
   1.28%  postgres  postgres           [.] ExecStoreBufferHeapTuple
   1.25%  postgres  postgres           [.] MemoryContextReset
   1.22%  postgres  plpgsql.so         [.] exec_eval_cleanup.isra.18
   1.20%  postgres  plpgsql.so         [.] exec_assign_expr
   1.05%  postgres  postgres           [.] SeqNext
   1.04%  postgres  postgres           [.] ResourceArrayRemove
   1.00%  postgres  postgres           [.] ScanQueryForLocks

Based on this profile it seems to me that plan cache overhead is
relatively small:

2.43%+1.60%+1.37% < 6%

But from the other side ExecInterpExpr itself takes also about 5%.
I do not completely understand why JIT is not currently used for
evaluation of SPI expressions
(why we call ExecInterpExpr and do not try  to compile this expression
even if JIT is enabled).
But event if we do it and improve speed of expression evaluation 10 or
more time, looks like
that effect on total query execution time will be also negligible (5%).

Most of the time is spent in pl_exec code, heap traversal , unpacking
and copying tuple data.
Looks like it can not be easily optimized and requires serious rewriting
of PL/pgSQL stuff.

--

Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company