I checked my tests and the most important is a remove a repeated detoast.
postgres=# CREATE OR REPLACE FUNCTION public.filter01(text[], text, integer)RETURNS text[]LANGUAGE plpgsql
AS $function$
DECLAREs text[] := '{}';l int := 0; i int;v text; loc text[] = $1;
BEGIN FOR i IN array_lower(loc,1)..array_upper(loc,1) LOOP EXIT WHEN l = $3; IF loc[i] LIKE $2 THEN s := s ||
loc[i]; l := l + 1; END IF; END LOOP; RETURN s;
END;$function$;
This code is very slow when array is large - tested on n=1000. With
one small modification can be 20x faster
DECLAREs text[] := '{}';l int := 0; i int;v text; loc text[] = $1 || '{}'::text[]; --<<< does just detoast and
docomprimation
BEGIN
the final version of test can be:
so result:
Don't access to large unmodified array inside cycle, when data comes
from table (for iteration over A[1000] of text(10)). A speadup is from
451 sec to 15 sec. This rule can be interesting for PostGIS people,
because it can be valid for other long varlena values. But still this
is 2x slower than special statement.
Regards
Pavel Stehule
samples % symbol name
332 22.1333 exec_eval_expr
311 20.7333 plpgsql_param_fetch
267 17.8000 exec_eval_datum
220 14.6667 exec_stmts
91 6.0667 setup_param_list
82 5.4667 exec_eval_cleanup.clone.10
71 4.7333 __i686.get_pc_thunk.bx
48 3.2000 exec_simple_cast_value
43 2.8667 exec_eval_boolean
samples % symbol name
4636 37.5994 array_seek.clone.0
961 7.7940 pglz_decompress
901 7.3074 list_member_ptr
443 3.5929 MemoryContextAllocZero
384 3.1144 AllocSetAlloc
381 3.0900 ExecEvalParamExtern
334 2.7088 GetSnapshotData
255 2.0681 AllocSetFree
254 2.0600 LWLockRelease
249 2.0195 ExecMakeFunctionResultNoSets
249 2.0195 UTF8_MatchText
234 1.8978 LWLockAcquire
195 1.5815 AllocSetReset
167 1.3544 AllocSetCheck
163 1.3220 pfree
151 1.2247 ExecEvalArrayRef
149 1.2084 RevalidateCachedPlan
138 1.1192 bms_is_member
126 1.0219 CopySnapshot