explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nJ3L : RRI PROD APRES VACCUM

Settings
# exclusive inclusive rows x rows loops node
1. 0.188 92,808.529 ↑ 1.0 1 1

Aggregate (cost=1,099,665.03..1,099,665.04 rows=1 width=8) (actual time=92,808.528..92,808.529 rows=1 loops=1)

  • Buffers: shared hit=6,323,842
  • Execution time: 92,809.077 ms(95 lignes)
2. 43.099 92,808.341 ↓ 3.0 307 1

Hash Left Join (cost=252,347.41..1,099,664.77 rows=104 width=8) (actual time=2,080.194..92,808.341 rows=307 loops=1)

  • Hash Cond: (dossier0_.id = dossierrei10_.id_dossier)
  • Filter: (((statuts14_.libelle)::text = 'TRANSFERE_DT'::text) OR ((statuts12_.libelle)::text = 'TRANSFERE_DT'::text))
  • Rows Removed by Filter: 45,742
  • Buffers: shared hit=6,323,842
3. 52.764 91,783.460 ↓ 1.0 46,046 1

Nested Loop Left Join (cost=15,993.37..863,045.01 rows=46,009 width=16) (actual time=108.034..91,783.460 rows=46,046 loops=1)

  • Buffers: shared hit=6,172,115
4. 144.789 829.289 ↑ 1.0 45,933 1

Hash Left Join (cost=15,981.67..313,859.65 rows=46,009 width=8) (actual time=107.954..829.289 rows=45,933 loops=1)

  • Hash Cond: ((etranger2_.id = nationalit3_.etranger_id) AND ((SubPlan 1) = refpays4_.id))
  • Buffers: shared hit=354,796
5. 41.220 194.035 ↑ 1.0 45,933 1

Hash Left Join (cost=12,387.18..22,534.27 rows=46,009 width=16) (actual time=61.837..194.035 rows=45,933 loops=1)

  • Hash Cond: (individus1_.id_etranger = etranger2_.id)
  • Buffers: shared hit=3,934
6. 67.993 125.990 ↑ 1.0 45,933 1

Hash Join (cost=9,913.21..19,427.68 rows=46,009 width=16) (actual time=34.690..125.990 rows=45,933 loops=1)

  • Hash Cond: (dossier0_.id = individus1_.id_dossier)
  • Buffers: shared hit=3,756
7. 23.667 23.667 ↑ 1.0 86,988 1

Seq Scan on dossier dossier0_ (cost=0.00..7,205.88 rows=86,988 width=16) (actual time=0.010..23.667 rows=86,988 loops=1)

  • Buffers: shared hit=1,584
8. 11.509 34.330 ↑ 1.0 45,933 1

