Re: Why DISTINCT ... DESC is slow?

"Brandon Aiken" <BAiken(at)winemantech(dot)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