select version();
version
---------------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 11beta1 (Ubuntu 11~beta1-2.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit
(1 ligne)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION
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
WITH serie(i) AS (SELECT generate_series(55000000,1000001,-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 54000000
ANALYZE geants;
ANALYZE
explain SELECT idu, devise FROM geants WHERE not actif AND not heureux AND veteran AND berserk AND cyclope;
QUERY PLAN
-------------------------------------------------------------------------------------
Gather (cost=1000.00..1111246.30 rows=3 width=49)
Workers Planned: 2
-> Parallel Seq Scan on geants (cost=0.00..1110246.00 rows=1 width=49)
Filter: ((NOT actif) AND (NOT heureux) AND veteran AND berserk AND cyclope)
JIT:
Functions: 4
Inlining: true
Optimization: true
set jit=off;
SET
explain SELECT idu, devise FROM geants WHERE not actif AND not heureux AND veteran AND berserk AND cyclope;
QUERY PLAN
-------------------------------------------------------------------------------------
Gather (cost=1000.00..1111246.30 rows=3 width=49)
Workers Planned: 2
-> Parallel Seq Scan on geants (cost=0.00..1110246.00 rows=1 width=49)
Filter: ((NOT actif) AND (NOT heureux) AND veteran AND berserk AND cyclope)
SELECT idu, devise FROM geants WHERE not actif AND not heureux AND veteran AND berserk AND cyclope;
SELECT count(*) FROM geants WHERE ere = 'LICORNE';
SELECT avg(taille) FROM geants WHERE revenu > 800000;
SELECT mode() WITHIN GROUP(ORDER BY actif) FROM geants WHERE dtn > CURRENT_TIMESTAMP - interval '10 years';
SELECT mode() WITHIN GROUP(ORDER BY cyclope) FROM geants WHERE dtn > CURRENT_TIMESTAMP - interval '10 years' and dtn < CURRENT_TIMESTAMP - interval '9 years';
SELECT avg(masse) FROM geants WHERE not actif;
SELECT count(*) FROM geants WHERE couleur = 'NOIR';
SELECT count(*) FROM geants WHERE genre = 'M' OR berserk;
SELECT count(*) FROM geants WHERE gabarit = 'MOYEN';
-- 11 jit=off 1 warm up run then 10 runs avg 54745.533 ms
-- 11 jit=on 1 warm up run then 10 runs avg 45155.808 ms
create index geants_br1 on geants using brin(dtn);
CREATE INDEX
create index geants_br2 on geants using brin(ere);
CREATE INDEX
-- 11 jit=off 1 warm up run then 10 runs avg 40162.014 ms
-- 11 jit=on 1 warm up run then 10 runs avg 32599.367 ms