Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Partitioning benchmark
CREATE TABLE journal (
id SERIAL PRIMARY KEY,
dt TIMESTAMP NOT NULL,
level INTEGER,
msg TEXT
);
CREATE INDEX journal_dt_idx ON journal (dt);
SELECT create_parent('public.journal', 'dt', 'time', 'daily', NULL, 290, NULL, '2016-01-01');
INSERT INTO journal (dt, level, msg)
SELECT g, random()*6, md5(g::text)
FROM generate_series('2016-01-01'::date, '2016-12-31'::date, '30 seconds') as g;
CREATE TABLE journal (
id SERIAL PRIMARY KEY,
dt TIMESTAMP NOT NULL,
level INTEGER,
msg TEXT
);
CREATE INDEX journal_dt_idx ON journal (dt);
SELECT create_range_partitions('journal', 'dt', '2016-01-01'::date, '1 day'::interval, 365);
INSERT INTO journal (dt, level, msg)
SELECT g, random()*6, md5(g::text)
FROM generate_series('2016-01-01'::date, '2016-12-31'::date, '30 seconds') as g;
CREATE TABLE journal (
id SERIAL PRIMARY KEY,
dt TIMESTAMP NOT NULL,
level INTEGER,
msg TEXT
);
CREATE INDEX journal_dt_idx ON journal (dt);
INSERT INTO journal (dt, level, msg)
SELECT g, random()*6, md5(g::text)
FROM generate_series('2016-01-01'::date, '2016-12-31'::date, '30 seconds') as g;
\setrandom days 0 364
\setrandom seconds 0 86400
INSERT INTO journal (dt, level, msg) SELECT '2016-01-01'::date + '1 second'::interval * :seconds + '1 day'::interval * :days, ceil(6 * random()), md5((random()*10000)::text);
\setrandom day 0 365
SELECT * FROM journal WHERE dt >= '2016-01-01'::date + '1 day'::interval * :day AND dt < '2016-01-01'::date + '1 day'::interval * :day + '1 day';
\setrandom days 0 365
\setrandom minutes 0 1440
SELECT * FROM journal WHERE dt = '2016-01-01'::date + '1 day'::interval * :days + '1 minute'::interval * :minutes;
\setrandom days 0 364
\setrandom halfminutes 0 1440
UPDATE journal SET level = ceil(6 * random()), msg = md5((random()*10000)::text) WHERE dt = '2016-01-01'::date + '1 minute'::interval * :minutes + '1 day'::interval * :days;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment