Pavel Stehule <pavel.stehule@gmail.com> writes:
> 2015-05-06 15:50 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
>> Another way to look at it is that in this example, plpgsql's attempts to
>> force the "a" array into expanded form are a mistake: we never get any
>> benefit because array_cat() just wants it in flat form again, and delivers
>> it in flat form. (It's likely that this is an unrealistic worst case:
>> it's hard to imagine real array-using applications that never do any
>> element-by-element access.) Possibly we could improve matters with a more
>> refined heuristic about whether to force arrays to expanded form during
>> assignments --- but I'm not sure what that would look like. plpgsql has
>> very little direct knowledge of which operations will be applied to the
>> array later.
> Isn't better to push information about possible target to function?
I don't think that would solve the problem. For example, one of the cases
I worry about is a function that does read-only examination of an array
argument; consider something like
create function sum_squares(a numeric[]) returns numeric as $$ declare s numeric := 0; begin for i in
array_lower(a,1) .. array_upper(a, 1) loop s := s + a[i] * a[i]; end loop; return s; end;$$ language
plpgsqlstrict immutable;
array_get_element() is not in a position here to force expansion of the
array variable, so unless plpgsql itself does something we're not going
to get a performance win (unless the argument happens to be already
expanded on arrival).
I'm inclined to think that we need to add information to pg_type about
whether a type supports expansion (and how to convert to expanded form
if so). In the patch as it stands, plpgsql just has hard-wired knowledge
that it can call expand_array() on arrays that it's putting into function
local variables. I'd be okay with shipping 9.5 like that, but pretty soon
we'll want a solution that extension data types can use too.
More generally, it'd be nice if the mechanism could be more flexible than
"always force variables of this type to expanded form". But I don't see
how to teach plpgsql itself how to decide that intelligently, let alone
how we might design an API that lets some third-party data type decide it
at arm's length from plpgsql ...
regards, tom lane