explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EUcq : RRI buffers

Settings
# exclusive inclusive rows x rows loops node
1. 0.208 93,893.804 ↑ 1.0 1 1

Aggregate (cost=1,205,871.78..1,205,871.79 rows=1 width=8) (actual time=93,893.804..93,893.804 rows=1 loops=1)

  • Buffers: shared hit=6409299 read=500
2. 41.209 93,893.596 ↓ 3.1 307 1

Hash Left Join (cost=259,969.54..1,205,871.53 rows=98 width=8) (actual time=2,097.918..93,893.596 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=6409299 read=500
3. 23.328 92,866.558 ↑ 1.0 46,046 1

Nested Loop Left Join (cost=18,990.04..964,625.78 rows=46,102 width=16) (actual time=118.562..92,866.558 rows=46,046 loops=1)

  • Buffers: shared hit=6257572 read=500
4. 99.038 885.364 ↑ 1.0 45,933 1

Hash Left Join (cost=18,978.24..409,426.56 rows=46,102 width=8) (actual time=118.457..885.364 rows=45,933 loops=1)

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

Hash Left Join (cost=15,355.75..25,287.58 rows=46,102 width=16) (actual time=70.205..204.627 rows=45,933 loops=1)

  • Hash Cond: (individus1_.id_etranger = etranger2_.id)
  • Buffers: shared hit=4756
6. 67.864 127.803 ↑ 1.0 45,933 1

Hash Join (cost=9,914.38..19,212.30 rows=46,102 width=16) (actual time=36.995..127.803 rows=45,933 loops=1)

  • Hash Cond: (dossier0_.id = individus1_.id_dossier)
  • Buffers: shared hit=3756
7. 23.349 23.349 ↑ 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..23.349 rows=86,988 loops=1)

  • Buffers: shared hit=1584
8. 11.416 36.590 ↑ 1.0 45,933 1

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

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

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

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

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3015kB
  • Buffers: shared hit=1000
11. 18.438 18.438 ↑ 1.0 64,061 1

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

  • Buffers: shared hit=1000
12. 15.237 47.891 ↑ 1.0 59,272 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3291kB
  • Buffers: shared hit=420
13. 21.602 32.654 ↑ 1.0 59,272 1

Hash Join (cost=41.70..2,733.41 rows=59,272 width=16) (actual time=0.267..32.654 rows=59,272 loops=1)

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

  • Buffers: shared hit=321
15. 0.042 0.236 ↑ 1.0 202 1

Hash (cost=39.18..39.18 rows=202 width=8) (actual time=0.236..0.236 rows=202 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
  • Buffers: shared hit=99
16. 0.194 0.194 ↑ 1.0 202 1

Index Only Scan using ref_pays_pkey on ref_pays refpays4_ (cost=0.14..39.18 rows=202 width=8) (actual time=0.042..0.194 rows=202 loops=1)

  • Heap Fetches: 154
  • Buffers: shared hit=99
17.          

SubPlan (for Hash Left Join)

18. 88.968 533.808 ↑ 1.0 1 88,968

Aggregate (cost=16.48..16.49 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=88,968)

  • Buffers: shared hit=435577
19. 180.834 444.840 ↑ 1.0 1 88,968

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

  • Buffers: shared hit=435577
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..8.16 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=86,070)

  • Index Cond: (id = nationalit5_.pays_id)
  • Heap Fetches: 85038
  • Buffers: shared hit=171109
22. 304.572 91,957.866 ↑ 1.0 1 45,933

Nested Loop Left Join (cost=11.80..12.03 rows=1 width=16) (actual time=0.945..2.002 rows=1 loops=45,933)

  • Buffers: shared hit=5816819 read=500
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=137108
24. 91,290.420 91,515.495 ↑ 1.0 1 45,015

Index Scan using idx_date_statut_retour on statut_dossier_retour statuts14_ (cost=11.51..11.67 rows=1 width=24) (actual time=0.955..2.033 rows=1 loops=45,015)

  • Index Cond: (date = (SubPlan 3))
  • Filter: (dossierret11_.id = id_dossier_retour)
  • Rows Removed by Filter: 9742
  • Buffers: shared hit=5478302 read=500
25.          

SubPlan (for Index Scan)

26. 45.015 225.075 ↑ 1.0 1 45,015

Aggregate (cost=11.08..11.09 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..11.07 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.815 985.829 ↓ 1.0 11,748 1

Hash (cost=240,832.69..240,832.69 rows=11,745 width=20) (actual time=985.829..985.829 rows=11,748 loops=1)

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

Nested Loop Left Join (cost=20.17..240,832.69 rows=11,745 width=20) (actual time=0.062..981.014 rows=11,748 loops=1)

  • Buffers: shared hit=151727
30. 2.354 2.354 ↑ 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.008..2.354 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=20.17..20.40 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=95653
32.          

SubPlan (for Index Scan)

33. 11.745 58.725 ↑ 1.0 1 11,745

Aggregate (cost=19.87..19.88 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.86 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.747 ms
Execution time : 93,894.307 ms