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