Re: Scrollable cursors and Sort performance

On Fri, Feb 10, 2006 at 07:16:41PM +0000, Simon Riggs wrote:
> On Fri, 2006-02-10 at 19:14 +0100, Martijn van Oosterhout wrote:
> > On Fri, Feb 10, 2006 at 04:48:42PM +0000, Simon Riggs wrote:
> > > 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.
> >
> > Umm, the documentation says: PostgreSQL will allow backward fetches
> > without SCROLL, if the cursor's query plan is simple enough that no
> > extra overhead is needed to support it.
> >
> > So if the default is SCROLL someone needs to fix the docs because
> > that's not what it says. It says that *some plans* can be fetched
> > backwards even if you don't say scroll. The documentation clearly says
> > we don't need to support backwards searches without scroll if it
> > causes problems.
>
> Changing the docs is not the problem here. I don't understand the point
> you are making and how it effects the issue.
>
> The problem is knowing before the sort is executed whether the sort
> result will ever be used in the future by a backward scan. We can only
> do this by definition, restricting the future use of a FETCH.

I think the point that Martijn was trying to make was that per our docs
it would be perfectly acceptable for us to make any cursor NO SCROLL
implicitly if it means less work for the optimizer.

Whether that's a good thing is a different story... ISTM doing so would
be very confusing for users.

Since this affects all queries with sorts I would definately be in favor
of exposing an option to change this ASAP, even if the default was to
maintain the current behavior for compatability. Disk sorts are hugely
expensive, and anything to reduce that expense would be very welcome.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461