explain.depesz.com

PostgreSQL's explain analyze made readable

Result: skYD : Optimization for: plan #bLRZ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,353,850.42..1,354,094.72 rows=5 width=852) (actual rows= loops=)

2.          

Initplan (for Nested Loop Left Join)

3. 0.000 0.000 ↓ 0.0

Seq Scan on r_nomenclature (cost=0.00..6.71 rows=1 width=4) (actual rows= loops=)

  • Filter: (((domaine)::text = 'TYPEFONCTION'::text) AND ((code)::text = 'SECOND'::text))
4. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,353,843.29..1,354,036.05 rows=5 width=785) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,353,843.01..1,354,034.06 rows=5 width=746) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,353,842.71..1,353,992.45 rows=5 width=734) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,353,842.57..1,353,991.58 rows=5 width=706) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,353,842.42..1,353,990.72 rows=5 width=678) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Limit (cost=1,353,837.98..1,353,910.13 rows=5 width=658) (actual rows= loops=)

10.          

Initplan (for Limit)

11. 0.000 0.000 ↓ 0.0

Seq Scan on r_nomenclature r_nomenclature_5 (cost=0.00..6.71 rows=1 width=4) (actual rows= loops=)

  • Filter: (((domaine)::text = 'TYPEFONCTION'::text) AND ((code)::text = 'PRINC'::text))
12. 0.000 0.000 ↓ 0.0

Result (cost=1,353,831.28..1,353,903.43 rows=5 width=658) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Sort (cost=1,353,831.28..1,353,831.29 rows=5 width=657) (actual rows= loops=)

  • Sort Key: uoversion.libelle_court, pdtcarto.code_sirhius
14. 0.000 0.000 ↓ 0.0

WindowAgg (cost=164.44..1,353,831.22 rows=5 width=657) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=164.44..1,353,831.13 rows=5 width=645) (actual rows= loops=)

  • Join Filter: ((zonecategorie.id_zone_geographique = payszone.id_zone_geographique) AND (zonecategorie.id_categorie_personnel = pdtcarto.id_categorie_personnel))
16. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=164.44..1,353,821.03 rows=5 width=649) (actual rows= loops=)

  • Join Filter: (uoversion.id_pays = payszone.id_pays)
17. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=164.44..1,353,812.93 rows=5 width=649) (actual rows= loops=)

  • Join Filter: (categoriepersonnel.id = pdtcarto.id_categorie_personnel)
18. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=164.44..1,353,788.32 rows=5 width=617) (actual rows= loops=)

  • Join Filter: (uoversion.id_ville = ville.id)
19. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=164.44..1,349,325.08 rows=5 width=586) (actual rows= loops=)

  • Join Filter: (pdtcarto.id = pdtf.id_pdt_carto_version)
20. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=46.60..1,340,087.63 rows=7,815 width=586) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=46.31..1,336,269.10 rows=7,815 width=551) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=46.02..1,332,450.58 rows=7,815 width=513) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=45.74..1,329,709.36 rows=7,815 width=482) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=45.45..1,326,968.15 rows=7,815 width=444) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=45.16..1,324,226.93 rows=7,815 width=441) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=45.02..1,322,868.93 rows=7,815 width=425) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=44.87..1,321,510.92 rows=7,815 width=409) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=44.58..1,318,401.40 rows=7,815 width=378) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Nested Loop (cost=44.29..1,315,660.41 rows=7,815 width=374) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=44.00..1,313,043.90 rows=7,815 width=172) (actual rows= loops=)

  • Filter: (((hashed SubPlan 10) AND (pdtcarto.date_effet <= '2020-09-07'::date) AND ((pdtcarto.date_fin_reelle IS NULL) OR (pdtcarto.date_fin_reelle >= '2020-09-07'::date))) OR ((hashed SubPlan 11) AND (SubPlan 12)))
31. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=29.21..1,148,724.06 rows=19,126 width=144) (actual rows= loops=)

  • Filter: ((hashed SubPlan 14) OR (statut.code IS NULL))
32. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=22.35..1,142,013.25 rows=38,252 width=116) (actual rows= loops=)

  • Merge Cond: (uo.id = pdtcarto.id_uo)
  • Filter: ((hashed SubPlan 13) OR (uoversion.id_statut_uo IS NULL))
33. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=15.23..227,560.27 rows=18,689 width=62) (actual rows= loops=)

  • Merge Cond: (uo.id = uoversion.id_uo)
