Re: why subplan is 10x faster then function?

2017-09-30 23:23 GMT+02:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

> Hi
>
> I have some strange slow queries based on usage "view" functions
>
> one function looks like this:
>
> CREATE OR REPLACE FUNCTION ides_funcs.najdatsplt_cislo_exekuce(mid_najdatsplt
> bigint)
> RETURNS character varying
> LANGUAGE sql
> STABLE
> AS $function$
> select CISLOEXEKUCE
> from najzalobpr MT, najvzallok A1,
> NAJZALOBST A2, NAJZALOBCE A3 where
> MT.ID_NAJVZALLOK= A1.ID_NAJVZALLOK AND
> A1.ID_NAJZALOBST=A2.ID_NAJZALOBST AND
> A2.ID_NAJZALOBCE= A3.ID_NAJZALOBCE AND
> MT.ID_NAJDATSPLT = mID_NAJDATSPLT LIMIT 1;
> $function$ cost 20
> ;
>
> I know so using this kind of functions is not good idea - it is customer
> old code generated from Oracle. I had idea about possible planner issues.
> But this is a executor issue.
>
> when this function is evaluated as function, then execution needs about 46
> sec
>
> -> Nested Loop Left Join (cost=0.71..780360.31 rows=589657
> width=2700) (actual time=47796.588..47796.588 rows=0 loops=1)
> -> Nested Loop (cost=0.29..492947.20 rows=589657 width=2559)
> (actual time=47796.587..47796.587 rows=0 loops=1)
> -> Seq Scan on najdatsplt mt (cost=0.00..124359.24
> rows=1106096 width=1013) (actual time=47796.587..47796.587 rows=0 loops=1)
> Filter: (najdatsplt_cislo_exekuce(id_najdatsplt) IS
> NOT NULL)
> Rows Removed by Filter: 1111654
>
> When I use correlated subquery, then
>
> -> Nested Loop (cost=0.29..19876820.11 rows=589657 width=2559) (actual
> time=3404.154..3404.154 rows=0 loops=1)
> -> Seq Scan on najdatsplt mt (cost=0.00..19508232.15 rows=1106096
> width=1013) (actual time=3404.153..3404.153 rows=0 loops=1)
> Filter: ((SubPlan 11) IS NOT NULL)
> Rows Removed by Filter: 1111654
> SubPlan 11
> -> Limit (cost=1.10..17.49 rows=1 width=144) (actual
> time=0.002..0.002 rows=0 loops=1111654)
> -> Nested Loop (cost=1.10..17.49 rows=1 width=144) (actual
> time=0.002..0.002 rows=0 loops=1111654)
> -> Nested Loop (cost=0.83..17.02 rows=1 width=8)
> (actual time=0.002..0.002 rows=0 loops=1111654)
> -> Nested Loop (cost=0.56..16.61 rows=1
> width=8) (actual time=0.002..0.002 rows=0 loops=1111654)
>
> The execution plan is +/- same - the bottleneck is in function execution
>
> Tested with same result on 9.6, 10.
>
> Is known overhead of function execution?
>
>
looks like this nested query are expensive - some expensive operatiions
are pushed to exec_init_node. When the query are executed from function,
then exec_init_note is called too often

> Regards
>
> Pavel
>