select version();
version
----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 11.2 (Ubuntu 11.2-2.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.3.0-27ubuntu1~18.04) 7.3.0, 64-bit
create table geants(
idu uuid,
dtn date,
genre char(1),
taille smallint,
masse smallint,
actif boolean,
devise varchar(128),
pw smallint,
heureux boolean,
couleur varchar(8),
veteran boolean,
clan smallint,
gabarit varchar(8),
revenu integer,
pm smallint,
berserk boolean,
tutelaire smallint,
ere varchar(10),
cyclope boolean);
CREATE TABLE
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
psql: NOTICE: l'extension « uuid-ossp » existe déjà, poursuite du traitement
CREATE EXTENSION
WITH serie(i) AS (SELECT generate_series(5500000,100001,-1))
insert into geants
select
uuid_generate_v4(),
current_date - (ceil(i/3) || ' minutes')::interval + (trunc(random() * 100 + 1) || ' days')::interval,
case when random() < 0.45 then 'M' else 'F' end,
200 + (trunc(random() * 200 + 1)),
300 + (trunc(random() * 200 + 1)),
case when random() < 0.7 then false else true end,
upper(md5(random()::text)),
(trunc(random()*100 + 1)),
case when random() < 0.1 then false else true end,
case when random() < 0.7 then 'GRIS' when random() < 0.8 then 'NOIR' else 'BLEU' end,
case when random() < 0.9 then false else true end,
(trunc(random()*1000 + 1)),
case when random() < 0.1 then 'PETIT' when random() < 0.9 then 'MOYEN' else 'GRAND' end,
(trunc(random()*1000000 + 1)),
(trunc(random()*10 + 1)),
case when random() < 0.01 then true else false end,
(trunc(random()*10 + 1)),
case when i < 18000000 then 'TAUREAU' when i < 40000000 then 'LICORNE' else 'DRAGON' end,
case when random() < 0.001 then true else false end
from serie;
INSERT 0 5400000
select avg(taille) from geants where dtn >= (current_date - interval '500 days') and genre = 'M';
avg
----------------------
300.5961816218027194
(1 ligne)
Temps : 367,581 ms
select avg(taille) from geants where dtn >= (current_date - interval '500 days') and genre = 'F';
avg
----------------------
300.5453445920576464
(1 ligne)
Temps : 405,593 ms
select avg(masse) from geants where dtn >= (current_date - interval '500 days') and actif;
avg
----------------------
400.5705109297197845
(1 ligne)
Temps : 311,944 ms
select avg(masse) from geants where dtn >= (current_date - interval '500 days') and not actif;
avg
----------------------
400.4414018601866963
(1 ligne)
Temps : 438,126 ms
select count(*) from geants where dtn >= (current_date - interval '500 days') and revenu > 900000;
count
--------
227384
(1 ligne)
Temps : 298,046 ms
select count(*) from geants where dtn >= (current_date - interval '500 days') and berserk and veteran;
count
-------
2235
(1 ligne)
Temps : 260,555 ms