SET log_temp_files = 0;

-- Make a simple relation with well distributed keys and correctly estimated size.
CREATE TABLE simple AS
SELECT generate_series(1, 1000000) AS id, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
ANALYZE simple;

-- Make a relation whose size we will under-estimate.
-- We want stats to say 1000 rows, but actually there are 1,000,000 rows.
CREATE TABLE bigger_than_it_looks AS
SELECT generate_series(1, 1000000) AS id, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
ALTER TABLE bigger_than_it_looks SET (autovacuum_enabled = 'false');
DELETE FROM bigger_than_it_looks WHERE id <= 999000;
VACUUM bigger_than_it_looks;
ANALYZE bigger_than_it_looks;
INSERT INTO bigger_than_it_looks
SELECT generate_series(1, 999000) AS id, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';

-- Make a relation whose size we underestimate and that also has a kind of skew that
-- breaks our batching scheme.  We want stats to say 1,000 rows, but actually
-- there are 1,000,000 rows and almost all of them have the same key.
CREATE TABLE awkwardly_skewed AS
SELECT generate_series(1, 1000000) AS id, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
ALTER TABLE awkwardly_skewed SET (autovacuum_enabled = 'false');
DELETE FROM awkwardly_skewed WHERE id <= 999000;
VACUUM awkwardly_skewed;
ANALYZE awkwardly_skewed;
INSERT INTO awkwardly_skewed
SELECT 42 AS id, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
  FROM generate_series(1, 999000);


\set ECHO ALL


-- The "optimal" case: the hash table fits in memory

-- non-parallel
SET max_parallel_workers_per_gather = 0;
SET work_mem = '64MB';
EXPLAIN ANALYZE
SELECT COUNT(*) FROM simple r JOIN simple s USING (id);

-- parallel with parallel-oblivious hash join
SET max_parallel_workers_per_gather = 8;
SET parallel_synchronization_cost = 1000000000;
SET work_mem = '64MB';
EXPLAIN ANALYZE
SELECT COUNT(*) FROM simple r JOIN simple s USING (id);
SELECT COUNT(*) FROM simple r JOIN simple s USING (id);

-- parallel with parallel-aware hash join
SET max_parallel_workers_per_gather = 8;
RESET parallel_synchronization_cost;
SET work_mem = '64MB';
EXPLAIN ANALYZE
SELECT COUNT(*) FROM simple r JOIN simple s USING (id);
SELECT COUNT(*) FROM simple r JOIN simple s USING (id);




-- The "good" case: batches required, but we plan the right number

-- non-parallel
SET max_parallel_workers_per_gather = 0;
SET work_mem = '1MB';
EXPLAIN ANALYZE
SELECT COUNT(*) FROM simple r JOIN simple s USING (id);

-- parallel with parallel-oblivious hash join
SET max_parallel_workers_per_gather = 8;
SET parallel_synchronization_cost = 1000000000;
SET work_mem = '1MB';
EXPLAIN ANALYZE
SELECT COUNT(*) FROM simple r JOIN simple s USING (id);
SELECT COUNT(*) FROM simple r JOIN simple s USING (id);

-- parallel with parallel-aware hash join
SET max_parallel_workers_per_gather = 8;
RESET parallel_synchronization_cost;
SET work_mem = '1MB';
EXPLAIN ANALYZE
SELECT COUNT(*) FROM simple r JOIN simple s USING (id);
SELECT COUNT(*) FROM simple r JOIN simple s USING (id);




-- The "bad" case: during execution we need to increase number of batches

-- non-parallel
SET max_parallel_workers_per_gather = 0;
SET work_mem = '1MB';
EXPLAIN ANALYZE
SELECT COUNT(*) FROM simple r JOIN bigger_than_it_looks s USING (id);

-- parallel with parallel-oblivious hash join
SET max_parallel_workers_per_gather = 8;
SET parallel_synchronization_cost = 1000000000;
SET work_mem = '1MB';
EXPLAIN ANALYZE
SELECT COUNT(*) FROM simple r JOIN bigger_than_it_looks s USING (id);
SELECT COUNT(*) FROM simple r JOIN bigger_than_it_looks s USING (id);

-- parallel with parallel-aware hash join
SET max_parallel_workers_per_gather = 8;
RESET parallel_synchronization_cost;
SET work_mem = '1MB';
EXPLAIN ANALYZE
SELECT COUNT(*) FROM simple r JOIN bigger_than_it_looks s USING (id);
SELECT COUNT(*) FROM simple r JOIN bigger_than_it_looks s USING (id);



-- The "ugly" case: increasing the number of batches during execution doesn't
-- help, so stop trying to fit in work_mem and hope for the best

-- non-parallel
SET max_parallel_workers_per_gather = 0;
SET work_mem = '1MB';
EXPLAIN ANALYZE
SELECT COUNT(*) FROM simple r JOIN awkwardly_skewed s USING (id);

-- parallel with parallel-oblivious hash join
SET max_parallel_workers_per_gather = 8;
SET parallel_synchronization_cost = 1000000000;
SET work_mem = '1MB';
EXPLAIN ANALYZE
SELECT COUNT(*) FROM simple r JOIN awkwardly_skewed s USING (id);
SELECT COUNT(*) FROM simple r JOIN awkwardly_skewed s USING (id);

-- parallel with parallel-aware hash join
SET max_parallel_workers_per_gather = 8;
RESET parallel_synchronization_cost;
SET work_mem = '1MB';
EXPLAIN ANALYZE
SELECT COUNT(*) FROM simple r JOIN awkwardly_skewed s USING (id);
SELECT COUNT(*) FROM simple r JOIN awkwardly_skewed s USING (id);

