Re: Sorting Improvements for 8.4

On Tue, Nov 27, 2007 at 06:03:46PM +0000, Simon Riggs wrote:
> Just wanted to review a few thoughts and ideas around improving external
> sorts, as recently encouraged to do by Jim Nasby.

Is there any way of PG knowing that having an index on a subset of the
sorted columns is sometimes a win? For example, if we have:

CREATE TABLE foo (
a INTEGER NOT NULL PRIMARY KEY,
b INTEGER NOT NULL,
c INTEGER
);

and we request:

SELECT * FROM foo ORDER BY a,b LIMIT 10;

then it may be a win to do smaller sorts for each value of "a", rather
than one big sort after all the data has been pulled out. Obviously,
it would depend on the distribution of "a", large numbers of distinct
values for "a" being good, and a small number being bad.

I think this would help in a number of other situations as well, but
that's just the most obvious case.

Sam