Optimizing tuplestore usage for SRFs

I wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> On Sun, 2008-10-26 at 21:49 -0400, Tom Lane wrote:
>>> ... effort into tuplestore optimization. (I note that the current
>>> tuplestore code writes 24 bytes per row for this example, which is a
>>> shade on the high side for only 4 bytes payload. It looks like it
>>> would be pretty easy to knock 10 bytes off that for a 40% savings in
>>> I/O volume.)

>> That seems like an important, possibly more important, change.

> Yeah, seeing that both WITH and window functions will be stressing
> tuplestore performance, anything we can save there is probably worth the
> trouble.

Six of the ten bytes I was speaking of are alignment padding, which
can be removed with some relatively simple hacking in tuplestore.c
(I think the same might apply in tuplesort.c BTW). The other place I
was looking at is that currently, a SRF's result tuplestore is always
built with randomAccess = true, which causes the tuplestore to write
a trailing length word on each tuple, to support the possibility of
being asked to read backwards. Of course, 99% of the time it never
will be asked to do so. So we ought to try to suppress that overhead.

I can see two ways we might do this:

1. Stop treating nodeFunctionscan.c as supporting backwards scan.
This would be an extremely localized change (about two lines ;-))
but the downside is that calling a function scan in a SCROLL CURSOR
would result in an extra Materialize node getting stuck in front
of the Functionscan node to support backwards scanning.

2. Propagate the Functionscan node's EXEC_FLAG_BACKWARD flag bit to
the called function. I'd be inclined to do this by adding it as an
additional bit in the rsinfo->allowedModes field.

In either case we would need cooperation from called SRFs to get
the optimization to happen --- the tuplestore_begin_heap calls are
not consolidated into one place (which maybe was a mistake) and
they're all passing constant TRUE for randomAccess. That coding
is safe but would need to change to reflect whichever policy we
adopt. (Note: one advantage of approach #1 is that if anyone is
mistakenly passing randomAccess = FALSE, it would become safe,
which it isn't now.)

I'm of mixed mind about which way to go. I'm not sure that optimizing
scroll cursors on functions is something worth worrying about. However,
if we do #1 now then we are probably locked into that approach and
could not change to #2 in the future --- we'd have to worry about
breaking third-party SRFs that are passing randomAccess = FALSE.

Comments?

regards, tom lane