On Fri, Mar 22, 2002 at 02:09:16PM +0000, Nigel J. Andrews wrote:
> > If you're selecting on one column (poster_name) and sorting on another
> > (time) it may help to have an index on both (time,poster_name) since that
> > avoids the sort step. (I hope 7.2 estimates sort costs better than earlier
> > versions).
>
> Thank you, it didn't occur to me that the two column index would ease the
> sorting. Would the order of the columns specified in the index creation be
> significant?
Yes. If you build an index on the columns (a,b), the index becomes a tree
where each value of a is ordered. At each node there is a subtree with each
value of b in ordered format. So if you do a sequential scan on an index it
comes out ordered by a then b.
Ofcourse, after identifying a tuple in the index, the database then has to
go back to the main table to check that it's valid in the current
transaction (and to get the data ofcourse). (There is a good reason
somewhere why transaction information is not stored within the index but I
don't remember it right now).
So it comes down to a comparison between index scan over most of the table
vs. sequential scan + sort of whole table. I'm not sure which would win...
HTH,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Ignorance continues to thrive when intelligent people choose to do
> nothing. Speaking out against censorship and ignorance is the imperative
> of all intelligent people.