Re: Scrollable cursors and Sort performance

On Fri, 2006-02-10 at 10:22 -0600, Jim C. Nasby wrote:
> On Fri, Feb 10, 2006 at 01:32:44PM +0000, Simon Riggs wrote:
> > I intend to add a short patch to pass down the cursor state during
> > planning, so that when it is explicitly specified the sort node is able
> > to recognise this and avoid work. Also, to add a GUC to force the
> > not-explicitly-specified case to be the same as the NO SCROLL case, as
> > the standard requires.
> 
> So is this only an issue if you're using a cursor, or does this affect
> plain SELECT ... ORDER BY as well?
> 
> Reason I'm asking is that users should be able to explicitly be able to
> turn the extra step off somehow. I'm not clear if NO SCROLL is
> sufficient to do that or not.

It effects all sorts, whether or not they are even cursors.

If a cursor is defined NO SCROLL, which is the SQL Standard implicit
default, then we are safe to assume there will be no rewinds or backward
scans. The PostgreSQL current implicit default is SCROLL, which means
that no part of the executor can currently make useful assumptions about
scan direction, so this is a wider issue than just sorts.

Best Regards, Simon Riggs