select version();
version
-----------------------------------------------------------------------------------------------------
PostgreSQL 10beta1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit
(1 ligne)
create unlogged table ventes(dtv timestamp, mtv integer);
CREATE TABLE
insert into ventes
select generate_series(date_trunc('second', current_timestamp-interval '1 year'), date_trunc('second', current_timestamp), '1 second'), trunc(random() * 100000 + 1);
\d+
Liste des relations
Schéma | Nom | Type | Propriétaire | Taille | Description
--------+--------+-------+--------------+---------+-------------
public | ventes | table | postgres | 1332 MB |
(1 ligne)
analyze ventes;
ANALYZE
explain select avg(mtv) from ventes where dtv >= to_timestamp('13/03/2017','DD/MM/YYYY') and dtv < to_timestamp('27/03/2017','DD/MM/YYYY');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=435589.54..435589.55 rows=1 width=32)
-> Gather (cost=435589.32..435589.53 rows=2 width=32)
Workers Planned: 2
-> Partial Aggregate (cost=434589.32..434589.33 rows=1 width=32)
-> Parallel Seq Scan on ventes (cost=0.00..433265.20 rows=529649 width=4)
Filter: ((dtv >= to_timestamp('13/03/2017'::text, 'DD/MM/YYYY'::text)) AND (dtv < to_timestamp('27/03/2017'::text, 'DD/MM/YYYY'::text)))
(6 lignes)
select avg(mtv) from ventes where dtv >= to_timestamp('13/03/2017','DD/MM/YYYY') and dtv < to_timestamp('27/03/2017','DD/MM/YYYY');
avg
--------------------
50019.437602819237
(1 ligne)
Time: 12114,818 ms (00:12,115)
...
Time: 12118,148 ms (00:12,118)
...
Time: 12103,279 ms (00:12,103)
create index ventes_br1 on ventes using brin(dtv);
CREATE INDEX
Time: 10305,214 ms (00:10,305)
\di+ ventes_br1
Liste des relations
Schéma | Nom | Type | Propriétaire | Table | Taille | Description
--------+------------+-------+--------------+--------+--------+-------------
public | ventes_br1 | index | postgres | ventes | 88 kB |
(1 ligne)
explain select avg(mtv) from ventes where dtv >= to_timestamp('13/03/2017','DD/MM/YYYY') and dtv < to_timestamp('27/03/2017','DD/MM/YYYY');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=183790.91..183790.92 rows=1 width=32)
-> Gather (cost=183790.69..183790.90 rows=2 width=32)
Workers Planned: 2
-> Partial Aggregate (cost=182790.69..182790.70 rows=1 width=32)
-> Parallel Bitmap Heap Scan on ventes (cost=347.52..181466.57 rows=529649 width=4)
Recheck Cond: ((dtv >= to_timestamp('13/03/2017'::text, 'DD/MM/YYYY'::text)) AND (dtv < to_timestamp('27/03/2017'::text, 'DD/MM/YYYY'::text)))
-> Bitmap Index Scan on ventes_br1 (cost=0.00..29.73 rows=1278486 width=0)
Index Cond: ((dtv >= to_timestamp('13/03/2017'::text, 'DD/MM/YYYY'::text)) AND (dtv < to_timestamp('27/03/2017'::text, 'DD/MM/YYYY'::text)))
(8 lignes)
select avg(mtv) from ventes where dtv >= to_timestamp('13/03/2017','DD/MM/YYYY') and dtv < to_timestamp('27/03/2017','DD/MM/YYYY');
avg
--------------------
50019.437602819237
(1 ligne)
Temps : 894,867 ms
...
Temps : 869,737 ms
...
Temps : 883,193 ms
create index ventes_i1 on ventes(dtv);
CREATE INDEX
Time: 33341,207 ms (00:33,341)
\di+ ventes_i1
Liste des relations
Schéma | Nom | Type | Propriétaire | Table | Taille | Description
--------+-----------+-------+--------------+--------+--------+-------------
public | ventes_i1 | index | postgres | ventes | 676 MB |
(1 ligne)
explain select avg(mtv) from ventes where dtv >= to_timestamp('13/03/2017','DD/MM/YYYY') and dtv < to_timestamp('27/03/2017','DD/MM/YYYY');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=41151.96..41151.97 rows=1 width=32)
-> Gather (cost=41151.75..41151.96 rows=2 width=32)
Workers Planned: 2
-> Partial Aggregate (cost=40151.75..40151.76 rows=1 width=32)
-> Parallel Index Scan using ventes_i1 on ventes (cost=0.57..38827.62 rows=529649 width=4)
Index Cond: ((dtv >= to_timestamp('13/03/2017'::text, 'DD/MM/YYYY'::text)) AND (dtv < to_timestamp('27/03/2017'::text, 'DD/MM/YYYY'::text)))
(6 lignes)
select avg(mtv) from ventes where dtv >= to_timestamp('13/03/2017','DD/MM/YYYY') and dtv < to_timestamp('27/03/2017','DD/MM/YYYY');
avg
--------------------
50019.437602819237
(1 ligne)
Temps : 408,298 ms
...
Temps : 412,096 ms
...
Temps : 409,682 ms