hj-test-queries.sql

-- Make a simple relation with well distributed keys and correctly estimated size.
DROP TABLE IF EXISTS simple;
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.
DROP TABLE IF EXISTS bigger_than_it_looks;
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 radix partitioning schemes.  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.
DROP TABLE IF EXISTS awkwardly_skewed;
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


-- Provoke the "good" case -- sequential
SET max_parallel_workers_per_gather = 0;
SET work_mem = '64MB';
EXPLAIN ANALYZE
SELECT COUNT(*) FROM simple r JOIN simple s USING (id);

-- Provoke the "good" case -- parallel
SET max_parallel_workers_per_gather = 8;
SET work_mem = '64MB';
EXPLAIN ANALYZE
SELECT COUNT(*) FROM simple r JOIN simple s USING (id);




-- Provoke the "bad" case -- sequential
SET max_parallel_workers_per_gather = 0;
SET work_mem = '1MB';
EXPLAIN ANALYZE
SELECT COUNT(*) FROM simple r JOIN simple s USING (id);

-- Provoke the "bad" case -- parallel
SET max_parallel_workers_per_gather = 8;
SET work_mem = '1MB';
EXPLAIN ANALYZE
SELECT COUNT(*) FROM simple r JOIN simple s USING (id);




-- Provoke the "ugly" case -- serial
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);

-- Provoke the "ugly" case -- parallel
SET max_parallel_workers_per_gather = 8;
SET work_mem = '1MB';
EXPLAIN ANALYZE
SELECT COUNT(*) FROM simple r JOIN bigger_than_it_looks s USING (id);



-- Provoke the "fail" case -- serial
SET max_parallel_workers_per_gather = 0;
SET work_mem = '1MB';
EXPLAIN ANALYZE
SELECT COUNT(*) FROM simple r JOIN awkwardly_skewed s USING (id);

-- Provoke the "fail" case -- parallel
SET max_parallel_workers_per_gather = 8;
SET work_mem = '1MB';
EXPLAIN ANALYZE
SELECT COUNT(*) FROM simple r JOIN awkwardly_skewed s USING (id);