Re: Does anybody use ORDER BY x USING y?

On Mon, Sep 19, 2005 at 12:21:00PM -0400, Greg Stark wrote:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> > Greg Stark <gsstark(at)mit(dot)edu> writes:
> > > The thing is that these opclasses you're describing are closely related. It
> > > ought to be possible to use a single index to produce results in any of the
> > > four orders you describe.
> > Wrong --- only two of them. You can't magically swap nulls from one end
> > of the index to the other (and Hannu's flight of fantasy about double
> > indexscans is just a flight of fantasy; it would be solving the problem
> > at entirely the wrong place).
> I think that was my flight of fantasy. I didn't say it was pretty but it would
> solve the problem. Whereas having a separate opclass would mean someone would
> need a second index to satisfy the ordering which seems silly.

As I understand it, they would only need a second index, if they did want
to use the index to determine the sort order, for two different sort orders.

I don't see any easy way out of this. I think it could be optimized to
scan less than twice, but it would be an incredibly effort and maintenance
nightmare, for a minimal return.

mark

--
mark(at)mielke(dot)cc / markm(at)ncf(dot)ca / markm(at)nortel(dot)com __________________________
. . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder
|\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ |
| | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada

One ring to rule them all, one ring to find them, one ring to bring them all
and in the darkness bind them...

http://mark.mielke.cc/