explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ih8J

Settings
# exclusive inclusive rows x rows loops node
1. 76.148 345.251 ↓ 345.2 162,222 1

Hash Left Join (cost=4,144.05..6,785.74 rows=470 width=1,059) (actual time=106.464..345.251 rows=162,222 loops=1)

  • Hash Cond: (i19_.organisme_gestionnaire = i22_.orgid)
2. 83.986 268.592 ↓ 345.2 162,222 1

Hash Join (cost=4,109.26..6,744.49 rows=470 width=1,009) (actual time=105.916..268.592 rows=162,222 loops=1)

  • Hash Cond: (i6_.id_jdd = i23_.id_jdd)
3. 7.699 107.597 ↓ 1.0 13,531 1

Hash Left Join (cost=1,388.63..3,966.91 rows=13,471 width=993) (actual time=28.865..107.597 rows=13,531 loops=1)

  • Hash Cond: (i6_.id_jdd = i19_.id_jdd_valide)
4. 7.075 94.253 ↓ 1.0 13,531 1

Hash Left Join (cost=1,021.13..3,531.98 rows=13,471 width=827) (actual time=23.200..94.253 rows=13,531 loops=1)

  • Hash Cond: (i0_.id_ca = i15_.id_ca_valide)
5. 6.795 83.787 ↓ 1.0 13,531 1

Hash Left Join (cost=821.93..2,305.61 rows=13,471 width=595) (actual time=19.796..83.787 rows=13,531 loops=1)

  • Hash Cond: (i6_.utilisateur_gestionnaire = i14_.uid)
6. 8.005 76.408 ↓ 1.0 13,531 1

Hash Left Join (cost=792.33..2,205.58 rows=13,471 width=580) (actual time=19.199..76.408 rows=13,531 loops=1)

  • Hash Cond: (i6_.organisme_gestionnaire = i13_.orgid)
7. 8.090 67.719 ↓ 1.0 13,531 1

Hash Left Join (cost=757.54..1,985.56 rows=13,471 width=530) (actual time=18.498..67.719 rows=13,531 loops=1)

  • Hash Cond: (i0_.organisme_gestionnaire = i12_.orgid)
8. 5.649 58.783 ↓ 1.0 13,531 1

Nested Loop (cost=722.74..1,765.54 rows=13,471 width=480) (actual time=17.625..58.783 rows=13,531 loops=1)

9. 0.017 0.017 ↑ 1.0 1 1

Index Scan using pkey_ref_statut_metadonnee on ref_statut_metadonnee i11_ (cost=0.15..8.17 rows=1 width=68) (actual time=0.011..0.017 rows=1 loops=1)

  • Index Cond: (id_ref = 3)
10. 7.840 53.117 ↓ 1.0 13,531 1

Hash Join (cost=722.59..1,622.66 rows=13,471 width=416) (actual time=17.609..53.117 rows=13,531 loops=1)

  • Hash Cond: (i6_.id_numerisateur = i10_.uid)
11. 7.764 44.622 ↓ 1.0 13,531 1

Hash Left Join (cost=692.99..1,407.84 rows=13,471 width=401) (actual time=16.926..44.622 rows=13,531 loops=1)

  • Hash Cond: (i7_.role_acteur = i9_.id_ref)
12. 7.889 36.845 ↓ 1.0 13,531 1

Hash Left Join (cost=669.49..1,199.11 rows=13,471 width=401) (actual time=16.897..36.845 rows=13,531 loops=1)

  • Hash Cond: (i7_.organisme = i8_.orgid)
13. 7.361 28.254 ↓ 1.0 13,531 1

Hash Join (cost=634.70..979.09 rows=13,471 width=349) (actual time=16.163..28.254 rows=13,531 loops=1)

  • Hash Cond: (i6_.id_ca = i0_.id_ca)
14. 2.749 8.575 ↑ 1.1 3,902 1

Hash Join (cost=283.12..466.93 rows=4,134 width=109) (actual time=3.828..8.575 rows=3,902 loops=1)

  • Hash Cond: (i7_.id_jdd = i6_.id_jdd)
15. 2.025 2.025 ↓ 1.0 4,825 1