Hash (cost=9,338.10..9,338.10 rows=46,009 width=16) (actual time=34.330..34.330 rows=45,933 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,666kB
  • Buffers: shared hit=2,169
9. 22.821 22.821 ↑ 1.0 45,933 1

Seq Scan on individu individus1_ (cost=0.00..9,338.10 rows=46,009 width=16) (actual time=0.005..22.821 rows=45,933 loops=1)

  • Filter: principal
  • Rows Removed by Filter: 20,277
  • Buffers: shared hit=2,169
10. 14.448 26.825 ↑ 1.0 64,061 1

Hash (cost=1,673.20..1,673.20 rows=64,061 width=8) (actual time=26.825..26.825 rows=64,061 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,015kB
  • Buffers: shared hit=178
11. 12.377 12.377 ↑ 1.0 64,061 1

Index Only Scan using etranger_pkey on etranger etranger2_ (cost=0.29..1,673.20 rows=64,061 width=8) (actual time=0.047..12.377 rows=64,061 loops=1)

  • Heap Fetches: 0
  • Buffers: shared hit=178
12. 14.680 45.625 ↑ 1.0 59,272 1

Hash (cost=2,705.41..2,705.41 rows=59,272 width=16) (actual time=45.625..45.625 rows=59,272 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,291kB
  • Buffers: shared hit=323
13. 22.726 30.945 ↑ 1.0 59,272 1

Hash Join (cost=13.70..2,705.41 rows=59,272 width=16) (actual time=0.130..30.945 rows=59,272 loops=1)

  • Hash Cond: (nationalit3_.pays_id = refpays4_.id)
  • Buffers: shared hit=323
14. 8.114 8.114 ↑ 1.0 59,272 1

Seq Scan on etranger_nationalite nationalit3_ (cost=0.00..1,876.72 rows=59,272 width=16) (actual time=0.008..8.114 rows=59,272 loops=1)

  • Buffers: shared hit=321
15. 0.047 0.105 ↑ 1.0 202 1

Hash (cost=11.17..11.17 rows=202 width=8) (actual time=0.105..0.105 rows=202 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
  • Buffers: shared hit=2
16. 0.058 0.058 ↑ 1.0 202 1

Index Only Scan using ref_pays_pkey on ref_pays refpays4_ (cost=0.14..11.17 rows=202 width=8) (actual time=0.029..0.058 rows=202 loops=1)

  • Heap Fetches: 0
  • Buffers: shared hit=2
17.          

SubPlan (for Hash Left Join)

18. 88.968 444.840 ↑ 1.0 1 88,968

Aggregate (cost=12.48..12.49 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=88,968)

  • Buffers: shared hit=350,539
19. 91.866 355.872 ↑ 1.0 1 88,968

Nested Loop (cost=0.43..12.48 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=88,968)

  • Buffers: shared hit=350,539
20. 177.936 177.936 ↑ 1.0 1 88,968

Index Scan using idx_etranger_nationalite_id on etranger_nationalite nationalit5_ (cost=0.29..8.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=88,968)

  • Index Cond: (etranger2_.id = etranger_id)
  • Buffers: shared hit=264,468
21. 86.070 86.070 ↑ 1.0 1 86,070

Index Only Scan using ref_pays_pkey on ref_pays refpays6_ (cost=0.14..4.16 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=86,070)

  • Index Cond: (id = nationalit5_.pays_id)
  • Heap Fetches: 0
  • Buffers: shared hit=86,071
22. 283.458 90,901.407 ↑ 1.0 1 45,933

Nested Loop Left Join (cost=11.70..11.93 rows=1 width=16) (actual time=0.936..1.979 rows=1 loops=45,933)

  • Buffers: shared hit=5,817,319
23. 137.799 137.799 ↑ 1.0 1 45,933

Index Scan using idx_dossier_retour_dossier on dossier_retour dossierret11_ (cost=0.29..0.36 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=45,933)

  • Index Cond: (dossier0_.id = id_dossier)
  • Buffers: shared hit=137,108
24. 90,255.075 90,480.150 ↑ 1.0 1 45,015

Index Scan using idx_date_statut_retour on statut_dossier_retour statuts14_ (cost=11.40..11.56 rows=1 width=24) (actual time=0.946..2.010 rows=1 loops=45,015)

  • Index Cond: (date = (SubPlan 3))
  • Filter: (dossierret11_.id = id_dossier_retour)
  • Rows Removed by Filter: 9,742
  • Buffers: shared hit=5,478,802
25.          

SubPlan (for Index Scan)

26. 45.015 225.075 ↑ 1.0 1 45,015

Aggregate (cost=10.97..10.98 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=45,015)

  • Buffers: shared hit=201,409
27. 180.060 180.060 ↑ 1.0 3 45,015

Index Scan using idx_statut_dossier_retour on statut_dossier_retour statutdoss15_ (cost=0.42..10.97 rows=3 width=8) (actual time=0.003..0.004 rows=3 loops=45,015)

  • Index Cond: (id_dossier_retour = dossierret11_.id)
  • Buffers: shared hit=201,409
28. 5.054 981.782 ↓ 1.0 11,748 1

Hash (cost=236,207.23..236,207.23 rows=11,745 width=20) (actual time=981.781..981.782 rows=11,748 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 718kB
  • Buffers: shared hit=151,727
29. 70.019 976.728 ↓ 1.0 11,748 1

Nested Loop Left Join (cost=19.78..236,207.23 rows=11,745 width=20) (actual time=0.058..976.728 rows=11,748 loops=1)

  • Buffers: shared hit=151,727
30. 2.344 2.344 ↑ 1.0 11,745 1

Seq Scan on dossier_reinsertion dossierrei10_ (cost=0.00..1,073.45 rows=11,745 width=16) (actual time=0.006..2.344 rows=11,745 loops=1)

  • Buffers: shared hit=239
31. 845.640 904.365 ↑ 1.0 1 11,745

Index Scan using idx_date_statut_reinsertion on statut_dossier_reinsertion statuts12_ (cost=19.78..20.01 rows=1 width=28) (actual time=0.040..0.077 rows=1 loops=11,745)

  • Index Cond: (date = (SubPlan 2))
  • Filter: (dossierrei10_.id = id_dossier_reinsertion)
  • Rows Removed by Filter: 369
  • Buffers: shared hit=95,653
32.          

SubPlan (for Index Scan)

33. 23.490 58.725 ↑ 1.0 1 11,745

Aggregate (cost=19.48..19.49 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=11,745)

  • Buffers: shared hit=55,835
34. 35.235 35.235 ↑ 1.0 4 11,745

Index Scan using idx_statut_dossier_reinsertion on statut_dossier_reinsertion statutdoss13_ (cost=0.29..19.47 rows=4 width=8) (actual time=0.002..0.003 rows=4 loops=11,745)

  • Index Cond: (id_dossier_reinsertion = dossierrei10_.id)
  • Buffers: shared hit=55,835
Planning time : 4.625 ms