Re: Generic Q about max(id) vs ORDER BY ID DESC LIMIT 1

On Sun, Nov 27, 2005 at 11:38:57PM +1100, Brendan Jurd wrote:
> > Already done in 8.1.  Here's an excerpt from the Release Notes:
> >
> > Automatically use indexes for MIN() and MAX() (Tom)
> >
> >     In previous releases, the only way to use an index for MIN()
> >     or MAX() was to rewrite the query as SELECT col FROM tab ORDER
> >     BY col LIMIT 1.  Index usage now happens automatically.
> >
>
> Which query form will generally be faster in 8.1 (or will they be
> exactly the same)?

They'll effectively be the same:

stats=# explain select id from stats_participant where id is not null order by id limit 1;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..3.40 rows=1 width=4)
   ->  Index Scan using stats_participant_pkey on stats_participant  (cost=0.00..1486391.76 rows=436912 width=4)
         Filter: (id IS NOT NULL)
(3 rows)

stats=# explain select min(id) from stats_participant;
                                                       QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------
 Result  (cost=3.40..3.41 rows=1 width=0)
   InitPlan
     ->  Limit  (cost=0.00..3.40 rows=1 width=4)
           ->  Index Scan using stats_participant_pkey on stats_participant  (cost=0.00..1486391.76 rows=436912
width=4)
                 Filter: (id IS NOT NULL)
(5 rows)

stats=#

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461