setup.sql

application/sql

Download

ALTER SYSTEM SET shared_buffers TO '8GB';
ALTER SYSTEM SET work_mem TO '1GB';
ALTER SYSTEM SET max_parallel_workers_per_gather TO 0;
ALTER SYSTEM SET hash_mem_multiplier TO 1;

DO $$
DECLARE
    type TEXT;
    generate TEXT;
    amount TEXT;
    table_name TEXT;
BEGIN
    FOR amount IN VALUES ('100'), ('1000'), ('10000'), ('100000'), ('1000000') LOOP
    FOR type, generate IN VALUES    ('int', '(random() * ' || amount || ')::int'),
                                    ('bigint', '(random() * ' || amount || ')::bigint'),
                                    ('uuid', 'gen_random_uuid()'),
                                    ('text', 'md5(i::text)') LOOP
        PERFORM setseed(0.42);

        table_name := type || amount;
        EXECUTE 'CREATE TABLE ' || table_name || '(i ' || type || ', j int)';
        EXECUTE 'INSERT INTO ' || table_name ||
                ' SELECT ' || generate || ', j FROM generate_series(1, ' || amount || ') i, generate_series(1, 3) j;';
        EXECUTE 'ANALYZE ' || table_name;
    END LOOP;
    END LOOP;
END
$$;

CREATE TABLE bigtext(i text, j int);
INSERT INTO bigtext SELECT repeat(md5(i::text), 1024), j FROM generate_series(1, 100000) i, generate_series(1, 3) j;
ANALYZE bigtext;