Does a DISTINCT sort data?

[hopewell]

Hi all,

When using a DISTINCT clause in a SELECT statement, will the result set always be sorted? When I test it seems so, but I just wanted to make sure ...

Code: Select all

dbadmin=> SELECT * FROM test;
 c1
----
  1
  1
  2
  3
  3
  1
(6 rows)

dbadmin=> SELECT DISTINCT c1 FROM test;
 c1
----
  1
  2
  3
(3 rows)

[NorbertKrupa]

Let's load some data up:

Code: Select all

dbadmin=> CREATE TABLE distinct_sort (a int);
CREATE TABLE
dbadmin=> COPY distinct_sort FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1
>> 2
>> 3
>> 4
>> 3
>> 5
>> 2
>> \.
dbadmin=> select * from distinct_sort;
 a
---
 5
 1
 2
 3
 3
 2
 4
(7 rows)

And try using DISTINCT:

Code: Select all

dbadmin=> select distinct a from distinct_sort;
 a
---
 5
 4
 1
 2
 3
(5 rows)

My guess is you're using a single node and the data was loaded in a sorted fashion. We can see that the execution engine doesn't perform a sort:

Code: Select all

 Access Path:
 +-GROUPBY PIPELINED [Cost: 6, Rows: 7 (NO STATISTICS)] (PATH ID: 1)
 |  Group By: distinct_sort.a
 |  Execute on: All Nodes
 | +---> STORAGE ACCESS for distinct_sort [Cost: 5, Rows: 7 (NO STATISTICS)] (PATH ID: 2)
 | |      Projection: public.distinct_sort_b0
 | |      Materialize: distinct_sort.a
 | |      Execute on: All Nodes