HLL_COUNT_ESTIMATE

Reference material for HLL_COUNT_ESTIMATE

Extracts a cardinality estimate of a single HLL++ sketch that was previously built using the aggregate function HLL_COUNT_BUILD.

Syntax

HLL_COUNT_ESTIMATE(<expression>)

Parameters

Parameter	Description	Supported input types
<expression>	An HLL++ sketch produced by the HLL_COUNT_BUILD function.	BYTEA

Return Type

BIGINT

Examples

The following example builds an HLL++ sketch from 1,000 unique integers using HLL_COUNT_BUILD and estimates the distinct count:

SELECT HLL_COUNT_ESTIMATE(HLL_COUNT_BUILD(number)) AS hll_estimate
FROM GENERATE_SERIES(1, 1000) AS t(number);

hll_estimate long
983

Rows: 1Execution time: 6.16ms

HLL_COUNT_ESTIMATE can also be applied to each sketch independently before merging. The following example builds two sketches from overlapping ranges and estimates the cardinality of each:

SELECT HLL_COUNT_ESTIMATE(sketch) AS hll_estimate
FROM (
    SELECT HLL_COUNT_BUILD(n) AS sketch FROM GENERATE_SERIES(1, 500) t(n)
    UNION ALL
    SELECT HLL_COUNT_BUILD(n) AS sketch FROM GENERATE_SERIES(251, 750) t(n)
) sketches;

hll_estimate long
487
497

Rows: 2Execution time: 6.18ms

To estimate the combined count across all sketches, use HLL_COUNT_MERGE to merge them first. The two ranges 1–500 and 251–750 share 250 values, giving 750 unique values in total:

SELECT HLL_COUNT_ESTIMATE(HLL_COUNT_MERGE(sketch)) AS merged_estimate
FROM (
    SELECT HLL_COUNT_BUILD(n) AS sketch FROM GENERATE_SERIES(1, 500) t(n)
    UNION ALL
    SELECT HLL_COUNT_BUILD(n) AS sketch FROM GENERATE_SERIES(251, 750) t(n)
) sketches;

merged_estimate long
730

Rows: 1Execution time: 6.21ms