select version();
version
----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 11.9 (Ubuntu 11.9-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-10ubuntu2) 9.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
WITH serie(i) AS (SELECT generate_series(55000000,1000001,-1))
insert into geants
select
gen_random_uuid(),
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
create index geants_i1 on geants(heureux);
create index geants_i2 on geants(veteran);
create index geants_i3 on geants(berserk);
create index geants_i4 on geants(cyclope);
create index geants_i5 on geants(ere);
create index geants_i6 on geants(revenu);
create index geants_i7 on geants(dtn);
create index geants_i8 on geants(actif);
create index geants_i9 on geants(couleur);
create index geants_i10 on geants(genre);
create index geants_i11 on geants(gabarit);
\d+
Liste des relations
Schéma | Nom | Type | Propriétaire | Persistence | Taille | Description
----------+--------+-------+--------------+-------------+---------+-------------
postgres | geants | table | postgres | permanent | 6918 MB |
\di+
Liste des relations
Schéma | Nom | Type | Propriétaire | Table | Persistence | Taille | Description
----------+------------+-------+--------------+--------+-------------+---------+-------------
postgres | geants_i1 | index | postgres | geants | permanent | 1157 MB |
postgres | geants_i10 | index | postgres | geants | permanent | 1157 MB |
postgres | geants_i11 | index | postgres | geants | permanent | 1157 MB |
postgres | geants_i2 | index | postgres | geants | permanent | 1157 MB |
postgres | geants_i3 | index | postgres | geants | permanent | 1157 MB |
postgres | geants_i4 | index | postgres | geants | permanent | 1157 MB |
postgres | geants_i5 | index | postgres | geants | permanent | 1157 MB |
postgres | geants_i6 | index | postgres | geants | permanent | 1157 MB |
postgres | geants_i7 | index | postgres | geants | permanent | 1157 MB |
postgres | geants_i8 | index | postgres | geants | permanent | 1157 MB |
postgres | geants_i9 | index | postgres | geants | permanent | 1157 MB |
\l+
Liste des bases de données
Nom | Propriétaire | Encodage | Collationnement | Type caract. | Droits d'accès | Taille | Tablespace | Description
-----------+--------------+----------+-----------------+--------------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | | 19 GB | pg_default | default administrative connection database