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

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