test-size.sql

text/x-sql

Download

\x
DROP TABLE test;
CREATE TABLE test(s SERIAL PRIMARY KEY,d TEXT);
CREATE INDEX i on TEST(s);

INSERT INTO test(d) SELECT 
  array_to_string(array(
    SELECT 
      chr(ascii('A') + (random() * 64)::integer) 
    FROM generate_series(1,20000)),'')  -- Size
  FROM generate_series(1,1000);  -- Rows

INSERT INTO test(d) SELECT 
  array_to_string(array(
    SELECT 
      chr(ascii('A') + (random() * 64)::integer) 
    FROM generate_series(1,1000)),'')  -- Size
  FROM generate_series(1,10000);  -- Rows

--insert into test (d) SELECT repeat('xyz123'::text,(1+random() * 1000)::integer) FROM generate_series(1,100000);

ANALYZE test;

SELECT pg_relation_size(relname::regclass),relpages * 8192 AS pages_size FROM pg_class where relname='test';
SELECT pg_table_size('test'::regclass) - pg_relation_size('test'::regclass) AS toast_and_fsm;
SELECT pg_table_size('test'::regclass);
SELECT pg_indexes_size('test'::regclass),pg_relation_size('test_pkey'::regclass) as pkey,pg_relation_size('i'::regclass) AS i;
SELECT pg_total_relation_size('test'::regclass),pg_table_size('test'::regclass)+pg_indexes_size('test'::regclass) AS computed_total;