SELECT version();
version
------------------------------------------------------------------------------------------
PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
(1 ligne)
create unlogged table ventes_agg(dtv timestamp, id_prod smallint, id_mag smallint, mtv integer);
CREATE TABLE
show shared_buffers;
shared_buffers
----------------
2GB
(1 ligne)
show work_mem;
work_mem
----------
64MB
(1 ligne)
WITH serie_1000000(i) AS (SELECT generate_series(1000000,1,-1))
INSERT INTO ventes_agg (SELECT current_timestamp - (i || ' minutes')::interval, trunc(random() * 100 + 1), trunc(random() * 100 + 1), trunc(random() * 90000 + 1) FROM serie_1000000);
INSERT 0 1000000
Temps : 5013,398 ms
ANALYZE VENTES_AGG;
ANALYZE
Temps : 148,111 ms
explain (select id_prod, null::smallint as id_mag, null::smallint as month_dtv, sum(mtv)
from ventes_agg
where dtv >= to_timestamp('01/01/2015','DD/MM/YYYY') and dtv < to_timestamp('01/01/2016','DD/MM/YYYY')
group by id_prod
order by 1 desc)
union all
(select null, id_mag as id_mag, null, sum(mtv)
from ventes_agg
where dtv >= to_timestamp('01/01/2015','DD/MM/YYYY') and dtv < to_timestamp('01/01/2016','DD/MM/YYYY')
group by id_mag
order by 2 desc)
union all
(select null, null, extract(month from dtv) as month_dtv, sum(mtv)
from ventes_agg
where dtv >= to_timestamp('01/01/2015','DD/MM/YYYY') and dtv < to_timestamp('01/01/2016','DD/MM/YYYY')
group by extract(month from dtv)
order by 3 desc
)
union all
select null, null, null, sum(mtv)
from ventes_agg
where dtv >= to_timestamp('01/01/2015','DD/MM/YYYY') and dtv < to_timestamp('01/01/2016','DD/MM/YYYY');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=28011.80..174450.27 rows=520496 width=20)
-> Result (cost=28011.80..56028.59 rows=200 width=20)
-> Append (cost=28011.80..56026.09 rows=200 width=14)
-> Subquery Scan on "*SELECT* 1" (cost=28011.80..28013.05 rows=100 width=14)
-> Sort (cost=28011.80..28012.05 rows=100 width=14)
Sort Key: ventes_agg.id_prod DESC
-> HashAggregate (cost=28007.47..28008.47 rows=100 width=14)
Group Key: ventes_agg.id_prod
-> Seq Scan on ventes_agg (cost=0.00..25406.00 rows=520295 width=6)
Filter: ((dtv >= to_timestamp('01/01/2015'::text, 'DD/MM/YYYY'::text)) AND (dtv < to_timestamp('01/01/2016'::text, 'DD/MM/YYYY'::text)))
-> Subquery Scan on "*SELECT* 2" (cost=28011.80..28013.05 rows=100 width=14)
-> Sort (cost=28011.80..28012.05 rows=100 width=14)
Sort Key: ventes_agg_1.id_mag DESC
-> HashAggregate (cost=28007.47..28008.47 rows=100 width=14)
Group Key: ventes_agg_1.id_mag
-> Seq Scan on ventes_agg ventes_agg_1 (cost=0.00..25406.00 rows=520295 width=6)
Filter: ((dtv >= to_timestamp('01/01/2015'::text, 'DD/MM/YYYY'::text)) AND (dtv < to_timestamp('01/01/2016'::text, 'DD/MM/YYYY'::text)))
-> GroupAggregate (cost=76106.07..86511.97 rows=520295 width=20)
Group Key: (date_part('month'::text, ventes_agg_2.dtv))
-> Sort (cost=76106.07..77406.81 rows=520295 width=12)
Sort Key: (date_part('month'::text, ventes_agg_2.dtv)) DESC
-> Seq Scan on ventes_agg ventes_agg_2 (cost=0.00..26706.74 rows=520295 width=12)
Filter: ((dtv >= to_timestamp('01/01/2015'::text, 'DD/MM/YYYY'::text)) AND (dtv < to_timestamp('01/01/2016'::text, 'DD/MM/YYYY'::text)))
-> Aggregate (cost=26706.74..26706.75 rows=1 width=20)
-> Seq Scan on ventes_agg ventes_agg_3 (cost=0.00..25406.00 rows=520295 width=4)
Filter: ((dtv >= to_timestamp('01/01/2015'::text, 'DD/MM/YYYY'::text)) AND (dtv < to_timestamp('01/01/2016'::text, 'DD/MM/YYYY'::text)))
(26 lignes)
Temps : 7,631 ms
(select id_prod, null::smallint as id_mag, null::smallint as month_dtv, sum(mtv)
from ventes_agg
where dtv >= to_timestamp('01/01/2015','DD/MM/YYYY') and dtv < to_timestamp('01/01/2016','DD/MM/YYYY')
group by id_prod
order by 1 desc)
union all
(select null, id_mag as id_mag, null, sum(mtv)
from ventes_agg
where dtv >= to_timestamp('01/01/2015','DD/MM/YYYY') and dtv < to_timestamp('01/01/2016','DD/MM/YYYY')
group by id_mag
order by 2 desc)
union all
(select null, null, extract(month from dtv) as month_dtv, sum(mtv)
from ventes_agg
where dtv >= to_timestamp('01/01/2015','DD/MM/YYYY') and dtv < to_timestamp('01/01/2016','DD/MM/YYYY')
group by extract(month from dtv)
order by 3 desc
)
union all
select null, null, null, sum(mtv)
from ventes_agg
where dtv >= to_timestamp('01/01/2015','DD/MM/YYYY') and dtv < to_timestamp('01/01/2016','DD/MM/YYYY');
id_prod | id_mag | month_dtv | sum
---------+--------+-----------+-------------
100 | | | 234388923
..
1 | | | 232510807
| 100 | | 229662843
..
| 1 | | 235714229
| | 12 | 2003411842
..
| | 1 | 1733544479
| | | 23413583856
(213 lignes)
Temps : 8517,258 ms
Temps : 8539,567 ms
Temps : 8555,327 ms