[HACKERS] why subplan is 10x faster then function?
От | Pavel Stehule |
---|---|
Тема | [HACKERS] why subplan is 10x faster then function? |
Дата | |
Msg-id | CAFj8pRDdfCq_P-caQAUzwF9z3ktOmg0vgn49+3EcefaBq9Szjw@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: [HACKERS] why subplan is 10x faster then function?
(Pavel Stehule <pavel.stehule@gmail.com>)
Re: [HACKERS] why subplan is 10x faster then function? (Pavel Stehule <pavel.stehule@gmail.com>) |
Список | pgsql-hackers |
Hi
I have some strange slow queries based on usage "view" functionsCREATE 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.
-> 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
-> 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)
Is known overhead of function execution?
В списке pgsql-hackers по дате отправления: