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
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