Re: Scrollable cursors and Sort performance

On Fri, 2006-02-10 at 17:46 +0000, Simon Riggs wrote:
> > Lastly, there isn't any obvious reason that I can see for having to
> > change the default assumption about cursors. Most queries don't go
> > through cursors. For those that do, we already document that specifying
> > NO SCROLL can be a performance win, so any app that's not saying that
> > when it could has only itself to blame.
>
> The obvious reason is why force people to go out of their way for a
> performance win? This is the same as OIDs, AFAICS. Some people used them
> in their programs - well fine, they can keep 'em. Most people didn't and
> don't and will appreciate having their programs speed up.
>
> Not everybody gets the chance to change the SQL in an application
> program, however much they might want to and know they should. Third
> party software is most software.
>
> The only way to please both is to have a GUC, whatever it is set to by
> default.

Further consideration: Since not all queries are cursors, as you say,
the issue is less important, once we patch the executor as discussed.
Maybe we *can* dispense with the GUC?

Currently, ExecSupportsBackwardScan() makes the decision based upon the
node types present in the plan. For an in-memory sort, allowing backward
scans is effectively zero cost. It is only when the sort goes to disk
that it incurs extra overhead that you might wish to avoid.

It would be possible to update the state of the cursor AFTER the sort
has been performed. The cursor's query is not executed until the first
fetch, at which point all meaningful cursor requests can be accomplished
with a forward-only cursor. So it would be possible to make the cursor
NO SCROLL at that point. My thinking is that non-deterministic behaviour
like that is probably not helpful and could still result in some
existing programs breaking.

So, although having a GUC is not the only way, it does seem like the
right thing to do....but secondary to the main executor change.

Best Regards, Simon Riggs