[HACKERS] why subplan is 10x faster then function?

[Pavel Stehule]

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?

Regards

Pavel