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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461