Re: SELECT DISTINCT performance issue

K C Lau <kclau60(at)netvigator(dot)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