From 707bedf6638eab4a532961fc53c3cd28aa883cb1 Mon Sep 17 00:00:00 2001 From: Jacob Champion Date: Tue, 6 Jun 2023 15:57:19 -0700 Subject: [PATCH v3 3/3] WIP: naive benchmarks Four benchmarks (bench1-3, 5) for varying degrees of selectivity depending on the table used. Two benchmarks (bench4, wide) for 1.0 selectivity, on both a single column and a larger number of columns. $ psql -f ./init postgres $ ./run --- bench1 | 1 + bench2 | 1 + bench3 | 1 + bench4 | 1 + bench5 | 1 + benchwide | 4 ++++ init | 57 +++++++++++++++++++++++++++++++++++++++++++++++++++++++ run | 26 +++++++++++++++++++++++++ 8 files changed, 92 insertions(+) create mode 100644 bench1 create mode 100644 bench2 create mode 100644 bench3 create mode 100644 bench4 create mode 100644 bench5 create mode 100644 benchwide create mode 100755 init create mode 100755 run 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..e075a0566f --- /dev/null +++ b/bench5 @@ -0,0 +1 @@ +SELECT COUNT(*) FROM :table WHERE abs(i) % 2 = 0; diff --git a/benchwide b/benchwide new file mode 100644 index 0000000000..90a77741f6 --- /dev/null +++ b/benchwide @@ -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..1a18d67b44 --- /dev/null +++ b/init @@ -0,0 +1,57 @@ +-- Tables are named after the percentage of values that are non-NULL. +DROP TABLE IF EXISTS zero; +DROP TABLE IF EXISTS five; +DROP TABLE IF EXISTS ten; +DROP TABLE IF EXISTS twenty; +DROP TABLE IF EXISTS fifty; +DROP TABLE IF EXISTS eighty; +DROP TABLE IF EXISTS ninety; +DROP TABLE IF EXISTS ninetyfive; +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 five AS + SELECT CASE WHEN random() < 0.05 THEN i ELSE NULL END AS i + FROM generate_series(1, 100000 * :scale) i; + +CREATE TABLE ten AS + SELECT CASE WHEN random() < 0.1 THEN i ELSE NULL END AS i + FROM generate_series(1, 100000 * :scale) i; + +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 ninety AS + SELECT CASE WHEN random() < 0.9 THEN i ELSE NULL END AS i + FROM generate_series(1, 100000 * :scale) i; + +CREATE TABLE ninetyfive AS + SELECT CASE WHEN random() < 0.95 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; diff --git a/run b/run new file mode 100755 index 0000000000..14563b92cf --- /dev/null +++ b/run @@ -0,0 +1,26 @@ +#! /bin/bash + +function bench() +{ + local index=$1 + + for t in zero five ten twenty fifty eighty ninety ninetyfive hundred; do + echo "= $index: $t =" + pgbench -n -f ./bench$index -T5 -c1 -j1 -Dtable=$t postgres + done; +} + +function all() +{ + for i in 1 2 3 4 5; do + bench $i + echo + done + + echo "= wide =" + pgbench -n -f ./benchwide -T5 -c1 -j1 postgres +} + +for typ in on off; do + PGOPTIONS="-c enable_seqscan_conds=$typ" all > $typ.perf +done -- 2.34.1