> > 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.
Yes, it helps!
But
> 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
is not so good:
=# SELECT login_id, MAX(collect_time) AS most_recent
-# FROM n_traffic
-# GROUP BY login_id
-# ORDER BY login_id DESC, collect_time DESC;
ERROR: column "n_traffic.collect_time" must appear in the GROUP BY
clause or be used in an aggregate function
If I correct this error (add collect time to GROUP BY) I'll just get
full table, sorted. And I tried to not use aggregate functions because
they make to do full table scan...
So,
=# explain analyze SELECT DISTINCT ON (login_id) login_id,
collect_time AS dt FROM n_traffic ORDER BY login_idDESC, collect_time
DESC;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=0.00..29843.08 rows=532 width=12) (actual
time=60.656..9747.985 rows=796 loops=1)
-> Index Scan Backward using n_traffic_login_id_collect_time on
n_traffic (cost=0.00..27863.94 rows=791656 width=12) (actual
time=60.645..8221.891 rows=789934 loops=1)
Total runtime: 9750.189 ms
(3 rows)
Indexes are used, this is good, but speed still not so good for
2xPIIIx1Ghz + 1Gb RAM + RAID5 on SCSI...
Anyhow, thank you!
--
engineer