explain.depesz.com

PostgreSQL's explain analyze made readable

Result: h1OQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 1,429.022 ↓ 18.0 18 1

Limit (cost=15,306.59..15,627.75 rows=1 width=863) (actual time=1,426.242..1,429.022 rows=18 loops=1)

2.          

CTE temp_agregation_etablissement_contrat1

3. 9.545 1,408.352 ↓ 4,386.0 4,386 1

GroupAggregate (cost=14,010.62..14,010.66 rows=1 width=104) (actual time=1,393.390..1,408.352 rows=4,386 loops=1)

  • Group Key: individu_rapproche.id
4.          

Initplan (for GroupAggregate)

5. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on type_base_assujettie (cost=0.00..1.68 rows=1 width=4) (actual time=0.003..0.008 rows=1 loops=1)

  • Filter: (code = '03'::bpchar)
  • Rows Removed by Filter: 53
6. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on type_base_assujettie type_base_assujettie_1 (cost=0.00..1.68 rows=1 width=4) (actual time=0.003..0.008 rows=1 loops=1)

  • Filter: (code = '02'::bpchar)
  • Rows Removed by Filter: 53
7. 0.020 0.020 ↑ 1.0 1 1

Seq Scan on type_base_assujettie type_base_assujettie_2 (cost=0.00..1.68 rows=1 width=4) (actual time=0.012..0.020 rows=1 loops=1)

  • Filter: (code = '04'::bpchar)
  • Rows Removed by Filter: 53
8. 15.567 1,398.771 ↓ 35,715.0 35,715 1

Sort (cost=14,005.59..14,005.60 rows=1 width=18) (actual time=1,393.331..1,398.771 rows=35,715 loops=1)

  • Sort Key: individu_rapproche.id
  • Sort Method: quicksort Memory: 4278kB
9. 27.508 1,383.204 ↓ 35,715.0 35,715 1

Nested Loop (cost=3.57..14,005.58 rows=1 width=18) (actual time=276.500..1,383.204 rows=35,715 loops=1)

10. 91.780 1,319.981 ↓ 35,715.0 35,715 1

Nested Loop (cost=3.15..14,002.19 rows=1 width=14) (actual time=276.488..1,319.981 rows=35,715 loops=1)

11. 48.570 825.781 ↓ 26,828.0 134,140 1

Nested Loop (cost=2.57..13,316.90 rows=5 width=12) (actual time=0.050..825.781 rows=134,140 loops=1)

12. 0.000 374.791 ↓ 6,097.3 134,140 1

Nested Loop (cost=2.00..13,238.97 rows=22 width=24) (actual time=0.039..374.791 rows=134,140 loops=1)

13. 18.627 49.792 ↓ 195.2 108,351 1

Nested Loop (cost=1.57..9,717.31 rows=555 width=16) (actual time=0.031..49.792 rows=108,351 loops=1)

14. 0.030 0.280 ↑ 1.5 29 1

Nested Loop (cost=1.00..295.38 rows=43 width=8) (actual time=0.016..0.280 rows=29 loops=1)

15. 0.019 0.019 ↓ 1.8 7 1

Index Scan using idx_etablissement_dsn_on_siret on etablissement_dsn e (cost=0.43..14.91 rows=4 width=8) (actual time=0.008..0.019 rows=7 loops=1)

  • Index Cond: ((siret)::text = '05980406200087'::text)
16. 0.231 0.231 ↑ 5.2 4 7

Index Scan using idx_declaration_dsn_on_fk_etablissement_dsn on declaration_dsn d (cost=0.57..69.91 rows=21 width=16) (actual time=0.025..0.033 rows=4 loops=7)

  • Index Cond: (fk_etablissement_dsn = e.id)
  • Filter: dsn_active
  • Rows Removed by Filter: 0
17. 30.885 30.885 ↓ 1.2 3,736 29

Index Scan using idx_versement_individu_salarie_dsn_on_fk_declaration_dsn on versement_individu_salarie_dsn vers (cost=0.57..188.96 rows=3,015 width=24) (actual time=0.012..1.065 rows=3,736 loops=29)

  • Index Cond: (fk_declaration_dsn = d.id)
18. 325.053 325.053 ↑ 1.0 1 108,351

