Re: Sort and index

> -----Original Message-----
> From: Andrei Gaspar [mailto:andi@softnrg.dnttm.ro]
> Sent: Monday, April 18, 2005 10:36 AM
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] Sort and index
>
> I thought that an index can be used for sorting.
> I'm a little confused about the following result:
>
> create index OperationsName on Operations(cOperationName);
> explain SELECT * FROM Operations ORDER BY cOperationName;
>                               QUERY PLAN
> --------------------------------------------------------------
> ---------
>  Sort  (cost=185.37..189.20 rows=1532 width=498)
>    Sort Key: coperationname
>    ->  Seq Scan on operations  (cost=0.00..104.32 rows=1532 width=498)
> (3 rows)
>
> Is this supposed to be so?

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).

__
David B. Held
Software Engineer/Array Services Group
200 14th Ave. East,  Sartell, MN 56377
320.534.3637 320.253.7800 800.752.8129