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 integer generated by default as identity primary key, dtn timestamp, nmg character varying(32));
CREATE TABLE
create table lancers(dtl timestamp, idg integer references geants(idg), perf integer);
CREATE TABLE
with recursive serie(i) as
(select 1
UNION ALL
select i + 1 from serie where i < 100)
insert into geants(dtn, nmg)
select
current_timestamp - (trunc(random() * 100000 + 1) || ' days')::interval,
upper(md5(random()::text))
from serie;
INSERT 0 100
with recursive serie(i) as (select 100000 UNION ALL select i - 1 from serie where i > 1)
insert into lancers(dtl, idg, perf)
select current_timestamp - (i || ' minutes')::interval,
trunc(random() * 100 + 1),
case when random() <= 0.0001 then 50000 + trunc(random() * 50000 + 1) else trunc(random() * 50000 + 1) end
from serie ;
INSERT 0 100000
vacuum analyze geants;
VACUUM
vacuum analyze lancers;
VACUUM
explain select * from geants where idg in ( select idg from lancers ) ;
QUERY PLAN
---------------------------------------------------------------------------------
Hash Join (cost=1793.25..1796.62 rows=100 width=45)
Hash Cond: (geants.idg = lancers.idg)
-> Seq Scan on geants (cost=0.00..3.00 rows=100 width=45)
-> Hash (cost=1792.00..1792.00 rows=100 width=4)
-> HashAggregate (cost=1791.00..1792.00 rows=100 width=4)
Group Key: lancers.idg
-> Seq Scan on lancers (cost=0.00..1541.00 rows=100000 width=4)
select * from geants where idg in ( select idg from lancers ) ;
Temps : 25,214 ms
Temps : 26,854 ms
Temps : 28,284 ms
explain select * from geants where exists
( select null from lancers where geants.idg = lancers.idg ) ;
QUERY PLAN
---------------------------------------------------------------------------------
Hash Join (cost=1793.25..1796.62 rows=100 width=45)
Hash Cond: (geants.idg = lancers.idg)
-> Seq Scan on geants (cost=0.00..3.00 rows=100 width=45)
-> Hash (cost=1792.00..1792.00 rows=100 width=4)
-> HashAggregate (cost=1791.00..1792.00 rows=100 width=4)
Group Key: lancers.idg
-> Seq Scan on lancers (cost=0.00..1541.00 rows=100000 width=4)
select * from geants where exists
( select null from lancers where geants.idg = lancers.idg ) ;
Temps : 26,974 ms
Temps : 27,840 ms
Temps : 26,510 ms
create index lancers_i1 on lancers(idg, dtl) ;
CREATE INDEX
Temps : 309,842 ms
explain select * from geants where idg in ( select idg from lancers ) ;
QUERY PLAN
-----------------------------------------------------------------------------------------
Nested Loop Semi Join (cost=0.42..52.35 rows=100 width=45)
-> Seq Scan on geants (cost=0.00..3.00 rows=100 width=45)
-> Index Only Scan using lancers_i1 on lancers (cost=0.42..28.48 rows=1000 width=4)
Index Cond: (idg = geants.idg)
select * from geants where idg in ( select idg from lancers ) ;
Temps : 1,837 ms
Temps : 1,507 ms
Temps : 1,503 ms
explain select * from geants where exists
( select null from lancers where geants.idg = lancers.idg ) ;
QUERY PLAN
-----------------------------------------------------------------------------------------
Nested Loop Semi Join (cost=0.42..52.35 rows=100 width=45)
-> Seq Scan on geants (cost=0.00..3.00 rows=100 width=45)
-> Index Only Scan using lancers_i1 on lancers (cost=0.42..28.48 rows=1000 width=4)
Index Cond: (idg = geants.idg)
select * from geants where exists
( select null from lancers where geants.idg = lancers.idg ) ;
Temps : 1,496 ms
Temps : 1,642 ms
Temps : 1,646 ms
truncate table geants restart identity cascade;
NOTICE: TRUNCATE cascade sur la table " lancers "
TRUNCATE TABLE
Temps : 30,076 ms
with recursive serie(i) as
(select 1
UNION ALL
select i + 1 from serie where i < 100000)
insert into geants(dtn, nmg)
select
current_timestamp - (trunc(random() * 100000 + 1) || ' days')::interval,
upper(md5(random()::text))
from serie;
INSERT 0 100000
Temps : 916,136 ms
with recursive serie(i) as (select 100000 UNION ALL select i - 1 from serie where i > 1)
insert into lancers(dtl, idg, perf)
select current_timestamp - (i || ' minutes')::interval,
trunc(random() * 100000 + 1),
case when random() <= 0.0001 then 50000 + trunc(random() * 50000 + 1) else trunc(random() * 50000 + 1) end
from serie ;
INSERT 0 100000
Time: 1699,158 ms (00:01,699)
vacuum analyze geants;
VACUUM
Temps : 124,784 ms
vacuum analyze lancers;
VACUUM
Temps : 78,835 ms
explain select * from geants where idg in ( select idg from lancers ) ;
QUERY PLAN
---------------------------------------------------------------------------------
Hash Join (cost=2986.47..5808.42 rows=53132 width=45)
Hash Cond: (geants.idg = lancers.idg)
-> Seq Scan on geants (cost=0.00..2031.00 rows=100000 width=45)
-> Hash (cost=2322.32..2322.32 rows=53132 width=4)
-> HashAggregate (cost=1791.00..2322.32 rows=53132 width=4)
Group Key: lancers.idg
-> Seq Scan on lancers (cost=0.00..1541.00 rows=100000 width=4)
select * from geants where idg in ( select idg from lancers ) ;
Temps : 132,647 ms
Temps : 127,509 ms
Temps : 117,696 ms
explain select * from geants where exists
( select null from lancers where geants.idg = lancers.idg ) ;
QUERY PLAN
---------------------------------------------------------------------------------
Hash Join (cost=2986.47..5808.42 rows=53132 width=45)
Hash Cond: (geants.idg = lancers.idg)
-> Seq Scan on geants (cost=0.00..2031.00 rows=100000 width=45)
-> Hash (cost=2322.32..2322.32 rows=53132 width=4)
-> HashAggregate (cost=1791.00..2322.32 rows=53132 width=4)
Group Key: lancers.idg
-> Seq Scan on lancers (cost=0.00..1541.00 rows=100000 width=4)
select * from geants where exists
( select null from lancers where geants.idg = lancers.idg ) ;
Temps : 128,294 ms
Temps : 133,283 ms
Temps : 121,785 ms
truncate table lancers;
TRUNCATE TABLE
Temps : 27,282 ms
with recursive serie(i) as (select 100 UNION ALL select i - 1 from serie where i > 1)
insert into lancers(dtl, idg, perf)
select current_timestamp - (i || ' minutes')::interval,
trunc(random() * 100000 + 1),
case when random() <= 0.0001 then 50000 + trunc(random() * 50000 + 1) else trunc(random() * 50000 + 1) end
from serie ;
INSERT 0 100
vacuum analyze lancers;
VACUUM
Temps : 1,820 ms
explain select * from geants where idg in ( select idg from lancers ) ;
QUERY PLAN
---------------------------------------------------------------------------------
Nested Loop (cost=2.54..758.26 rows=100 width=45)
-> HashAggregate (cost=2.25..3.25 rows=100 width=4)
Group Key: lancers.idg
-> Seq Scan on lancers (cost=0.00..2.00 rows=100 width=4)
-> Index Scan using geants_pkey on geants (cost=0.29..7.55 rows=1 width=45)
Index Cond: (idg = lancers.idg)
select * from geants where idg in ( select idg from lancers ) ;
Temps : 1,068 ms
Temps : 1,087 ms
Temps : 1,123 ms
explain select * from geants where exists
( select null from lancers where geants.idg = lancers.idg ) ;
QUERY PLAN
---------------------------------------------------------------------------------
Nested Loop (cost=2.54..758.26 rows=100 width=45)
-> HashAggregate (cost=2.25..3.25 rows=100 width=4)
Group Key: lancers.idg
-> Seq Scan on lancers (cost=0.00..2.00 rows=100 width=4)
-> Index Scan using geants_pkey on geants (cost=0.29..7.55 rows=1 width=45)
Index Cond: (idg = lancers.idg)
select * from geants where exists
( select null from lancers where geants.idg = lancers.idg ) ;
Temps : 1,128 ms
Temps : 1,136 ms
Temps : 1,075 ms