29

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!!:)

Community
  • 1
  • 1
Chiron
  • 974
  • 1
  • 8
  • 20
  • The question you linked is specifically about MS SQL Server behaviour. Different engines have different approaches, different optimizations etc. There is no reason to believe that just because `group by` and `distinct` are equivalent on MS SQL (of some specific version) this also applies to Hive. Have you used EXPLAIN to look at the actual execution plans? – Luaan Aug 07 '15 at 11:06
  • Thank you. I took your approach and used the passed two days looking into the query plans of my SQL. The interesting thing is, for simple case like above, the query plans are the same (all using group-by) but different when I put many DISTINCT+UNION versus GROUPBY+UNION. – Chiron Aug 10 '15 at 01:54
  • Maybe that has a good reason, or maybe it's just an oversight (distinct isn't quite as widely used as it used to be - it used to be the go-to hack for badly designed DBs). You could post this on Hadoop/Hive's issue tracker or something, but you'll still probably just have to roll with it. It's not always obvious why two similar queries end up executed wildly different. – Luaan Aug 10 '15 at 06:50
  • That's the point! You remind me of Third-Normal-Form. Totally agree with you. It is too obvious but I didn't even realize it! I think I have obtained the answer I want from your comments. DISTINCT is just a hack. – Chiron Aug 11 '15 at 02:55

2 Answers2

21

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you very much ! I used the passed two days looking into the query plans of my SQL. The interesting thing is, for simple case like above, the query plans are the same (all using group-by) but different when I put many DISTINCT+UNION versus GROUPBY+UNION. What I did is simply change every 'DISTINCT key' clause into 'GROUP BY key' clause without any other structural modification. The query plans showed that: in DISTINCT, my static column like : < "TagString" as tag > will be included into group-keys, but not when using 'group by key'. – Chiron Aug 10 '15 at 02:07
  • I don't understand why a single more static value would affect the speed of GROUP-BY. – Chiron Aug 10 '15 at 02:07
  • Thank you very much again! But I still feel hard to see the answers to my 3 confusions clearly from your answer. If you'd like to throw a little bit more light on my mind, I'd be very glad to settle this problem using your answer. – Chiron Aug 10 '15 at 02:12
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.

AlexRut
  • 39
  • 3