postgres=# select version();
version
-----------------------------------------------------------------------------------------------
PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
(1 ligne)
Temps : 6,071 ms
postgres=# \d+
Liste des relations
Schéma | Nom | Type | Propriétaire | Taille | Description
--------+------------+-------+--------------+---------+-------------
public | ventes_agg | table | postgres | 6000 MB |
(1 row)
postgres=# \di+
Aucune relation trouvée.
postgres=# explain create table mg7_1992 as
postgres-# select
postgres-# W.id_prod,
postgres-# W.jour,
postgres-# avg(W.mtv_cumul) moyenne_mobile_mtv_cumul_7
postgres-# from
postgres-# (
postgres(# select A.id_prod, A.jour, B.mtv_cumul
postgres(# from
postgres(# (
postgres(# select id_prod, date_trunc('day', dtv) jour
postgres(# from ventes_agg
postgres(# group by id_prod, date_trunc('day', dtv)
postgres(# ) AS A
postgres(# JOIN
postgres(# (
postgres(# select id_prod, date_trunc('day', dtv) jour, sum(mtv) mtv_cumul
postgres(# from ventes_agg
postgres(# group by id_prod, date_trunc('day', dtv)
postgres(# ) AS B
postgres(# ON (A.id_prod = B.id_prod AND B.jour between A.jour - interval '6 days' and A.jour)
postgres(# ) AS W
postgres-# group by id_prod, jour
postgres-# order by 1, 2 asc;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=58098762.04..23583914625.82 rows=40000 width=20)
Group Key: ventes_agg.id_prod, (date_trunc('day'::text, ventes_agg.dtv))
-> Merge Join (cost=58098762.04..22743814197.83 rows=112013323732 width=20)
Merge Cond: (ventes_agg.id_prod = ventes_agg_1.id_prod)
Join Filter: (((date_trunc('day'::text, ventes_agg_1.dtv)) <= (date_trunc('day'::text, ventes_agg.dtv))) AND ((date_trunc('day'::text, ventes_agg_1.dtv)) >= ((date_trunc('day'::text, ventes_agg.dtv)) - '6 days'::interval)))
-> Group (cost=29049381.02..30149837.25 rows=14199436 width=12)
Group Key: ventes_agg.id_prod, (date_trunc('day'::text, ventes_agg.dtv))
-> Sort (cost=29049381.02..29404366.90 rows=141994352 width=12)
Sort Key: ventes_agg.id_prod, (date_trunc('day'::text, ventes_agg.dtv))
-> Seq Scan on ventes_agg (cost=0.00..2542466.40 rows=141994352 width=12)
-> Materialize (cost=29049381.02..30824310.44 rows=14199436 width=20)
-> GroupAggregate (cost=29049381.02..30646817.49 rows=14199436 width=16)
Group Key: ventes_agg_1.id_prod, (date_trunc('day'::text, ventes_agg_1.dtv))
-> Sort (cost=29049381.02..29404366.90 rows=141994352 width=16)
Sort Key: ventes_agg_1.id_prod, (date_trunc('day'::text, ventes_agg_1.dtv))
-> Seq Scan on ventes_agg ventes_agg_1 (cost=0.00..2542466.40 rows=141994352 width=16)
(16 lignes)
Temps : 6,366 ms
postgres=# create table mg7_1992 as
postgres-# select
postgres-# W.id_prod,
postgres-# W.jour,
postgres-# avg(W.mtv_cumul) moyenne_mobile_mtv_cumul_7
postgres-# from
postgres-# (
postgres-# select A.id_prod, A.jour, B.mtv_cumul
postgres-# from
postgres-# (
postgres-# select id_prod, date_trunc('day', dtv) jour
postgres-# from ventes_agg
postgres-# group by id_prod, date_trunc('day', dtv)
postgres-# ) AS A
postgres-# JOIN
postgres-# (
postgres-# select id_prod, date_trunc('day', dtv) jour, sum(mtv) mtv_cumul
postgres-# from ventes_agg
postgres-# group by id_prod, date_trunc('day', dtv)
postgres-# ) AS B
postgres-# ON (A.id_prod = B.id_prod AND B.jour between A.jour - interval '6 days' and A.jour)
postgres-# ) AS W
postgres-# group by id_prod, jour
postgres-# order by 1, 2 asc;
SELECT 73200
Temps : 812469,215 ms
postgres=# explain create table mg7_2003 as
postgres-# select id_prod,
postgres-# date_trunc('day', dtv) jour,
postgres-# avg(sum(mtv)) over(partition by id_prod order by date_trunc('day', dtv) rows 6 preceding) moyenne_mobile_mtv_cumul_7
postgres-# from ventes_agg
postgres-# group by id_prod, date_trunc('day', dtv)
postgres-# order by 1,2 asc;
QUERY PLAN
-------------------------------------------------------------------------------------------
WindowAgg (cost=29049381.02..30930806.21 rows=14199436 width=16)
-> GroupAggregate (cost=29049381.02..30646817.49 rows=14199436 width=16)
Group Key: id_prod, (date_trunc('day'::text, dtv))
-> Sort (cost=29049381.02..29404366.90 rows=141994352 width=16)
Sort Key: id_prod, (date_trunc('day'::text, dtv))
-> Seq Scan on ventes_agg (cost=0.00..2542466.40 rows=141994352 width=16)
(6 lignes)
postgres=# create table mg7_2003 as
postgres-# select id_prod,
postgres-# date_trunc('day', dtv) jour,
postgres-# avg(sum(mtv)) over(partition by id_prod order by date_trunc('day', dtv) rows 6 preceding) moyenne_mobile_mtv_cumul_7
postgres-# from ventes_agg
postgres-# group by id_prod, date_trunc('day', dtv)
postgres-# order by 1,2 asc;
SELECT 73200
Temps : 438293,048 ms
La première requête globalement "SQL:1992" utilise une auto-jointure et donne lieu à 2 balayages complets de la table VENTES_AGG. La deuxième requête tire parti des possibilités offertes par la norme SQL:2003, ici les fonctions de fenêtrage, et neffectue quun seul balayage. La première requête a pris 13min32s alors que la deuxième na pris "que" 7min18s.