select version();
version
-------------------------------------------------------------
PostgreSQL 9.5.1, compiled by Visual C++ build 1800, 64-bit
(1 ligne)
create 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
----------
256MB
(1 ligne)
do
$$
begin
for i in reverse 669600..1 loop
insert into ventes_agg values(current_timestamp - (i || ' minutes')::interval, trunc(random() * 100 + 1), trunc(random() * 100 + 1), trunc(random() * 90000 + 1));
end loop;
end$$;
DO
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=19657.70..142074.85 rows=528644 width=12)
-> Result (cost=19657.70..39317.38 rows=158 width=6)
-> Append (cost=19657.70..39317.38 rows=158 width=6)
-> Subquery Scan on "*SELECT* 1" (cost=19657.70..19658.69 rows=79 width=6)
-> Sort (cost=19657.70..19657.90 rows=79 width=6)
Sort Key: ventes_agg.id_prod DESC
-> HashAggregate (cost=19654.43..19655.22 rows=79 width=6)
Group Key: ventes_agg.id_prod
-> Seq Scan on ventes_agg (cost=0.00..17012.00 rows=528485 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=19657.70..19658.69 rows=79 width=6)
-> Sort (cost=19657.70..19657.90 rows=79 width=6)
Sort Key: ventes_agg_1.id_mag DESC
-> HashAggregate (cost=19654.43..19655.22 rows=79 width=6)
Group Key: ventes_agg_1.id_mag
-> Seq Scan on ventes_agg ventes_agg_1 (cost=0.00..17012.00 rows=528485 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=68569.68..79139.38 rows=528485 width=12)
Group Key: (date_part('month'::text, ventes_agg_2.dtv))
-> Sort (cost=68569.68..69890.90 rows=528485 width=12)
Sort Key: (date_part('month'::text, ventes_agg_2.dtv)) DESC
-> Seq Scan on ventes_agg ventes_agg_2 (cost=0.00..18333.21 rows=528485 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=18333.21..18333.22 rows=1 width=4)
-> Seq Scan on ventes_agg ventes_agg_3 (cost=0.00..17012.00 rows=528485 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)
(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 | | | 234829908
..
1 | | | 235149951
| 100 | | 237283838
..
| 1 | | 239420604
| | 12 | 2008147978
..
| | 1 | 2010666350
| | | 23663496163
(213 lignes)
Temps : 4059,881 ms
Temps : 4037,476 ms
Temps : 4040,026 ms