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

Поиск
Список
Период
Сортировка
От Sean Harding
Тема full table scan on 'select max(value) from table'?
Дата
Msg-id 20010328204222.B2239@dogcow.org
обсуждение исходный текст
Ответы Re: full table scan on 'select max(value) from table'?
Список 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@dogcow.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

В списке pgsql-general по дате отправления:

Предыдущее
От: will trillich
Дата:
Сообщение: Re: explain shows lots-o-preliminary sorting
Следующее
От: will trillich
Дата:
Сообщение: Re: Determine Time in other Time Zone