approx_count_distinct

Applies to VAST Cluster 5.5.

Functions › Aggregate and analytic functions › Counting. Estimates the number of distinct values in a column, NULLs not counted. Use it instead of count(DISTINCT x), which the VAST Query Engine doesn't run.

Syntax

approx_count_distinct(x)

Returns: BIGINT.

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_aggregate_approx_count_distinct. Set QE_ENDPOINT, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and VASTDB_BUCKET (SQL reference), and run the blocks in order in one Python session. Below each block is the output you should see.

This block connects, defines the run() and plan() helpers, and creates a table of six page visits. Visits 1 and 2 are by the same user, spelt with different capitals, and visit 6 has no values:

import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_fn_aggregate_approx_count_distinct"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

def plan(sql):
    """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert."""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        steps = cur.fetchall()[0][1]
    print("\n".join(line for line in steps.splitlines() if "Rows" not in line))

TABLE = f"{SCHEMA}.visits"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, user_name VARCHAR, country VARCHAR, day DATE)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'ada', 'FR', DATE '2026-10-01'), "
    "(2, 'Ada', 'FR', DATE '2026-10-01'), "
    "(3, 'grace', 'US', DATE '2026-10-02'), "
    "(4, 'grace', 'US', DATE '2026-10-03'), "
    "(5, 'linus', 'FI', DATE '2026-10-03'), "
    "(6, NULL, NULL, NULL)")

[]
[]
[(6,)]

What it does

approx_count_distinct(x) is an aggregate: it returns an estimate of how many different non-NULL values x takes over the rows of the query. It takes any type: numbers, text, BLOB, BOOLEAN, dates and times, intervals and lists.

The result is an estimate, not an exact count. On a few values it's often exact, but not always: over the ten integers 0 to 9 it returns 11. Over the 100,000 integers 0 to 99,999 it returns 96,408, about 4% low; over the same numbers cast to text, 94,546; and over 100,000 DOUBLE values, 101,846, above the true count. You can't choose the accuracy: there's no second argument.

Values are compared exactly, so text is case-sensitive: 'ada' and 'Ada' are two values. Compare lower(x) to count them as one.

NULLs and edge cases

The same cases, as the cluster returns them. They use the table and helpers from Set up the examples:

run(f"SELECT approx_count_distinct(user_name), approx_count_distinct(lower(user_name)), "
    f"approx_count_distinct('a'), approx_count_distinct(NULL) FROM {TABLE}")
run(f"SELECT approx_count_distinct(user_name) FROM {TABLE} WHERE id = 6")
run(f"SELECT approx_count_distinct(user_name) FROM {TABLE} WHERE id > 100")
run("SELECT approx_count_distinct(x) FROM (VALUES ('NaN'::DOUBLE), ('inf'::DOUBLE), ('-inf'::DOUBLE), "
    "(1.0::DOUBLE), (-0.0::DOUBLE)) v(x)")
run("SELECT approx_count_distinct(x), count(*) FROM range(10) t(x)")
run(f"SELECT approx_count_distinct(id, 0.01) FROM {TABLE}")
run(f"SELECT approx_count_distinct(DISTINCT user_name) FROM {TABLE}")

[(4, 3, 1, 0)]
[(0,)]
[(0,)]
[(5,)]
[(11, 10)]
InternalError: Binder Error: No function matches the given name and argument types 'approx_count_distinct(BIGINT, DECIMAL(3,2))'. You might need to add explicit type casts.
InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.

Examples

The number of different users, countries and days with a visit, in one query:

run(f"SELECT approx_count_distinct(lower(user_name)), approx_count_distinct(country), "
    f"approx_count_distinct(day) FROM {TABLE}")

[(3, 3, 3)]

The number of different users on the days from 2026-10-02. The WHERE condition is evaluated in VAST DataBase, before the estimate:

run(f"SELECT approx_count_distinct(user_name) FROM {TABLE} WHERE day >= DATE '2026-10-02'")

[(2,)]

The estimate on larger inputs, next to the true count:

run("SELECT approx_count_distinct(x), count(*) FROM range(100000) t(x)")
run("SELECT approx_count_distinct(CAST(x AS VARCHAR)) FROM range(100000) t(x)")

[(96408, 100000)]
[(94546,)]

How it runs in VAST

The Query Engine runs on the cluster's CNodes. An approx_count_distinct query has two steps, which EXPLAIN shows:

1. A scan in VAST DataBase (VAST_SCAN), which reads only the column you count and evaluates the WHERE conditions it can push down.
2. One aggregate step (UNGROUPED_AGGREGATE) in the Query Engine, which builds the estimate from the values the scan sends and returns one row.

plan(f"SELECT approx_count_distinct(user_name) FROM {TABLE} WHERE day >= DATE '2026-10-02'")

┌───────────────────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│   approx_count_distinct   │
│        (user_name)        │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│         user_name         │
│                           │
└───────────────────────────┘

Make it faster

• Restrict the rows in WHERE. A comparison of a column with a constant is pushed down, so the rows that don't match never leave storage. A FILTER (WHERE ...) condition is evaluated in the aggregate step, after the scan has sent every row.
• Estimate several columns in one query. The estimates share one scan.
• Count a function of the column only when you need to. approx_count_distinct(lower(user_name)) computes lower() on every row in the Query Engine; if you always count case-insensitively, store the lower-case value in its own column.

Limitations and errors

• No exact distinct count. count(DISTINCT x) fails with The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage., and the workarounds that need grouping, SELECT count(*) FROM (SELECT DISTINCT x FROM t) and the same with GROUP BY x, fail with The operator 'HASH_GROUP_BY' type=9 is currently not supported. For an exact count, run the query in Trino or Spark.
• No accuracy argument. A second argument fails with No function matches the given name and argument types; the error lists the one form, approx_count_distinct(ANY) -> BIGINT.
• No DISTINCT or ORDER BY inside. Both fail with The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.
• No GROUP BY. An estimate per group fails with The operator 'HASH_GROUP_BY' type=9 is currently not supported.
• Window forms. approx_count_distinct(x) OVER (ROWS UNBOUNDED PRECEDING) runs, as a running estimate in the order the scan returns the rows. Other OVER (...) forms fail with The operator 'WINDOW' type=6 is currently not supported. See window functions.

run(f"SELECT count(DISTINCT user_name) FROM {TABLE}")
run(f"SELECT count(*) FROM (SELECT DISTINCT user_name FROM {TABLE})")
run(f"SELECT country, approx_count_distinct(user_name) FROM {TABLE} GROUP BY country")

InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.
InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported.
InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported.

Similar functions

run(f"SELECT approx_count_distinct(country), count(country) FROM {TABLE}")
run("SELECT list_unique(['FR', 'FR', 'US', NULL])")

[(3, 5)]
[(2,)]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• Aggregate and analytic functions: all the aggregates, and the ones that aren't available.
• count and count_if.
• Pushdowns and EXPLAIN.