\echo 'Setup\n-----'
CREATE TEMPORARY TABLE test (c1 INT, c2 INT);

INSERT INTO test SELECT c1, abs(floor(random() * 4)-1) FROM generate_series(1, 10000) AS a(c1);
INSERT INTO test SELECT c1, abs(floor(random() * 4)-1) FROM generate_series(10001, 15000) AS a(c1);
INSERT INTO test SELECT c1, abs(floor(random() * 4)-1) FROM generate_series(15001, 20000) AS a(c1);

-- add non-uniformly-distributed values to 'c2'
INSERT INTO test SELECT 20001, 3;
INSERT INTO test SELECT 20002, 4;

CREATE INDEX i_test_c1 ON test (c1);
CREATE INDEX i_test_c2 ON test (c2);

ANALYZE test;


\o /dev/null
PREPARE prep_c1 AS SELECT * FROM test WHERE c1 = $1;
\o

\echo '\nc1\n--'
\echo 'c1 is unique, 1 exists\n'
EXPLAIN EXECUTE prep_c1(1);
\o /dev/null
EXPLAIN EXECUTE prep_c1(1);
EXPLAIN EXECUTE prep_c1(1);
EXPLAIN EXECUTE prep_c1(1);
EXPLAIN EXECUTE prep_c1(1);
\o
EXPLAIN EXECUTE prep_c1(1);

\o /dev/null
DEALLOCATE prep_c1;
PREPARE prep_c1 AS SELECT * FROM test WHERE c1 = $1;
\o

\echo '\n0 does not exist\n'
EXPLAIN EXECUTE prep_c1(0);
\o /dev/null
EXPLAIN EXECUTE prep_c1(0);
EXPLAIN EXECUTE prep_c1(0);
EXPLAIN EXECUTE prep_c1(0);
EXPLAIN EXECUTE prep_c1(0);
\o
EXPLAIN EXECUTE prep_c1(0);

\o /dev/null
PREPARE prep_c2 AS SELECT * FROM test WHERE c2 = $1;
\o

\echo '\nc2\n--'
\echo 'c2 has this distribution\n'
SELECT c2, COUNT(*), round(COUNT(*) / SUM(COUNT(*)) OVER() * 100) AS pct
FROM test
GROUP BY 1
ORDER BY 1;

\echo '\n1 is 50% of rows\n'
EXPLAIN EXECUTE prep_c2(1);
\o /dev/null
EXPLAIN EXECUTE prep_c2(1);
EXPLAIN EXECUTE prep_c2(1);
EXPLAIN EXECUTE prep_c2(1);
EXPLAIN EXECUTE prep_c2(1);
\o
EXPLAIN EXECUTE prep_c2(1);

\o /dev/null
DEALLOCATE prep_c2;
PREPARE prep_c2 AS SELECT * FROM test WHERE c2 = $1;
\o

\echo '\n2 is 25% of rows\n'
EXPLAIN EXECUTE prep_c2(2);
\o /dev/null
EXPLAIN EXECUTE prep_c2(2);
EXPLAIN EXECUTE prep_c2(2);
EXPLAIN EXECUTE prep_c2(2);
EXPLAIN EXECUTE prep_c2(2);
\o
EXPLAIN EXECUTE prep_c2(2);

\o /dev/null
DEALLOCATE prep_c2;
PREPARE prep_c2 AS SELECT * FROM test WHERE c2 = $1;
\o

\echo '\n3 is unique\n'
EXPLAIN EXECUTE prep_c2(3);
\o /dev/null
EXPLAIN EXECUTE prep_c2(3);
EXPLAIN EXECUTE prep_c2(3);
EXPLAIN EXECUTE prep_c2(3);
EXPLAIN EXECUTE prep_c2(3);
\o
EXPLAIN EXECUTE prep_c2(3);

\o /dev/null
DEALLOCATE prep_c2;
PREPARE prep_c2 AS SELECT * FROM test WHERE c2 = $1;
\o

\echo '\n5 does not exist\n'
EXPLAIN EXECUTE prep_c2(5);
\o /dev/null
EXPLAIN EXECUTE prep_c2(5);
EXPLAIN EXECUTE prep_c2(5);
EXPLAIN EXECUTE prep_c2(5);
EXPLAIN EXECUTE prep_c2(5);
\o
EXPLAIN EXECUTE prep_c2(5);
