Skip to main content
Question

approximate_count_distinct limitations

  • August 27, 2025
  • 2 replies
  • 5 views
dimitri_p
Forum|alt.badge.img+2

I would usually expect and observe approximate_count_distinct() to be much more performant than count(distinct). But here's one case where it is actually significantly slower. I am looking for an explanation why that is the case and also some kind of a general applicability scope for approximate_count_distinct(), since it looks like its benefits do not always work

it feels like when combined with a GROUP BY each group adds a little bit of an overhead for approximate_count_distinct, eventually reaching the point of diminishing returns. Just a guess.

    2 replies

    Bryan_H
    Forum|alt.badge.img+2
    • Bryan_H
    • Participating Frequently
    • August 27, 2025

    How large is the set of each group produced by GROUP BY? We Implemented approximate functions as UDX, so there is some overhead to instantiate the function and call for each group. As such, it will probably not outperform exact COUNT DISTINCT unless the sets are very large and lacking order on the counted columns, since the execution engine should be able to count ordered, encoded data very quickly compared to passing sets to UDX.

    dimitri_p
    Forum|alt.badge.img+2
    • dimitri_p
    • Author
    • Participating Frequently
    • August 28, 2025

    @Bryan_H said:
    How large is the set of each group produced by GROUP BY? We Implemented approximate functions as UDX, so there is some overhead to instantiate the function and call for each group. As such, it will probably not outperform exact COUNT DISTINCT unless the sets are very large and lacking order on the counted columns, since the execution engine should be able to count ordered, encoded data very quickly compared to passing sets to UDX.

    That's what I thought! Each group is about 5k rows and there are thousands of groups. I noticed that if I modify the GROUP BY statement to reduce the number of groups, the approximate_count_distinct catches up with count distinct and eventually starts to outperform it.

    Thanks, Bryan!

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement