Re: Using "LIMIT" is much faster even though, searching

От: Andrew McMillan
Тема: Re: Using "LIMIT" is much faster even though, searching
Дата: ,
Msg-id: 1101889411.16058.241.camel@lamb.mcmillan.net.nz
(см: обсуждение, исходный текст)
Ответ на: Re: Using "LIMIT" is much faster even though, searching  ("Hyun-Sung, Jang")
Список: pgsql-performance

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

Using "LIMIT" is much faster even though, searching with PK.  (장현성, )
 Re: Using "LIMIT" is much faster even though, searching with PK.  (Tom Lane, )
  Re: Using "LIMIT" is much faster even though, searching  ("Hyun-Sung, Jang", )
   Re: Using "LIMIT" is much faster even though, searching  (Josh Berkus, )
    Re: Using "LIMIT" is much faster even though, searching  ("Hyun-Sung, Jang", )
     Re: Using "LIMIT" is much faster even though, searching  (Andrew McMillan, )
     Re: Using "LIMIT" is much faster even though, searching  (Josh Berkus, )

On Wed, 2004-12-01 at 15:03 +0900, Hyun-Sung, Jang wrote:
>
> < lots of information about seq scan vs index scan >
>

Hi,

Just because it has an ID that is the largest in the set, does not mean
it will be at the last position in the on-disk tables.  And similarly,
the lowest numbered ID does not mean it will be at the beginning in the
on-disk structures.

So when you 'LIMIT 1' the sequential scan stops as soon as it has found
the first row that matches, but in the no LIMIT case with a sequential
scan it will continue the scan to the end of the on-disk data.

Given that this column is unique, PostgreSQL could optimise this case
and imply LIMIT 1 for all sequential scans on such criteria, but in the
real world the optimisation is usually going to come from an index - at
least it will for larger tables - since that's a component of how
PostgreSQL is enforcing the unique constraint.

Regards,
                    Andrew McMillan.


-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/            PHYS: Level 2, 150-154 Willis St
DDI: +64(4)803-2201      MOB: +64(272)DEBIAN      OFFICE: +64(4)499-2267
                Chicken Little only has to be right once.
-------------------------------------------------------------------------


Вложения

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

От: "George Woodring"
Дата:
Сообщение: Re: Query Performance and IOWait
От: Shridhar Daithankar
Дата:
Сообщение: Re: pg_restore taking 4 hours!