Re: Scrollable cursors and Sort performance

On Sun, 2006-02-26 at 19:26 -0500, Tom Lane wrote:
> Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> > On Fri, 2006-02-10 at 11:58 -0500, Tom Lane wrote:
> >> I suspect that the right thing is not to see this as a planner issue at
> >> all, but to try to drive the choice off the context in which the plan
> >> gets invoked. Possibly we could pass a "need random access" boolean
> >> down through the ExecInitNode calls (I seem to recall some prior
> >> discussion of doing something like that, in the context of telling
> >> Materialize that it could be a no-op in some cases).
>
> > Yeh, that was me just being a little vague on implementation, but
> > handing off from planner to executor via the Plan node is what I was
> > hacking at now. I'll follow your recommendation and do it for the
> > general case. Propagating it down should allow a few similar
> > optimizations.
>
> Have you done anything with this idea yet? I was just thinking of
> attacking it myself.

Started, but have been side-tracked by other urgent matters.

Happy to complete this today with you? This mini-project has made me
realise I don't understand the executor as well as I should, so I'm keen
to see it through, even if I'm a bit slower at it.

> After looking at my old notes about Materialize,
> I am thinking that we should add a "int flags" parameter to the InitNode
> calls along with ExecutorStart and probably PortalStart. This would
> contain a bitwise OR of at least the following flag bits:
>
> need-ReScan
> need-backwards-scan
> need-mark-restore
> no-execute (so flags can replace ExecutorStart's explainOnly param)
>
> We'd have lots of room for expansion, but these are the ones that seem
> to have immediate use. And most callers of ExecutorStart/PortalStart
> know they don't need these things, so could just pass zero always.

Design-wise I was looking at putting a named struc in there, so it would
be easily expandible in the future to carry anything else that needs to
be passed down through the nodes like this. I guess thats the same
line-of-thought you're on too.

> Interesting point: how should EXPLAIN ANALYZE set these bits? For its
> own purposes it need not request random access, but it might be
> interesting to make it possible to examine both the random and nonrandom
> behaviors, now that these will be significantly different performancewise.
> Possibly we could make EXPLAIN ANALYZE EXECUTE set the random-access
> bits.

Good point. Whichever we do will be wrong in some cases.... I've no real
opinion on this other than a vague preference for it to be quick.

Best Regards, Simon Riggs