Re: select max() much slower than select min()

От: Kevin Grittner
Тема: Re: select max() much slower than select min()
Дата: ,
Msg-id: 4A3A925F0200002500027DE0@gw.wicourts.gov
(см: обсуждение, исходный текст)
Ответ на: select max() much slower than select min()  (Brian Cox)
Список: pgsql-performance

Скрыть дерево обсуждения

select max() much slower than select min()  (Brian Cox, )
 Re: select max() much slower than select min()  ("Kevin Grittner", )
 Re: select max() much slower than select min()  (Brian Cox, )
  Re: select max() much slower than select min()  ("Kevin Grittner", )
  Re: select max() much slower than select min()  (Tom Lane, )
   Re: select max() much slower than select min()  (Greg Stark, )
 Re: select max() much slower than select min()  ("Dave Dutcher", )
 Re: select max() much slower than select min()  (Brian Cox, )
  Re: select max() much slower than select min()  (David Rees, )
 Re: select max() much slower than select min()  (Brian Cox, )
  Re: select max() much slower than select min()  (David Rees, )

Brian Cox <> wrote:

> cemdb=# explain select min(ts_id) from
> ts_stats_transet_user_interval a
> where 0=0 and a.ts_interval_start_time >= '2009-6-16 01:00' and
> a.ts_interval_start_time < '2009-6-16 02:00';

> seems like max() shouldn't take any longer than min() and certainly
> not 10 times as long. Any ideas on how to determine the max more
> quickly?

Is there any correlation between ts_id and ts_interval_start_time?
Perhaps if you tried min and max with different time ranges it would
find a row on a backward scan faster.  It'll take ten times as long if
it has to scan through ten times as many rows to find a match.

I don't suppose you have an index on ts_interval_start_time?
If not, what happens if you run these queries after adding one?

-Kevin


В списке pgsql-performance по дате сообщения:

От: "Kevin Grittner"
Дата:
Сообщение: Re: select max() much slower than select min()
От: Kenneth Marshall
Дата:
Сообщение: Re: 8.4 COPY performance regression on Solaris