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

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> 2010/11/18 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.

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.

regards, tom lane