{ "cells": [ { "cell_type": "markdown", "id": "8919e2f5", "metadata": {}, "source": [ "# TPC-H: DuckDB vs Opteryx on the same Parquet\n", "\n", "Runs the 22 standard **TPC-H** queries against two embedded SQL engines. Both engines read **the same Parquet files**, exported by DuckDB, with no rewriting or engine-specific loading:\n", "\n", "| Engine | Reads |\n", "|---|---|\n", "| **DuckDB** | the Parquet files, through `read_parquet` views |\n", "| **Opteryx** | the same Parquet files, as a dataset folder per table |\n", "\n", "The pipeline is: generate TPC-H with DuckDB's built-in `dbgen`, export each table to Parquet with DuckDB's default writer settings, run all 22 queries on both engines, and compare wall-clock time.\n", "\n", "This notebook accompanies [Benchmarking](https://docs.opteryx.app/docs/reference/internals/benchmarking) on docs.opteryx.app. The numbers it produces belong to whatever machine runs it. A Colab runtime is small and shared, so compare the two engines with each other, not with figures published anywhere else.\n", "\n", "**Runtime note:** at the default scale factor of 1, `lineitem` is about 6M rows and the whole run takes a few minutes on a Colab CPU runtime. For a quick smoke test, lower `SCALE_FACTOR` to `0.1`." ] }, { "cell_type": "code", "id": "4606cb45", "metadata": {}, "source": [ "# --- Configuration -----------------------------------------------------\n", "SCALE_FACTOR = 1 # TPC-H scale factor (1 = ~8.66M rows total). DuckDB's\n", " # dbgen also accepts fractions, e.g. 0.1, for a fast smoke test.\n", "ITERATIONS = 3 # warm runs per query per engine; the minimum is reported\n", "import os\n", "WORK_DIR = os.path.abspath(\"/content/tpch_bench\" if os.path.isdir(\"/content\") else \"./tpch_bench\")\n", "\n", "os.makedirs(WORK_DIR, exist_ok=True)\n", "os.chdir(WORK_DIR)\n", "print(\"Working directory:\", os.getcwd())\n", "\n", "cpus = len(os.sched_getaffinity(0)) if hasattr(os, \"sched_getaffinity\") else os.cpu_count()\n", "print(\"CPUs available:\", cpus)" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "id": "9d452b8f", "metadata": {}, "source": [ "## 0. Install engines\n", "\n", "`duckdb` and `opteryx-core` install from PyPI." ] }, { "cell_type": "code", "id": "a062ad22", "metadata": {}, "source": [ "%pip install -q --upgrade duckdb opteryx-core pandas matplotlib\n", "\n", "import duckdb\n", "import opteryx\n", "\n", "print(\"duckdb \", duckdb.__version__)\n", "print(\"opteryx \", opteryx.__version__)" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "id": "90c98328", "metadata": {}, "source": [ "## 1. Generate TPC-H and export it to Parquet\n", "\n", "DuckDB's `tpch` extension generates the dataset into an in-memory connection, and each table is exported with DuckDB's own Parquet writer at its default settings. Those files are the only copy of the data either engine reads.\n", "\n", "Each table gets its own folder, `data/tpch//`, which is the layout Opteryx reads as a dataset. DuckDB reads the same files by path." ] }, { "cell_type": "code", "id": "96e67b29", "metadata": {}, "source": [ "import shutil\n", "\n", "con = duckdb.connect()\n", "con.execute(\"INSTALL tpch; LOAD tpch;\")\n", "con.execute(f\"CALL dbgen(sf={SCALE_FACTOR});\")\n", "\n", "DATA_ROOT = os.path.join(WORK_DIR, \"data\", \"tpch\")\n", "os.makedirs(DATA_ROOT, exist_ok=True)\n", "\n", "TABLES = [r[0] for r in con.execute(\"SHOW TABLES\").fetchall()]\n", "print(f\"{len(TABLES)} tables: {TABLES}\")\n", "for t in TABLES:\n", " table_dir = os.path.join(DATA_ROOT, t)\n", " shutil.rmtree(table_dir, ignore_errors=True) # a dataset folder must hold one format only\n", " os.makedirs(table_dir)\n", " output_path = os.path.join(table_dir, f\"{t}.parquet\")\n", " n = con.execute(f\"SELECT COUNT(*) FROM {t}\").fetchone()[0]\n", " con.execute(f\"COPY {t} TO '{output_path}' (FORMAT PARQUET)\")\n", " print(f\" {t:<10} {n:>12,} rows -> {output_path}\")\n", "con.close()" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "id": "af68f822", "metadata": {}, "source": [ "## 2. The 22 TPC-H queries\n", "\n", "These are the standard de-parameterized TPC-H queries that Opteryx validates in its own performance suite (`tests/performance/tpch/opteryx/queries/`). They are written with a `testdata.tpch.
` placeholder prefix. The only per-engine change is that prefix. Everything else runs unchanged on both engines:\n", "\n", "| | DuckDB | Opteryx |\n", "|---|---|---|\n", "| table prefix | stripped (bare table name) | rewritten to `data.tpch.` |\n", "\n", "The queries are saved to `queries.json` for the benchmark script in the next step." ] }, { "cell_type": "code", "id": "c5c7361e", "metadata": {}, "source": [ "QUERIES = {'Q01': \"select\\n l_returnflag,\\n l_linestatus,\\n sum(l_quantity) as sum_qty,\\n sum(l_extendedprice) as sum_base_price,\\n sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,\\n sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,\\n avg(l_quantity) as avg_qty,\\n avg(l_extendedprice) as avg_price,\\n avg(l_discount) as avg_disc,\\n count(*) as count_order\\nfrom\\n testdata.tpch.lineitem\\nwhere\\n l_shipdate <= '1998-09-16'::DATE\\ngroup by l_returnflag, l_linestatus\\norder by l_returnflag, l_linestatus;\", 'Q02': \"with q2_min_ps_supplycost as\\n(select\\n p_partkey as min_p_partkey,\\n min(ps_supplycost) as min_ps_supplycost\\nfrom\\n testdata.tpch.part,\\n testdata.tpch.partsupp,\\n testdata.tpch.supplier,\\n testdata.tpch.nation,\\n testdata.tpch.region\\nwhere\\n p_partkey = ps_partkey\\n and s_suppkey = ps_suppkey\\n and s_nationkey = n_nationkey\\n and n_regionkey = r_regionkey\\n and r_name = 'EUROPE'\\ngroup by\\n p_partkey)\\n\\nselect\\n s_acctbal,\\n s_name,\\n n_name,\\n p_partkey,\\n p_mfgr,\\n s_address,\\n s_phone,\\n s_comment\\nfrom\\n testdata.tpch.part,\\n testdata.tpch.supplier,\\n testdata.tpch.partsupp,\\n testdata.tpch.nation,\\n testdata.tpch.region,\\n q2_min_ps_supplycost\\nwhere\\n p_partkey = ps_partkey\\n and s_suppkey = ps_suppkey\\n and p_size = 37\\n and p_type like '%COPPER'\\n and s_nationkey = n_nationkey\\n and n_regionkey = r_regionkey\\n and r_name = 'EUROPE'\\n and ps_supplycost = min_ps_supplycost\\n and p_partkey = min_p_partkey\\norder by\\n s_acctbal desc,\\n n_name,\\n s_name,\\n p_partkey\\nlimit 100;\", 'Q03': \"select\\n l_orderkey,\\n sum(l_extendedprice * (1 - l_discount)) as revenue,\\n o_orderdate,\\n o_shippriority\\nfrom\\n testdata.tpch.customer,\\n testdata.tpch.orders,\\n testdata.tpch.lineitem\\nwhere\\n c_mktsegment = 'BUILDING'\\n and c_custkey = o_custkey\\n and l_orderkey = o_orderkey\\n and o_orderdate < '1995-03-22'::DATE\\n and l_shipdate > '1995-03-22'::DATE\\ngroup by\\n l_orderkey,\\n o_orderdate,\\n o_shippriority\\norder by\\n revenue desc,\\n o_orderdate\\nlimit 10;\", 'Q04': \"select\\n o_orderpriority,\\n count(*) as order_count\\nfrom\\n testdata.tpch.orders as o\\nwhere\\n o_orderdate >= '1996-05-01'::DATE\\n and o_orderdate < '1996-08-01'::DATE\\n and exists (\\n select\\n *\\n from\\n testdata.tpch.lineitem\\n where\\n l_orderkey = o.o_orderkey\\n and l_commitdate < l_receiptdate\\n )\\ngroup by\\n o_orderpriority\\norder by\\n o_orderpriority;\", 'Q05': \"select\\n n_name,\\n sum(l_extendedprice * (1 - l_discount)) as revenue\\nfrom\\n testdata.tpch.customer,\\n testdata.tpch.orders,\\n testdata.tpch.lineitem,\\n testdata.tpch.supplier,\\n testdata.tpch.nation,\\n testdata.tpch.region\\nwhere\\n c_custkey = o_custkey\\n and l_orderkey = o_orderkey\\n and l_suppkey = s_suppkey\\n and c_nationkey = s_nationkey\\n and s_nationkey = n_nationkey\\n and n_regionkey = r_regionkey\\n and r_name = 'AFRICA'\\n and o_orderdate >= '1993-01-01'::DATE\\n and o_orderdate < '1994-01-01'::DATE\\ngroup by\\n n_name\\norder by\\n revenue desc;\", 'Q06': \"select\\n sum(l_extendedprice * l_discount) as revenue\\nfrom\\n testdata.tpch.lineitem\\nwhere\\n l_shipdate >= '1993-01-01'::DATE\\n and l_shipdate < '1994-01-01'::DATE\\n and l_discount between 0.06 - 0.01 and 0.06 + 0.01\\n and l_quantity < 25;\", 'Q07': \"select\\n supp_nation,\\n cust_nation,\\n l_year,\\n sum(volume) as revenue\\nfrom\\n (\\n select\\n n1.n_name as supp_nation,\\n n2.n_name as cust_nation,\\n EXTRACT(YEAR FROM l_shipdate) as l_year,\\n l_extendedprice * (1 - l_discount) as volume\\n from\\n testdata.tpch.supplier,\\n testdata.tpch.lineitem,\\n testdata.tpch.orders,\\n testdata.tpch.customer,\\n testdata.tpch.nation n1,\\n testdata.tpch.nation n2\\n where\\n s_suppkey = l_suppkey\\n and o_orderkey = l_orderkey\\n and c_custkey = o_custkey\\n and s_nationkey = n1.n_nationkey\\n and c_nationkey = n2.n_nationkey\\n and (\\n (n1.n_name = 'KENYA' and n2.n_name = 'PERU')\\n or (n1.n_name = 'PERU' and n2.n_name = 'KENYA')\\n )\\n and l_shipdate between '1995-01-01'::DATE and '1996-12-31'::DATE\\n ) as shipping\\ngroup by\\n supp_nation,\\n cust_nation,\\n l_year\\norder by\\n supp_nation,\\n cust_nation,\\n l_year;\", 'Q08': \"select\\n o_year,\\n sum(case\\n when nation = 'PERU' then volume\\n else 0\\n end) / sum(volume) as mkt_share\\nfrom\\n (\\n select\\n EXTRACT(YEAR FROM o_orderdate) as o_year,\\n l_extendedprice * (1 - l_discount) as volume,\\n n2.n_name as nation\\n from\\n testdata.tpch.part,\\n testdata.tpch.supplier,\\n testdata.tpch.lineitem,\\n testdata.tpch.orders,\\n testdata.tpch.customer,\\n testdata.tpch.nation n1,\\n testdata.tpch.nation n2,\\n testdata.tpch.region\\n where\\n p_partkey = l_partkey\\n and s_suppkey = l_suppkey\\n and l_orderkey = o_orderkey\\n and o_custkey = c_custkey\\n and c_nationkey = n1.n_nationkey\\n and n1.n_regionkey = r_regionkey\\n and r_name = 'AMERICA'\\n and s_nationkey = n2.n_nationkey\\n and o_orderdate between '1995-01-01'::DATE and '1996-12-31'::DATE\\n and p_type = 'ECONOMY BURNISHED NICKEL'\\n ) as all_nations\\ngroup by\\n o_year\\norder by\\n o_year;\", 'Q09': \"select\\n nation,\\n o_year,\\n sum(amount) as sum_profit\\nfrom\\n (\\n select\\n n_name as nation,\\n EXTRACT(YEAR FROM o_orderdate) as o_year,\\n l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount\\n from\\n testdata.tpch.part,\\n testdata.tpch.supplier,\\n testdata.tpch.lineitem,\\n testdata.tpch.partsupp,\\n testdata.tpch.orders,\\n testdata.tpch.nation\\n where\\n s_suppkey = l_suppkey\\n and ps_suppkey = l_suppkey\\n and ps_partkey = l_partkey\\n and p_partkey = l_partkey\\n and o_orderkey = l_orderkey\\n and s_nationkey = n_nationkey\\n and p_name like '%plum%'\\n ) as profit\\ngroup by\\n nation,\\n o_year\\norder by\\n nation,\\n o_year desc;\", 'Q10': \"select\\n c_custkey,\\n c_name,\\n sum(l_extendedprice * (1 - l_discount)) as revenue,\\n c_acctbal,\\n n_name,\\n c_address,\\n c_phone,\\n c_comment\\nfrom\\n testdata.tpch.customer,\\n testdata.tpch.orders,\\n testdata.tpch.lineitem,\\n testdata.tpch.nation\\nwhere\\n c_custkey = o_custkey\\n and l_orderkey = o_orderkey\\n and o_orderdate >= '1993-07-01'::DATE\\n and o_orderdate < '1993-10-01'::DATE\\n and l_returnflag = 'R'\\n and c_nationkey = n_nationkey\\ngroup by\\n c_custkey,\\n c_name,\\n c_acctbal,\\n c_phone,\\n n_name,\\n c_address,\\n c_comment\\norder by\\n revenue desc\\nlimit 20;\", 'Q11': \"with q11_part_tmp_cached as\\n(select\\n ps_partkey,\\n sum(ps_supplycost * ps_availqty) as part_value\\nfrom\\n testdata.tpch.partsupp,\\n testdata.tpch.supplier,\\n testdata.tpch.nation\\nwhere\\n ps_suppkey = s_suppkey\\n and s_nationkey = n_nationkey\\n and n_name = 'GERMANY'\\ngroup by ps_partkey)\\n\\n, q11_sum_tmp_cached as\\n(select\\n sum(part_value) as total_value\\nfrom\\n q11_part_tmp_cached)\\n\\nselect\\n ps_partkey, part_value as value\\nfrom (\\n select\\n ps_partkey,\\n part_value,\\n total_value\\n from\\n q11_part_tmp_cached cross join q11_sum_tmp_cached\\n) a\\nwhere\\n part_value > total_value * 0.0001\\norder by\\n value desc;\", 'Q12': \"select\\n l_shipmode,\\n sum(case\\n when o_orderpriority = '1-URGENT'\\n or o_orderpriority = '2-HIGH'\\n then 1\\n else 0\\n end) as high_line_count,\\n sum(case\\n when o_orderpriority <> '1-URGENT'\\n and o_orderpriority <> '2-HIGH'\\n then 1\\n else 0\\n end) as low_line_count\\nfrom\\n testdata.tpch.orders,\\n testdata.tpch.lineitem\\nwhere\\n o_orderkey = l_orderkey\\n and l_shipmode in ('REG AIR', 'MAIL')\\n and l_commitdate < l_receiptdate\\n and l_shipdate < l_commitdate\\n and l_receiptdate >= '1995-01-01'::DATE\\n and l_receiptdate < '1996-01-01'::DATE\\ngroup by\\n l_shipmode\\norder by\\n l_shipmode;\", 'Q13': \"SELECT\\n c_count,\\n Count(*) AS custdist\\nFROM\\n (\\n SELECT\\n c_custkey,\\n Count(o_orderkey) AS c_count\\n FROM\\n testdata.tpch.customer\\n LEFT OUTER JOIN (\\n SELECT\\n *\\n FROM\\n testdata.tpch.orders\\n WHERE\\n o_comment NOT LIKE '%unusual%accounts%'\\n ) AS t ON c_custkey = t.o_custkey\\n GROUP BY\\n c_custkey\\n ) c_orders\\nGROUP BY\\n c_count\\nORDER BY\\n custdist DESC,\\n c_count DESC;\", 'Q14': \"select\\n 100.00 * sum(case\\n when p_type like 'PROMO%'\\n then l_extendedprice * (1 - l_discount)\\n else 0\\n end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue\\nfrom\\n testdata.tpch.lineitem,\\n testdata.tpch.part\\nwhere\\n l_partkey = p_partkey\\n and l_shipdate >= '1995-08-01'::DATE\\n and l_shipdate < '1995-09-01'::DATE;\", 'Q15': \"with revenue_cached as\\n(select\\n l_suppkey as supplier_no,\\n sum(l_extendedprice * (1 - l_discount)) as total_revenue\\nfrom\\n testdata.tpch.lineitem\\nwhere\\n l_shipdate >= '1996-01-01'::DATE\\n and l_shipdate < '1996-04-01'::DATE\\ngroup by l_suppkey)\\n\\n, max_revenue_cached as\\n(select\\n max(total_revenue) as max_revenue\\nfrom\\n revenue_cached)\\n\\nselect\\n s_suppkey,\\n s_name,\\n s_address,\\n s_phone,\\n total_revenue\\nfrom\\n testdata.tpch.supplier,\\n revenue_cached,\\n max_revenue_cached\\nwhere\\n s_suppkey = supplier_no\\n and total_revenue = max_revenue\\norder by s_suppkey;\", 'Q16': \"select\\n p_brand,\\n p_type,\\n p_size,\\n count(distinct ps_suppkey) as supplier_cnt\\nfrom\\n testdata.tpch.partsupp,\\n testdata.tpch.part\\nwhere\\n p_partkey = ps_partkey\\n and p_brand <> 'Brand#34'\\n and p_type not like 'ECONOMY BRUSHED%'\\n and p_size in (22, 14, 27, 49, 21, 33, 35, 28)\\n and partsupp.ps_suppkey not in (\\n select\\n s_suppkey\\n from\\n testdata.tpch.supplier\\n where\\n s_comment like '%Customer%Complaints%'\\n )\\ngroup by\\n p_brand,\\n p_type,\\n p_size\\norder by\\n supplier_cnt desc,\\n p_brand,\\n p_type,\\n p_size;\", 'Q17': \"select\\n cast(sum(l_extendedprice) / 7.0 as decimal(32,2)) as avg_yearly\\nfrom\\n testdata.tpch.lineitem,\\n testdata.tpch.part\\nwhere\\n p_partkey = l_partkey\\n and p_brand = 'Brand#23'\\n and p_container = 'MED BOX'\\n and l_quantity < (\\n select\\n 0.2 * avg(l_quantity)\\n from\\n testdata.tpch.lineitem\\n where\\n l_partkey = p_partkey\\n );\", 'Q18': 'select\\n c_name,\\n c_custkey,\\n o_orderkey,\\n o_orderdate,\\n o_totalprice,\\n sum(l_quantity)\\nfrom\\n testdata.tpch.customer,\\n testdata.tpch.orders,\\n testdata.tpch.lineitem\\nwhere\\n o_orderkey in (\\n select\\n l_orderkey\\n from\\n testdata.tpch.lineitem\\n group by\\n l_orderkey having\\n sum(l_quantity) > 300\\n )\\n and c_custkey = o_custkey\\n and o_orderkey = l_orderkey\\ngroup by\\n c_name,\\n c_custkey,\\n o_orderkey,\\n o_orderdate,\\n o_totalprice\\norder by\\n o_totalprice desc,\\n o_orderdate\\nlimit 100;', 'Q19': \"select\\n sum(l_extendedprice* (1 - l_discount)) as revenue\\nfrom\\n testdata.tpch.lineitem,\\n testdata.tpch.part\\nwhere\\n (\\n p_partkey = l_partkey\\n and p_brand = 'Brand#32'\\n and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')\\n and l_quantity >= 7 and l_quantity <= 7 + 10\\n and p_size between 1 and 5\\n and l_shipmode in ('AIR', 'AIR REG')\\n and l_shipinstruct = 'DELIVER IN PERSON'\\n )\\n or\\n (\\n p_partkey = l_partkey\\n and p_brand = 'Brand#35'\\n and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')\\n and l_quantity >= 15 and l_quantity <= 15 + 10\\n and p_size between 1 and 10\\n and l_shipmode in ('AIR', 'AIR REG')\\n and l_shipinstruct = 'DELIVER IN PERSON'\\n )\\n or\\n (\\n p_partkey = l_partkey\\n and p_brand = 'Brand#24'\\n and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')\\n and l_quantity >= 26 and l_quantity <= 26 + 10\\n and p_size between 1 and 15\\n and l_shipmode in ('AIR', 'AIR REG')\\n and l_shipinstruct = 'DELIVER IN PERSON'\\n );\", 'Q20': \"select\\n s_name,\\n s_address\\nfrom\\n testdata.tpch.supplier,\\n testdata.tpch.nation\\nwhere\\n s_suppkey in (\\n select\\n ps_suppkey\\n from\\n testdata.tpch.partsupp\\n where\\n ps_partkey in (\\n select\\n p_partkey\\n from\\n testdata.tpch.part\\n where\\n p_name like 'forest%'\\n )\\n and ps_availqty > (\\n select\\n 0.5 * sum(l_quantity)\\n from\\n testdata.tpch.lineitem\\n where\\n l_partkey = ps_partkey\\n and l_suppkey = ps_suppkey\\n and l_shipdate >= '1994-01-01'::DATE\\n and l_shipdate < '1995-01-01'::DATE\\n )\\n )\\n and s_nationkey = n_nationkey\\n and n_name = 'CANADA'\\norder by\\n s_name;\", 'Q21': \"select\\n s_name,\\n count(*) as numwait\\nfrom\\n testdata.tpch.supplier,\\n testdata.tpch.lineitem l1,\\n testdata.tpch.orders,\\n testdata.tpch.nation\\nwhere\\n s_suppkey = l1.l_suppkey\\n and o_orderkey = l1.l_orderkey\\n and o_orderstatus = 'F'\\n and l1.l_receiptdate > l1.l_commitdate\\n and exists (\\n select\\n *\\n from\\n testdata.tpch.lineitem l2\\n where\\n l2.l_orderkey = l1.l_orderkey\\n and l2.l_suppkey <> l1.l_suppkey\\n )\\n and not exists (\\n select\\n *\\n from\\n testdata.tpch.lineitem l3\\n where\\n l3.l_orderkey = l1.l_orderkey\\n and l3.l_suppkey <> l1.l_suppkey\\n and l3.l_receiptdate > l3.l_commitdate\\n )\\n and s_nationkey = n_nationkey\\n and n_name = 'SAUDI ARABIA'\\ngroup by\\n s_name\\norder by\\n numwait desc,\\n s_name\\nlimit 100;\", 'Q22': \"with q22_customer_tmp_cached as\\n(select\\n c_acctbal,\\n c_custkey,\\n substring(c_phone, 1, 2) as cntrycode\\nfrom\\n testdata.tpch.customer\\nwhere\\n substring(c_phone, 1, 2) = '13' or\\n substring(c_phone, 1, 2) = '31' or\\n substring(c_phone, 1, 2) = '23' or\\n substring(c_phone, 1, 2) = '29' or\\n substring(c_phone, 1, 2) = '30' or\\n substring(c_phone, 1, 2) = '18' or\\n substring(c_phone, 1, 2) = '17'\\n),\\n\\nq22_customer_tmp1_cached as\\n(select\\n avg(c_acctbal) as avg_acctbal\\nfrom\\n q22_customer_tmp_cached\\nwhere\\n c_acctbal > 0.00\\n),\\n\\nq22_orders_tmp_cached as\\n(select\\n o_custkey\\nfrom\\n testdata.tpch.orders\\ngroup by\\n o_custkey)\\n\\nselect\\n cntrycode,\\n count(1) as numcust,\\n sum(c_acctbal) as totacctbal\\nfrom (\\n select\\n cntrycode,\\n c_acctbal,\\n avg_acctbal\\n from\\n q22_customer_tmp1_cached ct1 cross join (\\n select\\n cntrycode,\\n c_acctbal\\n from\\n q22_orders_tmp_cached ot\\n right outer join q22_customer_tmp_cached ct\\n on ct.c_custkey = ot.o_custkey\\n where\\n o_custkey is null\\n ) ct2\\n) a\\nwhere\\n c_acctbal > avg_acctbal\\ngroup by\\n cntrycode\\norder by\\n cntrycode;\"}\n", "\n", "import json\n", "\n", "with open(\"queries.json\", \"w\") as fh:\n", " json.dump(QUERIES, fh)\n", "\n", "print(f\"{len(QUERIES)} queries saved: {list(QUERIES)}\")" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "id": "6cdabc0f", "metadata": {}, "source": [ "## 3. Benchmark runner\n", "\n", "The benchmark is a script, and each engine runs it in **its own fresh process**, the way the [Opteryx performance suites](https://docs.opteryx.app/docs/reference/internals/benchmarking#method) do. That means neither engine inherits anything from the data generation done in this notebook.\n", "\n", "Inside the script:\n", "\n", "- **Warm-up first.** The first query in a fresh process pays one-off costs: loading native libraries, starting thread pools, and the first read of each file from disk. On a new runtime that can take seconds. Each engine runs a `COUNT(*)` over `lineitem` first and reports it separately as the cold start, so the cost isn't charged to whichever query happens to run first.\n", "- **DuckDB** gets one in-memory connection with a view over each table's Parquet files, reused across all queries, which is normal embedded-engine usage.\n", "- **Opteryx** opens a fresh session per iteration and drains the result as morsels without rendering it. It leaves its worker pools at their defaults, which it sizes to the machine. Pinning them higher than the runtime's core count makes it slower, not faster.\n", "- Each query runs `ITERATIONS` warm iterations with a garbage collection before each, and the **minimum** time is reported.\n", "\n", "Row counts are recorded from both engines as a correctness check. Both engines query the same data, so they should return the same number of rows for every query." ] }, { "cell_type": "code", "id": "9d9c247f", "metadata": {}, "source": [ "%%writefile bench_tpch.py\n", "\"\"\"Run the TPC-H queries in queries.json on one engine and write the results as JSON.\n", "\n", "Usage: python bench_tpch.py {duckdb|opteryx} \n", "\"\"\"\n", "import gc\n", "import json\n", "import statistics\n", "import sys\n", "import time\n", "\n", "engine, iterations, out_path = sys.argv[1], int(sys.argv[2]), sys.argv[3]\n", "with open(\"queries.json\") as fh:\n", " QUERIES = json.load(fh)\n", "\n", "if engine == \"duckdb\":\n", " import duckdb\n", "\n", " import os\n", " con = duckdb.connect()\n", " # One view per table over the same Parquet files Opteryx reads.\n", " for t in sorted(os.listdir(\"data/tpch\")):\n", " con.execute(f\"CREATE VIEW {t} AS SELECT * FROM read_parquet('data/tpch/{t}/*.parquet')\")\n", " prefix = \"\"\n", "\n", " def run(sql):\n", " t0 = time.perf_counter()\n", " rows = len(con.execute(sql).fetchall())\n", " return (time.perf_counter() - t0) * 1000, rows\n", "\n", "elif engine == \"opteryx\":\n", " import opteryx\n", " from opteryx.connectors import DiskConnector\n", "\n", " # `data.tpch.
` resolves to ./data/tpch/
/, the Parquet files from step 1.\n", " opteryx.register_workspace(\"data\", DiskConnector)\n", " prefix = \"data.tpch.\"\n", "\n", " def run(sql):\n", " session = opteryx.session()\n", " try:\n", " rows = 0\n", " t0 = time.perf_counter()\n", " for morsel in session.execute_to_morsels(sql):\n", " if morsel is not None and hasattr(morsel, \"num_rows\"):\n", " rows += morsel.num_rows\n", " return (time.perf_counter() - t0) * 1000, rows\n", " finally:\n", " session.close()\n", "\n", "else:\n", " raise SystemExit(f\"unknown engine: {engine}\")\n", "\n", "t0 = time.perf_counter()\n", "run(f\"SELECT COUNT(*) FROM {prefix}lineitem\")\n", "print(f\"{engine} cold start: {(time.perf_counter() - t0) * 1000:,.1f}ms\", flush=True)\n", "\n", "results = {}\n", "suite_start = time.perf_counter()\n", "for name, raw_sql in QUERIES.items():\n", " sql = raw_sql.replace(\"testdata.tpch.\", prefix)\n", " times = []\n", " try:\n", " for _ in range(iterations):\n", " gc.collect()\n", " elapsed_ms, rows = run(sql)\n", " times.append(elapsed_ms)\n", " results[name] = {\n", " \"ms\": min(times),\n", " \"median_ms\": statistics.median(times),\n", " \"rows\": rows,\n", " \"error\": None,\n", " }\n", " print(f\" {name} {min(times):>10,.1f}ms\", flush=True)\n", " except Exception as exc:\n", " results[name] = {\"ms\": None, \"median_ms\": None, \"rows\": None,\n", " \"error\": f\"{type(exc).__name__}: {exc}\"}\n", " print(f\" {name} FAILED {results[name]['error']}\", flush=True)\n", "\n", "print(f\"{engine} done in {time.perf_counter() - suite_start:.1f}s\", flush=True)\n", "with open(out_path, \"w\") as fh:\n", " json.dump(results, fh)" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "id": "2a2618dd", "metadata": {}, "source": [ "## 4. Run the benchmark\n", "\n", "This cell takes the longest. It prints a line as each query finishes, DuckDB's pass first and then Opteryx's." ] }, { "cell_type": "code", "id": "85f5e510", "metadata": {}, "source": [ "import subprocess\n", "import sys\n", "\n", "\n", "def run_engine(engine):\n", " out_path = f\"results_{engine}.json\"\n", " proc = subprocess.Popen(\n", " [sys.executable, \"bench_tpch.py\", engine, str(ITERATIONS), out_path],\n", " stdout=subprocess.PIPE,\n", " stderr=subprocess.STDOUT,\n", " text=True,\n", " )\n", " for line in proc.stdout:\n", " print(line, end=\"\")\n", " if proc.wait() != 0:\n", " raise RuntimeError(f\"{engine} benchmark exited with code {proc.returncode}\")\n", " with open(out_path) as fh:\n", " return json.load(fh)\n", "\n", "\n", "by_engine = {\"duckdb\": run_engine(\"duckdb\"), \"opteryx\": run_engine(\"opteryx\")}\n", "\n", "results = [] # rows of dicts, one per query\n", "for name in QUERIES:\n", " row = {\"query\": name}\n", " for engine, engine_results in by_engine.items():\n", " r = engine_results[name]\n", " row[f\"{engine}_ms\"] = r[\"ms\"]\n", " row[f\"{engine}_median_ms\"] = r[\"median_ms\"]\n", " row[f\"{engine}_rows\"] = r[\"rows\"]\n", " row[f\"{engine}_error\"] = r[\"error\"]\n", " results.append(row)" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "id": "d71e0f3a", "metadata": {}, "source": [ "## 5. Results" ] }, { "cell_type": "code", "id": "f4ce70d3", "metadata": {}, "source": [ "import pandas as pd\n", "\n", "df = pd.DataFrame(results).set_index(\"query\")\n", "\n", "# Row-count cross-check: the engines should agree, query by query.\n", "row_cols = [\"duckdb_rows\", \"opteryx_rows\"]\n", "mismatches = df[row_cols].apply(lambda r: len(set(v for v in r if v is not None)) > 1, axis=1)\n", "if mismatches.any():\n", " print(\"ROW COUNT MISMATCH on:\", list(df.index[mismatches]))\n", " display(df.loc[mismatches, row_cols])\n", "else:\n", " print(\"Row counts agree across both engines on every query that ran.\")\n", "\n", "timing_cols = [\"duckdb_ms\", \"opteryx_ms\"]\n", "display(df[timing_cols].round(1))\n", "\n", "errors = df[[\"duckdb_error\", \"opteryx_error\"]].dropna(how=\"all\")\n", "if not errors.empty:\n", " print(\"\\nFailures:\")\n", " display(errors)\n", "\n", "compared = df[timing_cols].dropna()\n", "print(\"Total time, queries both engines completed \"\n", " f\"({len(compared)}/{len(df)} queries):\")\n", "for engine, col in zip((\"DuckDB\", \"Opteryx\"), timing_cols):\n", " print(f\" {engine:<8} {compared[col].sum():>10,.1f} ms\")" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "id": "7fadf8a2", "metadata": {}, "source": [ "## 6. Charts" ] }, { "cell_type": "code", "id": "45ddab7c", "metadata": {}, "source": [ "import matplotlib.pyplot as plt\n", "import numpy as np\n", "\n", "fig, ax = plt.subplots(figsize=(14, 6))\n", "x = np.arange(len(df))\n", "width = 0.38\n", "colors = {\"duckdb_ms\": \"#f0b429\", \"opteryx_ms\": \"#20bf6b\"}\n", "labels = {\"duckdb_ms\": \"DuckDB\", \"opteryx_ms\": \"Opteryx\"}\n", "\n", "for i, col in enumerate(timing_cols):\n", " values = df[col].fillna(0)\n", " offset = (i - (len(timing_cols) - 1) / 2) * width\n", " ax.bar(x + offset, values, width, label=labels[col], color=colors[col])\n", "\n", "ax.set_yscale(\"log\")\n", "ax.set_xticks(x)\n", "ax.set_xticklabels(df.index, rotation=45, ha=\"right\")\n", "ax.set_ylabel(\"min time (ms, log scale)\")\n", "ax.set_title(f\"TPC-H SF{SCALE_FACTOR} on DuckDB-written Parquet: per-query min time across {ITERATIONS} warm runs\")\n", "ax.legend()\n", "ax.grid(axis=\"y\", alpha=0.3, which=\"both\")\n", "fig.tight_layout()\n", "plt.show()\n", "\n", "fig, ax = plt.subplots(figsize=(6, 5))\n", "engines = [\"DuckDB\", \"Opteryx\"]\n", "values = [compared[c].sum() for c in timing_cols]\n", "bar_colors = [colors[c] for c in timing_cols]\n", "ax.bar(engines, values, color=bar_colors)\n", "ax.set_ylabel(\"total time (ms)\")\n", "ax.set_title(f\"TPC-H SF{SCALE_FACTOR}: total time, {len(compared)} comparable queries\")\n", "for i, v in enumerate(values):\n", " ax.text(i, v, f\" {v:,.0f} ms\", ha=\"center\", va=\"bottom\")\n", "fig.tight_layout()\n", "plt.show()" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "id": "27d5191d", "metadata": {}, "source": [ "## Notes and caveats\n", "\n", "- **Single process, single connection.** There is no concurrency and no cold-cache control. The OS page cache warms naturally after the first iteration of each query.\n", "- **Same files, DuckDB's writer.** Both engines read Parquet written by DuckDB at its default settings, so the file layout (row group size, compression, encodings and statistics) is DuckDB's choice. Neither engine reads its own native storage here: DuckDB's `.duckdb` format and Parquet written by Opteryx's writer, rugo, are both faster for their own engine than this shared file.\n", "- **Row-count cross-check.** The check in ยง5 catches query-translation bugs. If the engines disagree on row counts for a query, treat that query's timing as suspect.\n", "- **Scaling up.** Raise `SCALE_FACTOR` in the configuration cell, for example to `10` for SF10. Generating and querying SF10 takes considerably longer, and it may exceed the memory of a free Colab runtime.\n", "- **Not a published result.** As the [Benchmarking](https://docs.opteryx.app/docs/reference/internals/benchmarking#what-the-numbers-are-not) page says, numbers from one machine say nothing about another. This notebook exists so you can measure for yourself." ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "name": "python" } }, "nbformat": 4, "nbformat_minor": 5 }