34. 0.000 0.000 ↓ 0.0

Index Only Scan using e_uo_pkey on e_uo uo (cost=0.29..496.62 rows=18,689 width=4) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Index Scan using e_uo_version_id_uo on e_uo_version uoversion (cost=14.94..226,819.93 rows=15,759 width=62) (actual rows= loops=)

  • Filter: (((hashed SubPlan 6) AND (date_effet <= '2020-09-07'::date) AND ((date_fin_reelle IS NULL) OR (date_fin_reelle >= '2020-09-07'::date))) OR ((hashed SubPlan 7) AND (SubPlan 8)))
36.          

SubPlan (for Index Scan)

37. 0.000 0.000 ↓ 0.0

Seq Scan on r_nomenclature r_nomenclature_3 (cost=0.00..7.94 rows=1 width=4) (actual rows= loops=)

  • Filter: (((domaine)::text = 'ETAT_UO'::text) AND ((code)::text = ANY ('{VALID,VALNONCONF,VALCONF,VALTRANS,VALREJET,VALINTEG}'::text[])))
38. 0.000 0.000 ↓ 0.0

Seq Scan on r_nomenclature r_nomenclature_4 (cost=0.00..6.71 rows=1 width=4) (actual rows= loops=)

  • Filter: (((domaine)::text = 'ETAT_UO'::text) AND ((code)::text = 'ENPROJ'::text))
39. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=0.29..12.34 rows=2 width=4) (actual rows= loops=)

  • Group Key: uov.id_uo
  • Filter: (count(uov.id) = 1)
40. 0.000 0.000 ↓ 0.0

Index Scan using e_uo_version_id_uo on e_uo_version uov (cost=0.29..12.31 rows=2 width=8) (actual rows= loops=)

  • Index Cond: (id_uo = uoversion.id_uo)
41. 0.000 0.000 ↓ 0.0

Index Scan using e_pdt_carto_version_id_uo on e_pdt_carto_version pdtcarto (cost=0.42..913,254.75 rows=76,503 width=62) (actual rows= loops=)

  • Filter: (((date_effet <= '2020-09-07'::date) AND ((date_fin_reelle IS NULL) OR (date_fin_reelle >= '2020-09-07'::date))) OR (SubPlan 12))
42.          

SubPlan (for Index Scan)

43. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=0.42..16.39 rows=3 width=4) (actual rows= loops=)

  • Group Key: pdtv.id_poste_travail
  • Filter: (count(pdtv.id) = 1)
44. 0.000 0.000 ↓ 0.0

Index Scan using e_pdt_carto_version_id_poste_travail on e_pdt_carto_version pdtv (cost=0.42..16.34 rows=3 width=8) (actual rows= loops=)

  • Index Cond: (id_poste_travail = pdtcarto.id_poste_travail)
45.          

SubPlan (for Merge Right Join)

46. 0.000 0.000 ↓ 0.0

Seq Scan on r_nomenclature r_nomenclature_8 (cost=0.00..6.71 rows=1 width=4) (actual rows= loops=)

  • Filter: (((domaine)::text = 'STATUT_UO'::text) AND ((code)::text = 'ACTIF'::text))
47. 0.000 0.000 ↓ 0.0

Index Scan using r_nomenclature_pkey on r_nomenclature statut (cost=0.14..0.16 rows=1 width=32) (actual rows= loops=)

  • Index Cond: (pdtcarto.id_statut_poste = id)
48.          

SubPlan (for Nested Loop Left Join)

49. 0.000 0.000 ↓ 0.0

Seq Scan on r_nomenclature r_nomenclature_9 (cost=0.00..6.71 rows=1 width=4) (actual rows= loops=)

  • Filter: (((domaine)::text = 'STATUT_PDT'::text) AND ((code)::text = 'ACTIF'::text))
50. 0.000 0.000 ↓ 0.0

Index Scan using r_nomenclature_pkey on r_nomenclature etat (cost=0.14..0.16 rows=1 width=32) (actual rows= loops=)

  • Index Cond: (pdtcarto.id_etat = id)
51.          

SubPlan (for Nested Loop Left Join)

52. 0.000 0.000 ↓ 0.0

Seq Scan on r_nomenclature r_nomenclature_6 (cost=0.00..7.94 rows=1 width=4) (actual rows= loops=)

  • Filter: (((domaine)::text = 'ETAT_PDTCARTO'::text) AND ((code)::text = ANY ('{VALID,VALNONCONF,VALCONF,VALTRANS,VALREJET,VALINTEG}'::text[])))
53. 0.000 0.000 ↓ 0.0

Seq Scan on r_nomenclature r_nomenclature_7 (cost=0.00..6.71 rows=1 width=4) (actual rows= loops=)

  • Filter: (((domaine)::text = 'ETAT_PDTCARTO'::text) AND ((code)::text = 'ENPROJ'::text))
54. 0.000 0.000 ↓ 0.0

Index Scan using e_poste_travail_pkey on e_poste_travail pdt (cost=0.29..0.32 rows=1 width=206) (actual rows= loops=)

  • Index Cond: (id = pdtcarto.id_poste_travail)
55. 0.000 0.000 ↓ 0.0

Index Scan using a_pdt_fonction_id_pdt_carto_version on a_pdt_fonction fonctions_1 (cost=0.29..0.34 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (pdtcarto.id = id_pdt_carto_version)
  • Filter: (id_nature = $9)
56. 0.000 0.000 ↓ 0.0

Index Scan using r_nomenclature_externe_pkey on r_nomenclature_externe fonction_1 (cost=0.29..0.39 rows=1 width=39) (actual rows= loops=)

  • Index Cond: (fonctions_1.id_fonction_ref = id)
57. 0.000 0.000 ↓ 0.0

Index Scan using r_nomenclature_pkey on r_nomenclature typeposte (cost=0.14..0.16 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (uoversion.id_type_poste = id)
58. 0.000 0.000 ↓ 0.0

Index Scan using r_nomenclature_pkey on r_nomenclature formatposte (cost=0.14..0.16 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (uoversion.id_format_poste = id)
59. 0.000 0.000 ↓ 0.0

Index Scan using r_nomenclature_externe_pkey on r_nomenclature_externe pgmlolf (cost=0.29..0.34 rows=1 width=11) (actual rows= loops=)

  • Index Cond: (pdtcarto.id_programme = id)
60. 0.000 0.000 ↓ 0.0

Index Scan using r_nomenclature_externe_pkey on r_nomenclature_externe action (cost=0.29..0.34 rows=1 width=46) (actual rows= loops=)

  • Index Cond: (pdtcarto.id_action = id)
61. 0.000 0.000 ↓ 0.0

Index Scan using r_nomenclature_externe_pkey on r_nomenclature_externe niveauremun (cost=0.29..0.34 rows=1 width=39) (actual rows= loops=)

  • Index Cond: (pdtcarto.id_niveau_remuneration = id)
62. 0.000 0.000 ↓ 0.0

Index Scan using r_nomenclature_externe_pkey on r_nomenclature_externe dirrattachement (cost=0.29..0.48 rows=1 width=46) (actual rows= loops=)

  • Index Cond: (uoversion.id_direction = id)
63. 0.000 0.000 ↓ 0.0

Index Scan using r_nomenclature_externe_pkey on r_nomenclature_externe pays (cost=0.29..0.48 rows=1 width=39) (actual rows= loops=)

  • Index Cond: (uoversion.id_pays = id)
64. 0.000 0.000 ↓ 0.0

Materialize (cost=117.84..1,383.54 rows=67 width=4) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Hash Join (cost=117.84..1,383.20 rows=67 width=4) (actual rows= loops=)

  • Hash Cond: (pdtf.id_fonction_ref = fct.id)
66. 0.000 0.000 ↓ 0.0

Seq Scan on a_pdt_fonction pdtf (cost=0.00..1,017.14 rows=66,014 width=8) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Hash (cost=117.45..117.45 rows=31 width=4) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Index Only Scan using r_nomenclature_externe_pkey on r_nomenclature_externe fct (cost=0.29..117.45 rows=31 width=4) (actual rows= loops=)

  • Index Cond: (id = ANY ('{36036,36050,36455,36051,36052,36054,36055,36056,36057,36058,36059,36061,36060,36382,36068,36070,36076,36077,36080,36172,36082,36471,366688,36081,36472,366751,366755,366761,366762,36079,36373}'::integer[]))
69. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..1,294.76 rows=30,517 width=39) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Seq Scan on r_nomenclature_externe ville (cost=0.00..903.17 rows=30,517 width=39) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..6.71 rows=247 width=32) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Seq Scan on r_nomenclature categoriepersonnel (cost=0.00..5.47 rows=247 width=32) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..3.90 rows=58 width=8) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

