select version();
version
-----------------------------------------------------------------------------------------------------
PostgreSQL 10beta1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit
create table geants(idg smallint generated always as identity primary key, nmg character varying(32), actif boolean);
CREATE TABLE
insert into geants(nmg, actif) select upper(md5(random()::text)), case when random() < 0.7 then true else false end from generate_series(1,54);
INSERT 0 54
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);
CREATE INDEX
create index lancers_i2 on lancers(perf, dtl);
CREATE INDEX
analyze geants;
ANALYZE
analyze lancers;
ANALYZE
explain select l.idg, l.dtl, l.perf
from lancers l
join
(select g.idg idg, max(l.perf) perf
from geants g join
lancers l
on (g.idg = l.idg)
where g.actif = 't'
group by g.idg) perf_max
on (l.idg = perf_max.idg and l.perf = perf_max.perf)
order by 3 desc, 2 desc
limit 8;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Limit (cost=11963.49..11963.51 rows=8 width=14)
-> Sort (cost=11963.49..11963.51 rows=8 width=14)
Sort Key: l.perf DESC, l.dtl DESC
-> Nested Loop (cost=10271.71..11963.37 rows=8 width=14)
-> Finalize GroupAggregate (cost=10271.29..10272.14 rows=34 width=6)
Group Key: g.idg
-> Sort (cost=10271.29..10271.46 rows=68 width=6)
Sort Key: g.idg
-> Gather (cost=10262.08..10269.22 rows=68 width=6)
Workers Planned: 2
-> Partial HashAggregate (cost=9262.08..9262.42 rows=34 width=6)
Group Key: g.idg
-> Hash Join (cost=1.97..8506.52 rows=151111 width=6)
Hash Cond: (l_1.idg = g.idg)
-> Parallel Seq Scan on lancers l_1 (cost=0.00..5514.00 rows=240000 width=6)
-> Hash (cost=1.54..1.54 rows=34 width=2)
-> Seq Scan on geants g (cost=0.00..1.54 rows=34 width=2)
Filter: actif
-> Index Scan using lancers_i2 on lancers l (cost=0.42..49.72 rows=1 width=14)
Index Cond: (perf = (max(l_1.perf)))
Filter: (g.idg = idg)
select l.idg, l.dtl, l.perf
from lancers l
join
(select g.idg idg, max(l.perf) perf
from geants g join
lancers l
on (g.idg = l.idg)
where g.actif = 't'
group by g.idg) perf_max
on (l.idg = perf_max.idg and l.perf = perf_max.perf)
order by 3 desc, 2 desc
limit 8;
idg | dtl | perf
-----+----------------------------+-------
49 | 2016-10-17 00:05:17.882661 | 99568
17 | 2016-09-01 15:45:17.882661 | 95927
22 | 2017-04-02 22:55:17.882661 | 94996
50 | 2017-04-27 11:06:17.882661 | 94866
35 | 2016-10-26 23:45:17.882661 | 93456
52 | 2016-07-14 23:39:17.882661 | 93417
26 | 2017-05-09 15:09:17.882661 | 93126
5 | 2017-03-22 18:41:17.882661 | 91619
(8 lignes)
Temps : 166,649 ms
...
Temps : 166,399 ms
...
Temps : 166,593 ms