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

From: Andrew McMillan
Subject: Re: Using "LIMIT" is much faster even though, searching
Date: ,
Msg-id: 1101889411.16058.241.camel@lamb.mcmillan.net.nz
(view: Whole thread, Raw)
In response to: Re: Using "LIMIT" is much faster even though, searching  ("Hyun-Sung, Jang")
List: pgsql-performance

Tree view

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


Attachment

pgsql-performance by date:

From: "George Woodring"
Date:
Subject: Re: Query Performance and IOWait
From: Shridhar Daithankar
Date:
Subject: Re: pg_restore taking 4 hours!