Hello, Is there any performance difference between the following two queries for pinot

select distinct city from transactions limit 100000

select city from transactions group by city limit 100000

My guess was no, as implementation-wise it’s a similar operation. But just for grins I tried it on a large dataset (1.7b records) and got similar performance. I’d guess that memory usage would also be similar.

Hey distinct and group by use different engines internally, even though semantically they mean the same thing and might end up doing similar amount of work.

Just FYI I wound up getting the same first 10 results from both queries, which is why I thought the underlying implementation was the same, since there’s no ordering for the results. But based on what @Mayank said that’s not the case.

Yeah, the Operators for group-by and Distinct are implemented separately

select city

Though with my data set, so actually

select advertiser

Ok, my comments assumed the second query was aggregation group-by (didn't carefully see the second query).

Thanks for checking, that explains why the results were so similar 🙂

Yep, that is what got me thinking as well.