Обсуждение: max() not using index

Hi,


I have got a table with a non-unique integer value named "dam_id" and I
need to query the max(dam_id) value. Also put an index on that column, but
postgres always does a seq scan, which takes half a minute to complete.

    Column     |              Type               |      Modifiers
---------------+---------------------------------+----------------------
 dam_id        | integer                         |
[...]
Indexes: ix_dam_dam_id

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)

Another note: I vacuum full analyzed the table. Didn't help either.

Errrr, yes, I am porting a mySQL application to postgres, cause postgres
offers me the features I need for that app. Brought back the fun of adding
new features to my application :-)

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

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

( 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


> 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 ...
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster