[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    [HACKERS] why subplan is 10x faster then function?
From:       Pavel Stehule <pavel.stehule () gmail ! com>
Date:       2017-09-30 21:23:22
Message-ID: CAFj8pRDdfCq_P-caQAUzwF9z3ktOmg0vgn49+3EcefaBq9Szjw () mail ! gmail ! com
[Download RAW message or body]

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

[Attachment #3 (text/html)]

<div dir="ltr"><div><div><div><div><div><div><div><div>Hi<br><br></div>I have some \
strange slow queries based on usage &quot;view&quot; functions<br><br></div>one \
function looks like this:<br><br>CREATE OR REPLACE FUNCTION \
ides_funcs.najdatsplt_cislo_exekuce(mid_najdatsplt bigint)<br> RETURNS character \
varying<br> LANGUAGE sql<br> STABLE<br>AS $function$<br>select CISLOEXEKUCE<br>      \
from najzalobpr MT, najvzallok A1,<br>                    NAJZALOBST A2, NAJZALOBCE \
A3 where<br>                    MT.ID_NAJVZALLOK= A1.ID_NAJVZALLOK AND<br>            \
A1.ID_NAJZALOBST=A2.ID_NAJZALOBST AND<br>                    A2.ID_NAJZALOBCE= \
A3.ID_NAJZALOBCE AND<br>                    MT.ID_NAJDATSPLT = mID_NAJDATSPLT  LIMIT \
1;<br>$function$ cost 20<br>;</div><div><br></div><div>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.<br></div><div><br></div>when this function is evaluated as function, then \
execution needs about 46 sec<br><br>    -&gt;  Nested Loop Left Join  \
(cost=0.71..780360.31 rows=589657 width=2700) (actual time=47796.588..47796.588 \
rows=0 loops=1)<br>          -&gt;  Nested Loop  (cost=0.29..492947.20 rows=589657 \
width=2559) (actual time=47796.587..47796.587 rows=0 loops=1)                     \
<br>                -&gt;  Seq Scan on najdatsplt mt  (cost=0.00..124359.24 \
rows=1106096 width=1013) (actual time=47796.587..47796.587 rows=0 loops=1)<br>        \
Filter: (najdatsplt_cislo_exekuce(id_najdatsplt) IS NOT NULL)                         \
<br>                      Rows Removed by Filter: 1111654 <br><br></div>When I use \
correlated subquery, then <br><br> -&gt;  Nested Loop  (cost=0.29..19876820.11 \
rows=589657 width=2559) (actual time=3404.154..3404.154 rows=0 loops=1) <br>  -&gt;  \
Seq Scan on najdatsplt mt  (cost=0.00..19508232.15 rows=1106096 width=1013) (actual \
time=3404.153..3404.153 rows=0 loops=1) <br>      Filter: ((SubPlan 11) IS NOT NULL) \
<br>      Rows Removed by Filter: 1111654<br>      SubPlan 11 <br>        -&gt;  \
Limit  (cost=1.10..17.49 rows=1 width=144) (actual time=0.002..0.002 rows=0 \
loops=1111654)<br>              -&gt;  Nested Loop  (cost=1.10..17.49 rows=1 \
width=144) (actual time=0.002..0.002 rows=0 loops=1111654)<br>                    \
-&gt;  Nested Loop  (cost=0.83..17.02 rows=1 width=8) (actual time=0.002..0.002 \
rows=0 loops=1111654)<br>                          -&gt;  Nested Loop  \
(cost=0.56..16.61 rows=1 width=8) (actual time=0.002..0.002 rows=0 \
loops=1111654)<br><br></div>The execution plan is +/- same - the bottleneck is in \
function execution <br><br></div>Tested with same result on 9.6, \
10.</div><div><br></div><div>Is known overhead of function \
execution?</div><div><br></div>Regards<br><br></div>Pavel<br></div>



[prev in list] [next in list] [prev in thread] [next in thread]