explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6lyA

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

Limit (cost=3,948.92..4,571.54 rows=1 width=866) (actual time=28.391..28.391 rows=0 loops=1)

2. 0.002 28.391 ↓ 0.0 0 1

Subquery Scan on indiv (cost=3,948.92..4,571.54 rows=1 width=866) (actual time=28.391..28.391 rows=0 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)) ~~ '%':: (...)
3. 0.001 28.389 ↓ 0.0 0 1

Result (cost=3,948.92..4,570.41 rows=1 width=550) (actual time=28.389..28.389 rows=0 loops=1)

4. 0.005 28.388 ↓ 0.0 0 1

Sort (cost=3,948.92..3,948.93 rows=1 width=518) (actual time=28.388..28.388 rows=0 loops=1)

  • Sort Key: (COALESCE(i.nom_famille_sngi, i.nom_famille))
  • Sort Method: quicksort Memory: 25kB
5. 0.003 28.383 ↓ 0.0 0 1

WindowAgg (cost=3,948.86..3,948.91 rows=1 width=518) (actual time=28.383..28.383 rows=0 loops=1)

6. 0.006 28.380 ↓ 0.0 0 1

Sort (cost=3,948.86..3,948.86 rows=1 width=238) (actual time=28.380..28.380 rows=0 loops=1)

  • Sort Key: ir.id, decla.dt_mois_principal_declare DESC
  • Sort Method: quicksort Memory: 25kB
7. 0.000 28.374 ↓ 0.0 0 1

Nested Loop Left Join (cost=3.84..3,948.85 rows=1 width=238) (actual time=28.374..28.374 rows=0 loops=1)

  • Join Filter: (mt.indiv = ir.id)
8. 0.002 28.374 ↓ 0.0 0 1

Nested Loop (cost=3.84..3,920.34 rows=1 width=142) (actual time=28.374..28.374 rows=0 loops=1)

9. 0.000 28.372 ↓ 0.0 0 1

Nested Loop Left Join (cost=3.27..3,917.24 rows=1 width=142) (actual time=28.372..28.372 rows=0 loops=1)

  • Join Filter: (sexe.id = i.fk_sexe)
10. 0.000 28.372 ↓ 0.0 0 1

Nested Loop (cost=3.27..3,916.17 rows=1 width=143) (actual time=28.372..28.372 rows=0 loops=1)

11. 0.001 28.372 ↓ 0.0 0 1

Nested Loop (cost=2.70..3,910.61 rows=1 width=40) (actual time=28.372..28.372 rows=0 loops=1)

12. 0.000 28.371 ↓ 0.0 0 1

Nested Loop (cost=2.14..3,893.96 rows=3 width=51) (actual time=28.371..28.371 rows=0 loops=1)

13. 0.001 28.371 ↓ 0.0 0 1

Nested Loop (cost=1.57..3,877.28 rows=3 width=43) (actual time=28.371..28.371 rows=0 loops=1)

14. 0.006 28.370 ↓ 0.0 0 1

Nested Loop (cost=1.00..383.65 rows=1 width=27) (actual time=28.370..28.370 rows=0 loops=1)

15. 15.318 15.318 ↑ 4.0 1 1

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

  • Index Cond: ((siret)::text = '06180558600049'::text)
16. 13.046 13.046 ↓ 0.0 0 1

Index Scan using idx_declaration_dsn_on_fk_etablissement_dsn on declaration_dsn decla (cost=0.56..92.15 rows=1 width=20) (actual time=13.046..13.046 rows=0 loops=1)

  • Index Cond: (fk_etablissement_dsn = etab.id)
  • Filter: ((dsn_active IS TRUE) AND (dt_mois_principal_declare >= '2019-02-01'::date) AND (dt_mois_principal_declare <= '2019-02-28'::date))
  • Rows Removed by Filter: 21
17. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_relation_employeur_individu_on_decl_and_etab on relation_employeur_individu rei (cost=0.57..3,463.73 rows=2,990 width=24) (never executed)

  • Index Cond: (fk_declaration_dsn = decla.id)
18. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_r_individu_salarie_individu_rapproche_fk_individu_salarie on r_individu_salarie_individu_rapproche risi (cost=0.57..5.55 rows=1 width=8) (never executed)

  • Index Cond: (fk_individu_salarie_dsn = rei.fk_individu_salarie_dsn)
19. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_individu_rapproche on individu_rapproche ir (cost=0.56..5.54 rows=1 width=23) (never executed)

  • Index Cond: (id = risi.fk_individu_rapproche)
  • Filter: ((siret)::text = '06180558600049'::text)
20. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_individu_salarie_dsn on individu_salarie_dsn i (cost=0.57..5.55 rows=1 width=123) (never executed)

  • Index Cond: (id = risi.fk_individu_salarie_dsn)
21. 0.000 0.000 ↓ 0.0 0

Seq Scan on sexe (cost=0.00..1.03 rows=3 width=7) (never executed)

22. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_contrat_travail_dsn on contrat_travail_dsn con (cost=0.57..3.10 rows=1 width=8) (never executed)

  • Index Cond: (id = rei.fk_contrat_travail_dsn)
  • Heap Fetches: 0
23. 0.000 0.000 ↓ 0.0 0

Seq Scan on temp_agregation_etablissement_contrat1 mt (cost=0.00..19.89 rows=689 width=104) (never executed)

24.          

SubPlan (for Result)

25. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=621.44..621.45 rows=1 width=32) (never executed)

26. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=3.28..621.44 rows=1 width=6) (never executed)

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

Nested Loop (cost=3.28..619.90 rows=1 width=10) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2.71..613.88 rows=1 width=8) (never executed)

  • Join Filter: (d.fk_etablissement_dsn = e.id)
29. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2.27..595.57 rows=55 width=16) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.71..304.84 rows=57 width=16) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.14..32.47 rows=8 width=12) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_r_individu_salarie_individu_rapproche on r_individu_salarie_individu_rapproche risi_1 (cost=0.57..3.71 rows=8 width=4) (never executed)

  • Index Cond: (fk_individu_rapproche = ir.id)
  • Heap Fetches: 0
33. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_individu_salarie_dsn on individu_salarie_dsn ind (cost=0.57..3.58 rows=1 width=8) (never executed)

  • Index Cond: (id = risi_1.fk_individu_salarie_dsn)
  • Heap Fetches: 0
34. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_versement_individu_salarie_dsn_on_fk_individu_salarie_dsn on versement_individu_salarie_dsn vers (cost=0.57..33.71 rows=34 width=24) (never executed)

  • Index Cond: (fk_individu_salarie_dsn = ind.id)
35. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_declaration_dsn on declaration_dsn d (cost=0.56..5.09 rows=1 width=16) (never executed)

  • Index Cond: (id = vers.fk_declaration_dsn)
  • Filter: dsn_active
36. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.43..15.02 rows=4 width=8) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_etablissement_dsn_on_siret on etablissement_dsn e (cost=0.43..15.00 rows=4 width=8) (never executed)

  • Index Cond: ((siret)::text = '85520050700306'::text)
38. 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..6.01 rows=1 width=18) (never executed)

  • Index Cond: (fk_versement_individu_salarie_dsn = vers.id)
  • Filter: ((dt_deb_prd_rattachement >= '2019-02-01'::date) AND (dt_deb_prd_rattachement <= '2019-02-28'::date))
39. 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[]))
40.          

SubPlan (for Subquery Scan)

41. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=0.02..0.53 rows=100 width=32) (never executed)

  • Group Key: json_array_elements_text(array_to_json(indiv.listids))
42. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=32) (never executed)

43. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=0.02..0.53 rows=100 width=32) (never executed)

  • Group Key: json_array_elements_text(array_to_json(indiv.listidsdecla))
44. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=32) (never executed)

Planning time : 18.521 ms
Execution time : 28.747 ms