0

Why DISTINCT ON is sorting the rows? E.g. the query

SELECT DISTINCT ON (a) a
FROM (
  VALUES (2), (3), (1)
) x(a);

will have the result

1
2
3

while without DISTINCT ON

SELECT a
FROM (
  VALUES (2), (3), (1)
) x(a);

I'll get the correct order

2
3
1

How can I preserve the natural order of defined values? By "natural order" I mean "the order in which the values were defined". I understand that DBMS may change the order if I don't specify it explicitly with an ORDER BY, but I was not expecting that DISTINCT ON my interfere in this way.

2

1 Answer 1

2

Why DISTINCT ON is sorting the rows?

Distinct removes "duplicate" rows from the result set. That's a whole lot easier to do if the items being compared arrive neatly sorted into order.

Check the Explain Plan for this query - it will probably contain extra "Sort" and "Unique" steps. Guess what those are doing.

"select distinct" is always a Red Flag for me. It is almost always used poorly, a band-aid to get around a bigger problem and introduces problems of its own. Remember, the performance of ...

select distinct a, b, c 
. . . 

... is roughly comparable to that of ...

select a, b, c 
. . . 
group by a, b, c 
order by a, b, c 

Ask yourself why [you think] you need to use "distinct".

1
  • 1
    I think it is only fair to point out that sorting is not the only way to implement duplicate removal. Many database engines offer a choice between order-based and hash-based facilities. Hash may be optimal if an explicit sort would be needed, or if there are relatively few output groups. Perhaps address that from a Postgres perspective (if it applies) in your answer?
    – Paul White
    May 14, 2021 at 10:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.