explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rw1s

Settings
# exclusive inclusive rows x rows loops node
1. 39.394 2,920,605.901 ↓ 3,491.4 24,440 1

GroupAggregate (cost=154,476.80..154,565.11 rows=7 width=2,590) (actual time=2,920,563.239..2,920,605.901 rows=24,440 loops=1)

  • Output: soc.soc_id, soc_lan.nom_soc_ged, fam_prod.fam_id, prod.pro_id, prod.desc_courte, fam_prod.desc_courte, dossier.dossier_id, dossier.numero_dossier, dossier.dte_affiliation, mut_dossier.mut_dossier_id, mut_dossier.mut_id, ((SubPlan 1)), mut_dossier.dte_mut, mut_dossier.etat, ((SubPlan 2)), mut_dossier.statut, ((SubPlan 3)), prest_sortie.prest_sortie_id, sum(COALESCE(prest_sortie.avoir_min_lpp, '0'::numeric)), sum(COALESCE(prest_sortie.avoir_reglementaire, '0'::numeric)), sum(COALESCE(prest_sortie.prest_sortie, '0'::numeric)), a1_part_affi.part_id, a1_part_affi.numero_contact, (replace(((((btrim((a1_part_affi.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_part_affi.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_part_affi.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), a1_part_affi.sexe, a1_part_affi.dte_naissance, a1_part_affi.etat_civil, partie_dossier.partie_dossier_id, partie_dossier.etat, ((SubPlan 4)), partie_dossier.type_partie, ((SubPlan 5)), partie_dossier.dte_deb_partie, a1_adresses_affi.numero_contact, a1_adresses_affi.rue_formatee, a1_adresses_affi.npa_loc_formatee, a1_adresses_affi.code_iso2_pays, a1_adresses_affi.code_canton_etat
  • Group Key: soc.soc_id, soc_lan.nom_soc_ged, fam_prod.fam_id, prod.pro_id, dossier.dossier_id, mut_dossier.mut_dossier_id, ((SubPlan 1)), ((SubPlan 2)), ((SubPlan 3)), prest_sortie.prest_sortie_id, a1_part_affi.part_id, (replace(((((btrim((a1_part_affi.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_part_affi.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_part_affi.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), partie_dossier.partie_dossier_id, ((SubPlan 4)), ((SubPlan 5)), a1_adresses_affi.numero_contact, a1_adresses_affi.rue_formatee, a1_adresses_affi.npa_loc_formatee, a1_adresses_affi.code_iso2_pays, a1_adresses_affi.code_canton_etat
  • Buffers: shared hit=5601973 read=40242
2. 185.331 2,920,566.507 ↓ 3,491.4 24,440 1

Sort (cost=154,476.80..154,476.82 rows=7 width=2,512) (actual time=2,920,563.189..2,920,566.507 rows=24,440 loops=1)

  • Output: soc.soc_id, soc_lan.nom_soc_ged, fam_prod.fam_id, prod.pro_id, dossier.dossier_id, mut_dossier.mut_dossier_id, ((SubPlan 1)), ((SubPlan 2)), ((SubPlan 3)), prest_sortie.prest_sortie_id, a1_part_affi.part_id, (replace(((((btrim((a1_part_affi.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_part_affi.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_part_affi.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), partie_dossier.partie_dossier_id, ((SubPlan 4)), ((SubPlan 5)), a1_adresses_affi.numero_contact, a1_adresses_affi.rue_formatee, a1_adresses_affi.npa_loc_formatee, a1_adresses_affi.code_iso2_pays, a1_adresses_affi.code_canton_etat, prod.desc_courte, fam_prod.desc_courte, dossier.numero_dossier, dossier.dte_affiliation, mut_dossier.mut_id, mut_dossier.dte_mut, mut_dossier.etat, mut_dossier.statut, prest_sortie.avoir_min_lpp, prest_sortie.avoir_reglementaire, prest_sortie.prest_sortie, a1_part_affi.numero_contact, a1_part_affi.sexe, a1_part_affi.dte_naissance, a1_part_affi.etat_civil, partie_dossier.etat, partie_dossier.type_partie, partie_dossier.dte_deb_partie
  • Sort Key: soc_lan.nom_soc_ged, prod.pro_id, dossier.dossier_id, mut_dossier.mut_dossier_id, ((SubPlan 1)), ((SubPlan 2)), ((SubPlan 3)), prest_sortie.prest_sortie_id, a1_part_affi.part_id, (replace(((((btrim((a1_part_affi.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_part_affi.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_part_affi.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), partie_dossier.partie_dossier_id, ((SubPlan 4)), ((SubPlan 5)), a1_adresses_affi.numero_contact, a1_adresses_affi.rue_formatee, a1_adresses_affi.npa_loc_formatee, a1_adresses_affi.code_iso2_pays, a1_adresses_affi.code_canton_etat
  • Sort Method: quicksort Memory: 13314kB
  • Buffers: shared hit=5601973 read=40242
3. 522.590 2,920,381.176 ↓ 3,491.4 24,440 1

Merge Join (cost=116.91..154,476.70 rows=7 width=2,512) (actual time=4,951.255..2,920,381.176 rows=24,440 loops=1)

  • Output: soc.soc_id, soc_lan.nom_soc_ged, fam_prod.fam_id, prod.pro_id, dossier.dossier_id, mut_dossier.mut_dossier_id, (SubPlan 1), (SubPlan 2), (SubPlan 3), prest_sortie.prest_sortie_id, a1_part_affi.part_id, replace(((((btrim((a1_part_affi.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_part_affi.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_part_affi.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text), partie_dossier.partie_dossier_id, (SubPlan 4), (SubPlan 5), a1_adresses_affi.numero_contact, a1_adresses_affi.rue_formatee, a1_adresses_affi.npa_loc_formatee, a1_adresses_affi.code_iso2_pays, a1_adresses_affi.code_canton_etat, prod.desc_courte, fam_prod.desc_courte, dossier.numero_dossier, dossier.dte_affiliation, mut_dossier.mut_id, mut_dossier.dte_mut, mut_dossier.etat, mut_dossier.statut, prest_sortie.avoir_min_lpp, prest_sortie.avoir_reglementaire, prest_sortie.prest_sortie, a1_part_affi.numero_contact, a1_part_affi.sexe, a1_part_affi.dte_naissance, a1_part_affi.etat_civil, partie_dossier.etat, partie_dossier.type_partie, partie_dossier.dte_deb_partie
  • Inner Unique: true
  • Merge Cond: (a1_part_affi.numero_contact = a1_bi_partenaire_affi.numero_contact)
  • Buffers: shared hit=5601970 read=40242
4. 280.571 2,918,526.302 ↓ 1,357.8 24,440 1

Merge Join (cost=83.30..372,400.37 rows=18 width=216) (actual time=4,947.788..2,918,526.302 rows=24,440 loops=1)

  • Output: prest_sortie.prest_sortie_id, prest_sortie.avoir_min_lpp, prest_sortie.avoir_reglementaire, prest_sortie.prest_sortie, partie_dossier.partie_dossier_id, partie_dossier.etat, partie_dossier.type_partie, partie_dossier.dte_deb_partie, mut_dossier.mut_dossier_id, mut_dossier.mut_id, mut_dossier.dte_mut, mut_dossier.etat, mut_dossier.statut, dossier.dossier_id, dossier.numero_dossier, dossier.dte_affiliation, prod.pro_id, prod.desc_courte, fam_prod.fam_id, fam_prod.desc_courte, soc.soc_id, soc_lan.nom_soc_ged, a1_part_affi.part_id, a1_part_affi.numero_contact, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.sexe, a1_part_affi.dte_naissance, a1_part_affi.etat_civil, a1_adresses_affi.numero_contact, a1_adresses_affi.rue_formatee, a1_adresses_affi.npa_loc_formatee, a1_adresses_affi.code_iso2_pays, a1_adresses_affi.code_canton_etat
  • Merge Cond: (a1_part_affi.numero_contact = a1_adresses_affi.numero_contact)
  • Buffers: shared hit=5235383 read=38585
5. 2,267,230.870 2,917,261.533 ↓ 555.5 24,440 1

Nested Loop (cost=4.99..905,570.63 rows=44 width=174) (actual time=4,933.993..2,917,261.533 rows=24,440 loops=1)

  • Output: prest_sortie.prest_sortie_id, prest_sortie.avoir_min_lpp, prest_sortie.avoir_reglementaire, prest_sortie.prest_sortie, partie_dossier.partie_dossier_id, partie_dossier.etat, partie_dossier.type_partie, partie_dossier.dte_deb_partie, mut_dossier.mut_dossier_id, mut_dossier.mut_id, mut_dossier.dte_mut, mut_dossier.etat, mut_dossier.statut, dossier.dossier_id, dossier.numero_dossier, dossier.dte_affiliation, prod.pro_id, prod.desc_courte, fam_prod.fam_id, fam_prod.desc_courte, soc.soc_id, soc_lan.nom_soc_ged, a1_part_affi.part_id, a1_part_affi.numero_contact, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.sexe, a1_part_affi.dte_naissance, a1_part_affi.etat_civil
  • Join Filter: (a1_part_dossier_affi.part_dossier_part_id = a1_part_affi.part_id)
  • Rows Removed by Join Filter: 12434656520
  • Buffers: shared hit=5101202 read=24137
6. 2,348.631 2,348.631 ↑ 1.0 508,784 1

Index Scan using idx_part_numero_contact on iliade.part a1_part_affi (cost=0.42..17,815.33 rows=508,784 width=36) (actual time=0.033..2,348.631 rows=508,784 loops=1)

  • Output: a1_part_affi.part_id, a1_part_affi.type_part, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.nom_complementaire, a1_part_affi.nom_alliance, a1_part_affi.prenom, a1_part_affi.dte_naissance, a1_part_affi.dte_deces, a1_part_affi.sexe, a1_part_affi.etat_civil, a1_part_affi.no_avs, a1_part_affi.langue_correspondance, a1_part_affi.usr_log_i, a1_part_affi.dte_log_i, a1_part_affi.usr_log_u, a1_part_affi.dte_log_u, a1_part_affi.audit, a1_part_affi.visa, a1_part_affi.numero_contact
  • Buffers: shared hit=128094 read=10339
7. 642,847.033 647,682.032 ↓ 555.5 24,440 508,784

Materialize (cost=4.56..551,957.96 rows=44 width=142) (actual time=0.000..1.273 rows=24,440 loops=508,784)

  • Output: prest_sortie.prest_sortie_id, prest_sortie.avoir_min_lpp, prest_sortie.avoir_reglementaire, prest_sortie.prest_sortie, partie_dossier.partie_dossier_id, partie_dossier.etat, partie_dossier.type_partie, partie_dossier.dte_deb_partie, mut_dossier.mut_dossier_id, mut_dossier.mut_id, mut_dossier.dte_mut, mut_dossier.etat, mut_dossier.statut, dossier.dossier_id, dossier.numero_dossier, dossier.dte_affiliation, prod.pro_id, prod.desc_courte, fam_prod.fam_id, fam_prod.desc_courte, soc.soc_id, soc_lan.nom_soc_ged, a1_part_dossier_affi.part_dossier_part_id
  • Buffers: shared hit=4973108 read=13798
8. 5.709 4,834.999 ↓ 555.5 24,440 1

Nested Loop (cost=4.56..551,957.74 rows=44 width=142) (actual time=11.008..4,834.999 rows=24,440 loops=1)

  • Output: prest_sortie.prest_sortie_id, prest_sortie.avoir_min_lpp, prest_sortie.avoir_reglementaire, prest_sortie.prest_sortie, partie_dossier.partie_dossier_id, partie_dossier.etat, partie_dossier.type_partie, partie_dossier.dte_deb_partie, mut_dossier.mut_dossier_id, mut_dossier.mut_id, mut_dossier.dte_mut, mut_dossier.etat, mut_dossier.statut, dossier.dossier_id, dossier.numero_dossier, dossier.dte_affiliation, prod.pro_id, prod.desc_courte, fam_prod.fam_id, fam_prod.desc_courte, soc.soc_id, soc_lan.nom_soc_ged, a1_part_dossier_affi.part_dossier_part_id
  • Buffers: shared hit=4973108 read=13798
9. 0.003 0.045 ↑ 1.0 1 1

Nested Loop (cost=0.00..2.52 rows=1 width=26) (actual time=0.031..0.045 rows=1 loops=1)

  • Output: soc.soc_id, soc_lan.nom_soc_ged
  • Buffers: shared hit=2
10. 0.019 0.019 ↑ 1.0 1 1

Seq Scan on produit.soc (cost=0.00..1.09 rows=1 width=4) (actual time=0.015..0.019 rows=1 loops=1)

  • Output: soc.soc_id, soc.desc_courte, soc.usr_log_i, soc.dte_log_i, soc.usr_log_u, soc.dte_log_u
  • Filter: (soc.soc_id = 3)
  • Rows Removed by Filter: 6
  • Buffers: shared hit=1
11. 0.023 0.023 ↑ 1.0 1 1

Seq Scan on produit.soc_lan (cost=0.00..1.42 rows=1 width=26) (actual time=0.015..0.023 rows=1 loops=1)

  • Output: soc_lan.soc_id, soc_lan.lan, soc_lan.nom_soc, soc_lan.nom_soc_ged, soc_lan.sign_soc, soc_lan.desc_voies_recours
  • Filter: ((soc_lan.soc_id = 3) AND ((soc_lan.lan)::text = 'FR'::text))
  • Rows Removed by Filter: 27
  • Buffers: shared hit=1
12. 17.270 4,829.245 ↓ 555.5 24,440 1

Nested Loop (cost=4.56..551,954.79 rows=44 width=120) (actual time=10.973..4,829.245 rows=24,440 loops=1)

  • Output: prest_sortie.prest_sortie_id, prest_sortie.avoir_min_lpp, prest_sortie.avoir_reglementaire, prest_sortie.prest_sortie, partie_dossier.partie_dossier_id, partie_dossier.etat, partie_dossier.type_partie, partie_dossier.dte_deb_partie, mut_dossier.mut_dossier_id, mut_dossier.mut_id, mut_dossier.dte_mut, mut_dossier.etat, mut_dossier.statut, dossier.dossier_id, dossier.numero_dossier, dossier.dte_affiliation, prod.pro_id, prod.desc_courte, fam_prod.fam_id, fam_prod.desc_courte, fam_prod.soc_id, a1_part_dossier_affi.part_dossier_part_id
  • Buffers: shared hit=4973106 read=13798
13. 18.080 4,420.935 ↓ 543.1 24,440 1

Nested Loop (cost=4.13..551,917.38 rows=45 width=124) (actual time=10.898..4,420.935 rows=24,440 loops=1)

  • Output: prest_sortie.prest_sortie_id, prest_sortie.avoir_min_lpp, prest_sortie.avoir_reglementaire, prest_sortie.prest_sortie, partie_dossier.partie_dossier_id, partie_dossier.etat, partie_dossier.type_partie, partie_dossier.dte_deb_partie, partie_dossier.dossier_gen_id, dossier_gen.dossier_gen_id, mut_dossier.mut_dossier_id, mut_dossier.mut_id, mut_dossier.dte_mut, mut_dossier.etat, mut_dossier.statut, dossier.dossier_id, dossier.numero_dossier, dossier.dte_affiliation, prod.pro_id, prod.desc_courte, fam_prod.fam_id, fam_prod.desc_courte, fam_prod.soc_id
  • Join Filter: (contrat.contrat_id = mut_contrat.contrat_id)
  • Buffers: shared hit=4877372 read=10559
14. 5.429 3,571.895 ↓ 40.7 24,440 1

Nested Loop (cost=3.71..536,980.14 rows=601 width=132) (actual time=10.105..3,571.895 rows=24,440 loops=1)

  • Output: prest_sortie.prest_sortie_id, prest_sortie.avoir_min_lpp, prest_sortie.avoir_reglementaire, prest_sortie.prest_sortie, partie_dossier.partie_dossier_id, partie_dossier.etat, partie_dossier.type_partie, partie_dossier.dte_deb_partie, partie_dossier.dossier_gen_id, dossier_gen.dossier_gen_id, mut_dossier.mut_dossier_id, mut_dossier.mut_id, mut_dossier.dte_mut, mut_dossier.etat, mut_dossier.statut, dossier.dossier_id, dossier.numero_dossier, dossier.dte_affiliation, dossier.contrat_id, contrat.contrat_id, prod.pro_id, prod.desc_courte, fam_prod.fam_id, fam_prod.desc_courte, fam_prod.soc_id
  • Buffers: shared hit=3552918 read=10554
15. 23.675 3,224.306 ↓ 35.7 24,440 1

Nested Loop (cost=3.28..536,552.70 rows=684 width=110) (actual time=10.064..3,224.306 rows=24,440 loops=1)

  • Output: partie_dossier.partie_dossier_id, partie_dossier.etat, partie_dossier.type_partie, partie_dossier.dte_deb_partie, partie_dossier.dossier_gen_id, dossier_gen.dossier_gen_id, mut_dossier.mut_dossier_id, mut_dossier.mut_id, mut_dossier.dte_mut, mut_dossier.etat, mut_dossier.statut, dossier.dossier_id, dossier.numero_dossier, dossier.dte_affiliation, dossier.contrat_id, contrat.contrat_id, prod.pro_id, prod.desc_courte, fam_prod.fam_id, fam_prod.desc_courte, fam_prod.soc_id
  • Buffers: shared hit=3457018 read=8143
16. 12.369 3,103.147 ↓ 37.5 24,371 1

Nested Loop (cost=2.84..536,215.68 rows=650 width=88) (actual time=10.034..3,103.147 rows=24,371 loops=1)

  • Output: dossier_gen.dossier_gen_id, mut_dossier.mut_dossier_id, mut_dossier.mut_id, mut_dossier.dte_mut, mut_dossier.etat, mut_dossier.statut, dossier.dossier_id, dossier.numero_dossier, dossier.dte_affiliation, dossier.contrat_id, contrat.contrat_id, prod.pro_id, prod.desc_courte, fam_prod.fam_id, fam_prod.desc_courte, fam_prod.soc_id
  • Buffers: shared hit=3359922 read=7612
17. 30.141 2,968.923 ↓ 37.2 24,371 1

Nested Loop (cost=2.41..534,852.82 rows=655 width=84) (actual time=10.001..2,968.923 rows=24,371 loops=1)

  • Output: mut_dossier.mut_dossier_id, mut_dossier.mut_id, mut_dossier.dte_mut, mut_dossier.etat, mut_dossier.statut, dossier.dossier_id, dossier.numero_dossier, dossier.dte_affiliation, dossier.contrat_id, contrat.contrat_id, prod.pro_id, prod.desc_courte, fam_prod.fam_id, fam_prod.desc_courte, fam_prod.soc_id
  • Buffers: shared hit=3263931 read=5983
18. 11.283 130.982 ↓ 7.9 56,156 1

Nested Loop (cost=1.98..1,308.84 rows=7,073 width=62) (actual time=9.945..130.982 rows=56,156 loops=1)

  • Output: dossier.dossier_id, dossier.numero_dossier, dossier.dte_affiliation, dossier.contrat_id, contrat.contrat_id, prod.pro_id, prod.desc_courte, fam_prod.fam_id, fam_prod.desc_courte, fam_prod.soc_id
  • Buffers: shared hit=6239 read=2361
19. 3.773 13.287 ↑ 38.5 4 1

Nested Loop (cost=1.56..606.13 rows=154 width=46) (actual time=9.386..13.287 rows=4 loops=1)

  • Output: contrat.contrat_id, prod.pro_id, prod.desc_courte, fam_prod.fam_id, fam_prod.desc_courte, fam_prod.soc_id
  • Join Filter: (prod.pro_id = contrat.pro_id)
  • Rows Removed by Join Filter: 22392
  • Buffers: shared hit=3650 read=32
20. 9.514 9.514 ↑ 1.0 5,599 1

Index Scan using p_contrat on iliade.contrat (cost=0.28..264.21 rows=5,599 width=8) (actual time=0.709..9.514 rows=5,599 loops=1)

  • Output: contrat.contrat_id, contrat.employeur_part_id, contrat.mut_dossier_autorisee_id, contrat.pro_id, contrat.numero_contrat, contrat.dte_effet, contrat.dte_effet_pe, contrat.blocage_dossiers, contrat.blocage_fact, contrat.raison_blocage_fact, contrat.visa_blocage_fact, contrat.dte_operation_blocage_fact, contrat.dte_derniere_fact, contrat.responsable_gest, contrat.responsable_gest_medicale, contrat.ancien_numero_contrat, contrat.reass, contrat.usr_log_i, contrat.dte_log_i, contrat.usr_log_u, contrat.dte_log_u, contrat.dte_entree, contrat.quotite_apport_initial, contrat.comp_cmt_pari, contrat.calc_dte_affiliation, contrat.objet_facturable_allocemp_cree, contrat.dte_derniere_fact_prest, contrat.reass_complete, contrat.audit, contrat.blocage_extranet, contrat.secteur_gestion
  • Buffers: shared hit=3647 read=32
21. 0.000 0.000 ↑ 1.0 4 5,599

Materialize (cost=1.28..5.99 rows=4 width=42) (actual time=0.000..0.000 rows=4 loops=5,599)

  • Output: prod.pro_id, prod.desc_courte, fam_prod.fam_id, fam_prod.desc_courte, fam_prod.soc_id
  • Buffers: shared hit=3
22. 0.005 0.058 ↑ 1.0 4 1

Nested Loop (cost=1.28..5.97 rows=4 width=42) (actual time=0.055..0.058 rows=4 loops=1)

  • Output: prod.pro_id, prod.desc_courte, fam_prod.fam_id, fam_prod.desc_courte, fam_prod.soc_id
  • Buffers: shared hit=3
23. 0.026 0.026 ↑ 1.0 1 1

Seq Scan on produit.fam_prod (cost=0.00..1.60 rows=1 width=17) (actual time=0.024..0.026 rows=1 loops=1)

  • Output: fam_prod.fam_id, fam_prod.soc_id, fam_prod.desc_courte, fam_prod.type_famille, fam_prod.app_gest_id, fam_prod.usr_log_i, fam_prod.dte_log_i, fam_prod.usr_log_u, fam_prod.dte_log_u, fam_prod.dernier_num_police_utilise
  • Filter: ((fam_prod.fam_id = 27) AND (fam_prod.soc_id = 3))
  • Rows Removed by Filter: 39
  • Buffers: shared hit=1
24. 0.012 0.027 ↑ 1.0 4 1

Bitmap Heap Scan on produit.prod (cost=1.28..4.33 rows=4 width=29) (actual time=0.027..0.027 rows=4 loops=1)

  • Output: prod.pro_id, prod.fam_id, prod.dte_deb_app, prod.dte_fin_app, prod.dn_soc_id, prod.desc_courte, prod.usr_log_i, prod.dte_log_i, prod.usr_log_u, prod.dte_log_u, prod.default_nbr_bvr, prod.ged_definition1, prod.ged_definition2, prod.dernier_num_police_utilise, prod.type_prod, prod.ged_type_caisse
  • Recheck Cond: (prod.fam_id = 27)
  • Heap Blocks: exact=1
  • Buffers: shared hit=2
25. 0.015 0.015 ↑ 1.0 4 1

Bitmap Index Scan on idx_prod_fam_id (cost=0.00..1.28 rows=4 width=0) (actual time=0.015..0.015 rows=4 loops=1)

  • Index Cond: (prod.fam_id = 27)
  • Buffers: shared hit=1
26. 106.412 106.412 ↓ 126.5 14,039 4

Index Scan using c_in_d_fk on iliade.dossier (cost=0.42..3.45 rows=111 width=16) (actual time=0.187..26.603 rows=14,039 loops=4)

  • Output: dossier.dossier_id, dossier.contrat_id, dossier.dossier_parent_id, dossier.pro_id, dossier.numero_dossier, dossier.dte_affiliation, dossier.mnt_epa_tot, dossier.mnt_epa_min_lpp, dossier.dte_blocage_fact, dossier.raison_blocage_fact, dossier.visa_blocage_fact, dossier.dte_operation_blocage_fact, dossier.envoi_quest_mise_pension, dossier.objet_facturable_cree, dossier.deposant_epa, dossier.usr_log_i, dossier.dte_log_i, dossier.usr_log_u, dossier.dte_log_u, dossier.audit, dossier.blocage_extranet, dossier.responsable_gest, dossier.responsable_gest_medicale, dossier.id_mut_blocage_fact, dossier.raison_blocage_mutations, dossier.visa_blocage_mutations, dossier.blocage_mutations, dossier.dte_blocage_mutations, dossier.business_warning, dossier.sexe_affilie, dossier.dte_naissance_affilie
  • Index Cond: (dossier.contrat_id = contrat.contrat_id)
  • Buffers: shared hit=2589 read=2329
27. 960.820 2,807.800 ↓ 0.0 0 56,156

Index Scan using idx_mut_dossier_dossier_id_no_gen_dossier on iliade.mut_dossier (cost=0.43..75.42 rows=1 width=26) (actual time=0.050..0.050 rows=0 loops=56,156)

  • Output: mut_dossier.mut_dossier_id, mut_dossier.donnees_mut_dossier_id, mut_dossier.per_gest_id, mut_dossier.dossier_id, mut_dossier.mut_id, mut_dossier.no_gen_dossier, mut_dossier.dte_mut, mut_dossier.description, mut_dossier.desc_treeview, mut_dossier.etat, mut_dossier.statut, mut_dossier.statut_precedent, mut_dossier.no_mut, mut_dossier.no_mut_precedente, mut_dossier.no_mut_extournee, mut_dossier.maj_cpt_epa, mut_dossier.dte_prochaine_fact_escompte, mut_dossier.visa_cree_par, mut_dossier.dte_creation, mut_dossier.visa_modifie_par, mut_dossier.dte_modification, mut_dossier.visa_ctrl_par, mut_dossier.dte_ctrl, mut_dossier.usr_log_i, mut_dossier.dte_log_i, mut_dossier.usr_log_u, mut_dossier.dte_log_u, mut_dossier.numero_regrpmnt_mut, mut_dossier.dte_escompte, mut_dossier.audit, mut_dossier.orig_mut, mut_dossier.annonce_extranet_id, mut_dossier.no_offre, mut_dossier.bande_etat_process_id, mut_dossier.nouveau_cas, mut_dossier.pas_de_cas, mut_dossier.cas_id, mut_dossier.no_mut_bck, mut_dossier.no_mut_precedente_bck, mut_dossier.no_mut_extournee_bck
  • Index Cond: (mut_dossier.dossier_id = dossier.dossier_id)
  • Filter: (((mut_dossier.statut)::text <> 'TERM'::text) AND (mut_dossier.no_gen_dossier = (SubPlan 9)))
  • Rows Removed by Filter: 11
  • Buffers: shared hit=3257692 read=3622
28.          

SubPlan (for Index Scan)

29. 615.660 1,846.980 ↑ 1.0 1 615,660

Result (cost=1.85..1.86 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=615,660)

  • Output: $8
  • Buffers: shared hit=2468983 read=16
30.          

Initplan (for Result)

31. 0.000 1,231.320 ↑ 1.0 1 615,660

Limit (cost=0.43..1.85 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=615,660)

  • Output: a_6.no_gen_dossier
  • Buffers: shared hit=2468983 read=16
32. 1,231.320 1,231.320 ↑ 31.0 1 615,660

Index Scan Backward using idx_mut_dossier_dossier_id_no_gen_dossier on iliade.mut_dossier a_6 (cost=0.43..44.43 rows=31 width=4) (actual time=0.002..0.002 rows=1 loops=615,660)

  • Output: a_6.no_gen_dossier
  • Index Cond: ((mut_dossier.dossier_id = a_6.dossier_id) AND (a_6.no_gen_dossier IS NOT NULL))
  • Filter: ((a_6.etat)::text = 'VALI'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2468983 read=16
33. 121.855 121.855 ↑ 1.0 1 24,371

Index Scan using mu_d_in_d_g_fk on iliade.dossier_gen (cost=0.43..2.07 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=24,371)

  • Output: dossier_gen.dossier_gen_id, dossier_gen.mut_dossier_id, dossier_gen.somme_tx_validite_inva, dossier_gen.vers_anticipe_effectue, dossier_gen.dossier_id, dossier_gen.no_gen_dossier, dossier_gen.confidentiel, dossier_gen.code_voir_dossier, dossier_gen.impots_source, dossier_gen.avoir_50_ans, dossier_gen.retour_avoir_min_lpp, dossier_gen.int_retour_avoir_min_lpp, dossier_gen.retour_avoir_evlp, dossier_gen.int_retour_avoir_evlp, dossier_gen.restitution_avoir_min_lpp, dossier_gen.int_restitution_avoir_min_lpp, dossier_gen.restitution_avoir_evlp, dossier_gen.int_restitution_avoir_evlp, dossier_gen.beneficiaires_deces, dossier_gen.donnees_medicales_introduites, dossier_gen.refus_medical, dossier_gen.non_reponse_etat_sante, dossier_gen.suivi_medical, dossier_gen.dte_suivi_medical, dossier_gen.surprimes, dossier_gen.reserves, dossier_gen.inva, dossier_gen.tot_rev_paye, dossier_gen.dte_calc_avoir, dossier_gen.dte_precedent_calc_avoir, dossier_gen.dte_derniere_idx, dossier_gen.dte_prochaine_annualisation, dossier_gen.dte_prochain_bouclement, dossier_gen.dte_prochain_recalcul, dossier_gen.prise_cpt_causes_reduc, dossier_gen.dte_retraite, dossier_gen.trtmt_partie_active_retraite, dossier_gen.dte_deces_reel, dossier_gen.dte_demission, dossier_gen.freq_paie_rentes, dossier_gen.capital_inva, dossier_gen.tx_inva_externe, dossier_gen.cause_fin_inva, dossier_gen.vers_anticipe_en_cours, dossier_gen.dte_demande_vers_anticipe, dossier_gen.dte_der_vers_anticipe, dossier_gen.raison_vers_anticipe, dossier_gen.remb_tot_der_vers_anticipe, dossier_gen.remb_tot_ensbl_vers_anticipes, dossier_gen.div_en_cours, dossier_gen.inscription_registre_foncier, dossier_gen.mise_en_gage, dossier_gen.jamais_affilie, dossier_gen.dte_premiere_affiliation, dossier_gen.dte_demande_avance_avs, dossier_gen.avance_avs, dossier_gen.dte_fin_avance_avs, dossier_gen.mnt_avance_avs, dossier_gen.ajournement, dossier_gen.dte_fin_ajournement, dossier_gen.maintien_fina_ajournement, dossier_gen.ajournement_annee_en_annee, dossier_gen.dte_prochain_ajournement, dossier_gen.age_premier_ajournement, dossier_gen.calc_av_terme, dossier_gen.conge, dossier_gen.demande_affiliation_recue, dossier_gen.suri, dossier_gen.dte_der_ctrl_suri, dossier_gen.regles_avert_outrepassees, dossier_gen.adresse_paie_connue, dossier_gen.somme_risquee_reass_deces, dossier_gen.somme_risquee_reass_inva, dossier_gen.annonce_reass_deces, dossier_gen.annonce_reass_inva, dossier_gen.duree_cip_illimitee, dossier_gen.dte_entree_actuarielle, dossier_gen.dte_entree_av_conversion, dossier_gen.dte_deb_conge, dossier_gen.dte_deb_conge_effectif, dossier_gen.dte_retour_conge, dossier_gen.dte_retour_conge_effectif, dossier_gen.conge_administratif, dossier_gen.duree_conges_non_rachetes, dossier_gen.mnt_rachat_excd, dossier_gen.dte_rachat_excd, dossier_gen.dte_echeance_rachat_excd, dossier_gen.dte_fin_fine_entree, dossier_gen.sal_inferieur_min_lpp, dossier_gen.calc_prest_sortie_gar, dossier_gen.dte_demande_retraite_capital, dossier_gen.dte_ren_retraite_capital, dossier_gen.dte_ren_avance_avs, dossier_gen.type_compens, dossier_gen.dte_retraite_compensee, dossier_gen.capital_compens_retraite, dossier_gen.capital_compens_suppltemp, dossier_gen.paie_autr_beneficiaires_deces, dossier_gen.suppltemp_avs, dossier_gen.majo_suppltemp_avs, dossier_gen.dte_fin_suppltemp_avs, dossier_gen.dte_fin_majo_suppltemp_avs, dossier_gen.dte_calc_situation_ass, dossier_gen.cause_refus_medical, dossier_gen.majo_tx_pension, dossier_gen.no_gen_dossier_situation_ass, dossier_gen.numero_dossier_etat, dossier_gen.usr_log_i, dossier_gen.dte_log_i, dossier_gen.usr_log_u, dossier_gen.dte_log_u, dossier_gen.cause_changement_employeur, dossier_gen.age_terme_vise, dossier_gen.code_urev_urcip, dossier_gen.pas_pu_se_prononcer, dossier_gen.prest_sortie_en_especes, dossier_gen.gest_reservee, dossier_gen.reass_deces, dossier_gen.reass_inva, dossier_gen.inva_proportion_reassuree, dossier_gen.annonce_reass_deces_facultatif, dossier_gen.annonce_reass_inva_facultatif, dossier_gen.annonce_reass_deces_sinistre, dossier_gen.annonce_reass_inva_sinistre, dossier_gen.prct_avance_avs, dossier_gen.remb_avance_avs_prime_unique, dossier_gen.paie_remb_avance_avs_effectue, dossier_gen.frais_gest_demission, dossier_gen.audit, dossier_gen.suivi_demande_affiliation, dossier_gen.dte_suivi_demande_affiliation, dossier_gen.stipulation_beneficiaire, dossier_gen.mois_demission_paye, dossier_gen.pleine_capacite_travail, dossier_gen.beneficiaire_rte_ai, dossier_gen.dte_deb_inva_reconnue_ai, dossier_gen.envoi_doc_affiliation, dossier_gen.adresse_paie, dossier_gen.dte_calc_deroulement, dossier_gen.dte_fina_plus_maintenu, dossier_gen.donnees_retraite_annoncees, dossier_gen.mnt_rachat_deg_pos, dossier_gen.suivi_retraite, dossier_gen.dte_prochain_suivi_retraite, dossier_gen.quest_retraite_employeur_recu, dossier_gen.migration_hermes, dossier_gen.motif_paiement, dossier_gen.avoir_50_ans_lpp, dossier_gen.dossier_cible_id
  • Index Cond: (dossier_gen.mut_dossier_id = mut_dossier.mut_dossier_id)
  • Buffers: shared hit=95991 read=1629
34. 97.484 97.484 ↑ 2.0 1 24,371

Index Scan using d_g_in_pi_d_fk on iliade.partie_dossier (cost=0.43..0.50 rows=2 width=22) (actual time=0.004..0.004 rows=1 loops=24,371)

  • Output: partie_dossier.partie_dossier_id, partie_dossier.partie_liee_partie_dossier_id, partie_dossier.dossier_gen_id, partie_dossier.partie_dossier_orig_id, partie_dossier.dossier_id, partie_dossier.no_gen_dossier, partie_dossier.etat, partie_dossier.type_partie, partie_dossier.type_deces, partie_dossier.dte_deb_partie, partie_dossier.dte_effet_idx, partie_dossier.avoir_50_ans, partie_dossier.type_inva, partie_dossier.dte_deb_inca_gain, partie_dossier.age_deb_inca_gain, partie_dossier.dte_fin_inca_gain, partie_dossier.dte_reexamen, partie_dossier.tx_inca_gain_reel, partie_dossier.tx_inca_gain_calc, partie_dossier.lib_passee, partie_dossier.inva_passee, partie_dossier.rechute, partie_dossier.dte_rechute, partie_dossier.faute_grave, partie_dossier.tx_reduc_faute_grave, partie_dossier.reticence, partie_dossier.accident, partie_dossier.cause_medicale_sinistre, partie_dossier.cause_risque_special_sinistre, partie_dossier.sal_actif_reparti, partie_dossier.delai_attente_lib_consomme, partie_dossier.delai_attente_ri_consomme, partie_dossier.dte_age_terme, partie_dossier.dte_deb_mise_en_pension, partie_dossier.tx_retraite, partie_dossier.suri_avec_pe, partie_dossier.suri_avec_idx, partie_dossier.dte_revision_ai, partie_dossier.dte_revision_caisse_pension, partie_dossier.tx_sal_suri, partie_dossier.tot_rev_recu, partie_dossier.reprise_ancienne_ip, partie_dossier.deb_paie_effectif, partie_dossier.sal_presume_perdu, partie_dossier.usr_log_i, partie_dossier.dte_log_i, partie_dossier.usr_log_u, partie_dossier.dte_log_u, partie_dossier.audit, partie_dossier.dte_echeance_partie, partie_dossier.calc_av_terme, partie_dossier.mnt_cpt_droits_transitoires, partie_dossier.int_cpt_droits_transitoires, partie_dossier.somme_risquee_reass_deces, partie_dossier.somme_risquee_reass_inva, partie_dossier.annonce_reass_deces, partie_dossier.annonce_reass_inva, partie_dossier.annonce_reass_deces_facultatif, partie_dossier.annonce_reass_inva_facultatif, partie_dossier.annonce_reass_deces_sinistre, partie_dossier.annonce_reass_inva_sinistre, partie_dossier.reass_deces, partie_dossier.reass_inva, partie_dossier.inva_proportion_reassuree, partie_dossier.fo_reass_id, partie_dossier.avoir_50_ans_lpp, partie_dossier.annonce_deces, partie_dossier.annonce_invalidite
  • Index Cond: (partie_dossier.dossier_gen_id = dossier_gen.dossier_gen_id)
  • Filter: ((partie_dossier.etat)::text = 'VALI'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=97096 read=531
35. 342.160 342.160 ↑ 3.0 1 24,440

Index Scan using pi_in_prestso_fk on iliade.prest_sortie (cost=0.43..0.59 rows=3 width=26) (actual time=0.013..0.014 rows=1 loops=24,440)

  • Output: prest_sortie.prest_sortie_id, prest_sortie.partie_dossier_id, prest_sortie.dossier_id, prest_sortie.no_gen_dossier, prest_sortie.type_av_ap, prest_sortie.avoir_min_lpp, prest_sortie.avoir_reglementaire, prest_sortie.prest_propres_coti, prest_sortie.prest_sortie, prest_sortie.usr_log_i, prest_sortie.dte_log_i, prest_sortie.usr_log_u, prest_sortie.dte_log_u, prest_sortie.tx_impot_source, prest_sortie.mnt_impot_source, prest_sortie.prest_sortie_ms_impot_source, prest_sortie.avoir_plan_droits_acquis, prest_sortie.prest_sortie_gar, prest_sortie.prest_sortie_avec_rachats_nacq, prest_sortie.rachats_nacq, prest_sortie.audit, prest_sortie.mnt_max_va_logement, prest_sortie.mnt_pref_lp, prest_sortie.rachat_suppl_temporaire, prest_sortie.mnt_lpp_pref_lp
  • Index Cond: (prest_sortie.partie_dossier_id = partie_dossier.partie_dossier_id)
  • Filter: ((prest_sortie.type_av_ap)::text = 'APRE'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=95900 read=2411
36. 205.752 830.960 ↑ 1.0 1 24,440

Index Only Scan using idx_mut_dossier_no_gen_contrat_contrat_id on iliade.mut_contrat (cost=0.42..24.84 rows=1 width=4) (actual time=0.034..0.034 rows=1 loops=24,440)

  • Output: mut_contrat.contrat_id, mut_contrat.no_gen_contrat, mut_contrat.statut
  • Index Cond: (mut_contrat.contrat_id = dossier.contrat_id)
  • Filter: (mut_contrat.no_gen_contrat = (SubPlan 7))
  • Rows Removed by Filter: 12
  • Heap Fetches: 0
  • Buffers: shared hit=1324454 read=5
37.          

SubPlan (for Index Only Scan)

38. 0.000 625.208 ↑ 1.0 1 312,604

Result (cost=1.60..1.61 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=312,604)

  • Output: $6
  • Buffers: shared hit=1250413 read=3
39.          

Initplan (for Result)

40. 0.000 625.208 ↑ 1.0 1 312,604

Limit (cost=0.42..1.60 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=312,604)

  • Output: a_5.no_gen_contrat
  • Buffers: shared hit=1250413 read=3
41. 625.208 625.208 ↑ 15.0 1 312,604

Index Scan Backward using idx_mut_dossier_no_gen_contrat_contrat_id on iliade.mut_contrat a_5 (cost=0.42..18.12 rows=15 width=4) (actual time=0.002..0.002 rows=1 loops=312,604)

  • Output: a_5.no_gen_contrat
  • Index Cond: ((mut_contrat.contrat_id = a_5.contrat_id) AND (a_5.no_gen_contrat IS NOT NULL))
  • Filter: ((a_5.etat)::text = 'VALI'::text)
  • Buffers: shared hit=1250413 read=3
42. 391.040 391.040 ↑ 5.0 1 24,440

Index Scan using d_g_in_pt_d_fk on iliade.part_dossier a1_part_dossier_affi (cost=0.43..0.78 rows=5 width=8) (actual time=0.016..0.016 rows=1 loops=24,440)

  • Output: a1_part_dossier_affi.part_dossier_id, a1_part_dossier_affi.dossier_gen_id, a1_part_dossier_affi.part_dossier_part_id, a1_part_dossier_affi.dossier_id, a1_part_dossier_affi.no_gen_dossier, a1_part_dossier_affi.role, a1_part_dossier_affi.usr_log_i, a1_part_dossier_affi.dte_log_i, a1_part_dossier_affi.usr_log_u, a1_part_dossier_affi.dte_log_u, a1_part_dossier_affi.audit
  • Index Cond: (a1_part_dossier_affi.dossier_gen_id = dossier_gen.dossier_gen_id)
  • Filter: ((a1_part_dossier_affi.role)::text = 'AFFI'::text)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=95734 read=3239
43. 984.198 984.198 ↑ 1.0 348,342 1

Index Scan using adresse_formatee_numero_contact_idx on partner.adresse_formatee a1_adresses_affi (cost=0.42..21,644.51 rows=349,206 width=42) (actual time=0.477..984.198 rows=348,342 loops=1)

  • Output: a1_adresses_affi.id, a1_adresses_affi.type_adresse, a1_adresses_affi.adresse_inconnue, a1_adresses_affi.code_canton_etat, a1_adresses_affi.code_iso2_pays, a1_adresses_affi.code_postal, a1_adresses_affi.indice_numero_rue, a1_adresses_affi.langue, a1_adresses_affi.ligne_01, a1_adresses_affi.ligne_02, a1_adresses_affi.ligne_03, a1_adresses_affi.ligne_04, a1_adresses_affi.ligne_05, a1_adresses_affi.ligne_06, a1_adresses_affi.ligne_07, a1_adresses_affi.ligne_08, a1_adresses_affi.ligne_09, a1_adresses_affi.ligne_10, a1_adresses_affi.nom_commune, a1_adresses_affi.nom_district, a1_adresses_affi.nom_localite, a1_adresses_affi.npa_loc_formatee, a1_adresses_affi.numero_rue, a1_adresses_affi.rue, a1_adresses_affi.rue_formatee, a1_adresses_affi.sexe_complement_destinataire, a1_adresses_affi.pas_bella_vita, a1_adresses_affi.pas_correspondance, a1_adresses_affi.version, a1_adresses_affi.usr_log_i, a1_adresses_affi.dte_log_i, a1_adresses_affi.usr_log_u, a1_adresses_affi.dte_log_u, a1_adresses_affi.numero_contact
  • Filter: (((a1_adresses_affi.type_adresse)::text = 'BASE'::text) OR (a1_adresses_affi.type_adresse IS NULL))
  • Rows Removed by Filter: 11491
  • Buffers: shared hit=134181 read=14448
44. 110.284 110.284 ↑ 1.0 348,349 1

Index Only Scan using bi_partenaire_uk1 on biinfra.bi_partenaire a1_bi_partenaire_affi (cost=0.42..8,643.26 rows=348,929 width=4) (actual time=0.015..110.284 rows=348,349 loops=1)

  • Output: a1_bi_partenaire_affi.numero_contact
  • Heap Fetches: 0
  • Buffers: shared hit=8 read=1636
45.          

SubPlan (for Merge Join)

46. 342.160 342.160 ↑ 1.0 1 24,440

Index Scan using qsys_mut_lan_00001 on produit.mut_lan a (cost=0.28..2.50 rows=1 width=27) (actual time=0.013..0.014 rows=1 loops=24,440)

  • Output: a.nom_mut
  • Index Cond: ((a.mut_id = mut_dossier.mut_id) AND ((a.lan)::text = 'FR'::text))
  • Buffers: shared hit=73308 read=12
47. 342.160 342.160 ↑ 1.0 1 24,440

Index Scan using idx_code_texte_code_cle_code_type on produit.code_texte a_1 (cost=0.28..2.50 rows=1 width=16) (actual time=0.013..0.014 rows=1 loops=24,440)

  • Output: a_1.texte
  • Index Cond: (((mut_dossier.etat)::text = (a_1.code_cle)::text) AND ((a_1.code_type)::text = 'TypeEtatDossier'::text))
  • Filter: ((a_1.codet_langue)::text = 'FR'::text)
  • Buffers: shared hit=73318 read=2
48. 219.960 219.960 ↑ 1.0 1 24,440

Index Scan using idx_code_texte_code_cle_code_type on produit.code_texte a_2 (cost=0.28..2.50 rows=1 width=16) (actual time=0.008..0.009 rows=1 loops=24,440)

  • Output: a_2.texte
  • Index Cond: (((mut_dossier.statut)::text = (a_2.code_cle)::text) AND ((a_2.code_type)::text = 'TypeStatutDossier'::text))
  • Filter: ((a_2.codet_langue)::text = 'FR'::text)
  • Buffers: shared hit=73315 read=5
49. 171.080 171.080 ↑ 1.0 1 24,440

Index Scan using idx_code_texte_code_cle_code_type on produit.code_texte a_3 (cost=0.28..2.50 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=24,440)

  • Output: a_3.texte
  • Index Cond: (((a_3.code_cle)::text = (partie_dossier.etat)::text) AND ((a_3.code_type)::text = 'TypeEtat'::text))
  • Filter: ((a_3.codet_langue)::text = 'FR'::text)
  • Buffers: shared hit=73319 read=1
50. 146.640 146.640 ↑ 1.0 1 24,440

Index Scan using idx_code_texte_code_cle_code_type on produit.code_texte a_4 (cost=0.28..2.50 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=24,440)

  • Output: a_4.texte
  • Index Cond: (((a_4.code_cle)::text = (partie_dossier.type_partie)::text) AND ((a_4.code_type)::text = 'TypePartieDossier'::text))
  • Filter: ((a_4.codet_langue)::text = 'FR'::text)
  • Buffers: shared hit=73319 read=1
Planning time : 57.413 ms
Execution time : 2,920,614.239 ms