Re: SELECT DISTINCT performance issue

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: SELECT DISTINCT performance issue
Дата
Msg-id 1738.1117947997@sss.pgh.pa.us
обсуждение исходный текст
Ответ на SELECT DISTINCT performance issue  (K C Lau <kclau60@netvigator.com>)
Ответы Re: SELECT DISTINCT performance issue  (K C Lau <kclau60@netvigator.com>)
Список pgsql-general
K C Lau <kclau60@netvigator.com> writes:
> esdt=> explain analyze select DISTINCT ON (PlayerID) PlayerID,AtDate from
> Player
>   where PlayerID='22220' order by PlayerID desc, AtDate desc;
>   Unique  (cost=0.00..2507.66 rows=1 width=23) (actual time=0.000..187.000
> rows=1 loops=1)
>     ->  Index Scan Backward using pk_player on player  (cost=0.00..2505.55
> rows=8
> 43 width=23) (actual time=0.000..187.000 rows=1227 loops=1)
>           Index Cond: ((playerid)::text = '22220'::text)
>   Total runtime: 187.000 ms

> It appears that all the 1227 data records for that player were searched,
> even when doing a backward index scan. I would presume that, after locating
> the index for the highest AtDate, only the first data record needs to be
> retrieved.

If you'd said LIMIT 1, it indeed would have stopped sooner.  Since you
did not, it had to scan for more outputs.

            regards, tom lane

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

Предыдущее
От: K C Lau
Дата:
Сообщение: SELECT DISTINCT performance issue
Следующее
От: "Craig Bryden"
Дата:
Сообщение: Getting the Type Definition in the information schema