Seq Scan on jdd_contact_donnees i7_ (cost=0.00..124.89 rows=4,689 width=14) (actual time=0.008..2.025 rows=4,825 loops=1)

16. 1.183 3.801 ↑ 1.1 1,445 1

Hash (cost=263.95..263.95 rows=1,533 width=95) (actual time=3.801..3.801 rows=1,445 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 183kB
17. 2.618 2.618 ↑ 1.1 1,445 1

Seq Scan on jdd i6_ (cost=0.00..263.95 rows=1,533 width=95) (actual time=0.022..2.618 rows=1,445 loops=1)

  • Filter: ((NOT blocage_diffusion) AND (id_statut_metadonnee = 3) AND (type_donnees = 1) AND (id_diffusion = ANY ('{9,10,11,12,13}'::integer[])))
  • Rows Removed by Filter: 318
18. 2.650 12.318 ↑ 1.0 1,917 1

Hash (cost=327.38..327.38 rows=1,936 width=244) (actual time=12.318..12.318 rows=1,917 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 504kB
19. 1.506 9.668 ↑ 1.0 1,917 1

Hash Left Join (cost=197.48..327.38 rows=1,936 width=244) (actual time=3.621..9.668 rows=1,917 loops=1)

  • Hash Cond: (i1_.role_acteur = i3_.id_ref)
20. 1.703 8.150 ↑ 1.0 1,917 1

Hash Left Join (cost=173.98..277.26 rows=1,936 width=244) (actual time=3.593..8.150 rows=1,917 loops=1)

  • Hash Cond: (i1_.organisme = i2_.orgid)
21. 1.809 5.657 ↑ 1.0 1,917 1

Hash Right Join (cost=139.19..215.85 rows=1,936 width=192) (actual time=2.782..5.657 rows=1,917 loops=1)

  • Hash Cond: (i1_.id_ca = i0_.id_ca)
22. 1.093 1.093 ↑ 1.0 1,936 1

Seq Scan on ca_contact i1_ (cost=0.00..49.86 rows=1,986 width=14) (actual time=0.009..1.093 rows=1,936 loops=1)

23. 0.601 2.755 ↑ 1.0 579 1

Hash (cost=131.95..131.95 rows=579 width=182) (actual time=2.755..2.755 rows=579 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 113kB
24. 0.324 2.154 ↑ 1.0 579 1

Nested Loop (cost=29.75..131.95 rows=579 width=182) (actual time=0.619..2.154 rows=579 loops=1)

25. 0.008 0.008 ↑ 1.0 1 1

Index Scan using pkey_ref_statut_metadonnee on ref_statut_metadonnee i5_ (cost=0.15..8.17 rows=1 width=68) (actual time=0.006..0.008 rows=1 loops=1)

  • Index Cond: (id_ref = 3)
26. 0.468 1.822 ↑ 1.0 579 1

Hash Join (cost=29.60..117.99 rows=579 width=118) (actual time=0.610..1.822 rows=579 loops=1)

  • Hash Cond: (i0_.id_numerisateur = i4_.uid)
27. 0.780 0.780 ↑ 1.0 580 1

Seq Scan on ca i0_ (cost=0.00..80.42 rows=580 width=103) (actual time=0.009..0.780 rows=580 loops=1)

  • Filter: (id_statut_metadonnee = 3)
  • Rows Removed by Filter: 16
28. 0.310 0.574 ↓ 1.0 563 1

Hash (cost=22.60..22.60 rows=560 width=19) (actual time=0.574..0.574 rows=563 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
29. 0.264 0.264 ↓ 1.0 563 1

Seq Scan on utilisateur i4_ (cost=0.00..22.60 rows=560 width=19) (actual time=0.004..0.264 rows=563 loops=1)

30. 0.415 0.790 ↓ 1.1 648 1

Hash (cost=27.13..27.13 rows=613 width=54) (actual time=0.790..0.790 rows=648 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 55kB
31. 0.375 0.375 ↓ 1.1 648 1

Seq Scan on organisme i2_ (cost=0.00..27.13 rows=613 width=54) (actual time=0.009..0.375 rows=648 loops=1)

32. 0.002 0.012 ↑ 75.0 8 1

Hash (cost=16.00..16.00 rows=600 width=4) (actual time=0.012..0.012 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
33. 0.010 0.010 ↑ 75.0 8 1

Seq Scan on ref_role_acteur i3_ (cost=0.00..16.00 rows=600 width=4) (actual time=0.006..0.010 rows=8 loops=1)

34. 0.433 0.702 ↓ 1.1 648 1

Hash (cost=27.13..27.13 rows=613 width=54) (actual time=0.702..0.702 rows=648 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 55kB
35. 0.269 0.269 ↓ 1.1 648 1

Seq Scan on organisme i8_ (cost=0.00..27.13 rows=613 width=54) (actual time=0.005..0.269 rows=648 loops=1)

36. 0.007 0.013 ↑ 75.0 8 1

Hash (cost=16.00..16.00 rows=600 width=4) (actual time=0.013..0.013 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
37. 0.006 0.006 ↑ 75.0 8 1

Seq Scan on ref_role_acteur i9_ (cost=0.00..16.00 rows=600 width=4) (actual time=0.002..0.006 rows=8 loops=1)

38. 0.314 0.655 ↓ 1.0 563 1

Hash (cost=22.60..22.60 rows=560 width=19) (actual time=0.655..0.655 rows=563 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
39. 0.341 0.341 ↓ 1.0 563 1

Seq Scan on utilisateur i10_ (cost=0.00..22.60 rows=560 width=19) (actual time=0.010..0.341 rows=563 loops=1)

40. 0.408 0.846 ↓ 1.1 648 1

Hash (cost=27.13..27.13 rows=613 width=54) (actual time=0.846..0.846 rows=648 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 55kB
41. 0.438 0.438 ↓ 1.1 648 1

Seq Scan on organisme i12_ (cost=0.00..27.13 rows=613 width=54) (actual time=0.004..0.438 rows=648 loops=1)

42. 0.394 0.684 ↓ 1.1 648 1

Hash (cost=27.13..27.13 rows=613 width=54) (actual time=0.684..0.684 rows=648 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 55kB
43. 0.290 0.290 ↓ 1.1 648 1

Seq Scan on organisme i13_ (cost=0.00..27.13 rows=613 width=54) (actual time=0.004..0.290 rows=648 loops=1)

44. 0.321 0.584 ↓ 1.0 563 1

Hash (cost=22.60..22.60 rows=560 width=19) (actual time=0.584..0.584 rows=563 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
45. 0.263 0.263 ↓ 1.0 563 1

Seq Scan on utilisateur i14_ (cost=0.00..22.60 rows=560 width=19) (actual time=0.004..0.263 rows=563 loops=1)

46. 0.171 3.391 ↓ 0.0 0 1

Hash (cost=191.77..191.77 rows=594 width=236) (actual time=3.391..3.391 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 0kB
47. 0.491 3.220 ↓ 1.0 596 1

Hash Left Join (cost=88.34..191.77 rows=594 width=236) (actual time=1.347..3.220 rows=596 loops=1)

  • Hash Cond: (i15_.organisme_gestionnaire = i18_.orgid)
48. 0.489 2.015 ↓ 1.0 596 1

Hash Left Join (cost=53.55..148.81 rows=594 width=186) (actual time=0.613..2.015 rows=596 loops=1)

  • Hash Cond: (i15_.id_numerisateur = i17_.uid)
49. 0.693 0.966 ↓ 1.0 596 1

Hash Left Join (cost=23.95..111.06 rows=594 width=171) (actual time=0.033..0.966 rows=596 loops=1)

  • Hash Cond: (i15_.id_statut_metadonnee = i16_.id_ref)
50. 0.261 0.261 ↓ 1.0 596 1

Seq Scan on ca i15_ (cost=0.00..78.94 rows=594 width=107) (actual time=0.003..0.261 rows=596 loops=1)

51. 0.005 0.012 ↑ 124.0 5 1

Hash (cost=16.20..16.20 rows=620 width=68) (actual time=0.012..0.012 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
52. 0.007 0.007 ↑ 124.0 5 1

Seq Scan on ref_statut_metadonnee i16_ (cost=0.00..16.20 rows=620 width=68) (actual time=0.003..0.007 rows=5 loops=1)

53. 0.310 0.560 ↓ 1.0 563 1

Hash (cost=22.60..22.60 rows=560 width=19) (actual time=0.560..0.560 rows=563 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
54. 0.250 0.250 ↓ 1.0 563 1

Seq Scan on utilisateur i17_ (cost=0.00..22.60 rows=560 width=19) (actual time=0.003..0.250 rows=563 loops=1)

55. 0.419 0.714 ↓ 1.1 648 1

Hash (cost=27.13..27.13 rows=613 width=54) (actual time=0.714..0.714 rows=648 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 55kB
56. 0.295 0.295 ↓ 1.1 648 1

Seq Scan on organisme i18_ (cost=0.00..27.13 rows=613 width=54) (actual time=0.004..0.295 rows=648 loops=1)

57. 0.447 5.645 ↑ 869.5 2 1

Hash (cost=345.76..345.76 rows=1,739 width=170) (actual time=5.645..5.645 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
58. 1.449 5.198 ↓ 1.0 1,763 1

Hash Left Join (cost=53.55..345.76 rows=1,739 width=170) (actual time=0.717..5.198 rows=1,763 loops=1)

  • Hash Cond: (i19_.id_numerisateur = i21_.uid)
59. 1.346 3.103 ↓ 1.0 1,763 1

Hash Left Join (cost=23.95..292.25 rows=1,739 width=155) (actual time=0.034..3.103 rows=1,763 loops=1)

  • Hash Cond: (i19_.id_statut_metadonnee = i20_.id_ref)
60. 1.748 1.748 ↓ 1.0 1,763 1

Seq Scan on jdd i19_ (cost=0.00..244.39 rows=1,739 width=91) (actual time=0.004..1.748 rows=1,763 loops=1)

61. 0.006 0.009 ↑ 124.0 5 1

Hash (cost=16.20..16.20 rows=620 width=68) (actual time=0.009..0.009 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
62. 0.003 0.003 ↑ 124.0 5 1

Seq Scan on ref_statut_metadonnee i20_ (cost=0.00..16.20 rows=620 width=68) (actual time=0.002..0.003 rows=5 loops=1)

63. 0.363 0.646 ↓ 1.0 563 1

Hash (cost=22.60..22.60 rows=560 width=19) (actual time=0.646..0.646 rows=563 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
64. 0.283 0.283 ↓ 1.0 563 1

Seq Scan on utilisateur i21_ (cost=0.00..22.60 rows=560 width=19) (actual time=0.006..0.283 rows=563 loops=1)

65. 11.482 77.009 ↓ 269.7 22,383 1

Hash (cost=2,719.59..2,719.59 rows=83 width=28) (actual time=77.009..77.009 rows=22,383 loops=1)

  • Buckets: 1,024 Batches: 2 (originally 1) Memory Usage: 1,025kB
66. 15.765 65.527 ↓ 269.7 22,383 1

Hash Join (cost=72.78..2,719.59 rows=83 width=28) (actual time=1.525..65.527 rows=22,383 loops=1)

  • Hash Cond: (i24_.id_jdd = i23_.id_jdd)
67. 48.279 48.279 ↓ 269.7 22,383 1

Seq Scan on jdd_especes i24_ (cost=0.00..2,645.68 rows=83 width=4) (actual time=0.024..48.279 rows=22,383 loops=1)

  • Filter: (groupes_fonctionnels && '{12}'::integer[])
  • Rows Removed by Filter: 60,471
68. 0.693 1.483 ↑ 1.0 1,271 1

Hash (cost=56.89..56.89 rows=1,271 width=24) (actual time=1.483..1.483 rows=1,271 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 70kB
69. 0.790 0.790 ↑ 1.0 1,271 1

Seq Scan on jdd i23_ (cost=0.00..56.89 rows=1,271 width=24) (actual time=0.015..0.790 rows=1,271 loops=1)

  • Filter: (nb_obs_presence > 0)
70. 0.291 0.511 ↓ 1.1 648 1

Hash (cost=27.13..27.13 rows=613 width=54) (actual time=0.511..0.511 rows=648 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 55kB
71. 0.220 0.220 ↓ 1.1 648 1

Seq Scan on organisme i22_ (cost=0.00..27.13 rows=613 width=54) (actual time=0.008..0.220 rows=648 loops=1)

Total runtime : 368.390 ms