Index Scan using idx_r_individu_salarie_individu_rapproche_fk_individu_salarie on r_individu_salarie_individu_rapproche risi_1 (cost=0.42..6.34 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=108,351)

  • Index Cond: (fk_individu_salarie_dsn = vers.fk_individu_salarie_dsn)
19. 402.420 402.420 ↑ 1.0 1 134,140

Index Only Scan using pk_individu_salarie_dsn on individu_salarie_dsn indiv_1 (cost=0.57..3.53 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=134,140)

  • Index Cond: (id = vers.fk_individu_salarie_dsn)
  • Heap Fetches: 0
20. 402.420 402.420 ↓ 0.0 0 134,140

Index Scan using idx_base_assujettie_on_fk_versement_individu_salarie_dsn on base_assujettie base (cost=0.58..136.61 rows=45 width=18) (actual time=0.003..0.003 rows=0 loops=134,140)

  • Index Cond: (fk_versement_individu_salarie_dsn = vers.id)
  • Filter: ((dt_deb_prd_rattachement >= '2019-05-01'::date) AND (dt_deb_prd_rattachement <= '2019-05-30'::date))
  • Rows Removed by Filter: 6
21. 35.715 35.715 ↑ 1.0 1 35,715

Index Only Scan using pk_individu_rapproche on individu_rapproche (cost=0.42..3.39 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=35,715)

  • Index Cond: (id = risi_1.fk_individu_rapproche)
  • Heap Fetches: 0
22. 0.291 1,429.019 ↓ 18.0 18 1

