select version();
version
-----------------------------------------------------------------------------------------------------
PostgreSQL 10beta3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit
(1 ligne)
create table geants(
idg integer generated by default as identity,
idgu uuid,
dtn timestamp,
taille smallint,
devise varchar(128),
berserk boolean);
CREATE TABLE
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION
with recursive serie(i) as
(select 5500000
UNION ALL
select i - 1 from serie where i > 1000001)
insert into geants(idgu, dtn, taille, devise, berserk)
select
uuid_generate_v4(),
current_timestamp - (ceil(i/3) || ' minutes')::interval + (trunc(random() * 100 + 1) || ' days')::interval,
200 + (trunc(random() * 200 + 1)),
upper(md5(random()::text)),
case when random() < 0.001 then true else false end
from serie;
INSERT 0 4500000
create index geants_i1 on geants(dtn);
CREATE INDEX
\d geants
Table « public.geants »
Colonne | Type | Collationnement | NULL-able | Par défaut
---------+-----------------------------+-----------------+-----------+----------------------------------
idg | integer | | not null | generated by default as identity
idgu | uuid | | |
dtn | timestamp without time zone | | |
taille | smallint | | |
devise | character varying(128) | | |
berserk | boolean | | |
Index :
"geants_i1" btree (dtn)
analyze geants;
ANALYZE
\timing
Chronométrage activé.
-- requete 1
explain select avg(taille) from geants where to_char(dtn, 'MM/YYYY') = '05/2015';
QUERY PLAN
----------------------------------------------------------------------------------------
Finalize Aggregate (cost=84704.55..84704.56 rows=1 width=32)
-> Gather (cost=84704.33..84704.54 rows=2 width=32)
Workers Planned: 2
-> Partial Aggregate (cost=83704.33..83704.34 rows=1 width=32)
-> Parallel Seq Scan on geants (cost=0.00..83680.89 rows=9375 width=2)
Filter: (to_char(dtn, 'MM/YYYY'::text) = '05/2015'::text)
select avg(taille) from geants where to_char(dtn, 'MM/YYYY') = '05/2015';
avg
----------------------
300.5692719910785789
(1 ligne)
Durée : 4144,292 ms (00:04,144)
...
Durée : 3850,802 ms (00:03,851)
...
Durée : 4067,351 ms (00:04,067)
-- requete 2
explain select avg(taille) from geants where dtn >= to_timestamp('01/05/2015', 'DD/MM/YYYY') and dtn < to_timestamp('01/06/2015', 'DD/MM/YYYY');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=8159.07..8159.08 rows=1 width=32)
-> Index Scan using geants_i1 on geants (cost=0.44..7820.78 rows=135315 width=2)
Index Cond: ((dtn >= to_timestamp('01/05/2015'::text, 'DD/MM/YYYY'::text)) AND (dtn < to_timestamp('01/06/2015'::text, 'DD/MM/YYYY'::text)))
select avg(taille) from geants where dtn >= to_timestamp('01/05/2015', 'DD/MM/YYYY') and dtn < to_timestamp('01/06/2015', 'DD/MM/YYYY');
avg
----------------------
300.5692719910785789
(1 ligne)
Temps : 212,317 ms
...
Temps : 220,057 ms
...
Temps : 220,386 ms
-- requete 3
explain select avg(taille) from geants where dtn >= timestamp '01/05/2015' and dtn < timestamp '01/06/2015';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=8152.89..8152.90 rows=1 width=32)
-> Index Scan using geants_i1 on geants (cost=0.43..7814.73 rows=135262 width=2)
Index Cond: ((dtn >= '2015-05-01 00:00:00'::timestamp without time zone) AND (dtn < '2015-06-01 00:00:00'::timestamp without time zone))
select avg(taille) from geants where dtn >= timestamp '01/05/2015' and dtn < timestamp '01/06/2015';
avg
----------------------
300.5692719910785789
(1 ligne)
Temps : 183,920 ms
...
Temps : 184,647 ms
...
Temps : 169,943 ms