Hello
I tried to optimize repeated assign in plpgsql with elimination
unnecessary palloc/free calls.
I tested changes on simple bublesort
postgres=# \sf buble
CREATE OR REPLACE FUNCTION public.buble(integer[])RETURNS integer[]LANGUAGE plpgsql
AS $function$
declare unsorted bool := true; aux int;
begin while unsorted loop unsorted := false; for i in array_lower($1,1) .. array_upper($1, 1) - 1 loop if
$1[i]> $1[i+1] then aux := $1[i]; $1[i] := $1[i+1]; $1[i+1] := aux; unsorted := true; end
if; end loop; end loop; return $1;
end
$function$
The performance tests shows so this optimization is useless. But when
I checked a oprofile' result I was surprised by high a SearchCatCache
calls.
3008 13.0493 SearchCatCache
1306 5.6657 ExecEvalParamExtern
1143 4.9586 GetSnapshotData
1122 4.8675 AllocSetAlloc
1058 4.5898 MemoryContextAllocZero
1002 4.3469 ExecMakeFunctionResultNoSets
986 4.2775 ExecEvalArrayRef
851 3.6918 LWLockAcquire
783 3.3968 LWLockRelease
664 2.8806 RevalidateCachedPlan
646 2.8025 AllocSetFree
568 2.4641 array_ref
551 2.3904 CopySnapshot
519 2.2515 AllocSetReset
510 2.2125 array_set
492 2.1344 PopActiveSnapshot
381 1.6529 ArrayGetOffset
369 1.6008 AcquireExecutorLocks
348 1.5097 pfree
347 1.5054 MemoryContextAlloc
313 1.3579 bms_is_member
285 1.2364 CatalogCacheComputeHashValue
267 1.1583 PushActiveSnapshot
266 1.1540 hash_uint32
253 1.0976 pgstat_init_function_usage
233 1.0108 array_seek.clone.0
when I mark function buble as immutable I got a profile:
3006 18.6384 SearchCatCache
1239 7.6823 ExecEvalParamExtern
1061 6.5786 MemoryContextAllocZero
931 5.7726 ExecMakeFunctionResultNoSets
881 5.4625 ExecEvalArrayRef
590 3.6582 RevalidateCachedPlan
580 3.5962 array_ref
518 3.2118 AllocSetAlloc
488 3.0258 array_set
447 2.7716 AllocSetReset
383 2.3748 AcquireExecutorLocks
334 2.0709 bms_is_member
311 1.9283 ArrayGetOffset
285 1.7671 CatalogCacheComputeHashValue
269 1.6679 pgstat_init_function_usage
240 1.4881 hash_uint32
237 1.4695 ResourceOwnerForgetPlanCacheRef
214 1.3269 oideq
210 1.3021 ReleaseCachedPlan
204 1.2649 array_seek.clone.0
202 1.2525 ResourceOwnerForgetCatCacheRef
196 1.2153 SearchSysCache
188 1.1657 pg_detoast_datum
185 1.1471 ArrayGetNItems
183 1.1347 ExecEvalConst
181 1.1223 DirectFunctionCall1Coll
178 1.1037 hashoid
176 1.0913 check_stack_depth
174 1.0789 heap_getsysattr
174 1.0789 pgstat_end_function_usage
173 1.0727 FunctionCall2Coll
Is this profile expected?
Regards
Pavel Stehule