Subquery Scan on indiv (cost=1,295.93..1,617.09 rows=1 width=863) (actual time=1,426.241..1,429.019 rows=18 loops=1)

  • Filter: ((COALESCE(indiv.nir_sngi_tempo, indiv.nir_tempo, ''::bpchar) ~~ '%'::text) AND (indiv.date_naissance_sngi ~~ '%'::text) AND (indiv.num = 1) AND (COALESCE(lower((indiv.nom_sngi_tempo)::text), lower((indiv.nom_famille_tempo)::text)) ~~ '%':: (...)
  • Rows Removed by Filter: 1
23. 0.015 1,428.584 ↓ 19.0 19 1

Result (cost=1,295.93..1,615.96 rows=1 width=527) (actual time=1,426.161..1,428.584 rows=19 loops=1)

24. 0.031 1,425.852 ↓ 19.0 19 1

Sort (cost=1,295.93..1,295.94 rows=1 width=495) (actual time=1,425.850..1,425.852 rows=19 loops=1)

  • Sort Key: (COALESCE(i.nom_famille_sngi, i.nom_famille))
  • Sort Method: quicksort Memory: 30kB
25. 0.127 1,425.821 ↓ 19.0 19 1

WindowAgg (cost=1,295.86..1,295.92 rows=1 width=495) (actual time=1,425.743..1,425.821 rows=19 loops=1)

26. 0.024 1,425.694 ↓ 19.0 19 1

Sort (cost=1,295.86..1,295.87 rows=1 width=215) (actual time=1,425.693..1,425.694 rows=19 loops=1)

  • Sort Key: ir.id, decla.dt_mois_principal_declare DESC
  • Sort Method: quicksort Memory: 29kB
27. 8.103 1,425.670 ↓ 19.0 19 1

Nested Loop Left Join (cost=3.56..1,295.85 rows=1 width=215) (actual time=1,410.248..1,425.670 rows=19 loops=1)

  • Join Filter: (mt.indiv = ir.id)
  • Rows Removed by Join Filter: 83334
28. 0.015 1.269 ↓ 19.0 19 1

Nested Loop (cost=3.56..1,295.82 rows=1 width=119) (actual time=0.138..1.269 rows=19 loops=1)

29. 0.031 1.159 ↓ 19.0 19 1

Nested Loop Left Join (cost=2.99..1,292.35 rows=1 width=119) (actual time=0.128..1.159 rows=19 loops=1)

  • Join Filter: (sexe.id = i.fk_sexe)
  • Rows Removed by Join Filter: 38
30. 0.024 1.109 ↓ 19.0 19 1

Nested Loop (cost=2.99..1,291.28 rows=1 width=120) (actual time=0.122..1.109 rows=19 loops=1)

  • Join Filter: (risi.fk_individu_salarie_dsn = i.id)
31. 0.052 0.952 ↓ 19.0 19 1

Nested Loop (cost=2.42..1,284.95 rows=1 width=40) (actual time=0.105..0.952 rows=19 loops=1)

  • Join Filter: (decla.id = rei.fk_declaration_dsn)
  • Rows Removed by Join Filter: 476
32. 0.005 0.053 ↑ 1.0 1 1

Nested Loop (cost=1.00..295.02 rows=1 width=27) (actual time=0.041..0.053 rows=1 loops=1)

33. 0.018 0.018 ↑ 4.0 1 1

Index Scan using idx_etablissement_dsn_on_siret on etablissement_dsn etab (cost=0.43..14.91 rows=4 width=23) (actual time=0.018..0.018 rows=1 loops=1)

  • Index Cond: ((siret)::text = '06180558600049'::text)
34. 0.030 0.030 ↑ 1.0 1 1

Index Scan using idx_declaration_dsn_on_fk_etablissement_dsn on declaration_dsn decla (cost=0.57..70.02 rows=1 width=20) (actual time=0.021..0.030 rows=1 loops=1)

  • Index Cond: (fk_etablissement_dsn = etab.id)
  • Filter: ((dsn_active IS TRUE) AND (dt_mois_principal_declare >= '2019-05-01'::date) AND (dt_mois_principal_declare <= '2019-05-30'::date))
  • Rows Removed by Filter: 25
35. 0.739 0.847 ↑ 1.5 495 1

Nested Loop (cost=1.42..980.48 rows=756 width=51) (actual time=0.030..0.847 rows=495 loops=1)

  • -> Index Scan using idx_relation_employeur_individu_on_fk_individu_salarie_dsn on relation_employeur_individu rei (cost=0.57..61.76 rows=50 width=24) (actual time=0.008..0.013 rows=10 lo (...)
36. 0.088 0.108 ↓ 3.2 48 1

Nested Loop (cost=0.85..46.53 rows=15 width=27) (actual time=0.021..0.108 rows=48 loops=1)

  • -> Index Only Scan using pk_r_individu_salarie_individu_rapproche on r_individu_salarie_individu_rapproche risi (cost=0.42..3.48 rows=3 width=8) (actual time=0.002..0.003 rows=2 lo (...)
  • Index Cond: (fk_individu_salarie_dsn = risi.fk_individu_salarie_dsn)
37. 0.020 0.020 ↓ 3.7 26 1

Index Scan using idx_individu_rapproche_on_siret on individu_rapproche ir (cost=0.42..21.98 rows=7 width=23) (actual time=0.011..0.020 rows=26 loops=1)

  • Index Cond: ((siret)::text = '06180558600049'::text)
  • Index Cond: (fk_individu_rapproche = ir.id)
  • Heap Fetches: 0
38. 0.133 0.133 ↑ 1.0 1 19

Index Scan using pk_individu_salarie_dsn on individu_salarie_dsn i (cost=0.57..6.32 rows=1 width=100) (actual time=0.007..0.007 rows=1 loops=19)

  • Index Cond: (id = rei.fk_individu_salarie_dsn)
39. 0.019 0.019 ↑ 1.0 3 19

Seq Scan on sexe (cost=0.00..1.03 rows=3 width=7) (actual time=0.000..0.001 rows=3 loops=19)

40. 0.095 0.095 ↑ 1.0 1 19

Index Only Scan using pk_contrat_travail_dsn on contrat_travail_dsn con (cost=0.57..3.46 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=19)

  • Index Cond: (id = rei.fk_contrat_travail_dsn)
  • Heap Fetches: 0
41. 1,416.298 1,416.298 ↓ 4,386.0 4,386 19

CTE Scan on temp_agregation_etablissement_contrat1 mt (cost=0.00..0.02 rows=1 width=104) (actual time=73.337..74.542 rows=4,386 loops=19)

42.          

SubPlan (for Result)

43. 0.019 2.717 ↑ 1.0 1 19

Aggregate (cost=319.99..320.00 rows=1 width=32) (actual time=0.142..0.143 rows=1 loops=19)

44. 0.019 2.698 ↓ 0.0 0 19

Nested Loop (cost=3.14..319.98 rows=1 width=6) (actual time=0.142..0.142 rows=0 loops=19)

  • Join Filter: (aerdfraispro.fk_type_revenus_autres = typerevenu.id)
45. 0.000 2.679 ↓ 0.0 0 19

Nested Loop (cost=3.14..318.45 rows=1 width=10) (actual time=0.141..0.141 rows=0 loops=19)

46. 0.090 2.679 ↓ 0.0 0 19

Nested Loop (cost=2.57..311.18 rows=1 width=8) (actual time=0.141..0.141 rows=0 loops=19)

  • Join Filter: (d_1.fk_etablissement_dsn = e_1.id)
  • Rows Removed by Join Filter: 163
47. 0.004 2.147 ↓ 1.0 23 19

Nested Loop (cost=2.13..294.94 rows=22 width=16) (actual time=0.019..0.113 rows=23 loops=19)

48. 0.057 0.817 ↑ 1.0 23 19

Nested Loop (cost=1.57..150.97 rows=23 width=16) (actual time=0.016..0.043 rows=23 loops=19)

49. 0.019 0.266 ↑ 1.5 2 19

Nested Loop (cost=0.99..14.26 rows=3 width=12) (actual time=0.007..0.014 rows=2 loops=19)

50. 0.057 0.057 ↑ 1.5 2 19

Index Only Scan using pk_r_individu_salarie_individu_rapproche on r_individu_salarie_individu_rapproche risi_2 (cost=0.42..3.48 rows=3 width=4) (actual time=0.003..0.003 rows=2 loops=19)

  • Index Cond: (fk_individu_rapproche = ir.id)
  • Heap Fetches: 0
51. 0.190 0.190 ↑ 1.0 1 38

Index Only Scan using pk_individu_salarie_dsn on individu_salarie_dsn ind (cost=0.57..3.58 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=38)

  • Index Cond: (id = risi_2.fk_individu_salarie_dsn)
  • Heap Fetches: 0
52. 0.494 0.494 ↑ 2.9 12 38

Index Scan using idx_versement_individu_salarie_dsn_on_fk_individu_salarie_dsn on versement_individu_salarie_dsn vers_1 (cost=0.57..45.22 rows=35 width=24) (actual time=0.007..0.013 rows=12 loops=38)

  • Index Cond: (fk_individu_salarie_dsn = ind.id)
53. 1.326 1.326 ↑ 1.0 1 442

Index Scan using pk_declaration_dsn on declaration_dsn d_1 (cost=0.57..6.25 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=442)

  • Index Cond: (id = vers_1.fk_declaration_dsn)
  • Filter: dsn_active
54. 0.420 0.442 ↓ 1.8 7 442

Materialize (cost=0.43..14.93 rows=4 width=8) (actual time=0.000..0.001 rows=7 loops=442)

55. 0.022 0.022 ↓ 1.8 7 1

Index Scan using idx_etablissement_dsn_on_siret on etablissement_dsn e_1 (cost=0.43..14.91 rows=4 width=8) (actual time=0.013..0.022 rows=7 loops=1)

  • Index Cond: ((siret)::text = '05980406200087'::text)
56. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_autre_elmt_revenu_brut_on_fk_versement_indiv_salarie_dsn on autre_element_revenu_brut aerdfraispro (cost=0.57..7.26 rows=1 width=18) (never executed)

  • Index Cond: (fk_versement_individu_salarie_dsn = vers_1.id)
  • Filter: ((dt_deb_prd_rattachement >= '2019-05-01'::date) AND (dt_deb_prd_rattachement <= '2019-05-30'::date))
57. 0.000 0.000 ↓ 0.0 0

Seq Scan on type_revenus_autres typerevenu (cost=0.00..1.50 rows=3 width=4) (never executed)

  • Filter: (code = ANY ('{07,08,09}'::bpchar[]))
58.          

SubPlan (for Subquery Scan)

59. 0.018 0.090 ↑ 100.0 1 18

HashAggregate (cost=0.02..0.53 rows=100 width=32) (actual time=0.005..0.005 rows=1 loops=18)

  • Group Key: json_array_elements_text(array_to_json(indiv.listids))
60. 0.072 0.072 ↑ 1.0 1 18

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=18)

61. 0.018 0.054 ↑ 100.0 1 18

HashAggregate (cost=0.02..0.53 rows=100 width=32) (actual time=0.003..0.003 rows=1 loops=18)

  • Group Key: json_array_elements_text(array_to_json(indiv.listidsdecla))
62. 0.036 0.036 ↑ 1.0 1 18

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=18)

Planning time : 24.405 ms
Execution time : 1,429.458 ms