Created
August 6, 2019 19:36
-
-
Save feymartynov/cddedf3508d7415cb0b94d6c8157b11b to your computer and use it in GitHub Desktop.
INT vs VRCHAR vs ENUM
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
BEGIN; | |
--------- | |
-- INT -- | |
--------- | |
CREATE TABLE room_int ( | |
id UUID PRIMARY KEY, | |
time TSTZRANGE NOT NULL, | |
audience TEXT NOT NULL, | |
created_at TIMESTAMPTZ NOT NULL, | |
backend INT NOT NULL | |
); | |
WITH backends AS ( | |
SELECT '{0,1}'::INT[] AS values | |
) | |
INSERT INTO room_int (id, time, audience, created_at, backend) | |
SELECT | |
MD5(RANDOM()::TEXT || CLOCK_TIMESTAMP()::TEXT)::UUID AS id, | |
'[,)' AS time, | |
'dev.svc.example.org' AS audience, | |
NOW() AS created_at, | |
backends.values[1 + FLOOR(RANDOM() * ARRAY_LENGTH(backends.values, 1))::INT] AS backend | |
FROM GENERATE_SERIES(1, 1000000) AS i, | |
backends; | |
------------- | |
-- VARCHAR -- | |
------------- | |
CREATE TABLE room_varchar ( | |
id UUID PRIMARY KEY, | |
time TSTZRANGE NOT NULL, | |
audience TEXT NOT NULL, | |
created_at TIMESTAMPTZ NOT NULL, | |
backend VARCHAR(32) NOT NULL | |
); | |
WITH backends AS ( | |
SELECT '{none,janus}'::VARCHAR[] AS values | |
) | |
INSERT INTO room_varchar (id, time, audience, created_at, backend) | |
SELECT | |
MD5(RANDOM()::TEXT || CLOCK_TIMESTAMP()::TEXT)::UUID AS id, | |
'[,)' AS time, | |
'dev.svc.example.org' AS audience, | |
NOW() AS created_at, | |
backends.values[1 + FLOOR(RANDOM() * ARRAY_LENGTH(backends.values, 1))::INT] AS backend | |
FROM GENERATE_SERIES(1, 1000000) AS i, | |
backends; | |
---------- | |
-- ENUM -- | |
---------- | |
CREATE TYPE room_enum_backend AS ENUM ('none', 'janus'); | |
CREATE TABLE room_enum ( | |
id UUID PRIMARY KEY, | |
time TSTZRANGE NOT NULL, | |
audience TEXT NOT NULL, | |
created_at TIMESTAMPTZ NOT NULL, | |
backend room_enum_backend NOT NULL | |
); | |
WITH backends AS ( | |
SELECT '{none,janus}'::room_enum_backend[] AS values | |
) | |
INSERT INTO room_enum (id, time, audience, created_at, backend) | |
SELECT | |
MD5(RANDOM()::TEXT || CLOCK_TIMESTAMP()::TEXT)::UUID AS id, | |
'[,)' AS time, | |
'dev.svc.example.org' AS audience, | |
NOW() AS created_at, | |
backends.values[1 + FLOOR(RANDOM() * ARRAY_LENGTH(backends.values, 1))::INT] AS backend | |
FROM GENERATE_SERIES(1, 1000000) AS i, | |
backends; | |
COMMIT; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
EXPLAIN ANALYZE SELECT * FROM room_int WHERE backend = 1; | |
-- Seq Scan on room_int (cost=0.00..23864.00 rows=497133 width=54) (actual time=0.054..143.247 rows=499851 loops=1) | |
-- Filter: (backend = 1) | |
-- Rows Removed by Filter: 500149 | |
-- Planning Time: 0.069 ms | |
-- Execution Time: 172.262 ms | |
EXPLAIN ANALYZE SELECT * FROM room_varchar WHERE backend = 'janus'; | |
-- Seq Scan on room_varchar (cost=0.00..23864.00 rows=496000 width=55) (actual time=0.043..147.896 rows=499763 loops=1) | |
-- Filter: ((backend)::text = 'janus'::text) | |
-- Rows Removed by Filter: 500237 | |
-- Planning Time: 0.058 ms | |
-- Execution Time: 176.790 ms | |
EXPLAIN ANALYZE SELECT * FROM room_enum WHERE backend = 'janus'; | |
-- Seq Scan on room_enum (cost=0.00..23864.00 rows=499533 width=54) (actual time=0.027..123.763 rows=500229 loops=1) | |
-- Filter: (backend = 'janus'::room_enum_backend) | |
-- Rows Removed by Filter: 499771 | |
-- Planning Time: 0.084 ms | |
-- Execution Time: 153.067 ms |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Size seems to be the same: