explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oA7S : RRI

Settings
# exclusive inclusive rows x rows loops node
1. 0.199 78,582.980 ↑ 1.0 1 1

Aggregate (cost=1,135,845.49..1,135,845.50 rows=1 width=8) (actual time=78,582.980..78,582.980 rows=1 loops=1)

  • Execution time: 78,583.237 ms(65 lignes)
2. 41.564 78,582.781 ↓ 3.8 307 1

Hash Left Join (cost=246,156.01..1,135,845.28 rows=81 width=8) (actual time=2,122.296..78,582.781 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
3. 293.439 77,736.615 ↓ 1.0 46,046 1

Nested Loop Left Join (cost=10,571.41..899,995.21 rows=45,967 width=16) (actual time=125.469..77,736.615 rows=46,046 loops=1)

4. 57.088 5,006.835 ↑ 1.0 45,933 1

Hash Left Join (cost=10,559.92..364,675.11 rows=45,967 width=16) (actual time=125.437..5,006.835 rows=45,933 loops=1)

  • Hash Cond: (dossier0_.id = dossierret11_.id_dossier)
5. 73.210 4,912.951 ↑ 1.0 45,933 1

Hash Left Join (cost=7,406.40..360,114.87 rows=45,967 width=8) (actual time=87.789..4,912.951 rows=45,933 loops=1)

  • Hash Cond: ((etranger2_.id = nationalit3_.etranger_id) AND ((SubPlan 1) = refpays4_.id))
6. 44.988 175.749 ↑ 1.0 45,933 1

Hash Left Join (cost=4,780.06..9,631.15 rows=45,967 width=16) (actual time=49.674..175.749 rows=45,933 loops=1)

  • Hash Cond: (individus1_.id_etranger = etranger2_.id)
7. 64.367 105.329 ↑ 1.0 45,933 1

Hash Join (cost=2,338.69..6,557.73 rows=45,967 width=16) (actual time=23.946..105.329 rows=45,933 loops=1)

  • Hash Cond: (dossier0_.id = individus1_.id_dossier)
8. 17.335 17.335 ↑ 1.0 86,988 1

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

9. 9.411 23.627 ↑ 1.0 45,933 1

Hash (cost=1,764.10..1,764.10 rows=45,967 width=16) (actual time=23.627..23.627 rows=45,933 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,666kB
10. 14.216 14.216 ↑ 1.0 45,933 1

Seq Scan on individu individus1_ (cost=0.00..1,764.10 rows=45,967 width=16) (actual time=0.007..14.216 rows=45,933 loops=1)

  • Filter: principal
  • Rows Removed by Filter: 20,277
11. 12.037 25.432 ↑ 1.0 64,061 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,015kB
12. 13.395 13.395 ↑ 1.0 64,061 1

Seq Scan on etranger etranger2_ (cost=0.00..1,640.61 rows=64,061 width=8) (actual time=0.011..13.395 rows=64,061 loops=1)

13. 12.720 37.656 ↑ 1.0 59,272 1

Hash (cost=1,737.26..1,737.26 rows=59,272 width=16) (actual time=37.656..37.656 rows=59,272 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,291kB
14. 17.371 24.936 ↑ 1.0 59,272 1

Hash Join (cost=8.54..1,737.26 rows=59,272 width=16) (actual time=0.105..24.936 rows=59,272 loops=1)

  • Hash Cond: (nationalit3_.pays_id = refpays4_.id)
15. 7.486 7.486 ↑ 1.0 59,272 1

Seq Scan on etranger_nationalite nationalit3_ (cost=0.00..913.72 rows=59,272 width=16) (actual time=0.012..7.486 rows=59,272 loops=1)

16. 0.037 0.079 ↑ 1.0 202 1

Hash (cost=6.02..6.02 rows=202 width=8) (actual time=0.079..0.079 rows=202 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
17. 0.042 0.042 ↑ 1.0 202 1

Seq Scan on ref_pays refpays4_ (cost=0.00..6.02 rows=202 width=8) (actual time=0.006..0.042 rows=202 loops=1)

18.          

SubPlan (for Hash Left Join)

19. 88.968 4,626.336 ↑ 1.0 1 88,968

Aggregate (cost=15.11..15.12 rows=1 width=8) (actual time=0.052..0.052 rows=1 loops=88,968)

20. 2,204.784 4,537.368 ↑ 1.0 1 88,968

Hash Join (cost=8.32..15.11 rows=1 width=8) (actual time=0.022..0.051 rows=1 loops=88,968)

  • Hash Cond: (refpays6_.id = nationalit5_.pays_id)
21. 2,065.680 2,065.680 ↑ 1.0 202 86,070

Seq Scan on ref_pays refpays6_ (cost=0.00..6.02 rows=202 width=8) (actual time=0.001..0.024 rows=202 loops=86,070)

22. 88.968 266.904 ↑ 1.0 1 88,968

Hash (cost=8.31..8.31 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=88,968)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
23. 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)
24. 18.466 36.796 ↑ 1.0 85,979 1

Hash (cost=1,658.79..1,658.79 rows=85,979 width=16) (actual time=36.795..36.796 rows=85,979 loops=1)

  • Buckets: 131,072 Batches: 2 Memory Usage: 3,041kB
25. 18.330 18.330 ↑ 1.0 85,979 1

Seq Scan on dossier_retour dossierret11_ (cost=0.00..1,658.79 rows=85,979 width=16) (actual time=0.010..18.330 rows=85,979 loops=1)

26. 72,206.676 72,436.341 ↑ 1.0 1 45,933

Index Scan using idx_date_statut_retour on statut_dossier_retour statuts14_ (cost=11.49..11.64 rows=1 width=24) (actual time=0.741..1.577 rows=1 loops=45,933)

  • Index Cond: (date = (SubPlan 3))
  • Filter: (dossierret11_.id = id_dossier_retour)
  • Rows Removed by Filter: 9,547
27.          

SubPlan (for Index Scan)

28. 45.933 229.665 ↑ 1.0 1 45,933

Aggregate (cost=11.06..11.07 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=45,933)

29. 183.732 183.732 ↑ 1.0 3 45,933

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

  • Index Cond: (id_dossier_retour = dossierret11_.id)
30. 3.771 804.602 ↓ 1.0 11,748 1

Hash (cost=235,437.79..235,437.79 rows=11,745 width=20) (actual time=804.601..804.602 rows=11,748 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 718kB
31. 58.604 800.831 ↓ 1.0 11,748 1

Nested Loop Left Join (cost=19.78..235,437.79 rows=11,745 width=20) (actual time=0.032..800.831 rows=11,748 loops=1)

32. 2.292 2.292 ↑ 1.0 11,745 1

Seq Scan on dossier_reinsertion dossierrei10_ (cost=0.00..291.45 rows=11,745 width=16) (actual time=0.008..2.292 rows=11,745 loops=1)

33. 692.955 739.935 ↑ 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.033..0.063 rows=1 loops=11,745)

  • Index Cond: (date = (SubPlan 2))
  • Filter: (dossierrei10_.id = id_dossier_reinsertion)
  • Rows Removed by Filter: 369
34.          

SubPlan (for Index Scan)

35. 11.745 46.980 ↑ 1.0 1 11,745

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

36. 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.001..0.003 rows=4 loops=11,745)

  • Index Cond: (id_dossier_reinsertion = dossierrei10_.id)
Planning time : 1.597 ms