Wide Table benchmark

wide-table.sql

-- 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