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

2010/11/18 Tom Lane <tgl@sss.pgh.pa.us>:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>> 2010/11/18 Tom Lane <tgl@sss.pgh.pa.us>:
>>> More to the point, if there is indeed an interesting performance win
>>> here, we could get the same win by internally optimizing the existing
>>> syntax.
>
>> sorry, but I don't agree. I don't think, so there are some big space
>> for optimizing - and if then it means much more code complexity for
>> current expr executor. Next - FOR IN ARRAY takes fields from array on
>> request - and it is possible, because a unpacking of array is
>> controlled by statement - it's impossible do same when unpacking is
>> inside other functions with same effectivity.
>
> Just because you haven't thought about it doesn't mean it's impossible
> or impractical.
>
> The first implementation I was thinking of would involve looking at the
> SELECT querytree after parsing to see if it's "SELECT UNNEST(something)"
> --- that is, empty jointree and so on, single tlist item that is an
> invocation of the unnest() function.  If it is, you pull out the
> argument expression of unnest() and go from there, with exactly the same
> execution behavior as in the specialized-syntax patch.  This is
> perfectly safe if you identify the array_unnest function by OID: since
> it's a built-in function you know exactly what it's supposed to do.

this additional control will do slow down for any expression - more -
somebody can use a form: SELECT FROM unnest(), and this form will be
slower.

>
> But having said that, it's still not apparent to me that array_unnest
> itself is markedly slower than what you could hope to do in plpgsql.
> I think the real issue here is that plpgsql's simple-expression code
> can't be used with set-returning expressions, which means that we have
> to go through the vastly more expensive SPI code path.  But that
> restriction is largely based on fear of re-using expression trees, which
> is something we fixed a few weeks ago.  I think that it would now be
> interesting to look at whether "FOR x IN SELECT simple-expression" could
> use the simple-expression code even when the expression returns set.
> That approach might bring a useful speedup not just for unnest, but for
> many other use-cases as well.
>

any SRF call must not be faster then direct access to array. There is
overhead with tuples.

I don't understand you. Sorry. I don't think, so your objections are objective.

Regards

Pavel

>                        regards, tom lane
>