Skip to content

Instantly share code, notes, and snippets.

@florianherrengt
Last active November 26, 2022 13:13
Embed
What would you like to do?
Wide Table benchmark
-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS events_id_seq;
CREATE SEQUENCE IF NOT EXISTS countries_id_seq;
CREATE SEQUENCE IF NOT EXISTS browsers_id_seq;
CREATE SEQUENCE IF NOT EXISTS devices_id_seq;
-- Table Definition
CREATE TABLE "public"."countries" (
"id" int4 NOT NULL DEFAULT nextval('countries_id_seq'::regclass),
"name" varchar,
PRIMARY KEY ("id")
);
CREATE TABLE "public"."browsers" (
"id" int4 NOT NULL DEFAULT nextval('browsers_id_seq'::regclass),
"name" varchar,
PRIMARY KEY ("id")
);
CREATE TABLE "public"."devices" (
"id" int4 NOT NULL DEFAULT nextval('devices_id_seq'::regclass),
"name" varchar,
PRIMARY KEY ("id")
);
CREATE TABLE "public"."events" (
"id" int4 NOT NULL DEFAULT nextval('events_id_seq'::regclass),
"name" varchar,
"value" int,
"country_id" int,
"browser_id" int,
"device_id" int,
PRIMARY KEY ("id")
);
CREATE TABLE "public"."events_wide" (
"id" int4 NOT NULL DEFAULT nextval('events_id_seq'::regclass),
"name" varchar,
"value" int,
"country" varchar,
"browser" varchar,
"device" varchar,
PRIMARY KEY ("id")
);;
-- Data
insert into browsers (name) values
('Firefox'),
('Samsung'),
('Chrome'),
('Opera'),
('Safari'),
('Yandex'),
('Internet'),
('Edge'),
('Edge'),
('Edge'),
('Vivaldi'),
('Brave'),
('UC'),
('Pale'),
('Android'),
('iOS'),
('Opera'),
('Unknown'),
('Silk'),
('Amigo'),
('Waterfox'),
('KaiOS');
insert into countries (name) values
('Afghanistan'),
('Albania'),
('Algeria'),
('American'),
('Andorra'),
('Angola'),
('Anguilla'),
('Antarctica'),
('Antigua'),
('Argentina'),
('Armenia'),
('Aruba'),
('Australia'),
('Austria'),
('Azerbaijan'),
('Bahamas'),
('Bahrain'),
('Bangladesh'),
('Barbados'),
('Belarus'),
('Belgium'),
('Belize'),
('Benin'),
('Bermuda'),
('Bhutan'),
('Bolivia'),
('Bonaire'),
('Bosnia'),
('Botswana'),
('Bouvet'),
('Brazil'),
('British'),
('Brunei'),
('Bulgaria'),
('Burkina'),
('Burundi'),
('Cabo'),
('Cambodia'),
('Cameroon'),
('Canada'),
('Cayman'),
('Central'),
('Chad'),
('Chile'),
('China'),
('Christmas'),
('Cocos'),
('Colombia'),
('Comoros'),
('Congo'),
('Congo'),
('Cook'),
('Costa'),
('Croatia'),
('Cuba'),
('Curaçao'),
('Cyprus'),
('Czechia'),
('Côte'),
('Denmark'),
('Djibouti'),
('Dominica'),
('Dominican'),
('Ecuador'),
('Egypt'),
('El'),
('Equatorial'),
('Eritrea'),
('Estonia'),
('Eswatini'),
('Ethiopia'),
('Falkland'),
('Faroe'),
('Fiji'),
('Finland'),
('France'),
('French'),
('French'),
('French'),
('Gabon'),
('Gambia'),
('Georgia'),
('Germany'),
('Ghana'),
('Gibraltar'),
('Greece'),
('Greenland'),
('Grenada'),
('Guadeloupe'),
('Guam'),
('Guatemala'),
('Guernsey'),
('Guinea'),
('Guinea'),
('Guyana'),
('Haiti'),
('Heard'),
('Holy'),
('Honduras'),
('Hong'),
('Hungary'),
('Iceland'),
('India'),
('Indonesia'),
('Iran'),
('Iraq'),
('Ireland'),
('Isle'),
('Israel'),
('Italy'),
('Jamaica'),
('Japan'),
('Jersey'),
('Jordan'),
('Kazakhstan'),
('Kenya'),
('Kiribati'),
('Korea'),
('Korea'),
('Kuwait'),
('Kyrgyzstan'),
('Lao'),
('Latvia'),
('Lebanon'),
('Lesotho'),
('Liberia'),
('Libya'),
('Liechtenstein'),
('Lithuania'),
('Luxembourg'),
('Macao'),
('Madagascar'),
('Malawi'),
('Malaysia'),
('Maldives'),
('Mali'),
('Malta'),
('Marshall'),
('Martinique'),
('Mauritania'),
('Mauritius'),
('Mayotte'),
('Mexico'),
('Micronesia'),
('Moldova'),
('Monaco'),
('Mongolia'),
('Montenegro'),
('Montserrat'),
('Morocco'),
('Mozambique'),
('Myanmar'),
('Namibia'),
('Nauru'),
('Nepal'),
('Netherlands'),
('New'),
('New'),
('Nicaragua'),
('Niger'),
('Nigeria'),
('Niue'),
('Norfolk'),
('Northern'),
('Norway'),
('Oman'),
('Pakistan'),
('Palau'),
('Palestine'),
('Panama'),
('Papua'),
('Paraguay'),
('Peru'),
('Philippines'),
('Pitcairn'),
('Poland'),
('Portugal'),
('Puerto'),
('Qatar'),
('Republic'),
('Romania'),
('Russian'),
('Rwanda'),
('Saint'),
('Samoa'),
('San'),
('Sao'),
('Saudi'),
('Senegal'),
('Serbia'),
('Seychelles'),
('Sierra'),
('Singapore'),
('Sint'),
('Slovakia'),
('Slovenia'),
('Solomon'),
('Somalia'),
('South'),
('Spain'),
('Sri'),
('Sudan'),
('Suriname'),
('Svalbard'),
('Sweden'),
('Switzerland'),
('Syrian'),
('Taiwan'),
('Tajikistan'),
('Tanzania'),
('Thailand'),
('Timor'),
('Togo'),
('Tokelau'),
('Tonga'),
('Trinidad'),
('Tunisia'),
('Turkey'),
('Turkmenistan'),
('Turks'),
('Tuvalu'),
('Uganda'),
('Ukraine'),
('United Kingdom'),
('Uruguay'),
('Uzbekistan'),
('Vanuatu'),
('Venezuela'),
('Virgin'),
('Wallis'),
('Western'),
('Yemen'),
('Zambia'),
('Zimbabwe');
insert into devices (name)
select
md5(random()::text)
from generate_series(1, 100000) s(i);
insert into events (
name, value, country_id, browser_id, device_id
)
select
md5(random()::text),
floor(random() * 10 + 1)::int,
(SELECT id FROM countries ORDER BY random()+s LIMIT 1),
(SELECT id FROM browsers ORDER BY random()+s LIMIT 1),
(SELECT id FROM devices ORDER BY random()+s LIMIT 1)
from generate_series(1, 1000000) s(i);
insert into events (name, value, country_id, browser_id, device_id)
select name, value, country_id, browser_id, device_id from events;
create table events_wide as
select
events.id as id,
events.name as name,
events.value as value,
countries.name as country,
browsers.name as browser,
devices.name as device
from events
join countries on events.country_id = countries.id
join browsers on events.browser_id = browsers.id
join devices on events.device_id = devices.id
create index countries_name on countries(name);
create index browsers_name on browsers(name);
create index devices_name on devices(name);
create index events_country_id on events(country_id);
create index events_browser_id on events(browser_id);
create index events_device_id on events(device_id);
create index events_wide_country on events_wide(country);
create index events_wide_browser on events_wide(browser);
create index events_wide_device on events_wide(device);
-- cost=12406.82
explain select devices.name, count(devices.name)
from events
join countries on events.country_id = countries.id
join browsers on events.browser_id = browsers.id
join devices on events.device_id = devices.id
where browsers.name = 'Firefox' and countries.name = 'United Kingdom'
group by devices.name order by count desc;
-- cost=2030.21
explain select device, count(device)
from events_wide
where browser = 'Firefox' and country = 'United Kingdom'
group by device order by count desc
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment