Use the cardinality estimation extension to estimate business metrics-ApsaraDB RDS(RDS)-阿里云帮助中心

The hll extension provides the HyperLogLog data type. This data type lets you quickly estimate business metrics such as page views (PV) and unique visitors (UV).

Join the RDS PostgreSQL Plugin Exchange DingTalk group (ID: 103525002795) to ask questions, share information, and provide feedback.

Prerequisites

The major version of your RDS instance is PostgreSQL 11 or later.
If the major version of your instance meets the requirements but the feature is not supported, upgrade the minor engine version. For more information, see Upgrade the minor engine version.

Background information

The hll extension provides support for HyperLogLog (hll), which is a variable-length, set-like data type. It returns approximate distinct values with a specified degree of precision. For example, a 1280 byte hll data type can estimate tens of billions of distinct values with high precision. The hll extension is suitable for industries, such as internet advertising analysis, that require this type of estimation. It can be used to quickly estimate business metrics such as PV and UV.

For more information about how to use the hll extension, see postgresql-hll.

For a detailed description of the algorithm, see the paper HyperLogLog: the analysis of a near-optimal cardinality estimation algorithm.

Create the hll extension

Connect to your instance and run the following command to create the hll extension:

CREATE EXTENSION hll;

Basic operations

Create a table that contains an hll field. Run the following command:
```
create table agg (id int primary key,userids hll);
```
Transform the int data type to hll_hashval. Run the following command:
```
select 1::hll_hashval;
```

Basic operators

The hll type supports the following operators:

Example:

select hll_add_agg(1::hll_hashval) = hll_add_agg(2::hll_hashval);
select hll_add_agg(1::hll_hashval) || hll_add_agg(2::hll_hashval);
select #hll_add_agg(1::hll_hashval);

The hll_hashval type supports the following operators:

Example:

select 1::hll_hashval = 2::hll_hashval;
select 1::hll_hashval <> 2::hll_hashval;

Basic functions

The extension provides hash functions such as hll_hash_boolean, hll_hash_smallint, and hll_hash_bigint. Example:
```
select hll_hash_boolean(true);
select hll_hash_integer(1);
```
The extension provides the hll_add_agg function to transform the int data type to the hll format. Example:
```
select hll_add_agg(1::hll_hashval);
```
The extension provides the hll_union function to return the union of hll values. Example:
```
select hll_union(hll_add_agg(1::hll_hashval),hll_add_agg(2::hll_hashval));
```
The extension provides the hll_set_defaults function to set the precision. Example:
```
select hll_set_defaults(15,5,-1,1);
```
The extension provides the hll_print function to print debug information. Example:
```
select hll_print(hll_add_agg(1::hll_hashval));
```

Example

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);
postgres=# select #userids from access_date where acc_date=current_date;
     ?column?
------------------
 9725.85273370708
(1 row)
postgres=# select #userids from access_date where acc_date=current_date-1;
     ?column?
------------------
 14968.6596883279
(1 row)
postgres=# select #userids from access_date where acc_date=current_date-2;
     ?column?
------------------
 29361.5209149911
(1 row)
This feature is supported in RDS PostgreSQL 11 and later versions. If you are prompted that this feature is not supported, upgrade the minor engine version. For more information, see Upgrade a minor engine version.