Re: Sort and index

From: Dave Held
Subject: Re: Sort and index
Date: ,
Msg-id: 49E94D0CFCD4DB43AFBA928DDD20C8F9026184B6@asg002.asg.local
(view: Whole thread, Raw)
In response to: Sort and index  (Andrei Gaspar)
Responses: Re: Sort and index  (Andrei Gaspar)
Re: Sort and index  (Michael Fuhr)
Re: Sort and index  ("Jim C. Nasby")
List: pgsql-performance

Tree view

Sort and index  (Andrei Gaspar, )
 Re: Sort and index  ("Dave Held", )
  Re: Sort and index  (Andrei Gaspar, )
  Re: Sort and index  (Michael Fuhr, )
   Re: Sort and index  (Andrei Gaspar, )
  Re: Sort and index  ("Jim C. Nasby", )
   Re: Sort and index  (Tom Lane, )
    Re: Sort and index  ("Jim C. Nasby", )
     Re: Sort and index  ("Jim C. Nasby", )
      Re: Sort and index  (Tom Lane, )
       Re: Sort and index  ("Jim C. Nasby", )
        Re: Sort and index  (Tom Lane, )
         Re: Sort and index  ("Jim C. Nasby", )
          Re: Sort and index  (Manfred Koizar, )
           Re: Sort and index  ("Jim C. Nasby", )
            Re: Sort and index  (Manfred Koizar, )
             Re: Sort and index  ("Jim C. Nasby", )

> -----Original Message-----
> From: Andrei Gaspar [mailto:]
> Sent: Monday, April 18, 2005 10:36 AM
> To: 
> 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


pgsql-performance by date:

From: Bruce Momjian
Date:
Subject: Re: How to improve db performance with $7K?
From: Alex Turner
Date:
Subject: Re: How to improve db performance with $7K?