Hi team, I am seeing inaccurate result in pinot aggregation query 👀. For this table that has 7B records, I try to run a group by uuid to see how many records for each uuid, and for most of the uuid they should have around 100 records. But when I run something like

select uuid, count(*) from table group by 1

I get very inaccurate aggregation result, for example, uuid

a

will show it only has 3 records in count() here, but if I only query specifically for this uuid, like `select uuid, count() from table where uuid='a' group by 1` , it will show the correct result which is 100. Can someone help me understand what is going on here?🙏

hi which version are you using?

0.9.2

can you just check what happens when you write

group by uuid

instead? Very likely the same thing, but doesn't hurt to check

yes

group by uuid

return the same result

if you upgrade to 0.10.0 you can get an explain plan

because uuid cardinality is so high, the group by is truncated

Is there any writeup help me understand the truncate behavior here? Is this a feature? If so why it seems to introduce such high discrepancy?

it's not a solution, but it allows you to give us an explain plan

in the middle of upgrade now, will try add order and query again after it’s done

hey @User I guess it's related to this https://docs.pinot.apache.org/users/user-guide-query/grouping-algorithm#group-by-behavior. Group by is by default an approximation, in your case, it will lead to problems in your case

Hi Haitao thanks for the reply! Yeah I’ve also landed in this page during investigation but haven’t got time to try out the configurations suggested in this wiki yet. I plan to play around with the related confs here to improve the accuracy with reasonable cost and will report back how it goes. Let me know if you happened to have experience similar problem before or if you have any other suggestions 🙏.