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
You can’t perform that action at this time.