Re: Does anybody use ORDER BY x USING y?

On P, 2005-09-18 at 23:34 -0400, Tom Lane wrote:
> Greg Stark <gsstark(at)mit(dot)edu> writes:
> > Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> >> That would be an extremely bad idea, because it would immediately remove
> >> index scans as one way to meet an ORDER BY.
>
> > Well couldn't the index scan be taught to go fetch the NULLs in a separate
> > traversal?
>
> (1) IS NULL is not an indexable operation, so no, not without
> significant overhaul of the index AM API.

But we do store NULLs in indexes, so why is it not indexable?

This is either an interface bug (not making use of stored info) or
storage bug (wasting space storing unneccessary info)

> (2) This propagates a problem that is specific to orderable indexes (ie
> btree) into code that is generic to all indexes, and thus creates the
> problem of how do you deal with specifying NULL ordering without any
> definition of ordering for non-NULLs.

we dont need an ordering of NULLs for cases without ORDER BY. You can't
specify NULLS FIRST/LAST without ORDER BY.

When one needs to use index for ordering we could use a plan like

APPEND
INDEX SCAN FOR NULLS, FILTER IS NULL
INDEX SCAN FOR NOT NULLS, FILTER IS NOT NULL

if NULL's are needed to be returned as sorted first/last

If no index scan is used, sorting code should be made smart enough to
recognize nulls and deal with it.

> (3) You still have to invent a mechanism to define whether you want
> nulls first or last ... and make sure that that mechanism works for
> plans that use explicit SORT steps as well as those that use index
> scans.

The main place I see problems is multiple field indexes, where some non-
first field is null. For single field indexes simply making two index
scans, possibly in different directions seems easy.

--
Hannu Krosing <hannu(at)skype(dot)net>