lz4.sql

application/octet-stream

Download

SET enable_parallel_hash = OFF;
SET enable_mergejoin = OFF;
SET enable_nestloop TO off;

create database huge_tables;
\c huge_tables 
CREATE TABLE table_a (id BIGINT PRIMARY KEY,data TEXT);
CREATE TABLE table_b  (id BIGINT PRIMARY KEY, data TEXT);
INSERT INTO table_a (id, data) SELECT i, repeat('ABCD', 100)  -- "ABCDABCD..." repeated 100 times
FROM generate_series(1, 10000000) AS i;
-- Insert a similar repeated pattern into table_b
INSERT INTO table_b (id, data) SELECT i, repeat('EFGH', 100)  -- "EFGHEFGH..." repeated 100 times
FROM generate_series(1, 10000000) AS i;
-- Insert overlapping data in table_b 
INSERT INTO table_b (id, data) SELECT i, repeat('ABCD', 100)  -- "ABCDABCD..." repeated 100 times
FROM generate_series(10000001, 20000000) AS i;

set temp_file_compression = "no";
SELECT datname, temp_bytes / 1024 / 1024 AS temp_bytes_mb FROM pg_stat_database;
SELECT pg_stat_reset();

set temp_file_compression = "lz4";
EXPLAIN ANALYZE SELECT a.id, a.data, b.data FROM table_a a JOIN table_b b ON a.id = b.id;

SELECT datname, temp_bytes / 1024 / 1024 AS temp_bytes_mb FROM pg_stat_database;
SELECT pg_stat_reset();

create database random;
\c random;
CREATE TABLE table_a (id SERIAL PRIMARY KEY, data_blob TEXT);
CREATE TABLE table_b (id SERIAL PRIMARY KEY,data_blob TEXT);
INSERT INTO table_a (data_blob) SELECT md5(random()::text || clock_timestamp()::text) FROM generate_series(1, 100000);
INSERT INTO table_a (data_blob) SELECT md5(random()::text || clock_timestamp()::text) FROM generate_series(1, 10000);


-- EXPLAIN ANALYZE SELECT a.id, b.id FROM table_a a JOIN table_b b ON a.id = b.id;