btree_compression_test.sql

CREATE OR REPLACE FUNCTION test_btbuild(n_values INT) RETURNS VOID AS
$$
	DECLARE
		n_distinct_values INT;
		size BIGINT;
		size_pretty TEXT;
		time TEXT;
	BEGIN
		n_distinct_values = n_values;
		LOOP
			CREATE TABLE tbl (c1 int);
			RAISE NOTICE 'n_distinct_values is %', n_distinct_values;

			EXECUTE 'INSERT INTO tbl SELECT c1%'
			|| quote_literal(n_distinct_values)
			|| 'from generate_series(1,'
			|| quote_literal(n_values)
			|| ') as c1;';

			SELECT INTO time timeofday();
    		RAISE NOTICE 'Time: %', time;

			CREATE INDEX idx ON tbl using btree(c1);

			SELECT INTO time timeofday();
    		RAISE NOTICE 'Time: %', time;

			SELECT INTO size pg_relation_size('idx');
			SELECT INTO size_pretty pg_size_pretty(pg_relation_size('idx'));
			RAISE NOTICE 'idxsize is % = %', size, size_pretty;

			DROP TABLE tbl;
			n_distinct_values = n_distinct_values/10;

			IF n_distinct_values = 0 THEN
        		EXIT;
    		END IF;
		END LOOP;
	END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION test_ginbuild(n_values INT) RETURNS VOID AS
$$
	DECLARE
		n_distinct_values INT;
		size BIGINT;
		size_pretty TEXT;
		time TEXT;
	BEGIN
		n_distinct_values = n_values;
		LOOP
			CREATE TABLE tbl (c1 int);
			RAISE NOTICE 'n_distinct_values is %', n_distinct_values;

			EXECUTE 'INSERT INTO tbl SELECT c1%'
			|| quote_literal(n_distinct_values)
			|| 'from generate_series(1,'
			|| quote_literal(n_values)
			|| ') as c1;';

			SELECT INTO time timeofday();
    		RAISE NOTICE 'Time: %', time;

			CREATE INDEX idx ON tbl using gin(c1);

			SELECT INTO time timeofday();
    		RAISE NOTICE 'Time: %', time;

    		SELECT INTO size pg_relation_size('idx');
			SELECT INTO size_pretty pg_size_pretty(pg_relation_size('idx'));
			RAISE NOTICE 'idxsize is % = %', size, size_pretty;

			DROP TABLE tbl;
			n_distinct_values = n_distinct_values/10;
			IF n_distinct_values = 0 THEN
        		EXIT;
    		END IF;
		END LOOP;
	END;
$$ LANGUAGE plpgsql;




CREATE OR REPLACE FUNCTION test_btinsert(n_values INT) RETURNS VOID AS
$$
	DECLARE
		n_distinct_values INT;
		size BIGINT;
		size_pretty TEXT;
		time TEXT;
	BEGIN
		n_distinct_values = n_values;
		LOOP
			CREATE TABLE tbl (c1 int);
			RAISE NOTICE 'n_distinct_values is %', n_distinct_values;

			CREATE INDEX idx ON tbl using btree(c1);

			SELECT INTO time timeofday();
    		RAISE NOTICE 'Time: %', time;

			EXECUTE 'INSERT INTO tbl SELECT c1%'
			|| quote_literal(n_distinct_values)
			|| 'from generate_series(1,'
			|| quote_literal(n_values)
			|| ') as c1;';

			SELECT INTO time timeofday();
    		RAISE NOTICE 'Time: %', time;

			SELECT INTO size pg_relation_size('idx');
			SELECT INTO size_pretty pg_size_pretty(pg_relation_size('idx'));
			RAISE NOTICE 'idxsize is % = %', size, size_pretty;

			DROP TABLE tbl;
			n_distinct_values = n_distinct_values/10;

			IF n_distinct_values = 0 THEN
        		EXIT;
    		END IF;
		END LOOP;
	END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION test_gininsert(n_values INT) RETURNS VOID AS
$$
	DECLARE
		n_distinct_values INT;
		size BIGINT;
		size_pretty TEXT;
		time TEXT;
	BEGIN
		n_distinct_values = n_values;
		LOOP
			CREATE TABLE tbl (c1 int);
			RAISE NOTICE 'n_distinct_values is %', n_distinct_values;

			CREATE INDEX idx ON tbl using gin(c1);

			SELECT INTO time timeofday();
    		RAISE NOTICE 'Time: %', time;

			EXECUTE 'INSERT INTO tbl SELECT c1%'
			|| quote_literal(n_distinct_values)
			|| 'from generate_series(1,'
			|| quote_literal(n_values)
			|| ') as c1;';

			SELECT INTO time timeofday();
    		RAISE NOTICE 'Time: %', time;

    		SELECT INTO size pg_relation_size('idx');
			SELECT INTO size_pretty pg_size_pretty(pg_relation_size('idx'));
			RAISE NOTICE 'idxsize is % = %', size, size_pretty;

			DROP TABLE tbl;
			n_distinct_values = n_distinct_values/10;
			IF n_distinct_values = 0 THEN
        		EXIT;
    		END IF;
		END LOOP;
	END;
$$ LANGUAGE plpgsql;