#	exclusive	inclusive	rows x	rows	loops	node
1.	11.200	2892.835	↑ 1.4	136	1	→ HashAggregate (cost=484060.91..484062.86 rows=195 width=16) (actual time=2892.814..2892.835 rows=136 loops=1)
2.	6.605	2881.635	↑ 20.5	28486	1	→ Hash Join (cost=82918.13..476747.79 rows=585050 width=16) (actual time=88.336..2881.635 rows=28486 loops=1) Hash Cond: (rem.id_reference = reference.id_reference)
3.	1035.068	2875.015	↑ 20.5	28486	1	→ Hash Join (cost=82916.96..472797.52 rows=585050 width=16) (actual time=88.312..2875.015 rows=28486 loops=1) Hash Cond: (rem.id_entreprise = entrep.id_entreprise)
4.	1777.417	1777.417	↑ 1.0	14156521	1	→ Seq Scan on resultat_entrep_mensuel rem (cost=0.00..335892.84 rows=14157005 width=20) (actual time=0.005..1777.417 rows=14156521 loops=1) Filter: donnee_valide
5.	1.803	62.530	↑ 2.9	4269	1	→ Hash (cost=82760.32..82760.32 rows=12531 width=12) (actual time=62.530..62.530 rows=4269 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 184kB
6.	1.421	60.727	↑ 2.9	4269	1	→ Nested Loop (cost=16251.33..82760.32 rows=12531 width=12) (actual time=11.244..60.727 rows=4269 loops=1)
7.	1.646	36.746	↑ 2.9	3760	1	→ Nested Loop (cost=16251.33..48576.68 rows=11011 width=8) (actual time=11.236..36.746 rows=3760 loops=1)
8.	2.615	12.540	↑ 2.9	3760	1	→ HashAggregate (cost=16251.33..16361.44 rows=11011 width=4) (actual time=11.222..12.540 rows=3760 loops=1)
9.	0.841	9.925	↑ 2.9	3760	1	→ Nested Loop (cost=45.44..16223.80 rows=11011 width=4) (actual time=2.203..9.925 rows=3760 loops=1)
10.	0.324	0.324	↑ 4.0	1	1	→ Seq Scan on naf (cost=0.00..18.74 rows=4 width=4) (actual time=0.311..0.324 rows=1 loops=1) Filter: (substr((code)::text, 1, 4) = '7420'::text)
11.	7.795	8.760	↓ 1.3	3760	1	→ Bitmap Heap Scan on etablissement etb2 (cost=45.44..4015.82 rows=2836 width=8) (actual time=1.889..8.760 rows=3760 loops=1) Recheck Cond: (id_naf = naf.id_naf) Filter: siege
12.	0.965	0.965	↓ 1.3	3760	1	→ Bitmap Index Scan on etablissement_id_naf_siege_idx (cost=0.00..44.73 rows=2836 width=0) (actual time=0.965..0.965 rows=3760 loops=1) Index Cond: ((id_naf = naf.id_naf) AND (siege = true))
13.	22.560	22.560	↑ 1.0	1	3760	→ Index Scan using entreprise_pkey on entreprise entrep (cost=0.00..2.90 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=3760) Index Cond: (id_entreprise = etb2.id_entreprise)
14.	22.560	22.560	↑ 1.0	1	3760	→ Index Scan using etablissement_id_entreprise_id_naf_idx on etablissement etb (cost=0.00..3.09 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=3760) Index Cond: (id_entreprise = entrep.id_entreprise)
15.	0.002	0.015	↑ 1.0	3	1	→ Hash (cost=1.14..1.14 rows=3 width=4) (actual time=0.015..0.015 rows=3 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB
16.	0.005	0.013	↑ 1.0	3	1	→ HashAggregate (cost=1.11..1.14 rows=3 width=4) (actual time=0.012..0.013 rows=3 loops=1)
17.	0.008	0.008	↑ 1.0	3	1	→ Seq Scan on reference (cost=0.00..1.10 rows=3 width=4) (actual time=0.005..0.008 rows=3 loops=1) Filter: (NOT test_edd)

 HashAggregate  (cost=484060.91..484062.86 rows=195 width=16) (actual time=2892.814..2892.835 rows=136 loops=1)
   ->  Hash Join  (cost=82918.13..476747.79 rows=585050 width=16) (actual time=88.336..2881.635 rows=28486 loops=1)
         Hash Cond: (rem.id_reference = reference.id_reference)
         ->  Hash Join  (cost=82916.96..472797.52 rows=585050 width=16) (actual time=88.312..2875.015 rows=28486 loops=1)
               Hash Cond: (rem.id_entreprise = entrep.id_entreprise)
               ->  Seq Scan on resultat_entrep_mensuel rem  (cost=0.00..335892.84 rows=14157005 width=20) (actual time=0.005..1777.417 rows=14156521 loops=1)
                     Filter: donnee_valide
               ->  Hash  (cost=82760.32..82760.32 rows=12531 width=12) (actual time=62.530..62.530 rows=4269 loops=1)
                     Buckets: 2048  Batches: 1  Memory Usage: 184kB
                     ->  Nested Loop  (cost=16251.33..82760.32 rows=12531 width=12) (actual time=11.244..60.727 rows=4269 loops=1)
                           ->  Nested Loop  (cost=16251.33..48576.68 rows=11011 width=8) (actual time=11.236..36.746 rows=3760 loops=1)
                                 ->  HashAggregate  (cost=16251.33..16361.44 rows=11011 width=4) (actual time=11.222..12.540 rows=3760 loops=1)
                                       ->  Nested Loop  (cost=45.44..16223.80 rows=11011 width=4) (actual time=2.203..9.925 rows=3760 loops=1)
                                             ->  Seq Scan on naf  (cost=0.00..18.74 rows=4 width=4) (actual time=0.311..0.324 rows=1 loops=1)
                                                   Filter: (substr((code)::text, 1, 4) = '7420'::text)
                                             ->  Bitmap Heap Scan on etablissement etb2  (cost=45.44..4015.82 rows=2836 width=8) (actual time=1.889..8.760 rows=3760 loops=1)
                                                   Recheck Cond: (id_naf = naf.id_naf)
                                                   Filter: siege
                                                   ->  Bitmap Index Scan on etablissement_id_naf_siege_idx  (cost=0.00..44.73 rows=2836 width=0) (actual time=0.965..0.965 rows=3760 loops=1)
                                                         Index Cond: ((id_naf = naf.id_naf) AND (siege = true))
                                 ->  Index Scan using entreprise_pkey on entreprise entrep  (cost=0.00..2.90 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=3760)
                                       Index Cond: (id_entreprise = etb2.id_entreprise)
                           ->  Index Scan using etablissement_id_entreprise_id_naf_idx on etablissement etb  (cost=0.00..3.09 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=3760)
                                 Index Cond: (id_entreprise = entrep.id_entreprise)
         ->  Hash  (cost=1.14..1.14 rows=3 width=4) (actual time=0.015..0.015 rows=3 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 1kB
               ->  HashAggregate  (cost=1.11..1.14 rows=3 width=4) (actual time=0.012..0.013 rows=3 loops=1)
                     ->  Seq Scan on reference  (cost=0.00..1.10 rows=3 width=4) (actual time=0.005..0.008 rows=3 loops=1)
                           Filter: (NOT test_edd)

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	7.795 ms	0.3 %
Bitmap Index Scan	1	0.965 ms	0.0 %
Hash	2	1.805 ms	0.1 %
Hash Join	2	1041.673 ms	36.0 %
HashAggregate	3	13.820 ms	0.5 %
Index Scan	2	45.120 ms	1.6 %
Nested Loop	3	3.908 ms	0.1 %
Seq Scan	3	1777.749 ms	61.5 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
entreprise	1	22.560 ms	0.8 %
Index Scan	1	22.560 ms	100.0 %
etablissement	2	30.355 ms	1.0 %
Bitmap Heap Scan	1	7.795 ms	25.7 %
Index Scan	1	22.560 ms	74.3 %
naf	1	0.324 ms	0.0 %
Seq Scan	1	0.324 ms	100.0 %
reference	1	0.008 ms	0.0 %
Seq Scan	1	0.008 ms	100.0 %
resultat_entrep_mensuel	1	1777.417 ms	61.4 %
Seq Scan	1	1777.417 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: gKH

Colorize:

Columns visibility:

Per node type stats

Per table stats