Seq Scan on a_pays_zone_geo_valo_bud payszone (cost=0.00..3.61 rows=58 width=8) (actual rows= loops=)

  • Filter: (('2020-09-07'::date >= date_effet) AND ('2020-09-07'::date <= COALESCE(date_fin, '9999-12-31'::date)))
75. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..5.00 rows=60 width=12) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Seq Scan on e_parametrage_cout_etp zonecategorie (cost=0.00..4.70 rows=60 width=12) (actual rows= loops=)

  • Filter: (('2020-09-07'::date >= date_effet) AND ('2020-09-07'::date <= COALESCE(date_fin, '9999-12-31'::date)))
77.          

SubPlan (for Result)

78. 0.000 0.000 ↓ 0.0

Index Scan using e_mesure_programmation_id_poste_travail on e_mesure_programmation mdp (cost=6.38..14.41 rows=1 width=0) (actual rows= loops=)

  • Index Cond: (id_poste_travail = pdtcarto.id_poste_travail)
  • Filter: ((NOT (hashed SubPlan 2)) AND ((date_part('year'::text, (date_effet)::timestamp without time zone))::integer >= 2,020))
79.          

SubPlan (for Index Scan)

80. 0.000 0.000 ↓ 0.0

Seq Scan on r_nomenclature r_nomenclature_1 (cost=0.00..6.09 rows=6 width=4) (actual rows= loops=)

  • Filter: ((domaine)::text = 'ETAT_ABANDONNE_MDP'::text)
81. 0.000 0.000 ↓ 0.0

Seq Scan on e_mesure_programmation mdp_1 (cost=6.10..302.93 rows=391 width=4) (actual rows= loops=)

  • Filter: ((NOT (hashed SubPlan 4)) AND ((date_part('year'::text, (date_effet)::timestamp without time zone))::integer >= 2,020))
82.          

SubPlan (for Seq Scan)

83. 0.000 0.000 ↓ 0.0

Seq Scan on r_nomenclature r_nomenclature_2 (cost=0.00..6.09 rows=6 width=4) (actual rows= loops=)

  • Filter: ((domaine)::text = 'ETAT_ABANDONNE_MDP'::text)
84. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on e_pdt_carto_version autresversions (cost=4.44..16.08 rows=3 width=24) (actual rows= loops=)

  • Recheck Cond: (id_poste_travail = pdtcarto.id_poste_travail)
  • Filter: (id <> pdtcarto.id)
85. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on e_pdt_carto_version_id_poste_travail (cost=0.00..4.44 rows=3 width=0) (actual rows= loops=)

  • Index Cond: (id_poste_travail = pdtcarto.id_poste_travail)
86. 0.000 0.000 ↓ 0.0

Index Scan using r_nomenclature_pkey on r_nomenclature autresversionsetat (cost=0.14..0.16 rows=1 width=32) (actual rows= loops=)

  • Index Cond: (autresversions.id_etat = id)
87. 0.000 0.000 ↓ 0.0

Index Scan using r_nomenclature_pkey on r_nomenclature autresversionsstatut (cost=0.14..0.16 rows=1 width=32) (actual rows= loops=)

  • Index Cond: (autresversions.id_statut_poste = id)
88. 0.000 0.000 ↓ 0.0

Index Scan using a_pdt_fonction_id_pdt_carto_version on a_pdt_fonction fonctions (cost=0.29..8.31 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (pdtcarto.id = id_pdt_carto_version)
  • Filter: (id_nature = $0)
89. 0.000 0.000 ↓ 0.0

Index Scan using r_nomenclature_externe_pkey on r_nomenclature_externe fonction (cost=0.29..0.39 rows=1 width=39) (actual rows= loops=)

  • Index Cond: (fonctions.id_fonction_ref = id)
90. 0.000 0.000 ↓ 0.0

Index Scan using r_affectation_code_pdt on r_affectation occupant (cost=0.42..10.38 rows=1 width=77) (actual rows= loops=)

  • Index Cond: ((code_pdt)::text = (pdtcarto.code_sirhius)::text)
  • Filter: ((date_debut <= '2020-09-07'::date) AND ((date_fin IS NULL) OR (date_fin >= '2020-09-07'::date)))