Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
create or replace function propagate_table_with_data()
RETURNS integer AS $$
DECLARE
count integer;
sql text;
begin
count = 1;
LOOP
sql = 'insert into computer(computer_id, computer_ram, cpu_id, video_id, bigstring) values';
sql = sql || '('|| count ||', ' || random()*1024 || ', 1, 1, repeat(''P'', 1000))';
EXECUTE sql;
count = count + 1;
EXIT WHEN count > 100000000; -- create 100M rows,
END LOOP;
return count;
END;
$$ LANGUAGE plpgsql;
drop table computer;
create table computer (
computer_id serial primary key, -- 4B
computer_ram integer, -- 4B
bigstring TEXT,
cpu_id integer, --4B
video_id integer -- 4B
); -- 40 B totalper row
-- vacuum computer; -- unneeded after a truncate, apparently: http://www.postgresql.org/docs/8.4/static/sql-truncate.html
select * from propagate_table_with_data();
SELECT reltuples FROM pg_class WHERE relname = 'computer';
-- perform experiments here:
-- XXXs to create...
truncate table computer; -- 100M 5516ms, + 76ms to drop
drop table computer; -- 5680ms + 381ms to recreate
-- delete from computer; -- known to be super slow
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment