23.1. Estimating the Number of Distinct Values (approx_count_distinct) #
The approx_count_distinct stored procedure uses the HyperLogLog algorithm to count distinct values. This is faster than the COUNT(DISTINCT) aggregate, but the result has some error rate.
Required privileges: Postgres Pro AXE administrator only. For a full list of stored procedures and privileges, refer to Section 12.1.
Execute the following command on the Postgres Pro AXE server:
SELECT approx_count_distinct(expression) FROMsource;
Where:
expression: The expression whose distinct values must be estimated.source: The source of the expression, e.g., an analytical table, aread_parquetstored procedure, or a subquery.
Postgres Pro AXE returns an estimate of the number of distinct values as a BIGINT value.
Example 23.1. Executing the approx_count_distinct Stored Procedure
Estimating the number of distinct values in the customer_id column of the orders table:
SELECT approx_count_distinct(customer_id) AS approx_distinct FROM orders;
approx_distinct
-----------------
1234
As compared to the COUNT(DISTINCT) aggregate:
SELECT COUNT(DISTINCT customer_id) AS exact_distinct FROM orders;
exact_distinct
----------------
1237