DuckDB's Engine

DuckDB's Engine

DuckDB's Columnar, Vectorized Engine

Above the storage and vectors of Column Stores Under the Hood sits a push-based executor: pipelines in which a source (a scan) pushes vectors through operators into a sink (a hash table, a sort, the result). Threads claim morsels (chunks of row groups) in turn, so parallelism adapts at run time. JSON profiling shows what a query ran:

profile_plan.py: the operator tree DuckDB executedPython

import duckdb, json
con = duckdb.connect("booknest.duckdb", read_only=True)
con.execute("PRAGMA enable_profiling = 'json'; PRAGMA profiling_output = 'profile.json'")
con.sql("""SELECT genre, sum(gross_amount), count(*) FROM sales
           WHERE status <> 'cancelled' GROUP BY genre""").fetchall()
p = json.load(open("profile.json"))
print(f"read {p['total_bytes_read']:,} bytes, latency {p['latency'] * 1000:.1f} ms")
def walk(node, depth=0):
    info = node["extra_info"]
    detail = info.get("Filters") or info.get("Aggregates") or info.get("Projections", "")
    name = "  " * depth + node["operator_name"]
    print(f"{name:<22}{node['operator_cardinality']:>8,}  {detail}")
    for child in node["children"]:
        walk(child, depth + 1)
walk(p["children"][0])

Output

read 262,144 bytes, latency 14.6 ms
PROJECTION                   6  ['__internal_decompress_string(#0)', '#1', '#2']
  HASH_GROUP_BY              6  ['sum_no_overflow(#1)', 'count_star()']
    PROJECTION         129,885  ['genre', 'gross_amount']
      PROJECTION       129,885  ['__internal_compress_string_uhugeint(#0)', '#1']
        SEQ_SCAN       129,885  status!='cancelled'

The filter was pushed into the scan, which read 3 of 15 columns and returned the 129,885 matching rows; the query read one 256 kB block of a 19 MB file, in 15 to 17 ms from a fresh process. The optimizer packed the short genre strings into 128-bit integers so the hash table compares numbers, and chose sum_no_overflow, having proved from statistics that the total cannot overflow. Hash tables and sorts that outgrow memory_limit spill to disk.

MENU