Re: final patch - plpgsql: for-in-array

2010/11/18 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> what is a slow:
>
>> a) repeated detoasting - access with subscripts - maybe detoasted
>> values can be cached?
>> b) evaluation of SRF expression - maybe call of SRF function can be
>> simple expression,
>> c) faster evaluation ro query
>
>> The most important is @a.
>
> Really?  Becase AFAICS array_unnest only detoasts the source array once,
> and saves the value between calls.

I know.

this note was a different -only a few people use FOR IN SELECT UNNEST
for iteration over array. So from Robert's question (what is important
for current code?) perspective the more significant is access to
individual fields via subscripts. For example:

for i in 1..10000 loop
s := s + A[i];
end loop

is slow, when high limit of array is some bigger number > 1000. But
almost all stored procedures used this pattern. I know so some people
use a pattern FOR IN SELECT UNNEST, but (for example) I didn't meet
that developer in Czech Rep. It isn't usual so people can mix SQL and
PL well.

It has a practical reasons - using a UNNEST for small arrays is slower.

>
> array_unnest doesn't currently have any smarts about fetching slices
> of an array.  I'm not sure how useful that would be in practice, since
> (1) in most usages you probably run the function to the end and fetch
> all the values anyway; (2) it's hard to see how to optimize that way
> if the elements are varlena, which they most likely are in most usages
> where this could possibly be a win.  But if Cedric's use-case is really
> worth optimizing, I'd sure rather see the smarts for it in the general
> purpose array_unnest function instead of buried in plpgsql's FOR logic.
>

Probably - example with LIKE filter is really specific. But there can
be a tasks, where you can early break a iteration where you find a
value higher or less then some constant - it's not too artificial -
test "IS MEMBER OF"

Regards

Pavel

>                        regards, tom lane
>