Re: Sort and index

On Mon, Apr 18, 2005 at 10:44:43AM -0500, Dave Held wrote:
> Since you are fetching the entire table, you are touching all the rows.
> If the query were to fetch the rows in index order, it would be seeking
> all over the table's tracks.  By fetching in sequence order, it has a
> much better chance of fetching rows in a way that minimizes head seeks.
> Since disk I/O is generally 10-100x slower than RAM, the in-memory sort
> can be surprisingly slow and still beat indexed disk access.  Of course,
> this is only true if the table can fit and be sorted entirely in memory
> (which, with 1500 rows, probably can).

Actually, the planner (at least in 7.4) isn't smart enough to consider
if the sort would fit in memory or not. I'm running a test right now to
see if it's actually faster to use an index in this case.
--
Jim C. Nasby, Database Consultant               decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"