select version();
version
------------------------------------------------------------------------------------------
PostgreSQL 9.6.0 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 integer, mtv integer) ;
CREATE TABLE
do
$$
declare x real;
begin
for i in reverse 31536000..1 loop
x := random();
if x < 0.1 then
insert into ventes_agg values(current_timestamp - (i || ' seconds')::interval, trunc(random() * 200 + 1), trunc(random() * 100 + 1));
end if;
if x < 0.2 then
insert into ventes_agg values(current_timestamp - (i || ' seconds')::interval, trunc(random() * 200 + 1), trunc(random() * 100 + 1));
end if;
if x < 0.3 then
insert into ventes_agg values(current_timestamp - (i || ' seconds')::interval, trunc(random() * 200 + 1), trunc(random() * 100 + 1));
end if;
if x < 0.4 then
insert into ventes_agg values(current_timestamp - (i || ' seconds')::interval, trunc(random() * 200 + 1), trunc(random() * 100 + 1));
end if;
if x < 0.5 then
insert into ventes_agg values(current_timestamp - (i || ' seconds')::interval, trunc(random() * 200 + 1), trunc(random() * 100 + 1));
end if;
if x < 0.6 then
insert into ventes_agg values(current_timestamp - (i || ' seconds')::interval, trunc(random() * 200 + 1), trunc(random() * 100 + 1));
end if;
if x < 0.7 then
insert into ventes_agg values(current_timestamp - (i || ' seconds')::interval, trunc(random() * 200 + 1), trunc(random() * 100 + 1));
end if;
if x < 0.8 then
insert into ventes_agg values(current_timestamp - (i || ' seconds')::interval, trunc(random() * 200 + 1), trunc(random() * 100 + 1));
end if;
if x < 0.9 then
insert into ventes_agg values(current_timestamp - (i || ' seconds')::interval, trunc(random() * 200 + 1), trunc(random() * 100 + 1));
end if;
end loop;
end$$;
DO
\d+
Liste des relations
Schéma | Nom | Type | Propriétaire | Taille | Description
--------+------------+-------+--------------+---------+-------------
public | ventes_agg | table | postgres | 5999 MB |
(1 ligne)
\timing
Chronométrage activé.
explain select id_prod, sum(mtv) from ventes_agg
where id_prod in(5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199)
group by id_prod
order by 2 desc
fetch first 3 rows only;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=9431419.85..9431419.86 rows=3 width=12)
-> Sort (cost=9431419.85..9431420.35 rows=200 width=12)
Sort Key: (sum(mtv)) DESC
-> HashAggregate (cost=9431415.27..9431417.27 rows=200 width=12)
Group Key: id_prod
-> Seq Scan on ventes_agg (cost=0.00..9289387.44 rows=28405565 width=8)
Filter: (id_prod = ANY ('{5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199}'::integer[]))
(7 lignes)
Temps : 2,420 ms
select id_prod, sum(mtv) from ventes_agg
where id_prod in(5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199)
group by id_prod
order by 2 desc
fetch first 3 rows only;
id_prod | sum
---------+----------
122 | 35956746
120 | 35944459
157 | 35938210
(3 lignes)
Temps : 106156,300 ms
select id_prod, sum(mtv) from ventes_agg
where id_prod in(5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199)
group by id_prod
order by 2 desc
fetch first 3 rows only;
id_prod | sum
---------+----------
122 | 35956746
120 | 35944459
157 | 35938210
(3 lignes)
Temps : 105568,138 ms
select id_prod, sum(mtv) from ventes_agg
where id_prod in(5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199)
group by id_prod
order by 2 desc
fetch first 3 rows only;
id_prod | sum
---------+----------
122 | 35956746
120 | 35944459
157 | 35938210
(3 lignes)
Temps : 105595,819 ms
create index ventes_agg_i1 on ventes_agg(id_prod);
CREATE INDEX
Temps : 392099,795 ms
explain select id_prod, sum(mtv) from ventes_agg
where id_prod in(5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199)
group by id_prod
order by 2 desc
fetch first 3 rows only;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=3117584.70..3117584.71 rows=3 width=12)
-> Sort (cost=3117584.70..3117585.20 rows=200 width=12)
Sort Key: (sum(mtv)) DESC
-> HashAggregate (cost=3117580.12..3117582.12 rows=200 width=12)
Group Key: id_prod
-> Bitmap Heap Scan on ventes_agg (cost=503501.89..2975552.41 rows=28405542 width=8)
Recheck Cond: (id_prod = ANY ('{5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199}'::integer[]))
-> Bitmap Index Scan on ventes_agg_i1 (cost=0.00..496400.50 rows=28405542 width=0)
Index Cond: (id_prod = ANY ('{5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199}'::integer[]))
(9 lignes)
Temps : 0,823 ms
select id_prod, sum(mtv) from ventes_agg
where id_prod in(5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199)
group by id_prod
order by 2 desc
fetch first 3 rows only;
id_prod | sum
---------+----------
122 | 35956746
120 | 35944459
157 | 35938210
(3 lignes)
Temps : 169538,116 ms
select id_prod, sum(mtv) from ventes_agg
where id_prod in(5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199)
group by id_prod
order by 2 desc
fetch first 3 rows only;
id_prod | sum
---------+----------
122 | 35956746
120 | 35944459
157 | 35938210
(3 lignes)
Temps : 166987,215 ms
select id_prod, sum(mtv) from ventes_agg
where id_prod in(5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199)
group by id_prod
order by 2 desc
fetch first 3 rows only;
id_prod | sum
---------+----------
122 | 35956746
120 | 35944459
157 | 35938210
(3 lignes)
Temps : 171519,968 ms
drop index ventes_agg_i1;
DROP INDEX
Temps : 1004,865 ms
set max_parallel_workers_per_gather=3;
SET
Temps : 0,347 ms
explain select id_prod, sum(mtv) from ventes_agg
where id_prod in(5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199)
group by id_prod
order by 2 desc
fetch first 3 rows only;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=3563555.62..3563555.63 rows=3 width=12)
-> Sort (cost=3563555.62..3563556.12 rows=200 width=12)
Sort Key: (sum(mtv)) DESC
-> Finalize GroupAggregate (cost=3563546.53..3563553.03 rows=200 width=12)
Group Key: id_prod
-> Sort (cost=3563546.53..3563548.03 rows=600 width=12)
Sort Key: id_prod
-> Gather (cost=3563456.85..3563518.85 rows=600 width=12)
Workers Planned: 3
-> Partial HashAggregate (cost=3562456.85..3562458.85 rows=200 width=12)
Group Key: id_prod
-> Parallel Seq Scan on ventes_agg (cost=0.00..3516641.46 rows=9163078 width=8)
Filter: (id_prod = ANY ('{5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199}'::integer[]))
(13 lignes)
select id_prod, sum(mtv) from ventes_agg
where id_prod in(5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199)
group by id_prod
order by 2 desc
fetch first 3 rows only;
id_prod | sum
---------+----------
122 | 35956746
120 | 35944459
157 | 35938210
(3 lignes)
Temps : 26989,625 ms
select id_prod, sum(mtv) from ventes_agg
where id_prod in(5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199)
group by id_prod
order by 2 desc
fetch first 3 rows only;
id_prod | sum
---------+----------
122 | 35956746
120 | 35944459
157 | 35938210
(3 lignes)
Temps : 26973,156 ms
select id_prod, sum(mtv) from ventes_agg
where id_prod in(5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199)
group by id_prod
order by 2 desc
fetch first 3 rows only;
id_prod | sum
---------+----------
122 | 35956746
120 | 35944459
157 | 35938210
(3 lignes)
Temps : 26975,316 ms