explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YfBu : test

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

Subquery Scan on indiv (cost=118.12..122.74 rows=1 width=944) (actual time=0.052..0.052 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)) ~~ '%'::text) AND (COALESCE(lower((indiv.nom_marital_sngi_tempo)::text), lower((indiv.nom_marital_tempo)::text), ''::text) ~~ '%'::text) AND ((COALESCE(indiv.total, '0'::numeric))::text ~~ '%'::text) AND ((COALESCE(indiv.plafond, '0'::numeric))::text ~~ '%'::text) AND ((COALESCE(indiv.csg, '0'::numeric))::text ~~ '%'::text) AND ((COALESCE(indiv.frais_pro, '0'::numeric))::text ~~ '%'::text))
  • Buffers: shared hit=2
2. 0.004 0.052 ↓ 0.0 0 1

Sort (cost=118.12..118.12 rows=1 width=1,724) (actual time=0.052..0.052 rows=0 loops=1)

  • Sort Key: (COALESCE(i.nom_famille_sngi, i.nom_famille))
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2
3. 0.001 0.048 ↓ 0.0 0 1

WindowAgg (cost=81.03..118.11 rows=1 width=1,724) (actual time=0.048..0.048 rows=0 loops=1)

  • Buffers: shared hit=2
4. 0.005 0.047 ↓ 0.0 0 1

Sort (cost=81.03..81.03 rows=1 width=1,466) (actual time=0.047..0.047 rows=0 loops=1)

  • Sort Key: ir.id, decla.dt_mois_principal_declare DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2
5. 0.014 0.042 ↓ 0.0 0 1

Hash Right Join (cost=77.48..81.02 rows=1 width=1,466) (actual time=0.042..0.042 rows=0 loops=1)

  • Hash Cond: (individu_rapproche.id = ir.id)
  • Buffers: shared hit=2
6. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=49.24..51.22 rows=113 width=104) (never executed)

  • Group Key: individu_rapproche.id
7. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=12.06..42.23 rows=113 width=28) (never executed)

  • Join Filter: (vers.fk_individu_salarie_dsn = indiv_1.id)
8. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=11.92..40.18 rows=11 width=40) (never executed)

9. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=11.77..38.12 rows=11 width=36) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=11.62..37.60 rows=1 width=28) (never executed)

11. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=11.48..34.16 rows=3 width=36) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=11.33..31.76 rows=11 width=36) (never executed)

  • Hash Cond: (vers.id = base.fk_versement_individu_salarie_dsn)
13. 0.000 0.000 ↓ 0.0 0

Seq Scan on versement_individu_salarie_dsn vers (cost=0.00..17.50 rows=750 width=24) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Hash (cost=11.30..11.30 rows=3 width=28) (never executed)

15. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on base_assujettie base (cost=4.18..11.30 rows=3 width=28) (never executed)

  • Recheck Cond: ((dt_deb_prd_rattachement >= '2018-04-01'::date) AND (dt_deb_prd_rattachement <= '2018-04-30'::date))
16. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_base_assujettie_on_dt_deb_fin_prd_rattachement (cost=0.00..4.18 rows=3 width=0) (never executed)

  • Index Cond: ((dt_deb_prd_rattachement >= '2018-04-01'::date) AND (dt_deb_prd_rattachement <= '2018-04-30'::date))
17. 0.000 0.000 ↓ 0.0 0

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

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

Index Scan using pk_etablissement_dsn on etablissement_dsn e (cost=0.14..0.90 rows=1 width=8) (never executed)

  • Index Cond: (id = d.fk_etablissement_dsn)
  • Filter: ((siret)::text = '55201662800182'::text)
19. 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_1 (cost=0.15..0.41 rows=11 width=8) (never executed)

  • Index Cond: (fk_individu_salarie_dsn = vers.fk_individu_salarie_dsn)
20. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_individu_rapproche on individu_rapproche (cost=0.15..0.19 rows=1 width=8) (never executed)

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

Index Only Scan using pk_individu_salarie_dsn on individu_salarie_dsn indiv_1 (cost=0.14..0.17 rows=1 width=8) (never executed)

  • Index Cond: (id = risi_1.fk_individu_salarie_dsn)
  • Heap Fetches: 0
22.          

SubPlan (for HashAggregate)

23. 0.000 0.000 ↓ 0.0 0

Seq Scan on type_base_assujettie type_base_assujettie_2 (cost=0.00..1.68 rows=1 width=4) (never executed)

  • Filter: (code = '04'::bpchar)
24. 0.000 0.000 ↓ 0.0 0

Seq Scan on type_base_assujettie type_base_assujettie_1 (cost=0.00..1.68 rows=1 width=4) (never executed)

  • Filter: (code = '02'::bpchar)
25. 0.000 0.000 ↓ 0.0 0

Seq Scan on type_base_assujettie (cost=0.00..1.68 rows=1 width=4) (never executed)

  • Filter: (code = '03'::bpchar)
26. 0.000 0.028 ↓ 0.0 0 1

Hash (cost=28.22..28.22 rows=1 width=1,370) (actual time=0.028..0.028 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=2
27. 0.001 0.028 ↓ 0.0 0 1

Nested Loop (cost=5.03..28.22 rows=1 width=1,370) (actual time=0.027..0.028 rows=0 loops=1)

  • Buffers: shared hit=2
28. 0.000 0.027 ↓ 0.0 0 1

Nested Loop Left Join (cost=4.89..27.86 rows=1 width=1,370) (actual time=0.027..0.027 rows=0 loops=1)

  • Join Filter: (sexe.id = i.fk_sexe)
  • Buffers: shared hit=2
29. 0.000 0.027 ↓ 0.0 0 1

Nested Loop (cost=4.89..26.79 rows=1 width=1,362) (actual time=0.027..0.027 rows=0 loops=1)

  • Buffers: shared hit=2
30. 0.000 0.027 ↓ 0.0 0 1

Nested Loop (cost=4.74..26.59 rows=1 width=1,404) (actual time=0.027..0.027 rows=0 loops=1)

  • Buffers: shared hit=2
31. 0.000 0.027 ↓ 0.0 0 1

Nested Loop (cost=4.60..26.41 rows=1 width=82) (actual time=0.027..0.027 rows=0 loops=1)

  • Buffers: shared hit=2
32. 0.000 0.027 ↓ 0.0 0 1

Nested Loop (cost=4.45..25.86 rows=1 width=74) (actual time=0.027..0.027 rows=0 loops=1)

  • Buffers: shared hit=2
33. 0.001 0.027 ↓ 0.0 0 1

Nested Loop (cost=0.29..16.34 rows=1 width=58) (actual time=0.027..0.027 rows=0 loops=1)

  • Join Filter: (decla.fk_etablissement_dsn = etab.id)
  • Buffers: shared hit=2
34. 0.026 0.026 ↓ 0.0 0 1

Index Scan using idx_declaration_on_dt_mois_principal_declare_no_fraction_cast on declaration_dsn decla (cost=0.14..8.16 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)

  • Index Cond: ((dt_mois_principal_declare >= '2018-04-01'::date) AND (dt_mois_principal_declare <= '2018-04-30'::date))
  • Filter: (dsn_active IS TRUE)
  • Buffers: shared hit=2
35. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_etablissement_dsn_on_siret on etablissement_dsn etab (cost=0.14..8.16 rows=1 width=54) (never executed)

  • Index Cond: ((siret)::text = '55201662800182'::text)
36. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on relation_employeur_individu rei (cost=4.16..9.50 rows=2 width=24) (never executed)

  • Recheck Cond: (fk_declaration_dsn = decla.id)
37. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_relation_employeur_individu_on_decl_and_etab (cost=0.00..4.16 rows=2 width=0) (never executed)

  • Index Cond: (fk_declaration_dsn = decla.id)
38. 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.15..0.44 rows=11 width=8) (never executed)

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

Index Scan using pk_individu_salarie_dsn on individu_salarie_dsn i (cost=0.14..0.17 rows=1 width=1,342) (never executed)

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

Index Scan using pk_individu_rapproche on individu_rapproche ir (cost=0.15..0.19 rows=1 width=54) (never executed)

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

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

42. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_contrat_travail_dsn on contrat_travail_dsn con (cost=0.14..0.25 rows=1 width=8) (never executed)

  • Index Cond: (id = rei.fk_contrat_travail_dsn)
  • Filter: ((no_contrat)::text ~~ '1000-01-01'::text)
43.          

SubPlan (for WindowAgg)

44. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=37.01..37.02 rows=1 width=32) (never executed)

45. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=14.89..37.01 rows=1 width=20) (never executed)

46. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=14.74..36.76 rows=1 width=36) (never executed)

47. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=14.60..36.54 rows=1 width=28) (never executed)

48. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=14.46..34.89 rows=1 width=36) (never executed)

49. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=14.32..34.67 rows=1 width=36) (never executed)

  • Hash Cond: (vers_1.id = aerdfraispro.fk_versement_individu_salarie_dsn)
50. 0.000 0.000 ↓ 0.0 0

Seq Scan on versement_individu_salarie_dsn vers_1 (cost=0.00..17.50 rows=750 width=24) (never executed)

51. 0.000 0.000 ↓ 0.0 0

Hash (cost=14.30..14.30 rows=1 width=28) (never executed)

52. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=4.19..14.30 rows=1 width=28) (never executed)

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

Bitmap Heap Scan on autre_element_revenu_brut aerdfraispro (cost=4.19..12.66 rows=4 width=32) (never executed)

  • Recheck Cond: ((dt_deb_prd_rattachement >= '2018-04-01'::date) AND (dt_deb_prd_rattachement <= '2018-04-30'::date))
54. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_autre_element_revenu_brut_on_dt_deb_fin_prd_rattachement (cost=0.00..4.19 rows=4 width=0) (never executed)

  • Index Cond: ((dt_deb_prd_rattachement >= '2018-04-01'::date) AND (dt_deb_prd_rattachement <= '2018-04-30'::date))
55. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.47 rows=3 width=4) (never executed)

56. 0.000 0.000 ↓ 0.0 0

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

  • Filter: (code = ANY ('{07,08,09}'::bpchar[]))
57. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_declaration_dsn on declaration_dsn d_1 (cost=0.14..0.22 rows=1 width=16) (never executed)

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

Index Scan using pk_etablissement_dsn on etablissement_dsn e_1 (cost=0.14..0.90 rows=1 width=8) (never executed)

  • Index Cond: (id = d_1.fk_etablissement_dsn)
  • Filter: ((siret)::text = '55201662800182'::text)
59. 0.000 0.000 ↓ 0.0 0

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

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

Index Only Scan using pk_r_individu_salarie_individu_rapproche on r_individu_salarie_individu_rapproche risi_2 (cost=0.15..0.23 rows=1 width=4) (never executed)

  • Index Cond: ((fk_individu_rapproche = ir.id) AND (fk_individu_salarie_dsn = vers_1.fk_individu_salarie_dsn))
  • Heap Fetches: 0
61.          

SubPlan (for Subquery Scan)

62. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=0.77..2.27 rows=100 width=32) (never executed)

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

ProjectSet (cost=0.00..0.52 rows=100 width=32) (never executed)

64. 0.000 0.000 ↓ 0.0 0

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

65. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=0.77..2.27 rows=100 width=32) (never executed)

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

ProjectSet (cost=0.00..0.52 rows=100 width=32) (never executed)

67. 0.000 0.000 ↓ 0.0 0

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

Planning time : 6.487 ms
Execution time : 0.464 ms