Re: Why DISTINCT ... DESC is slow?

Michael Glaesemann wrote:
>
> On Dec 12, 2006, at 16:43 , Richard Huxton wrote:
>
>> 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.
>
> My understanding is that DISTINCT ON requires the ORDER BY, so I'd be
> surprised if ORDER BY is applied after. (Though I'm happy to hear more
> about this.)

(goes away and tests) Ah, you're quite right. I was worried about
ill-defined results, but it prevents you from doing that.

--
   Richard Huxton
   Archonet Ltd