Anton wrote:
While without DESC query goes faster... But not so fast!
=# explain analyze SELECT DISTINCT ON (login_id) login_id,
collect_time AS dt FROM n_traffic ORDER BY login_id collect_time;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=0.00..29843.08 rows=532 width=12) (actual
time=0.045..5146.768 rows=798 loops=1)
-> Index Scan using n_traffic_login_id_collect_time on n_traffic
(cost=0.00..27863.94 rows=791656 width=12) (actual
time=0.037..3682.853 rows=791656 loops=1)
Total runtime: 5158.735 ms
(3 rows)
Why? 768 rows is about 1000 times smaller than entire n_traffic. And
why Index Scan used without DESC but with DESC is not?
For the DESC version to use the index try "login_id DESC collect_time
DESC" - so both are reversed.
I'm also not sure what this query is meant to do precisely. ORDER BY is
usually the last stage in a query, so it might be applied *after* the
DISTINCT ON.
If you want the most recent collect_time for each login I'd use
something like:
SELECT login_id, MAX(collect_time) AS most_recent
FROM n_traffic
GROUP BY login_id
ORDER BY login_id DESC, collect_time DESC
--
Richard Huxton
Archonet Ltd
