demos/ch04/scripts/bench.sh runs six queries, written once with {S} and {D} placeholders, on PostgreSQL 1,289 (mart.sales, mart.dim_date) and DuckDB 61,228 (sales, dim_date), at two scales: the 137,944-line mart and the 1,379,440-line sales_x10 of EXPLAIN ANALYZE in Parallel, which 483-postgres.sql also builds in DuckDB.
-- Q1 scan and aggregate
SELECT genre, sum(gross_amount), count(*) FROM {S} WHERE status <> 'cancelled' GROUP BY genre;
-- Q2 join, group and top-N
SELECT d.year, d.quarter, s.country, sum(s.gross_amount) AS gross
FROM {S} s JOIN {D} d ON d.full_date = s.order_date
GROUP BY 1, 2, 3 ORDER BY gross DESC LIMIT 5;
-- Q3 distinct count per month
SELECT date_trunc('month', order_date) AS m, count(DISTINCT customer_id) FROM {S} GROUP BY 1;
-- Q4 window over an aggregate: best-selling title per country
SELECT * FROM (SELECT country, title, sum(qty) AS units,
rank() OVER (PARTITION BY country ORDER BY sum(qty) DESC) AS r
FROM {S} GROUP BY 1, 2) t WHERE r = 1;
-- Q5 point lookup
SELECT * FROM {S} WHERE order_id = 54321;
-- Q6 single-row update
UPDATE {S} SET status = status WHERE order_id = 54321 AND line_no = 1;Both engines get four CPUs (PostgreSQL max_parallel_workers_per_gather = 3 plus the leader, DuckDB threads = 4) and ample memory (work_mem = '64MB'). PostgreSQL gets a B-tree on order_id for Q5 and Q6, dropped after; DuckDB's rows are stored in order_id order, so its zone maps narrow the lookup. The best of five or seven runs counts, so caches are warm. Times come from psql's \timing and time.perf_counter() around DuckDB's Python client. Money differs as built: unconstrained numeric in PostgreSQL, DECIMAL(10,2) in DuckDB.