explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YEFJ

Settings
# exclusive inclusive rows x rows loops node
1. 16,172.228 16,561.513 ↓ 345.2 162,222 1

Sort (cost=6,806.60..6,807.78 rows=470 width=1,059) (actual time=14,425.294..16,561.513 rows=162,222 loops=1)

  • Sort Key: i0_.nom_complet, i6_.nom_complet
  • Sort Method: external merge Disk: 96,032kB
2. 86.804 389.285 ↓ 345.2 162,222 1

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

  • Hash Cond: (i19_.organisme_gestionnaire = i22_.orgid)
3. 103.417 301.704 ↓ 345.2 162,222 1

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

  • Hash Cond: (i6_.id_jdd = i23_.id_jdd)
4. 8.314 115.080 ↓ 1.0 13,531 1

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

  • Hash Cond: (i6_.id_jdd = i19_.id_jdd_valide)
5. 7.380 101.242 ↓ 1.0 13,531 1

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

  • Hash Cond: (i0_.id_ca = i15_.id_ca_valide)
6. 7.341 90.476 ↓ 1.0 13,531 1

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

  • Hash Cond: (i6_.utilisateur_gestionnaire = i14_.uid)
7. 8.931 82.558 ↓ 1.0 13,531 1

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

  • Hash Cond: (i6_.organisme_gestionnaire = i13_.orgid)
8. 8.697 72.953 ↓ 1.0 13,531 1

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

  • Hash Cond: (i0_.organisme_gestionnaire = i12_.orgid)
9. 6.065 63.569 ↓ 1.0 13,531 1

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

10. 0.016 0.016 ↑ 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.016 rows=1 loops=1)

  • Index Cond: (id_ref = 3)
11. 8.846 57.488 ↓ 1.0 13,531 1

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

  • Hash Cond: (i6_.id_numerisateur = i10_.uid)
12. 8.132 47.998 ↓ 1.0 13,531 1

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

  • Hash Cond: (i7_.role_acteur = i9_.id_ref)
13. 8.749 39.854 ↓ 1.0 13,531 1

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

  • Hash Cond: (i7_.organisme = i8_.orgid)
14. 8.568 30.404 ↓ 1.0 13,531 1

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

  • Hash Cond: (i6_.id_ca = i0_.id_ca)
15. 3.614 9.992 ↑ 1.1 3,902 1

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

  • Hash Cond: (i7_.id_jdd = i6_.id_jdd)
16. 2.527 2.527 ↓ 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.011..2.527 rows=4,825 loops=1)

17. 1.166 3.851 ↑ 1.1 1,445 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 183kB
18. 2.685 2.685 ↑ 1.1 1,445 1

Seq Scan on jdd i6_ (cost=0.00..263.95 rows=1,533 width=95) (actual time=0.053..2.685 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
19. 2.386 11.844 ↑ 1.0 1,917 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 504kB
20. 1.533 9.458 ↑ 1.0 1,917 1

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

  • Hash Cond: (i1_.role_acteur = i3_.id_ref)
21. 1.528 7.910 ↑ 1.0 1,917 1

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

  • Hash Cond: (i1_.organisme = i2_.orgid)
22. 1.770 5.634 ↑ 1.0 1,917 1

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

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

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

24. 0.613 2.723 ↑ 1.0 579 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 113kB
25. 0.285 2.110 ↑ 1.0 579 1

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

26. 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)
27. 0.488 1.817 ↑ 1.0 579 1

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

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

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

  • Filter: (id_statut_metadonnee = 3)
  • Rows Removed by Filter: 16
29. 0.312 0.572 ↓ 1.0 563 1

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

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

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

31. 0.443 0.748 ↓ 1.1 648 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 55kB
32. 0.305 0.305 ↓ 1.1 648 1

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

33. 0.008 0.015 ↑ 75.0 8 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
34. 0.007 0.007 ↑ 75.0 8 1

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

35. 0.421 0.701 ↓ 1.1 648 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 55kB
36. 0.280 0.280 ↓ 1.1 648 1

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

37. 0.007 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
38. 0.005 0.005 ↑ 75.0 8 1

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

39. 0.331 0.644 ↓ 1.0 563 1

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

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

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

41. 0.409 0.687 ↓ 1.1 648 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 55kB
42. 0.278 0.278 ↓ 1.1 648 1

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

43. 0.416 0.674 ↓ 1.1 648 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 55kB
44. 0.258 0.258 ↓ 1.1 648 1

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

45. 0.329 0.577 ↓ 1.0 563 1

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

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

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

47. 0.157 3.386 ↓ 0.0 0 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 0kB
48. 0.516 3.229 ↓ 1.0 596 1

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

  • Hash Cond: (i15_.organisme_gestionnaire = i18_.orgid)
49. 0.484 1.988 ↓ 1.0 596 1

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

  • Hash Cond: (i15_.id_numerisateur = i17_.uid)
50. 0.676 0.939 ↓ 1.0 596 1

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

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

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

52. 0.006 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
53. 0.006 0.006 ↑ 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.006 rows=5 loops=1)

54. 0.307 0.565 ↓ 1.0 563 1

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

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

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

56. 0.408 0.725 ↓ 1.1 648 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 55kB
57. 0.317 0.317 ↓ 1.1 648 1

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

58. 0.423 5.524 ↑ 869.5 2 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
59. 1.474 5.101 ↓ 1.0 1,763 1

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

  • Hash Cond: (i19_.id_numerisateur = i21_.uid)
60. 1.385 3.060 ↓ 1.0 1,763 1

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

  • Hash Cond: (i19_.id_statut_metadonnee = i20_.id_ref)
61. 1.667 1.667 ↓ 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.667 rows=1,763 loops=1)

62. 0.006 0.008 ↑ 124.0 5 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
63. 0.002 0.002 ↑ 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.002 rows=5 loops=1)

64. 0.324 0.567 ↓ 1.0 563 1

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

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

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

66. 13.259 83.207 ↓ 269.7 22,383 1

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

  • Buckets: 1,024 Batches: 2 (originally 1) Memory Usage: 1,025kB
67. 17.084 69.948 ↓ 269.7 22,383 1

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

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

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

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

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

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

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

  • Filter: (nb_obs_presence > 0)
71. 0.428 0.777 ↓ 1.1 648 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 55kB
72. 0.349 0.349 ↓ 1.1 648 1

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

Total runtime : 16,604.497 ms