От: Jim C. Nasby
Тема: Re: Sort and index
Дата: ,
Msg-id: 20050420004234.GX58835@decibel.org
(см: обсуждение, исходный текст)
Ответ на: Re: Sort and index  ("Dave Held")
Ответы: Re: Sort and index  (Tom Lane)
Список: 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", )

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               
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?"


В списке pgsql-performance по дате сообщения:

От: Jeff Frost
Дата:
Сообщение: Re: Opteron vs Xeon (Was: What to do with 6 disks?)
От: Dawid Kuroczko
Дата:
Сообщение: Re: immutable functions vs. join for lookups ?