minmax-multi.sql
application/sql
SET max_parallel_workers_per_gather = 0;
CREATE EXTENSION tsm_system_rows;
CREATE EXTENSION btree_gin;
-- IP generated by hashing the seed
CREATE OR REPLACE FUNCTION random_ip_address(p_seed BIGINT) RETURNS INET AS $$
DECLARE
v_ip text;
v_hash bigint;
BEGIN
v_hash := abs(hashint8(p_seed));
v_ip := (1 + ((v_hash >> 24) % 253)) || '.' || (1 + ((v_hash >> 16) % 253)) || '.' || (1 + ((v_hash >> 8) % 253)) || '.' || (1 + (v_hash % 253));
RETURN v_ip::inet;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION ip_to_int(p_ip inet) RETURNS BIGINT AS $$
DECLARE
v_ip text;
v_parts text[];
v_int bigint;
BEGIN
v_ip := substring(p_ip::text from '[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+');
v_parts := regexp_split_to_array(v_ip, '\.');
v_int := (v_parts[1]::bigint << 24) + (v_parts[2]::bigint << 16) + (v_parts[3]::bigint << 8) + v_parts[4]::bigint;
RETURN v_int;
END;
$$ LANGUAGE plpgsql;
CREATE TABLE t (a int, b timestamptz, c inet, d numeric) WITH (fillfactor=90);
INSERT INTO t SELECT
(i + 100000 * random())/100,
'2000-01-01 00:00:00'::timestamptz + ((i + 600*random())::int || ' seconds')::interval,
random_ip_address(i/100 + (10 * random())::int), -- sequence of 100 records with the same IP address, slightly mixed
((i + (100000 * random())::int)/100)::numeric/10
FROM generate_series(1,100000000) s(i);
-- compute distance per range
SELECT
COUNT(*), AVG(a), AVG(b), AVG(c), AVG(d)
FROM
(
SELECT
((ctid::text::point)[0]/128)::int AS range_number,
(MAX(a) - MIN(a)) AS a,
(MAX(EXTRACT(epoch FROM b)) - MIN(EXTRACT(epoch FROM b))) AS b,
(MAX(ip_to_int(c)) - MIN(ip_to_int(c))) AS c,
(MAX(d) - MIN(d)) AS d
FROM t GROUP BY 1
) foo;
UPDATE t SET a = a - 1000000, b = b - '1000000 seconds'::interval, d = d - 100000 WHERE random() < 0.001;
UPDATE t SET a = a + 1000000, b = b + '1000000 seconds'::interval, d = d + 100000 WHERE random() < 0.001;
-- compute distance per range
SELECT
COUNT(*), AVG(a), AVG(b), AVG(c), AVG(d)
FROM
(
SELECT
((ctid::text::point)[0]/128)::int AS range_number,
(MAX(a) - MIN(a)) AS a,
(MAX(EXTRACT(epoch FROM b)) - MIN(EXTRACT(epoch FROM b))) AS b,
(MAX(ip_to_int(c)) - MIN(ip_to_int(c))) AS c,
(MAX(d) - MIN(d)) AS d
FROM t GROUP BY 1
) foo;
VACUUM ANALYZE t;
-- compute parameters for point queries
SELECT percentile_disc(0.5) within group (order by a) AS a FROM t TABLESAMPLE system_rows(10000) \gset
SELECT ('''' || (percentile_disc(0.5) within group (order by b)) || '''') AS b FROM t TABLESAMPLE system_rows(10000) \gset
SELECT ('''' || (percentile_disc(0.5) within group (order by c)) || '''') AS c FROM t TABLESAMPLE system_rows(10000) \gset
SELECT percentile_disc(0.5) within group (order by d) AS d FROM t TABLESAMPLE system_rows(10000) \gset
-- compute parameters for range queries
SELECT percentile_disc(0.49) within group (order by a) AS a_min, percentile_disc(0.51) within group (order by a) AS a_max FROM t TABLESAMPLE system_rows(10000) \gset
SELECT ('''' || (percentile_disc(0.49) within group (order by b)) || '''') AS b_min, ('''' || (percentile_disc(0.51) within group (order by b)) || '''') AS b_max FROM t TABLESAMPLE system_rows(10000) \gset
SELECT ('''' || (percentile_disc(0.49) within group (order by c)) || '''') AS c_min, ('''' || (percentile_disc(0.51) within group (order by c)) || '''') AS c_max FROM t TABLESAMPLE system_rows(10000) \gset
SELECT percentile_disc(0.49) within group (order by d) AS d_min, percentile_disc(0.51) within group (order by d) AS d_max FROM t TABLESAMPLE system_rows(10000) \gset
\d+
-- first let's try a seqscan
ANALYZE t;
\echo ================= dataset #1 seqscan : plans =================
EXPLAIN ANALYZE SELECT * FROM t WHERE a = :a;
EXPLAIN ANALYZE SELECT * FROM t WHERE b = :b::timestamptz;
EXPLAIN ANALYZE SELECT * FROM t WHERE c = :c::inet;
EXPLAIN ANALYZE SELECT * FROM t WHERE d = :d;
EXPLAIN ANALYZE SELECT * FROM t WHERE a >= :a_min AND a <= :a_max;
EXPLAIN ANALYZE SELECT * FROM t WHERE b >= :b_min::timestamptz AND b <= :b_max::timestamptz;
EXPLAIN ANALYZE SELECT * FROM t WHERE c >= :c_min::inet AND c <= :c_max::inet;
EXPLAIN ANALYZE SELECT * FROM t WHERE d >= :d_min AND d <= :d_max;
\timing on
\echo ================= dataset #1 seqscan : query timing =================
SELECT COUNT(*) FROM t WHERE a = :a;
SELECT COUNT(*) FROM t WHERE b = :b::timestamptz;
SELECT COUNT(*) FROM t WHERE c = :c::inet;
SELECT COUNT(*) FROM t WHERE d = :d;
SELECT COUNT(*) FROM t WHERE a >= :a_min AND a <= :a_max;
SELECT COUNT(*) FROM t WHERE b >= :b_min::timestamptz AND b <= :b_max::timestamptz;
SELECT COUNT(*) FROM t WHERE c >= :c_min::inet AND c <= :c_max::inet;
SELECT COUNT(*) FROM t WHERE d >= :d_min AND d <= :d_max;
\timing off
-- disable seqscan, to force index plans
SET enable_seqscan = off;
-- now let's try btree indexes
\timing on
\echo ================= dataset #1 btree : create index =================
CREATE INDEX btree_a_idx ON t (a);
CREATE INDEX btree_b_idx ON t (b);
CREATE INDEX btree_c_idx ON t (c);
CREATE INDEX btree_d_idx ON t (d);
\timing off
\echo ================= dataset #1 btree : index size =================
\di+
ANALYZE t;
\echo ================= dataset #1 btree : plans =================
EXPLAIN ANALYZE SELECT * FROM t WHERE a = :a;
EXPLAIN ANALYZE SELECT * FROM t WHERE b = :b::timestamptz;
EXPLAIN ANALYZE SELECT * FROM t WHERE c = :c::inet;
EXPLAIN ANALYZE SELECT * FROM t WHERE d = :d;
EXPLAIN ANALYZE SELECT * FROM t WHERE a >= :a_min AND a <= :a_max;
EXPLAIN ANALYZE SELECT * FROM t WHERE b >= :b_min::timestamptz AND b <= :b_max::timestamptz;
EXPLAIN ANALYZE SELECT * FROM t WHERE c >= :c_min::inet AND c <= :c_max::inet;
EXPLAIN ANALYZE SELECT * FROM t WHERE d >= :d_min AND d <= :d_max;
\timing on
\echo ================= dataset #1 btree : query timing =================
SELECT COUNT(*) FROM t WHERE a = :a;
SELECT COUNT(*) FROM t WHERE b = :b::timestamptz;
SELECT COUNT(*) FROM t WHERE c = :c::inet;
SELECT COUNT(*) FROM t WHERE d = :d;
SELECT COUNT(*) FROM t WHERE a >= :a_min AND a <= :a_max;
SELECT COUNT(*) FROM t WHERE b >= :b_min::timestamptz AND b <= :b_max::timestamptz;
SELECT COUNT(*) FROM t WHERE c >= :c_min::inet AND c <= :c_max::inet;
SELECT COUNT(*) FROM t WHERE d >= :d_min AND d <= :d_max;
\timing off
DROP INDEX btree_a_idx;
DROP INDEX btree_b_idx;
DROP INDEX btree_c_idx;
DROP INDEX btree_d_idx;
-- then GIN indexes, created using btree_gin
\timing on
\echo ================= dataset #1 gin : create index =================
CREATE INDEX gin_a_idx ON t USING GIN (a);
CREATE INDEX gin_b_idx ON t USING GIN (b);
CREATE INDEX gin_c_idx ON t USING GIN (c);
CREATE INDEX gin_d_idx ON t USING GIN (d);
\timing off
\echo ================= dataset #1 gin : index size =================
\di+
ANALYZE t;
\echo ================= dataset #1 gin : plans =================
EXPLAIN ANALYZE SELECT * FROM t WHERE a = :a;
EXPLAIN ANALYZE SELECT * FROM t WHERE b = :b::timestamptz;
EXPLAIN ANALYZE SELECT * FROM t WHERE c = :c::inet;
EXPLAIN ANALYZE SELECT * FROM t WHERE d = :d;
EXPLAIN ANALYZE SELECT * FROM t WHERE a >= :a_min AND a <= :a_max;
EXPLAIN ANALYZE SELECT * FROM t WHERE b >= :b_min::timestamptz AND b <= :b_max::timestamptz;
EXPLAIN ANALYZE SELECT * FROM t WHERE c >= :c_min::inet AND c <= :c_max::inet;
EXPLAIN ANALYZE SELECT * FROM t WHERE d >= :d_min AND d <= :d_max;
\timing on
\echo ================= dataset #1 gin : query timing =================
SELECT COUNT(*) FROM t WHERE a = :a;
SELECT COUNT(*) FROM t WHERE b = :b::timestamptz;
SELECT COUNT(*) FROM t WHERE c = :c::inet;
SELECT COUNT(*) FROM t WHERE d = :d;
SELECT COUNT(*) FROM t WHERE a >= :a_min AND a <= :a_max;
SELECT COUNT(*) FROM t WHERE b >= :b_min::timestamptz AND b <= :b_max::timestamptz;
SELECT COUNT(*) FROM t WHERE c >= :c_min::inet AND c <= :c_max::inet;
SELECT COUNT(*) FROM t WHERE d >= :d_min AND d <= :d_max;
\timing off
DROP INDEX gin_a_idx;
DROP INDEX gin_b_idx;
DROP INDEX gin_c_idx;
DROP INDEX gin_d_idx;
-- and now finally the brin indexes
\timing on
\echo ================= dataset #1 brin minmax : create index =================
CREATE INDEX brin_a_idx ON t USING BRIN (a);
CREATE INDEX brin_b_idx ON t USING BRIN (b);
CREATE INDEX brin_c_idx ON t USING BRIN (c);
CREATE INDEX brin_d_idx ON t USING BRIN (d);
\timing off
\echo ================= dataset #1 brin minmax : index size =================
\di+
ANALYZE t;
\echo ================= dataset #1 brin minmax : plans =================
EXPLAIN ANALYZE SELECT * FROM t WHERE a = :a;
EXPLAIN ANALYZE SELECT * FROM t WHERE b = :b::timestamptz;
EXPLAIN ANALYZE SELECT * FROM t WHERE c = :c::inet;
EXPLAIN ANALYZE SELECT * FROM t WHERE d = :d;
EXPLAIN ANALYZE SELECT * FROM t WHERE a >= :a_min AND a <= :a_max;
EXPLAIN ANALYZE SELECT * FROM t WHERE b >= :b_min::timestamptz AND b <= :b_max::timestamptz;
EXPLAIN ANALYZE SELECT * FROM t WHERE c >= :c_min::inet AND c <= :c_max::inet;
EXPLAIN ANALYZE SELECT * FROM t WHERE d >= :d_min AND d <= :d_max;
\timing on
\echo ================= dataset #1 brin minmax : query timing =================
SELECT COUNT(*) FROM t WHERE a = :a;
SELECT COUNT(*) FROM t WHERE b = :b::timestamptz;
SELECT COUNT(*) FROM t WHERE c = :c::inet;
SELECT COUNT(*) FROM t WHERE d = :d;
SELECT COUNT(*) FROM t WHERE a >= :a_min AND a <= :a_max;
SELECT COUNT(*) FROM t WHERE b >= :b_min::timestamptz AND b <= :b_max::timestamptz;
SELECT COUNT(*) FROM t WHERE c >= :c_min::inet AND c <= :c_max::inet;
SELECT COUNT(*) FROM t WHERE d >= :d_min AND d <= :d_max;
\timing off
DROP INDEX brin_a_idx;
DROP INDEX brin_b_idx;
DROP INDEX brin_c_idx;
DROP INDEX brin_d_idx;
\timing on
\echo ================= dataset #1 brin minmax multi : create index =================
CREATE INDEX brin_a_idx ON t USING BRIN (a int4_minmax_multi_ops);
CREATE INDEX brin_b_idx ON t USING BRIN (b timestamptz_minmax_multi_ops);
CREATE INDEX brin_c_idx ON t USING BRIN (c inet_minmax_multi_ops);
CREATE INDEX brin_d_idx ON t USING BRIN (d numeric_minmax_multi_ops);
\timing off
\echo ================= dataset #1 brin minmax multi : index size =================
\di+
ANALYZE t;
\echo ================= dataset #1 brin minmax multi : plans =================
EXPLAIN ANALYZE SELECT * FROM t WHERE a = :a;
EXPLAIN ANALYZE SELECT * FROM t WHERE b = :b::timestamptz;
EXPLAIN ANALYZE SELECT * FROM t WHERE c = :c::inet;
EXPLAIN ANALYZE SELECT * FROM t WHERE d = :d;
EXPLAIN ANALYZE SELECT * FROM t WHERE a >= :a_min AND a <= :a_max;
EXPLAIN ANALYZE SELECT * FROM t WHERE b >= :b_min::timestamptz AND b <= :b_max::timestamptz;
EXPLAIN ANALYZE SELECT * FROM t WHERE c >= :c_min::inet AND c <= :c_max::inet;
EXPLAIN ANALYZE SELECT * FROM t WHERE d >= :d_min AND d <= :d_max;
\timing on
\echo ================= dataset #1 brin minmax multi : query timing =================
SELECT COUNT(*) FROM t WHERE a = :a;
SELECT COUNT(*) FROM t WHERE b = :b::timestamptz;
SELECT COUNT(*) FROM t WHERE c = :c::inet;
SELECT COUNT(*) FROM t WHERE d = :d;
SELECT COUNT(*) FROM t WHERE a >= :a_min AND a <= :a_max;
SELECT COUNT(*) FROM t WHERE b >= :b_min::timestamptz AND b <= :b_max::timestamptz;
SELECT COUNT(*) FROM t WHERE c >= :c_min::inet AND c <= :c_max::inet;
SELECT COUNT(*) FROM t WHERE d >= :d_min AND d <= :d_max;
\timing off
DROP INDEX brin_a_idx;
DROP INDEX brin_b_idx;
DROP INDEX brin_c_idx;
DROP INDEX brin_d_idx;
\timing on
\echo ================= dataset #1 brin minmax multi (128) : create index =================
CREATE INDEX brin_a_idx ON t USING BRIN (a int4_minmax_multi_ops) WITH (values_per_range=128);
CREATE INDEX brin_b_idx ON t USING BRIN (b timestamptz_minmax_multi_ops) WITH (values_per_range=128);
CREATE INDEX brin_c_idx ON t USING BRIN (c inet_minmax_multi_ops) WITH (values_per_range=128);
CREATE INDEX brin_d_idx ON t USING BRIN (d numeric_minmax_multi_ops) WITH (values_per_range=128);
\timing off
\echo ================= dataset #1 brin minmax multi (128) : index size =================
\di+
ANALYZE t;
\echo ================= dataset #1 brin minmax multi (128) : plans =================
EXPLAIN ANALYZE SELECT * FROM t WHERE a = :a;
EXPLAIN ANALYZE SELECT * FROM t WHERE b = :b::timestamptz;
EXPLAIN ANALYZE SELECT * FROM t WHERE c = :c::inet;
EXPLAIN ANALYZE SELECT * FROM t WHERE d = :d;
EXPLAIN ANALYZE SELECT * FROM t WHERE a >= :a_min AND a <= :a_max;
EXPLAIN ANALYZE SELECT * FROM t WHERE b >= :b_min::timestamptz AND b <= :b_max::timestamptz;
EXPLAIN ANALYZE SELECT * FROM t WHERE c >= :c_min::inet AND c <= :c_max::inet;
EXPLAIN ANALYZE SELECT * FROM t WHERE d >= :d_min AND d <= :d_max;
\timing on
\echo ================= dataset #1 brin minmax multi (128) : query timing =================
SELECT COUNT(*) FROM t WHERE a = :a;
SELECT COUNT(*) FROM t WHERE b = :b::timestamptz;
SELECT COUNT(*) FROM t WHERE c = :c::inet;
SELECT COUNT(*) FROM t WHERE d = :d;
SELECT COUNT(*) FROM t WHERE a >= :a_min AND a <= :a_max;
SELECT COUNT(*) FROM t WHERE b >= :b_min::timestamptz AND b <= :b_max::timestamptz;
SELECT COUNT(*) FROM t WHERE c >= :c_min::inet AND c <= :c_max::inet;
SELECT COUNT(*) FROM t WHERE d >= :d_min AND d <= :d_max;
\timing off
-- now let's try with fewer distinct values per range
RESET enable_seqscan;
DROP TABLE t;
CREATE TABLE t (a int, b timestamptz, c inet, d numeric) WITH (fillfactor=90);
INSERT INTO t SELECT
(i + 10000 * random())/100,
'2000-01-01 00:00:00'::timestamptz + ((i + 60*random())::int || ' seconds')::interval,
random_ip_address(i/200 + (10 * random())::int), -- sequence of 200 records with the same IP address, slightly mixed
((i + (10000 * random())::int)/100)::numeric/10
FROM generate_series(1,100000000) s(i);
-- compute distance per range
SELECT
COUNT(*), AVG(a), AVG(b), AVG(c), AVG(d)
FROM
(
SELECT
((ctid::text::point)[0]/128)::int AS range_number,
(MAX(a) - MIN(a)) AS a,
(MAX(EXTRACT(epoch FROM b)) - MIN(EXTRACT(epoch FROM b))) AS b,
(MAX(ip_to_int(c)) - MIN(ip_to_int(c))) AS c,
(MAX(d) - MIN(d)) AS d
FROM t GROUP BY 1
) foo;
UPDATE t SET a = a - 1000000, b = b - '1000000 seconds'::interval, d = d - 100000 WHERE random() < 0.001;
UPDATE t SET a = a + 1000000, b = b + '1000000 seconds'::interval, d = d + 100000 WHERE random() < 0.001;
-- compute distance per range
SELECT
COUNT(*), AVG(a), AVG(b), AVG(c), AVG(d)
FROM
(
SELECT
((ctid::text::point)[0]/128)::int AS range_number,
(MAX(a) - MIN(a)) AS a,
(MAX(EXTRACT(epoch FROM b)) - MIN(EXTRACT(epoch FROM b))) AS b,
(MAX(ip_to_int(c)) - MIN(ip_to_int(c))) AS c,
(MAX(d) - MIN(d)) AS d
FROM t GROUP BY 1
) foo;
VACUUM ANALYZE t;
-- compute parameters for point queries
SELECT percentile_disc(0.5) within group (order by a) AS a FROM t TABLESAMPLE system_rows(10000) \gset
SELECT ('''' || (percentile_disc(0.5) within group (order by b)) || '''') AS b FROM t TABLESAMPLE system_rows(10000) \gset
SELECT ('''' || (percentile_disc(0.5) within group (order by c)) || '''') AS c FROM t TABLESAMPLE system_rows(10000) \gset
SELECT percentile_disc(0.5) within group (order by d) AS d FROM t TABLESAMPLE system_rows(10000) \gset
-- compute parameters for range queries
SELECT percentile_disc(0.49) within group (order by a) AS a_min, percentile_disc(0.51) within group (order by a) AS a_max FROM t TABLESAMPLE system_rows(10000) \gset
SELECT ('''' || (percentile_disc(0.49) within group (order by b)) || '''') AS b_min, ('''' || (percentile_disc(0.51) within group (order by b)) || '''') AS b_max FROM t TABLESAMPLE system_rows(10000) \gset
SELECT ('''' || (percentile_disc(0.49) within group (order by c)) || '''') AS c_min, ('''' || (percentile_disc(0.51) within group (order by c)) || '''') AS c_max FROM t TABLESAMPLE system_rows(10000) \gset
SELECT percentile_disc(0.49) within group (order by d) AS d_min, percentile_disc(0.51) within group (order by d) AS d_max FROM t TABLESAMPLE system_rows(10000) \gset
\d+
-- first let's try a seqscan
ANALYZE t;
\echo ================= dataset #1 seqscan : plans =================
EXPLAIN ANALYZE SELECT * FROM t WHERE a = :a;
EXPLAIN ANALYZE SELECT * FROM t WHERE b = :b::timestamptz;
EXPLAIN ANALYZE SELECT * FROM t WHERE c = :c::inet;
EXPLAIN ANALYZE SELECT * FROM t WHERE d = :d;
EXPLAIN ANALYZE SELECT * FROM t WHERE a >= :a_min AND a <= :a_max;
EXPLAIN ANALYZE SELECT * FROM t WHERE b >= :b_min::timestamptz AND b <= :b_max::timestamptz;
EXPLAIN ANALYZE SELECT * FROM t WHERE c >= :c_min::inet AND c <= :c_max::inet;
EXPLAIN ANALYZE SELECT * FROM t WHERE d >= :d_min AND d <= :d_max;
\timing on
\echo ================= dataset #1 seqscan : query timing =================
SELECT COUNT(*) FROM t WHERE a = :a;
SELECT COUNT(*) FROM t WHERE b = :b::timestamptz;
SELECT COUNT(*) FROM t WHERE c = :c::inet;
SELECT COUNT(*) FROM t WHERE d = :d;
SELECT COUNT(*) FROM t WHERE a >= :a_min AND a <= :a_max;
SELECT COUNT(*) FROM t WHERE b >= :b_min::timestamptz AND b <= :b_max::timestamptz;
SELECT COUNT(*) FROM t WHERE c >= :c_min::inet AND c <= :c_max::inet;
SELECT COUNT(*) FROM t WHERE d >= :d_min AND d <= :d_max;
\timing off
SET enable_seqscan = off;
-- now let's try btree indexes
\timing on
\echo ================= dataset #2 btree : create index =================
CREATE INDEX btree_a_idx ON t (a);
CREATE INDEX btree_b_idx ON t (b);
CREATE INDEX btree_c_idx ON t (c);
CREATE INDEX btree_d_idx ON t (d);
\timing off
\echo ================= dataset #2 btree : index size =================
\di+
ANALYZE t;
\echo ================= dataset #2 btree : plans =================
EXPLAIN ANALYZE SELECT * FROM t WHERE a = :a;
EXPLAIN ANALYZE SELECT * FROM t WHERE b = :b::timestamptz;
EXPLAIN ANALYZE SELECT * FROM t WHERE c = :c::inet;
EXPLAIN ANALYZE SELECT * FROM t WHERE d = :d;
EXPLAIN ANALYZE SELECT * FROM t WHERE a >= :a_min AND a <= :a_max;
EXPLAIN ANALYZE SELECT * FROM t WHERE b >= :b_min::timestamptz AND b <= :b_max::timestamptz;
EXPLAIN ANALYZE SELECT * FROM t WHERE c >= :c_min::inet AND c <= :c_max::inet;
EXPLAIN ANALYZE SELECT * FROM t WHERE d >= :d_min AND d <= :d_max;
\timing on
\echo ================= dataset #2 btree : query timing =================
SELECT COUNT(*) FROM t WHERE a = :a;
SELECT COUNT(*) FROM t WHERE b = :b::timestamptz;
SELECT COUNT(*) FROM t WHERE c = :c::inet;
SELECT COUNT(*) FROM t WHERE d = :d;
SELECT COUNT(*) FROM t WHERE a >= :a_min AND a <= :a_max;
SELECT COUNT(*) FROM t WHERE b >= :b_min::timestamptz AND b <= :b_max::timestamptz;
SELECT COUNT(*) FROM t WHERE c >= :c_min::inet AND c <= :c_max::inet;
SELECT COUNT(*) FROM t WHERE d >= :d_min AND d <= :d_max;
\timing off
DROP INDEX btree_a_idx;
DROP INDEX btree_b_idx;
DROP INDEX btree_c_idx;
DROP INDEX btree_d_idx;
-- then GIN indexes, created using btree_gin
\timing on
\echo ================= dataset #2 gin : create index =================
CREATE INDEX gin_a_idx ON t USING GIN (a);
CREATE INDEX gin_b_idx ON t USING GIN (b);
CREATE INDEX gin_c_idx ON t USING GIN (c);
CREATE INDEX gin_d_idx ON t USING GIN (d);
\timing off
\echo ================= dataset #2 gin : index size =================
\di+
ANALYZE t;
\echo ================= dataset #2 gin : plans =================
EXPLAIN ANALYZE SELECT * FROM t WHERE a = :a;
EXPLAIN ANALYZE SELECT * FROM t WHERE b = :b::timestamptz;
EXPLAIN ANALYZE SELECT * FROM t WHERE c = :c::inet;
EXPLAIN ANALYZE SELECT * FROM t WHERE d = :d;
EXPLAIN ANALYZE SELECT * FROM t WHERE a >= :a_min AND a <= :a_max;
EXPLAIN ANALYZE SELECT * FROM t WHERE b >= :b_min::timestamptz AND b <= :b_max::timestamptz;
EXPLAIN ANALYZE SELECT * FROM t WHERE c >= :c_min::inet AND c <= :c_max::inet;
EXPLAIN ANALYZE SELECT * FROM t WHERE d >= :d_min AND d <= :d_max;
\timing on
\echo ================= dataset #2 gin : query timing =================
SELECT COUNT(*) FROM t WHERE a = :a;
SELECT COUNT(*) FROM t WHERE b = :b::timestamptz;
SELECT COUNT(*) FROM t WHERE c = :c::inet;
SELECT COUNT(*) FROM t WHERE d = :d;
SELECT COUNT(*) FROM t WHERE a >= :a_min AND a <= :a_max;
SELECT COUNT(*) FROM t WHERE b >= :b_min::timestamptz AND b <= :b_max::timestamptz;
SELECT COUNT(*) FROM t WHERE c >= :c_min::inet AND c <= :c_max::inet;
SELECT COUNT(*) FROM t WHERE d >= :d_min AND d <= :d_max;
\timing off
DROP INDEX gin_a_idx;
DROP INDEX gin_b_idx;
DROP INDEX gin_c_idx;
DROP INDEX gin_d_idx;
-- and now finally the bloom indexes
\timing on
\echo ================= dataset #2 brin minmax : create index =================
CREATE INDEX brin_a_idx ON t USING BRIN (a);
CREATE INDEX brin_b_idx ON t USING BRIN (b);
CREATE INDEX brin_c_idx ON t USING BRIN (c);
CREATE INDEX brin_d_idx ON t USING BRIN (d);
\timing off
\echo ================= dataset #2 brin minmax : index size =================
\di+
ANALYZE t;
\echo ================= dataset #2 brin minmax : plans =================
EXPLAIN ANALYZE SELECT * FROM t WHERE a = :a;
EXPLAIN ANALYZE SELECT * FROM t WHERE b = :b::timestamptz;
EXPLAIN ANALYZE SELECT * FROM t WHERE c = :c::inet;
EXPLAIN ANALYZE SELECT * FROM t WHERE d = :d;
EXPLAIN ANALYZE SELECT * FROM t WHERE a >= :a_min AND a <= :a_max;
EXPLAIN ANALYZE SELECT * FROM t WHERE b >= :b_min::timestamptz AND b <= :b_max::timestamptz;
EXPLAIN ANALYZE SELECT * FROM t WHERE c >= :c_min::inet AND c <= :c_max::inet;
EXPLAIN ANALYZE SELECT * FROM t WHERE d >= :d_min AND d <= :d_max;
\timing on
\echo ================= dataset #2 brin minmax : query timing =================
SELECT COUNT(*) FROM t WHERE a = :a;
SELECT COUNT(*) FROM t WHERE b = :b::timestamptz;
SELECT COUNT(*) FROM t WHERE c = :c::inet;
SELECT COUNT(*) FROM t WHERE d = :d;
SELECT COUNT(*) FROM t WHERE a >= :a_min AND a <= :a_max;
SELECT COUNT(*) FROM t WHERE b >= :b_min::timestamptz AND b <= :b_max::timestamptz;
SELECT COUNT(*) FROM t WHERE c >= :c_min::inet AND c <= :c_max::inet;
SELECT COUNT(*) FROM t WHERE d >= :d_min AND d <= :d_max;
\timing off
DROP INDEX brin_a_idx;
DROP INDEX brin_b_idx;
DROP INDEX brin_c_idx;
DROP INDEX brin_d_idx;
\timing on
\echo ================= dataset #2 brin minmax multi : create index =================
CREATE INDEX brin_a_idx ON t USING BRIN (a int4_minmax_multi_ops);
CREATE INDEX brin_b_idx ON t USING BRIN (b timestamptz_minmax_multi_ops);
CREATE INDEX brin_c_idx ON t USING BRIN (c inet_minmax_multi_ops);
CREATE INDEX brin_d_idx ON t USING BRIN (d numeric_minmax_multi_ops);
\timing off
\echo ================= dataset #2 brin minmax multi : index size =================
\di+
ANALYZE t;
\echo ================= dataset #2 brin minmax multi : plans =================
EXPLAIN ANALYZE SELECT * FROM t WHERE a = :a;
EXPLAIN ANALYZE SELECT * FROM t WHERE b = :b::timestamptz;
EXPLAIN ANALYZE SELECT * FROM t WHERE c = :c::inet;
EXPLAIN ANALYZE SELECT * FROM t WHERE d = :d;
EXPLAIN ANALYZE SELECT * FROM t WHERE a >= :a_min AND a <= :a_max;
EXPLAIN ANALYZE SELECT * FROM t WHERE b >= :b_min::timestamptz AND b <= :b_max::timestamptz;
EXPLAIN ANALYZE SELECT * FROM t WHERE c >= :c_min::inet AND c <= :c_max::inet;
EXPLAIN ANALYZE SELECT * FROM t WHERE d >= :d_min AND d <= :d_max;
\timing on
\echo ================= dataset #2 brin minmax multi : query timing =================
SELECT COUNT(*) FROM t WHERE a = :a;
SELECT COUNT(*) FROM t WHERE b = :b::timestamptz;
SELECT COUNT(*) FROM t WHERE c = :c::inet;
SELECT COUNT(*) FROM t WHERE d = :d;
SELECT COUNT(*) FROM t WHERE a >= :a_min AND a <= :a_max;
SELECT COUNT(*) FROM t WHERE b >= :b_min::timestamptz AND b <= :b_max::timestamptz;
SELECT COUNT(*) FROM t WHERE c >= :c_min::inet AND c <= :c_max::inet;
SELECT COUNT(*) FROM t WHERE d >= :d_min AND d <= :d_max;
\timing off
DROP INDEX brin_a_idx;
DROP INDEX brin_b_idx;
DROP INDEX brin_c_idx;
DROP INDEX brin_d_idx;
\timing on
\echo ================= dataset #2 brin minmax multi (128) : create index =================
CREATE INDEX brin_a_idx ON t USING BRIN (a int4_minmax_multi_ops) WITH (values_per_range=128);
CREATE INDEX brin_b_idx ON t USING BRIN (b timestamptz_minmax_multi_ops) WITH (values_per_range=128);
CREATE INDEX brin_c_idx ON t USING BRIN (c inet_minmax_multi_ops) WITH (values_per_range=128);
CREATE INDEX brin_d_idx ON t USING BRIN (d numeric_minmax_multi_ops) WITH (values_per_range=128);
\timing off
\echo ================= dataset #2 brin minmax multi (128) : index size =================
\di+
ANALYZE t;
\echo ================= dataset #2 brin minmax multi (128) : plans =================
EXPLAIN ANALYZE SELECT * FROM t WHERE a = :a;
EXPLAIN ANALYZE SELECT * FROM t WHERE b = :b::timestamptz;
EXPLAIN ANALYZE SELECT * FROM t WHERE c = :c::inet;
EXPLAIN ANALYZE SELECT * FROM t WHERE d = :d;
EXPLAIN ANALYZE SELECT * FROM t WHERE a >= :a_min AND a <= :a_max;
EXPLAIN ANALYZE SELECT * FROM t WHERE b >= :b_min::timestamptz AND b <= :b_max::timestamptz;
EXPLAIN ANALYZE SELECT * FROM t WHERE c >= :c_min::inet AND c <= :c_max::inet;
EXPLAIN ANALYZE SELECT * FROM t WHERE d >= :d_min AND d <= :d_max;
\timing on
\echo ================= dataset #2 brin minmax multi (128): query timing =================
SELECT COUNT(*) FROM t WHERE a = :a;
SELECT COUNT(*) FROM t WHERE b = :b::timestamptz;
SELECT COUNT(*) FROM t WHERE c = :c::inet;
SELECT COUNT(*) FROM t WHERE d = :d;
SELECT COUNT(*) FROM t WHERE a >= :a_min AND a <= :a_max;
SELECT COUNT(*) FROM t WHERE b >= :b_min::timestamptz AND b <= :b_max::timestamptz;
SELECT COUNT(*) FROM t WHERE c >= :c_min::inet AND c <= :c_max::inet;
SELECT COUNT(*) FROM t WHERE d >= :d_min AND d <= :d_max;
\timing off