From 45ed1948b6aac4fc8a268a77211327786fd4315f Mon Sep 17 00:00:00 2001 From: Jacob Champion Date: Tue, 6 Jun 2023 15:57:19 -0700 Subject: [PATCH 3/3] WIP: naive benchmarks Three benchmarks (bench1-3) for varying degrees of selectivity depending on the table used. Two benchmarks (bench4-5) for 1.0 selectivity, on both a single column and a larger number of columns. $ psql -f ./init postgres $ for i in 1 2 3 4; do for t in zero twenty fifty eighty hundred; do echo "= $i: $t =" pgbench -n -f ./bench$i -T5 -c1 -j1 -Dtable=$t postgres done; done $ pgbench -n -f ./bench5 -T5 -c1 -j1 postgres --- bench1 | 1 + bench2 | 1 + bench3 | 1 + bench4 | 1 + bench5 | 4 ++++ init | 37 +++++++++++++++++++++++++++++++++++++ 6 files changed, 45 insertions(+) create mode 100644 bench1 create mode 100644 bench2 create mode 100644 bench3 create mode 100644 bench4 create mode 100644 bench5 create mode 100755 init diff --git a/bench1 b/bench1 new file mode 100644 index 0000000000..9cb32d4fcb --- /dev/null +++ b/bench1 @@ -0,0 +1 @@ +SELECT COUNT(i) FROM :table; diff --git a/bench2 b/bench2 new file mode 100644 index 0000000000..1377bae0f5 --- /dev/null +++ b/bench2 @@ -0,0 +1 @@ +SELECT COUNT(*) FROM :table WHERE i IS NOT NULL; diff --git a/bench3 b/bench3 new file mode 100644 index 0000000000..524a140c0a --- /dev/null +++ b/bench3 @@ -0,0 +1 @@ +SELECT COUNT(*) FROM :table WHERE i IS NULL; diff --git a/bench4 b/bench4 new file mode 100644 index 0000000000..c8216537db --- /dev/null +++ b/bench4 @@ -0,0 +1 @@ +SELECT COUNT(*) FROM :table WHERE i > 0; diff --git a/bench5 b/bench5 new file mode 100644 index 0000000000..90a77741f6 --- /dev/null +++ b/bench5 @@ -0,0 +1,4 @@ +SELECT COUNT(*) FROM wide + WHERE i <> 0 AND j <> 0 AND k <> 0 AND l <> 0 AND m <> 0 AND n <> 0 AND o <> 0 + AND p <> 0 AND q <> 0 AND r <> 0 AND s <> 0 AND t <> 0 AND u <> 0 AND v <> 0 + AND w <> 0 AND x <> 0 AND y <> 0 AND z <> 0; diff --git a/init b/init new file mode 100755 index 0000000000..98c4a0acdb --- /dev/null +++ b/init @@ -0,0 +1,37 @@ +-- Tables are named after the percentage of values that are non-NULL. +DROP TABLE IF EXISTS zero; +DROP TABLE IF EXISTS twenty; +DROP TABLE IF EXISTS fifty; +DROP TABLE IF EXISTS eighty; +DROP TABLE IF EXISTS hundred; + +DROP TABLE IF EXISTS wide; + +\if :{?scale} +\else + \set scale 1 +\endif + +CREATE TABLE zero AS + SELECT NULL::int AS i FROM generate_series(1, 100000 * :scale); + +CREATE TABLE twenty AS + SELECT CASE WHEN random() < 0.2 THEN i ELSE NULL END AS i + FROM generate_series(1, 100000 * :scale) i; + +CREATE TABLE fifty AS + SELECT CASE WHEN random() < 0.5 THEN i ELSE NULL END AS i + FROM generate_series(1, 100000 * :scale) i; + +CREATE TABLE eighty AS + SELECT CASE WHEN random() < 0.8 THEN i ELSE NULL END AS i + FROM generate_series(1, 100000 * :scale) i; + +CREATE TABLE hundred AS + SELECT i FROM generate_series(1, 100000 * :scale) i; + +CREATE TABLE wide AS + SELECT i, 2 AS j, 3 AS k, 4 AS l, 5 AS m, 6 AS n, 7 AS o, 8 AS p, 9 AS q, + 10 AS r, 11 AS s, 12 AS t, 13 AS u, 14 AS v, 15 AS w, 16 AS x, 17 AS y, + 18 AS z + FROM generate_series(1, 100000 * :scale) i; -- 2.25.1