distinct vs group by which is better

Question

for the simplest case we all refer to:

select id from mytbl 
group by id

and

select distinct id from mytbl

as we know, they generate same query plan which had been repeatedly mentioned in some items like Which is better: Distinct or Group By

In hive, however, the former only has one reduce task while the latter has many.

From experiments, I founded that the GROUP BY is 10+ times faster than DISTINCT.

They are different.

So what I learned is:

GROUP-BY is anyway not worse than DISTINCT, and it is better sometimes.

I would like to know:

1. If this conclusion is true.

2. If true, I shall consider DISTINCT as a approach for logical convenience, but why DISTINCT doesn't take GROUP-BY's better implementation?

3. If false, I would be very eager to know its decent usage under big-data situation.

Thank you very much!!:)

Answer 1

Your experience is interesting. I have not seen the single reducer effect for distinct versus group by. Perhaps there is some subtle difference in the optimizer between the two constructs.

A "famous" example in Hive is:

select count(distinct id)
from mytbl;

versus

select count(*)
from (select distinct id
      from mytbl
     ) t;

The former only uses one reducer and the latter operates in parallel. I have seen this both in my experience, and it is documented and discussed (for example, on slides 26 and 27 in this presentation). So, distinct can definitely take advantage of parallelism.

I imagine that as Hive matures, such problems will be fixed. However, it is ironic that Postgres has a similar performance issue with COUNT(DISTINCT), although I think the underlying reason is a little bit different.