Essql COUNT() vs Measure Unique (Canvas Metric)

[_Louw (Mia Louw Håkonsen)]

I am using Kibana Canvas and making a Metric element over how many logins the system has logged. I ran into a big offset in counts.

When using this query in essql:
SELECT reference_ as logins FROM "*canvas"

and in "Display" using Unique on "logins" I get a count of 342.

When using
SELECT COUNT(DISTINCT reference_) as logins FROM "*canvas"

and in "Display" using Value on "logins" I get a count of 1010.

I looked at this question and it seems the unique measure is just an approximation.

But why is the difference so big? And am I sure to get the full count by doing it as example 2 in essql?

[Andrei_Stefan]

Not 100% about this, but it's possible the 342 you get to be over a limited number of documents: Canvas Metric Element: Unique count. Also, the documentation seems to mention this default value: https://www.elastic.co/guide/en/kibana/current/canvas-function-reference.html#essql_fn.

From ES-SQL point of view, the first SELECT just retrieves some documents. The second one, on other hand, is running a cardinality aggregation on reference_ field and does actually count the unique, non-null terms in that field. I would trust the second query, and would look for clues (like the one I mentioned above) on what kind of count does Canvas calculate on what set of documents.