max() not using index

Hi Peter,

-> Seq Scan on dam (cost=0.00..5442.92 rows=132692 width=4)

select dam_id from dam order by dam_id desc limit 1;

Thanks alot. That did the trick.

Kind regards
... Ralph ...

"Ralph Graulich" wrote:

EXPLAIN SELECT MAX(dam_id) FROM dam;
NOTICE: QUERY PLAN:

Aggregate (cost=5774.65..5774.65 rows=1 width=4)
-> Seq Scan on dam (cost=0.00..5442.92 rows=132692 width=4)

select dam_id from dam order by dam_id desc limit 1;

--
Peter Gibbs
EmKel Systems

( combining this issue with the thread: [GENERAL] MySQL vs. PostgreSQL )

Taken from mysql manual:

Indexes are used to:

[....]
Find the MAX() or MIN() value for a specific indexed column. This is
optimised by a preprocessor that checks if you are using WHERE key_part_# =
constant on all key parts < N. In this case MySQL will do a single key
lookup and replace the MIN() expression with a constant. If all expressions
are replaced with constants, the query will return at once:
SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10

Sort or group a table if the sorting or grouping is done on a leftmost
prefix of a usable key (for example, ORDER BY key_part_1,key_part_2 ). The
key is read in reverse order if all key parts are followed by DESC. See
section 5.2.7 How MySQL Optimises ORDER BY.

Mit freundlichem Gru�

Henrik Steffen
Gesch�ftsf�hrer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com Tel. +49 4141 991230
mail: steffen@topconcepts.com Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline: +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Ralph Graulich" <maillist@shauny.de>
To: "Peter Gibbs" <peter@emkel.co.za>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, July 17, 2002 2:40 PM
Subject: Re: [GENERAL] max() not using index