select version();
version
-----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
create table lancers(dtl timestamp, idg smallint, perf integer);
CREATE TABLE
with recursive serie(i) as (select 576000 UNION ALL select i - 1 from serie where i > 1)
insert into lancers(dtl, idg, perf)
select current_timestamp - (i || ' minutes')::interval,
trunc(random() * 54 + 1),
case when random() <= 0.0001 then 50000 + trunc(random() * 50000 + 1) else trunc(random() * 50000 + 1) end
from serie ;
INSERT 0 576000
create index lancers_i1 on lancers(idg, dtl);
CREATE INDEX
create index lancers_br1 on lancers using brin(dtl);
CREATE INDEX
explain
select
W.idg geant,
W.jour,
avg(W.perf_max) moyenne_mobile_perf_max_7,
avg(W.perf_min) moyenne_mobile_perf_min_7
from
(
select A.idg, A.jour, B.perf_max, B.perf_min
from
(
select idg, date_trunc('day', dtl) jour
from lancers
group by idg, date_trunc('day', dtl)
) AS A
JOIN
(
select idg, date_trunc('day', dtl) jour, max(perf) perf_max, min(perf) perf_min
from lancers
group by idg, date_trunc('day', dtl)
) AS B
ON (A.idg = B.idg AND B.jour between A.jour - interval '6 days' and A.jour)
) AS W
group by idg, jour
order by 1, 2 asc;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=150540.69..38889920.69 rows=40000 width=42)
Group Key: lancers.idg, (date_trunc('day'::text, lancers.dtl))
-> Merge Join (cost=150540.69..37507020.69 rows=184320000 width=18)
Merge Cond: (lancers.idg = lancers_1.idg)
Join Filter: (((date_trunc('day'::text, lancers_1.dtl)) <= (date_trunc('day'::text, lancers.dtl))) AND ((date_trunc('day'::text, lancers_1.dtl)) >= ((date_trunc('day'::text, lancers.dtl)) - '6 days'::interval)))
-> Group (cost=75270.34..81030.34 rows=576000 width=10)
Group Key: lancers.idg, (date_trunc('day'::text, lancers.dtl))
-> Sort (cost=75270.34..76710.34 rows=576000 width=10)
Sort Key: lancers.idg, (date_trunc('day'::text, lancers.dtl))
-> Seq Scan on lancers (cost=0.00..10314.00 rows=576000 width=10)
-> Materialize (cost=75270.34..95430.34 rows=576000 width=18)
-> GroupAggregate (cost=75270.34..88230.34 rows=576000 width=18)
Group Key: lancers_1.idg, (date_trunc('day'::text, lancers_1.dtl))
-> Sort (cost=75270.34..76710.34 rows=576000 width=14)
Sort Key: lancers_1.idg, (date_trunc('day'::text, lancers_1.dtl))
-> Seq Scan on lancers lancers_1 (cost=0.00..10314.00 rows=576000 width=14)
select
W.idg geant,
W.jour,
avg(W.perf_max) moyenne_mobile_perf_max_7,
avg(W.perf_min) moyenne_mobile_perf_min_7
from
(
select A.idg, A.jour, B.perf_max, B.perf_min
from
(
select idg, date_trunc('day', dtl) jour
from lancers
group by idg, date_trunc('day', dtl)
) AS A
JOIN
(
select idg, date_trunc('day', dtl) jour, max(perf) perf_max, min(perf) perf_min
from lancers
group by idg, date_trunc('day', dtl)
) AS B
ON (A.idg = B.idg AND B.jour between A.jour - interval '6 days' and A.jour)
) AS W
group by idg, jour
order by 1, 2 asc;
...
Durée : 1676,266 ms (00:01,676)
Durée : 1579,950 ms (00:01,580)
Durée : 1603,530 ms (00:01,604)
explain
select idg geant,
date_trunc('day', dtl) jour,
avg(max(perf)) over(partition by idg order by date_trunc('day', dtl) rows 6 preceding) moyenne_mobile_perf_max_7,
avg(min(perf)) over(partition by idg order by date_trunc('day', dtl) rows 6 preceding) moyenne_mobile_perf_min_7
from lancers
group by idg, date_trunc('day', dtl)
order by 1,2 asc;
QUERY PLAN
-----------------------------------------------------------------------------------
WindowAgg (cost=75270.34..102630.34 rows=576000 width=74)
-> GroupAggregate (cost=75270.34..89670.34 rows=576000 width=18)
Group Key: idg, (date_trunc('day'::text, dtl))
-> Sort (cost=75270.34..76710.34 rows=576000 width=14)
Sort Key: idg, (date_trunc('day'::text, dtl))
-> Seq Scan on lancers (cost=0.00..10314.00 rows=576000 width=14)
select idg geant,
date_trunc('day', dtl) jour,
avg(max(perf)) over(partition by idg order by date_trunc('day', dtl) rows 6 preceding) moyenne_mobile_perf_max_7,
avg(min(perf)) over(partition by idg order by date_trunc('day', dtl) rows 6 preceding) moyenne_mobile_perf_min_7
from lancers
group by idg, date_trunc('day', dtl)
order by 1,2 asc;
...
Temps : 401,613 ms
Temps : 396,167 ms
Temps : 375,216 ms