explain.depesz.com

PostgreSQL's explain analyze made readable

Result: okEj : RRI PROD

Settings
# exclusive inclusive rows x rows loops node
1. 0.176 92,469.145 ↑ 1.0 1 1

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

  • Buffers: shared hit=6323842
  • Execution time: 92469.552 ms(95 lignes)
2. 35.454 92,468.969 ↓ 3.0 307 1

Hash Left Join (cost=252,347.41..1,099,664.77 rows=104 width=8) (actual time=2,144.231..92,468.969 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: 45742
  • Buffers: shared hit=6323842
3. 30.813 91,433.353 ↓ 1.0 46,046 1

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

  • Buffers: shared hit=6172115
4. 94.766 776.731 ↑ 1.0 45,933 1

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

  • Hash Cond: ((etranger2_.id = nationalit3_.etranger_id) AND ((SubPlan 1) = refpays4_.id))
  • Buffers: shared hit=354796
5. 41.411 188.830 ↑ 1.0 45,933 1

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

  • Hash Cond: (individus1_.id_etranger = etranger2_.id)
  • Buffers: shared hit=3934
6. 62.097 118.449 ↑ 1.0 45,933 1

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

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

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

  • Buffers: shared hit=1584
8. 11.670 36.253 ↑ 1.0 45,933 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2666kB
  • Buffers: shared hit=2169
9. 24.583 24.583 ↑ 1.0 45,933 1

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

  • Filter: principal
  • Rows Removed by Filter: 20277
  • Buffers: shared hit=2169
10. 15.443 28.970 ↑ 1.0 64,061 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3015kB
  • Buffers: shared hit=178
11. 13.527 13.527 ↑ 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.040..13.527 rows=64,061 loops=1)

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

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3291kB
  • Buffers: shared hit=323
13. 24.629 33.202 ↑ 1.0 59,272 1

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

  • Hash Cond: (nationalit3_.pays_id = refpays4_.id)
  • Buffers: shared hit=323
14. 8.476 8.476 ↑ 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.011..8.476 rows=59,272 loops=1)

  • Buffers: shared hit=321
15. 0.039 0.097 ↑ 1.0 202 1

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

  • Buckets: 1024 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=350539
19. 91.866 355.872 ↑ 1.0 1 88,968

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

  • Buffers: shared hit=350539
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=264468
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=86071
22. 323.883 90,625.809 ↑ 1.0 1 45,933

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

  • Buffers: shared hit=5817319
23. 91.866 91.866 ↑ 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.002 rows=1 loops=45,933)

  • Index Cond: (dossier0_.id = id_dossier)
  • Buffers: shared hit=137108
24. 89,984.985 90,210.060 ↑ 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.940..2.004 rows=1 loops=45,015)

  • Index Cond: (date = (SubPlan 3))
  • Filter: (dossierret11_.id = id_dossier_retour)
  • Rows Removed by Filter: 9742
  • Buffers: shared hit=5478802
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=201409
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=201409
28. 4.878 1,000.162 ↓ 1.0 11,748 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 718kB
  • Buffers: shared hit=151727
29. 76.767 995.284 ↓ 1.0 11,748 1

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

  • Buffers: shared hit=151727
30. 2.407 2.407 ↑ 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.007..2.407 rows=11,745 loops=1)

  • Buffers: shared hit=239
31. 857.385 916.110 ↑ 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.078 rows=1 loops=11,745)

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

SubPlan (for Index Scan)

33. 11.745 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=55835
34. 46.980 46.980 ↑ 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.004 rows=4 loops=11,745)

  • Index Cond: (id_dossier_reinsertion = dossierrei10_.id)
  • Buffers: shared hit=55835
Planning time : 4.283 ms