CREATE EXTENSION IF NOT EXISTS CUBE ;

DROP TABLE IF EXISTS TESTTABLE ;
DROP OPERATOR CLASS IF EXISTS gist_cube_ops_nocompress USING GIST;

CREATE OPERATOR CLASS gist_cube_ops_nocompress
    FOR TYPE CUBE USING GIST AS
	OPERATOR	3	&& ,
	OPERATOR	6	= ,
	OPERATOR	7	@> ,
	OPERATOR	8	<@ ,
	OPERATOR	13	@ ,
	OPERATOR	14	~ ,

	FUNCTION	1	g_cube_consistent (INTERNAL, CUBE, SMALLINT, OID, INTERNAL),
	FUNCTION	2	g_cube_union (INTERNAL, INTERNAL),
	--we do not have functions 3 and for (compress and decompress)
	FUNCTION	5	g_cube_penalty (INTERNAL, INTERNAL, INTERNAL),
	FUNCTION	6	g_cube_picksplit (INTERNAL, INTERNAL),
	FUNCTION	7	g_cube_same (CUBE, CUBE, INTERNAL),
	FUNCTION	8	g_cube_distance (INTERNAL, CUBE, SMALLINT, OID, INTERNAL),
	FUNCTION	9	g_cube_decompress (INTERNAL);--fetch function, not for compression



BEGIN;
SELECT SETSEED(0.5);

CREATE TABLE TESTTABLE AS SELECT CUBE(RANDOM()) C FROM GENERATE_SERIES(1,500000) I;

\timing

--testing time to create compressed index
CREATE INDEX COMPRESSED ON TESTTABLE USING GIST(C gist_cube_ops);
--testing time to create uncompressed index
CREATE INDEX UNCOMPRESSED ON TESTTABLE USING GIST(C gist_cube_ops_nocompress);

\timing
set enable_bitmapscan = false;

UPDATE PG_INDEX SET INDISVALID = FALSE WHERE INDEXRELID = 'UNCOMPRESSED'::REGCLASS;
UPDATE PG_INDEX SET INDISVALID = TRUE WHERE INDEXRELID = 'COMPRESSED'::REGCLASS;

EXPLAIN ANALYZE select (SELECT COUNT(*) FROM TESTTABLE WHERE C <@ CUBE(-b,1)) from generate_series(1,50) b;
EXPLAIN ANALYZE select (SELECT COUNT(*) FROM TESTTABLE WHERE C <@ CUBE(-b,1)) from generate_series(1,50) b;

UPDATE PG_INDEX SET INDISVALID = TRUE WHERE INDEXRELID = 'UNCOMPRESSED'::REGCLASS;
UPDATE PG_INDEX SET INDISVALID = FALSE WHERE INDEXRELID = 'COMPRESSED'::REGCLASS;

EXPLAIN ANALYZE select (SELECT COUNT(*) FROM TESTTABLE WHERE C <@ CUBE(-b,1)) from generate_series(1,50) b;
EXPLAIN ANALYZE select (SELECT COUNT(*) FROM TESTTABLE WHERE C <@ CUBE(-b,1)) from generate_series(1,50) b;

COMMIT;