How to use the hll extension - PolarDB - Alibaba Cloud Documentation Center

The hll extension provides the HyperLogLog (HLL) data type, which enables fast estimation of metrics such as page views (PVs) and unique visitors (UVs).

Prerequisites

The hll extension is supported on the following PolarDB for PostgreSQL versions:

PostgreSQL 14 (minor engine version 14.5.1.0 or later)
PostgreSQL 11 (minor engine version 1.1.28 or later)

Note

You can run the following statements to check the minor engine version of PolarDB for PostgreSQL:

PostgreSQL 14
```
SELECT version();
```
PostgreSQL 11
```
SHOW polar_version;
```

Background information

The hll extension provides a variable-length, set-like data type called HyperLogLog (HLL). It is commonly used to return approximate distinct values at a specified precision. For example, 1,280 bytes of HLL data can estimate nearly ten billion distinct values with high accuracy. HLL is suitable for Internet advertising analytics and other industries that require approximate estimation, enabling fast estimation of metrics such as PVs and UVs.

Install the hll extension

CREATE EXTENSION hll;

Basic operations

Create a table with an hll column:

CREATE TABLE agg (id INT PRIMARY KEY, userids hll);

Convert the INT data type to the hll_hashval type:

SELECT 1::hll_hashval;

Result:

 hll_hashval
-------------
 1
(1 row)

Basic operators

The hll type supports the following operators:

Examples:

SELECT hll_add_agg(1::hll_hashval) = hll_add_agg(2::hll_hashval);

Result:

 ?column?
----------
 f
(1 row)

SELECT hll_add_agg(1::hll_hashval) || hll_add_agg(2::hll_hashval);

Result:

                 ?column?
------------------------------------------
 \x128b7f00000000000000010000000000000002
(1 row)

SELECT #hll_add_agg(1::hll_hashval);

Result:

 ?column?
----------
        1
(1 row)

The hll_hashval type supports the following operators:

Examples:

SELECT 1::hll_hashval = 2::hll_hashval;

Result:

 ?column?
----------
 f
(1 row)

SELECT 1::hll_hashval <> 2::hll_hashval;

Result:

 ?column?
----------
 t
(1 row)

Basic functions

Hash functions such as hll_hash_boolean, hll_hash_smallint, and hll_hash_bigint are supported. Examples:

SELECT hll_hash_boolean(true);

Result:

  hll_hash_boolean
---------------------
 8849112093580131862
(1 row)

SELECT hll_hash_integer(1);

Result:

   hll_hash_integer
----------------------
 -8604791237420463362
(1 row)

The hll_add_agg function converts INT values to the hll format. Example:

SELECT hll_add_agg(1::hll_hashval);

Result:

       hll_add_agg
--------------------------
 \x128b7f0000000000000001
(1 row)

The hll_union function computes the union of hll values. Example:

SELECT hll_union(hll_add_agg(1::hll_hashval), hll_add_agg(2::hll_hashval));

Result:

                hll_union
------------------------------------------
 \x128b7f00000000000000010000000000000002
(1 row)

The hll_set_defaults function sets accuracy parameters. Example:

SELECT hll_set_defaults(15,5,-1,1);

Result:

 hll_set_defaults
------------------
 (11,5,-1,1)
(1 row)

The hll_print function prints debug information. Example:

SELECT hll_print(hll_add_agg(1::hll_hashval));

Result:

                                  hll_print
-----------------------------------------------------------------------------
 EXPLICIT, 1 elements, nregs=32768, nbits=5, expthresh=-1(2560), sparseon=1:+
 0:                    1
(1 row)

Uninstall the hll extension

DROP EXTENSION hll;

Examples

Prepare sample data.

CREATE TABLE access_date (acc_date DATE UNIQUE, userids hll);
INSERT INTO access_date
     SELECT current_date, hll_add_agg(hll_hash_integer(user_id))
     FROM generate_series(1,10000) t(user_id);
INSERT INTO access_date
     SELECT current_date-1, hll_add_agg(hll_hash_integer(user_id))
     FROM generate_series(5000,20000) t(user_id);
INSERT INTO access_date
     SELECT current_date-2, hll_add_agg(hll_hash_integer(user_id))
     FROM generate_series(9000,40000) t(user_id);

Run the following queries.

SELECT #userids FROM access_date WHERE acc_date=current_date;

Result:

     ?column?
------------------
 9725.852733707077
(1 row)

SELECT #userids FROM access_date WHERE acc_date=current_date-1;

Result:

     ?column?
------------------
 14968.65968832792
(1 row)

SELECT #userids FROM access_date WHERE acc_date=current_date-2;

Result:

     ?column?
------------------
 29361.520914991113
(1 row)