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 plpgsql strict immutable;
I remember this issue
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 ...
I agree - the core of work have to be elsewhere than in plpgsql. Some years ago there was a idea about toast cache.