DO $$ BEGIN -- Table t IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 't') THEN CREATE TABLE t(id bigserial primary key, order_date date, amount numeric(18, 2)); INSERT INTO t(order_date, amount) SELECT DATE '2024-01-01' + (gs % 730), gs::numeric(18, 2) * gs % 998244353 * gs % 1000000007 * 133.31 FROM generate_series(1, 1000000) AS gs; END IF; -- Table t_big IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 't_big') THEN CREATE TABLE t_big(id bigserial primary key, order_date date, amount numeric(38, 2)); INSERT INTO t_big(order_date, amount) SELECT DATE '2024-01-01' + (gs % 730), gs::numeric(38, 2) * gs * gs * 133.31 FROM generate_series(1, 1000000) AS gs; END IF; -- Table t_small IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 't_small') THEN CREATE TABLE t_small(id bigserial primary key, order_date date, amount numeric(9, 2)); INSERT INTO t_small(order_date, amount) SELECT DATE '2024-01-01' + (gs % 730), (gs % 1331) * (gs % 1331) * 1.31 FROM generate_series(1, 1000000) AS gs; END IF; END $$; VACUUM ANALYZE t; VACUUM ANALYZE t_big; VACUUM ANALYZE t_small; CREATE OR REPLACE FUNCTION benchmark_queries(queries text[]) RETURNS TABLE(query_number integer, query_text text, min_runtime_ms numeric) AS $$ DECLARE q text; start_time timestamp; end_time timestamp; runtime_ms numeric; i integer; min_time numeric; query_idx integer := 0; total_runtime numeric := 0; BEGIN SET LOCAL max_parallel_workers_per_gather = 0; FOREACH q IN ARRAY queries LOOP query_idx := query_idx + 1; min_time := NULL; FOR i IN 1..2 LOOP start_time := clock_timestamp(); EXECUTE q; end_time := clock_timestamp(); runtime_ms := EXTRACT(EPOCH FROM (end_time - start_time)) * 1000; IF min_time IS NULL OR runtime_ms < min_time THEN min_time := runtime_ms; END IF; END LOOP; query_number := query_idx; query_text := q; min_runtime_ms := min_time; RETURN NEXT; total_runtime := total_runtime + min_time; RAISE WARNING 'Query % completed: min runtime = % ms', query_idx, min_time; END LOOP; query_number := NULL; query_text := 'TOTAL'; min_runtime_ms := total_runtime; RETURN NEXT; RETURN; END; $$ LANGUAGE plpgsql; CREATE TABLE numeric_benchmark_results AS SELECT * FROM benchmark_queries(ARRAY[ -- Table t 'select max(amount) from t', 'select max(amount) from t where order_date > ''2025-01-01''', 'select order_date, max(amount) from t group by order_date', 'select order_date, max(amount) from t where order_date > ''2025-01-01'' group by order_date', 'select min(amount) from t', 'select min(amount) from t where order_date > ''2025-01-01''', 'select order_date, min(amount) from t group by order_date', 'select order_date, min(amount) from t where order_date > ''2025-01-01'' group by order_date', 'select sum(amount) from t', 'select sum(amount) from t where order_date > ''2025-01-01''', 'select order_date, sum(amount) from t group by order_date', 'select order_date, sum(amount) from t where order_date > ''2025-01-01'' group by order_date', 'select avg(amount) from t', 'select avg(amount) from t where order_date > ''2025-01-01''', 'select order_date, avg(amount) from t group by order_date', 'select order_date, avg(amount) from t where order_date > ''2025-01-01'' group by order_date', 'select variance(amount) from t', 'select variance(amount) from t where order_date > ''2025-01-01''', 'select order_date, variance(amount) from t group by order_date', 'select order_date, variance(amount) from t where order_date > ''2025-01-01'' group by order_date', 'select max(amount), min(amount), sum(amount), avg(amount), variance(amount) from t', 'select max(amount), min(amount), sum(amount), avg(amount), variance(amount) from t where order_date > ''2025-01-01''', 'select order_date, max(amount), min(amount), sum(amount), avg(amount), variance(amount) from t group by order_date', 'select order_date, max(amount), min(amount), sum(amount), avg(amount), variance(amount) from t where order_date > ''2025-01-01'' group by order_date', 'select count(*) from t where amount = 12345.67', 'select count(*) from t where amount <> 0', 'select count(*) from t where amount > 10000', 'select count(*) from t where amount >= 10000', 'select count(*) from t where amount < 50000', 'select count(*) from t where amount <= 50000', 'select count(*) from t where amount > amount * 0.5', 'select count(*) from t where amount + 100 > amount * 1.1', 'select sum(amount + 100) from t', 'select sum(amount - 50) from t', 'select sum(amount * 2) from t', 'select sum(amount / 3) from t', 'select sum(amount % 10) from t', 'select count(*) from t where amount * 2 > 100000', 'select count(*) from t where amount / 10 > 5000', 'select count(*) from t where amount + amount > 200000', 'select sum((amount + 100) * 0.95) from t', 'select sum(amount * 1.08 - amount * 0.03) from t', 'select sum(abs(amount)) from t', 'select sum(abs(amount - 50000)) from t', 'select count(*) from t where abs(amount - 50000) < 10000', 'select sum(round(amount, 0)) from t', 'select sum(round(amount, 1)) from t', 'select sum(trunc(amount, 0)) from t', 'select sum(trunc(amount, 1)) from t', 'select sum(floor(amount)) from t', 'select sum(ceil(amount)) from t', 'select sum(ceiling(amount)) from t', 'select sum(sign(amount)) from t', 'select count(*) from t where sign(amount) > 0', 'select sum(power(amount, 2)) from t', 'select sum(sqrt(abs(amount))) from t', -- 'select sum(power(amount, 0.5)) from t', 'select sum(mod(amount, 100)) from t', 'select count(*) from t where mod(amount, 10) = 0', 'select count(*) from t where amount % 1000 < 100', 'select count(*), width_bucket(amount, 0, 100000, 10) from t group by width_bucket(amount, 0, 100000, 10)', 'select sum(round(abs(amount - 50000), 2)) from t', 'select sum(floor(amount / 100) * 100) from t', 'select sum(ceil(amount / 100) * 100) from t', 'select sum(case when amount > 50000 then amount * 0.9 else amount * 1.1 end) from t', 'select amount from t order by amount limit 1000', 'select amount from t order by amount desc limit 1000', 'select amount from t order by abs(amount - 50000) limit 1000', -- Table t_big 'select max(amount) from t_big', 'select max(amount) from t_big where order_date > ''2025-01-01''', 'select order_date, max(amount) from t_big group by order_date', 'select order_date, max(amount) from t_big where order_date > ''2025-01-01'' group by order_date', 'select min(amount) from t_big', 'select min(amount) from t_big where order_date > ''2025-01-01''', 'select order_date, min(amount) from t_big group by order_date', 'select order_date, min(amount) from t_big where order_date > ''2025-01-01'' group by order_date', 'select sum(amount) from t_big', 'select sum(amount) from t_big where order_date > ''2025-01-01''', 'select order_date, sum(amount) from t_big group by order_date', 'select order_date, sum(amount) from t_big where order_date > ''2025-01-01'' group by order_date', 'select avg(amount) from t_big', 'select avg(amount) from t_big where order_date > ''2025-01-01''', 'select order_date, avg(amount) from t_big group by order_date', 'select order_date, avg(amount) from t_big where order_date > ''2025-01-01'' group by order_date', 'select variance(amount) from t_big', 'select variance(amount) from t_big where order_date > ''2025-01-01''', 'select order_date, variance(amount) from t_big group by order_date', 'select order_date, variance(amount) from t_big where order_date > ''2025-01-01'' group by order_date', 'select max(amount), min(amount), sum(amount), avg(amount), variance(amount) from t_big', 'select max(amount), min(amount), sum(amount), avg(amount), variance(amount) from t_big where order_date > ''2025-01-01''', 'select order_date, max(amount), min(amount), sum(amount), avg(amount), variance(amount) from t_big group by order_date', 'select order_date, max(amount), min(amount), sum(amount), avg(amount), variance(amount) from t_big where order_date > ''2025-01-01'' group by order_date', 'select count(*) from t_big where amount = 12345.67', 'select count(*) from t_big where amount <> 0', 'select count(*) from t_big where amount > 10000', 'select count(*) from t_big where amount >= 10000', 'select count(*) from t_big where amount < 50000', 'select count(*) from t_big where amount <= 50000', 'select count(*) from t_big where amount > amount * 0.5', 'select count(*) from t_big where amount + 100 > amount * 1.1', 'select sum(amount + 100) from t_big', 'select sum(amount - 50) from t_big', 'select sum(amount * 2) from t_big', 'select sum(amount / 3) from t_big', 'select sum(amount % 10) from t_big', 'select count(*) from t_big where amount * 2 > 100000', 'select count(*) from t_big where amount / 10 > 5000', 'select count(*) from t_big where amount + amount > 200000', 'select sum((amount + 100) * 0.95) from t_big', 'select sum(amount * 1.08 - amount * 0.03) from t_big', 'select sum(abs(amount)) from t_big', 'select sum(abs(amount - 50000)) from t_big', 'select count(*) from t_big where abs(amount - 50000) < 10000', 'select sum(round(amount, 0)) from t_big', 'select sum(round(amount, 1)) from t_big', 'select sum(trunc(amount, 0)) from t_big', 'select sum(trunc(amount, 1)) from t_big', 'select sum(floor(amount)) from t_big', 'select sum(ceil(amount)) from t_big', 'select sum(ceiling(amount)) from t_big', 'select sum(sign(amount)) from t_big', 'select count(*) from t_big where sign(amount) > 0', 'select sum(power(amount, 2)) from t_big', 'select sum(sqrt(abs(amount))) from t_big', --'select sum(power(amount, 0.5)) from t_big', 'select sum(mod(amount, 100)) from t_big', 'select count(*) from t_big where mod(amount, 10) = 0', 'select count(*) from t_big where amount % 1000 < 100', 'select count(*), width_bucket(amount, 0, 100000, 10) from t_big group by width_bucket(amount, 0, 100000, 10)', 'select sum(round(abs(amount - 50000), 2)) from t_big', 'select sum(floor(amount / 100) * 100) from t_big', 'select sum(ceil(amount / 100) * 100) from t_big', 'select sum(case when amount > 50000 then amount * 0.9 else amount * 1.1 end) from t_big', 'select amount from t_big order by amount limit 1000', 'select amount from t_big order by amount desc limit 1000', 'select amount from t_big order by abs(amount - 50000) limit 1000', -- Table t_small 'select max(amount) from t_small', 'select max(amount) from t_small where order_date > ''2025-01-01''', 'select order_date, max(amount) from t_small group by order_date', 'select order_date, max(amount) from t_small where order_date > ''2025-01-01'' group by order_date', 'select min(amount) from t_small', 'select min(amount) from t_small where order_date > ''2025-01-01''', 'select order_date, min(amount) from t_small group by order_date', 'select order_date, min(amount) from t_small where order_date > ''2025-01-01'' group by order_date', 'select sum(amount) from t_small', 'select sum(amount) from t_small where order_date > ''2025-01-01''', 'select order_date, sum(amount) from t_small group by order_date', 'select order_date, sum(amount) from t_small where order_date > ''2025-01-01'' group by order_date', 'select avg(amount) from t_small', 'select avg(amount) from t_small where order_date > ''2025-01-01''', 'select order_date, avg(amount) from t_small group by order_date', 'select order_date, avg(amount) from t_small where order_date > ''2025-01-01'' group by order_date', 'select variance(amount) from t_small', 'select variance(amount) from t_small where order_date > ''2025-01-01''', 'select order_date, variance(amount) from t_small group by order_date', 'select order_date, variance(amount) from t_small where order_date > ''2025-01-01'' group by order_date', 'select max(amount), min(amount), sum(amount), avg(amount), variance(amount) from t_small', 'select max(amount), min(amount), sum(amount), avg(amount), variance(amount) from t_small where order_date > ''2025-01-01''', 'select order_date, max(amount), min(amount), sum(amount), avg(amount), variance(amount) from t_small group by order_date', 'select order_date, max(amount), min(amount), sum(amount), avg(amount), variance(amount) from t_small where order_date > ''2025-01-01'' group by order_date', 'select count(*) from t_small where amount = 123.45', 'select count(*) from t_small where amount <> 0', 'select count(*) from t_small where amount > 100', 'select count(*) from t_small where amount >= 100', 'select count(*) from t_small where amount < 500', 'select count(*) from t_small where amount <= 500', 'select count(*) from t_small where amount > amount * 0.5', 'select count(*) from t_small where amount + 10 > amount * 1.1', 'select sum(amount + 10) from t_small', 'select sum(amount - 5) from t_small', 'select sum(amount * 2) from t_small', 'select sum(amount / 3) from t_small', 'select sum(amount % 10) from t_small', 'select count(*) from t_small where amount * 2 > 1000', 'select count(*) from t_small where amount / 10 > 50', 'select count(*) from t_small where amount + amount > 2000', 'select sum((amount + 10) * 0.95) from t_small', 'select sum(amount * 1.08 - amount * 0.03) from t_small', 'select sum(abs(amount)) from t_small', 'select sum(abs(amount - 500)) from t_small', 'select count(*) from t_small where abs(amount - 500) < 100', 'select sum(round(amount, 0)) from t_small', 'select sum(round(amount, 1)) from t_small', 'select sum(trunc(amount, 0)) from t_small', 'select sum(trunc(amount, 1)) from t_small', 'select sum(floor(amount)) from t_small', 'select sum(ceil(amount)) from t_small', 'select sum(ceiling(amount)) from t_small', 'select sum(sign(amount)) from t_small', 'select count(*) from t_small where sign(amount) > 0', 'select sum(power(amount, 2)) from t_small', 'select sum(sqrt(abs(amount))) from t_small', -- 'select sum(power(amount, 0.5)) from t_small', 'select sum(mod(amount, 100)) from t_small', 'select count(*) from t_small where mod(amount, 10) = 0', 'select count(*) from t_small where amount % 1000 < 100', 'select count(*), width_bucket(amount, 0, 1000, 10) from t_small group by width_bucket(amount, 0, 1000, 10)', 'select sum(round(abs(amount - 500), 2)) from t_small', 'select sum(floor(amount / 100) * 100) from t_small', 'select sum(ceil(amount / 100) * 100) from t_small', 'select sum(case when amount > 500 then amount * 0.9 else amount * 1.1 end) from t_small', 'select amount from t_small order by amount limit 1000', 'select amount from t_small order by amount desc limit 1000', 'select amount from t_small order by abs(amount - 500) limit 1000' ]);