select version();
version
---------------------------------------------------------------------------------------------------
PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-10) 6.3.0 20170321, 64-bit
(1 ligne)
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";
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.5259089808952008
(1 ligne)
Temps : 1704,752 ms
Temps : 1687,436 ms
Temps : 1699,131 ms
select avg(taille) from geants where dtn >= (current_date - interval '500 days') and genre = 'F';
avg
----------------------
300.5224571954215197
(1 ligne)
Temps : 1947,302 ms
Temps : 1946,471 ms
Temps : 1923,097 ms
select avg(masse) from geants where dtn >= (current_date - interval '500 days') and actif;
avg
----------------------
400.5495511178533237
(1 ligne)
Temps : 1247,134 ms
Temps : 1261,909 ms
Temps : 1244,002 ms
select avg(masse) from geants where dtn >= (current_date - interval '500 days') and not actif;
avg
----------------------
400.4168965184201251
(1 ligne)
Temps : 2129,949 ms
Temps : 2112,982 ms
Temps : 2119,222 ms
select count(*) from geants where dtn >= (current_date - interval '500 days') and revenu > 900000;
count
--------
228287
(1 ligne)
Temps : 1137,624 ms
Temps : 1102,817 ms
Temps : 1109,720 ms
select count(*) from geants where dtn >= (current_date - interval '500 days') and berserk and veteran;
count
-------
2256
(1 ligne)
Temps : 781,292 ms
Temps : 798,236 ms
Temps : 772,947 ms
explain select
avg(taille) filter (where genre = 'M') taille_moyenne_geants,
avg(taille) filter (where genre = 'F') taille_moyenne_geantes,
avg(masse) filter (where actif) masse_moyenne_actifs,
avg(masse) filter (where not actif) masse_moyenne_blesses,
count(*) filter (where revenu > 900000) nombre_riches,
count(*) filter (where berserk and veteran) nombre_guerriers_fous
from geants
where dtn >= (current_date - interval '500 days');
QUERY PLAN
--------------------------------------------------------------------------
Aggregate (cost=129910.46..129910.46 rows=1 width=144)
-> Seq Scan on geants (cost=0.00..118623.50 rows=501642 width=19)
Filter: (dtn >= (('now'::cstring)::date - '500 days'::interval))
(3 lignes)
select
avg(taille) filter (where genre = 'M') taille_moyenne_geants,
avg(taille) filter (where genre = 'F') taille_moyenne_geantes,
avg(masse) filter (where actif) masse_moyenne_actifs,
avg(masse) filter (where not actif) masse_moyenne_blesses,
count(*) filter (where revenu > 900000) nombre_riches,
count(*) filter (where berserk and veteran) nombre_guerriers_fous
from geants
where dtn >= (current_date - interval '500 days');
taille_moyenne_geants | taille_moyenne_geantes | masse_moyenne_actifs | masse_moyenne_blesses | nombre_riches | nombre_guerriers_fous
-----------------------+------------------------+----------------------+-----------------------+---------------+-----------------------
300.5259089808952008 | 300.5224571954215197 | 400.5495511178533237 | 400.4168965184201251 | 228287 | 2256
(1 ligne)
Temps : 3155,818 ms
Temps : 3167,542 ms
Temps : 3141,067 ms