explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5QTb : RRI PP BUFFERS

Settings
# exclusive inclusive rows x rows loops node
1. 0.085 3,317.338 ↑ 1.0 1 1

Aggregate (cost=950,622.44..950,622.45 rows=1 width=8) (actual time=3,317.338..3,317.338 rows=1 loops=1)

  • Buffers: shared hit=1,002,969, temp read=514 written=510
  • Execution time: 3,317.719 ms(101 lignes)
2. 18.343 3,317.253 ↓ 2.4 333 1

Hash Left Join (cost=243,581.20..950,622.10 rows=136 width=8) (actual time=275.927..3,317.253 rows=333 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,511
  • Buffers: shared hit=1,002,969, temp read=514 written=510
3. 171.517 3,214.127 ↑ 1.0 45,841 1

Nested Loop Left Join (cost=12,272.69..719,049.22 rows=45,848 width=16) (actual time=132.360..3,214.127 rows=45,841 loops=1)

  • Buffers: shared hit=914,371, temp read=514 written=510
4. 30.224 574.216 ↑ 1.0 45,711 1

Hash Left Join (cost=12,263.80..305,253.55 rows=45,848 width=16) (actual time=132.316..574.216 rows=45,711 loops=1)

  • Hash Cond: (dossier0_.id = dossierret11_.id_dossier)
  • Buffers: shared hit=364,190, temp read=514 written=510
5. 27.211 509.607 ↑ 1.0 45,711 1

Hash Join (cost=9,118.52..300,704.16 rows=45,848 width=8) (actual time=97.514..509.607 rows=45,711 loops=1)

  • Hash Cond: (individus1_.id_dossier = dossier0_.id)
  • Buffers: shared hit=363,393, temp read=245 written=243
6. 64.668 445.038 ↑ 1.0 45,711 1

Hash Left Join (cost=5,162.62..295,333.86 rows=45,848 width=8) (actual time=59.696..445.038 rows=45,711 loops=1)

  • Hash Cond: ((etranger2_.id = nationalit3_.etranger_id) AND ((SubPlan 1) = refpays4_.id))
  • Buffers: shared hit=361,810
7. 25.975 75.226 ↑ 1.0 45,711 1

Hash Left Join (cost=2,429.15..5,876.27 rows=45,848 width=16) (actual time=24.607..75.226 rows=45,711 loops=1)

  • Hash Cond: (individus1_.id_etranger = etranger2_.id)
  • Buffers: shared hit=3,153
8. 24.887 24.887 ↑ 1.0 45,711 1

Seq Scan on individu individus1_ (cost=0.00..2,816.71 rows=45,848 width=16) (actual time=0.008..24.887 rows=45,711 loops=1)

  • Filter: principal
  • Rows Removed by Filter: 20,160
  • Buffers: shared hit=2,158
9. 10.741 24.364 ↑ 1.0 63,740 1

Hash (cost=1,632.40..1,632.40 rows=63,740 width=8) (actual time=24.363..24.364 rows=63,740 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,002kB
  • Buffers: shared hit=995
10. 13.623 13.623 ↑ 1.0 63,740 1

Seq Scan on etranger etranger2_ (cost=0.00..1,632.40 rows=63,740 width=8) (actual time=0.008..13.623 rows=63,740 loops=1)

  • Buffers: shared hit=995
11. 11.739 34.811 ↑ 1.0 61,594 1

Hash (cost=1,809.56..1,809.56 rows=61,594 width=16) (actual time=34.811..34.811 rows=61,594 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,400kB
  • Buffers: shared hit=335
12. 15.513 23.072 ↑ 1.0 61,594 1

Hash Join (cost=13.70..1,809.56 rows=61,594 width=16) (actual time=0.106..23.072 rows=61,594 loops=1)

  • Hash Cond: (nationalit3_.pays_id = refpays4_.id)
  • Buffers: shared hit=335
13. 7.474 7.474 ↑ 1.0 61,594 1

Seq Scan on etranger_nationalite nationalit3_ (cost=0.00..948.94 rows=61,594 width=16) (actual time=0.008..7.474 rows=61,594 loops=1)

  • Buffers: shared hit=333
14. 0.032 0.085 ↑ 1.0 202 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
  • Buffers: shared hit=2
15. 0.053 0.053 ↑ 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.030..0.053 rows=202 loops=1)

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

SubPlan (for Hash Left Join)

17. 0.000 270.333 ↑ 1.0 1 90,111

Aggregate (cost=12.48..12.49 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=90,111)

  • Buffers: shared hit=358,322
18. 91.422 270.333 ↑ 1.0 1 90,111

Nested Loop (cost=0.43..12.48 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=90,111)

  • Buffers: shared hit=358,322
19. 90.111 90.111 ↑ 1.0 1 90,111

Index Scan using idx_etranger_nationalite_id on etranger_nationalite nationalit5_ (cost=0.29..8.31 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=90,111)

  • Index Cond: (etranger2_.id = etranger_id)
  • Buffers: shared hit=269,521
20. 88.800 88.800 ↑ 1.0 1 88,800

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=88,800)

  • Index Cond: (id = nationalit5_.pays_id)
  • Heap Fetches: 0
  • Buffers: shared hit=88,801
21. 17.375 37.358 ↑ 1.0 86,751 1

Hash (cost=2,447.51..2,447.51 rows=86,751 width=16) (actual time=37.358..37.358 rows=86,751 loops=1)

  • Buckets: 131,072 Batches: 2 Memory Usage: 2,862kB
  • Buffers: shared hit=1,580, temp written=164
22. 19.983 19.983 ↑ 1.0 86,751 1

Seq Scan on dossier dossier0_ (cost=0.00..2,447.51 rows=86,751 width=16) (actual time=0.004..19.983 rows=86,751 loops=1)

  • Buffers: shared hit=1,580
23. 16.409 34.385 ↑ 1.0 85,746 1

Hash (cost=1,654.46..1,654.46 rows=85,746 width=16) (actual time=34.385..34.385 rows=85,746 loops=1)

  • Buckets: 131,072 Batches: 2 Memory Usage: 3,034kB
  • Buffers: shared hit=797, temp written=188
24. 17.976 17.976 ↑ 1.0 85,746 1

Seq Scan on dossier_retour dossierret11_ (cost=0.00..1,654.46 rows=85,746 width=16) (actual time=0.010..17.976 rows=85,746 loops=1)

  • Buffers: shared hit=797
25. 2,331.261 2,468.394 ↑ 1.0 1 45,711

Index Scan using idx_date_statut_retour on statut_dossier_retour statuts14_ (cost=8.89..9.02 rows=1 width=24) (actual time=0.028..0.054 rows=1 loops=45,711)

  • Index Cond: (date = (SubPlan 3))
  • Filter: (dossierret11_.id = id_dossier_retour)
  • Rows Removed by Filter: 342
  • Buffers: shared hit=365,656
26.          

SubPlan (for Index Scan)

27. 0.000 137.133 ↑ 1.0 1 45,711

Aggregate (cost=8.46..8.47 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=45,711)

  • Buffers: shared hit=184,525
28. 137.133 137.133 ↓ 1.5 3 45,711

Index Scan using idx_statut_dossier_retour on statut_dossier_retour statutdoss15_ (cost=0.42..8.46 rows=2 width=8) (actual time=0.002..0.003 rows=3 loops=45,711)

  • Index Cond: (id_dossier_retour = dossierret11_.id)
  • Buffers: shared hit=184,525
29. 2.485 84.783 ↓ 1.0 11,665 1

Hash (cost=231,162.73..231,162.73 rows=11,662 width=20) (actual time=84.783..84.783 rows=11,665 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 693kB
  • Buffers: shared hit=88,598
30. 68.939 82.298 ↓ 1.0 11,665 1

Nested Loop Left Join (cost=19.56..231,162.73 rows=11,662 width=20) (actual time=0.074..82.298 rows=11,665 loops=1)

  • Buffers: shared hit=88,598
31. 1.697 1.697 ↑ 1.0 11,662 1

Seq Scan on dossier_reinsertion dossierrei10_ (cost=0.00..328.62 rows=11,662 width=16) (actual time=0.008..1.697 rows=11,662 loops=1)

  • Buffers: shared hit=212
32. 0.000 11.662 ↑ 1.0 1 11,662

Index Scan using idx_date_statut_reinsertion on statut_dossier_reinsertion statuts12_ (cost=19.56..19.78 rows=1 width=28) (actual time=0.001..0.001 rows=1 loops=11,662)

  • Index Cond: (date = (SubPlan 2))
  • Filter: (dossierrei10_.id = id_dossier_reinsertion)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=35,071
33.          

SubPlan (for Index Scan)

34. 23.324 58.310 ↑ 1.0 1 11,662

Aggregate (cost=19.26..19.27 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=11,662)

  • Buffers: shared hit=53,315
35. 23.324 34.986 ↑ 1.0 4 11,662

Bitmap Heap Scan on statut_dossier_reinsertion statutdoss13_ (cost=4.32..19.25 rows=4 width=8) (actual time=0.002..0.003 rows=4 loops=11,662)

  • Recheck Cond: (id_dossier_reinsertion = dossierrei10_.id)
  • Heap Blocks: exact=29,836
  • Buffers: shared hit=53,315
36. 11.662 11.662 ↑ 1.0 4 11,662

Bitmap Index Scan on idx_statut_dossier_reinsertion (cost=0.00..4.32 rows=4 width=0) (actual time=0.001..0.001 rows=4 loops=11,662)

  • Index Cond: (id_dossier_reinsertion = dossierrei10_.id)
  • Buffers: shared hit=23,479
Planning time : 3.119 ms