list_approx_count_distinct

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Aggregate a list. Estimates how many distinct non-NULL elements a list has, and returns one BIGINT per row. The result is an estimate: on short lists it's usually exact, on long lists it can be off in either direction. For an exact count, use list_unique.

Syntax

list_approx_count_distinct(l)

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_lists_list_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 five rows of lists: numbers, measurements and words. Row 2 has NULL elements and duplicates, row 3 has empty lists and row 4 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_lists_list_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}.samples"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, nums BIGINT[], vals DOUBLE[], words VARCHAR[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, [1, 2, 3], [0.5, 2.5, -1.0], ['a', 'b', 'c']), "
    "(2, [3, NULL, 3], [2.0, NULL], ['b', NULL, 'B']), "
    "(3, [], [], []), "
    "(4, NULL, NULL, NULL), "
    "(5, [-5, 0, -5, 10], [1e308, 1e308, -1e-308], ['\u00e9', 'Zo\u00eb', '\U0001F1EB\U0001F1F7', ''])")

[]
[]
[(5,)]

What it does

list_approx_count_distinct(l) estimates the number of different non-NULL values among the elements of l:

• Duplicates count once: list_approx_count_distinct([1, 1, 2]) is 2.
• NULL elements are skipped: list_approx_count_distinct([NULL, 1]) is 1.
• Text is compared exactly, so 'a' and 'A' are two values.
• An empty list returns 0, not NULL.

The count is an estimate and can be off in either direction. list_approx_count_distinct(range(1000)), a list of 1,000 distinct numbers, returns 1249 (too high); list_approx_count_distinct(range(100000)) returns 96408 (too low). Use it where a rough count is enough, and list_unique where you need the exact number.

NULLs and edge cases

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

run("SELECT list_approx_count_distinct(NULL::BIGINT[]), list_approx_count_distinct([]::BIGINT[]), "
    "list_approx_count_distinct([NULL, 1]), list_approx_count_distinct([1, 1, 2]), list_approx_count_distinct(['a', 'A'])")
run("SELECT list_approx_count_distinct(range(1000)), list_approx_count_distinct(list_concat(range(1000), range(1000))), "
    "list_approx_count_distinct(range(100000))")
run(f"SELECT list_approx_count_distinct(123) FROM {TABLE} LIMIT 1")

[(None, 0, 1, 2, 2)]
[(1249, 1249, 96408)]
InternalError: Binder Error: No function matches the given name and argument types 'list_aggr(INTEGER_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.

Examples

Estimate the distinct values in each row's lists. Row 3's empty lists return 0 and row 4's NULL lists return NULL. Row 5's words have four distinct values (one is an empty string), and the estimate is 3:

run(f"SELECT id, list_approx_count_distinct(nums), list_approx_count_distinct(vals), "
    f"list_approx_count_distinct(words) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 3, 3, 3), (2, 1, 1, 2), (3, 0, 0, 0), (4, None, None, None), (5, 3, 2, 3)]

Keep the rows whose list has more than one distinct value:

run(f"SELECT id, nums FROM {TABLE} WHERE list_approx_count_distinct(nums) > 1 ORDER BY id LIMIT 10")

[(1, [1, 2, 3]), (5, [-5, 0, -5, 10])]

How it runs in VAST

The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (VAST_SCAN). The scan reads a list column whole, and list_approx_count_distinct() always runs in the Query Engine:

• In the SELECT list, a projection step computes the estimate for each row after the scan.
• In WHERE, the scan sends the list for every row, and a FILTER step above the scan computes the condition.

The plan of the WHERE example shows the FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE list_approx_count_distinct(nums) > 1")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│     (list_aggr(nums,      │
│ 'approx_count_distinct') >│
│             1)            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            nums           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Add a condition on a plain column. A comparison of a column with a constant, such as id > 1, is evaluated in VAST DataBase, so the estimate is computed only for the rows that pass it. The plan for id > 1 alone has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE id > 1")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

Limitations and errors

• The result is an estimate, even on short lists. Use list_unique for an exact count.
• The argument must be a list. Anything else fails with Binder Error: No function matches the given name and argument types 'list_aggr(...)'; the message names list_aggr because the function is a form of list_aggregate.
• ORDER BY needs a LIMIT. Without one, the query fails with The operator 'ORDER_BY' type=1 is currently not supported for this usage.

Similar functions

run("SELECT list_approx_count_distinct(l), list_unique(l), list_count(l), len(l), list_distinct(l), list_histogram(l) "
    "FROM (SELECT [3, 1, NULL, 3, 10] AS l)")

[(3, 3, 4, 5, [10, 3, 1], [(1, 1), (3, 2), (10, 1)])]

Clean up:

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

[]
[]

See also

• List, struct and map functions: all the list aggregates.
• approx_count_distinct: the same estimate over the rows of a table.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.