Re: Why DISTINCT ... DESC is slow?

"Brandon Aiken" <BAiken@winemantech.com> writes:
> If you have, say, an index(x, y) then that index will often double as an
> index(x).  It will generally not double as an index(y).

It's not hard to understand why, if you think about the sort ordering of
a double-column index:

    x    y

    1    1
    1    2
    1    3
    2    1
    2    2
    2    3
    3    1
    ...

All similar values of x are brought together, so scanning the index for
x alone works just the same as it would in a one-column index ... the
index entries are bigger so it's marginally less efficient, but only
marginally.  On the other hand, the entries for a specific value or
range of y will be scattered all over the index, so it's almost useless
to use the index for a search on y alone.

As of PG 8.1 or 8.2 (I forget) the optimizer will *consider* using such
an index for a y-only query, but it'll nearly always decide it's a bad
idea.

            regards, tom lane