Re: full table scan on 'select max(value) from table'?

--- Sean Harding <sharding(at)dogcow(dot)org> wrote:
> I have a table, 'mesg_headers', which holds headers from email
>
> messages. Each message has a unique integer ID within the
> system,
> 'mesgid'. mesgid is the primary key for mesg_headers, so it
> has index
> mesg_headers_pkey. This index is used if I do 'select * from
> mesg_headers
> where mesgnum = whatever', but if I do 'select max(mesgnum)
> from
> mesg_headers', I get a full table scan, which takes a long
> time (there are
> currently over 370,000 rows). Explains:
>
> email=# explain select * from mesg_headers where mesgnum =
> 100;
> NOTICE: QUERY PLAN:
>
> Index Scan using mesg_headers_pkey on mesg_headers
> (cost=0.00..4.99 rows=1 width=92)
>
> EXPLAIN
> email=# explain select max(mesgnum) from mesg_headers;
> NOTICE: QUERY PLAN:
>
> Aggregate (cost=80319.44..80319.44 rows=1 width=4)
> -> Seq Scan on mesg_headers (cost=0.00..79392.55
> rows=370755 width=4)
>
> EXPLAIN
>
>
> So is there anything I can do about this, or will max(mesgnum)
> never use an
> index? I'm migrating this db from MySQL, where the same query
> returns almost
> instantanously, so some of my code makes the assumption that
> it's a cheap
> operation. I could work around it, but it would definitely be
> nicer to find
> a way to just make it use an index.

That functionality has been placed on the TODO list
(http://www.postgresql.org/docs/todo.html) under INDEXES.

Brent

__________________________________________________
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail.
http://personal.mail.yahoo.com/?.refer=text