2017-09-30 23:23 GMT+02:00 Pavel Stehule <pavel.steh...@gmail.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 >
