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

From: Sean Harding <sharding(at)dogcow(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: full table scan on 'select max(value) from table'?
Date: 2001-03-29 04:42:22
Message-ID: 20010328204222.B2239@dogcow.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Thanks.

sean

--
Sean Harding sharding(at)dogcow(dot)org | "I am the captain and I have been told
http://www.dogcow.org/sean/ | that tomorrow we land and my ship has
| been sold." --Dar Williams

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Maurizio Ortolan 2001-03-29 04:50:56 Error in the date field (with NULL value...).Thanks!
Previous Message will trillich 2001-03-29 04:27:36 Re: explain shows lots-o-preliminary sorting