От: Dave Held
Тема: Re: Sort and index
Дата: ,
Msg-id: 49E94D0CFCD4DB43AFBA928DDD20C8F9026184B6@asg002.asg.local
(см: обсуждение, исходный текст)
Ответ на: Sort and index  (Andrei Gaspar)
Ответы: Re: Sort and index  (Andrei Gaspar)
Re: Sort and index  (Michael Fuhr)
Re: Sort and index  ("Jim C. Nasby")
Список: pgsql-performance

Скрыть дерево обсуждения

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 по дате сообщения:

От: Bruce Momjian
Дата:
Сообщение: Re: How to improve db performance with $7K?
От: Alex Turner
Дата:
Сообщение: Re: How to improve db performance with $7K?