explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0DE0 : RRI PP

Settings
# exclusive inclusive rows x rows loops node
1. 0.103 3,338.135 ↑ 1.0 1 1

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

2. 23.686 3,338.032 ↓ 2.4 333 1

Hash Left Join (cost=243,581.20..950,622.10 rows=136 width=8) (actual time=294.349..3,338.032 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: 45511
3. 199.058 3,225.468 ↑ 1.0 45,841 1

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

4. 37.681 603.727 ↑ 1.0 45,711 1

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

  • Hash Cond: (dossier0_.id = dossierret11_.id_dossier)
5. 31.366 531.629 ↑ 1.0 45,711 1

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

  • Hash Cond: (individus1_.id_dossier = dossier0_.id)
6. 63.838 455.068 ↑ 1.0 45,711 1

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

  • Hash Cond: ((etranger2_.id = nationalit3_.etranger_id) AND ((SubPlan 1) = refpays4_.id))
7. 33.015 83.142 ↑ 1.0 45,711 1

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

  • Hash Cond: (individus1_.id_etranger = etranger2_.id)
8. 23.299 23.299 ↑ 1.0 45,711 1

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

  • Filter: principal
  • Rows Removed by Filter: 20160
9. 12.165 26.828 ↑ 1.0 63,740 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3002kB
10. 14.663 14.663 ↑ 1.0 63,740 1

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

11. 13.518 37.755 ↑ 1.0 61,594 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3400kB
12. 16.094 24.237 ↑ 1.0 61,594 1

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

  • Hash Cond: (nationalit3_.pays_id = refpays4_.id)
13. 8.033 8.033 ↑ 1.0 61,594 1

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

14. 0.041 0.110 ↑ 1.0 202 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
15. 0.069 0.069 ↑ 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.045..0.069 rows=202 loops=1)

  • Heap Fetches: 0
16.          

SubPlan (for Hash Left Join)

17. 90.111 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)

18. 1.311 180.222 ↑ 1.0 1 90,111

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

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)
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.000..0.001 rows=1 loops=88,800)

  • Index Cond: (id = nationalit5_.pays_id)
  • Heap Fetches: 0
21. 21.094 45.195 ↑ 1.0 86,751 1

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

  • Buckets: 131072 Batches: 2 Memory Usage: 2862kB
22. 24.101 24.101 ↑ 1.0 86,751 1

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

23. 16.921 34.417 ↑ 1.0 85,746 1

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

  • Buckets: 131072 Batches: 2 Memory Usage: 3034kB
24. 17.496 17.496 ↑ 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.015..17.496 rows=85,746 loops=1)

25. 2,285.550 2,422.683 ↑ 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.053 rows=1 loops=45,711)

  • Index Cond: (date = (SubPlan 3))
  • Filter: (dossierret11_.id = id_dossier_retour)
  • Rows Removed by Filter: 342
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)

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)
29. 3.114 88.878 ↓ 1.0 11,665 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 693kB
30. 72.254 85.764 ↓ 1.0 11,665 1

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

31. 1.848 1.848 ↑ 1.0 11,662 1

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

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
33.          

SubPlan (for Index Scan)

34. 11.662 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)

35. 23.324 46.648 ↑ 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.004 rows=4 loops=11,662)

  • Recheck Cond: (id_dossier_reinsertion = dossierrei10_.id)
  • Heap Blocks: exact=29836
36. 23.324 23.324 ↑ 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.002..0.002 rows=4 loops=11,662)

  • Index Cond: (id_dossier_reinsertion = dossierrei10_.id)
Planning time : 3.846 ms
Execution time : 3,338.674 ms