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.

Answer 2

I take same task and analyze by postgres commands. For distinct query: explain (analyze) select distinct product_id, size from logistic.product_stock where status = 'STOCK' I get:

HashAggregate  (cost=2166.24..2232.35 rows=6611 width=23) (actual time=46.417..47.104 rows=3770 loops=1)
   Group Key: product_id, size
   Batches: 1  Memory Usage: 721kB
   ->  Seq Scan on product_stock  (cost=0.00..2050.57 rows=23133 width=23) (actual time=0.144..39.954 rows=22357 loops=1)
         Filter: ((status)::text = 'STOCK'::text)
         Rows Removed by Filter: 44930
 Planning Time: 0.126 ms
 Execution Time: 47.517 ms

For group by query explain (analyze) select product_id, size from logistic.product_stock where status = 'STOCK' group by product_id, size I get next:

HashAggregate  (cost=2166.24..2232.35 rows=6611 width=23) (actual time=40.519..41.273 rows=3775 loops=1)
   Group Key: product_id, size
   Batches: 1  Memory Usage: 721kB
   ->  Seq Scan on product_stock  (cost=0.00..2050.57 rows=23133 width=23) (actual time=0.053..34.159 rows=22362 loops=1)
         Filter: ((status)::text = 'STOCK'::text)
         Rows Removed by Filter: 44930
 Planning Time: 0.802 ms
 Execution Time: 41.768 ms

As we can see: steps are same in both situations. Seq scan -> Group key.

ANSWER: NO MATTER WHAT QUERY YOU CHOOSE

PS. time depends on cache.