explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OCKI

Settings
# exclusive inclusive rows x rows loops node
1. 86.080 1,265,802.212 ↓ 38,723.0 38,723 1

GroupAggregate (cost=2,825,089.47..2,825,099.67 rows=1 width=2,497) (actual time=1,265,713.013..1,265,802.212 rows=38,723 loops=1)

  • Output: soc.soc_id, dossier.dossier_id, dossier.numero_dossier, (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.dte_naissance, (replace(((((btrim((a1_part_assure.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_part_assure.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_part_assure.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), a1_part_assure.dte_naissance, a1_part_dossier_assure.role, partie_dossier.type_partie, type_couv.desc_courte, couv.desc_courte, sum(COALESCE(pa_gen_prest.prest_due, '0'::numeric)), a1_adresses_assu.ligne_01, a1_adresses_assu.ligne_02, a1_adresses_assu.ligne_03, a1_adresses_assu.ligne_04, a1_adresses_assu.ligne_05, a1_adresses_assu.ligne_06, a1_adresses_assu.ligne_07, a1_adresses_assu.ligne_08, a1_adresses_assu.ligne_09, a1_adresses_assu.ligne_10, montant_a_payer.id, paiement.numero, paiement.numero_compte, paiement.numero_swift, paiement.numero_compte_postal, paiement.numero_iban, sum((((COALESCE(pa_gen_prest.prest_tot, '0'::numeric) + COALESCE(pa_gen_prest.idx, '0'::numeric)) + COALESCE(pa_gen_prest.idx_employeur_fine_capi, '0'::numeric)) + COALESCE(pa_gen_prest.idx_employeur_fine_perio, '0'::numeric))), sum(COALESCE(pa_gen_prest.prest_tot, '0'::numeric)), mut_dossier.statut, ((SubPlan 1)), ((SubPlan 2)), soc.desc_courte, fam_prod.fam_id, fam_prod.desc_courte, prod.pro_id, prod.desc_courte, contrat.contrat_id, contrat.numero_contrat, a1_part_employeur.nom_ou_raison_sociale, a1_part_affi.sexe, ((SubPlan 3)), a1_part_affi.etat_civil, ((SubPlan 4)), partie_dossier.dte_deb_partie
  • Group Key: soc.soc_id, dossier.dossier_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)), a1_part_affi.dte_naissance, (replace(((((btrim((a1_part_assure.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_part_assure.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_part_assure.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), a1_part_assure.dte_naissance, a1_part_dossier_assure.role, partie_dossier.type_partie, type_couv.desc_courte, couv.desc_courte, a1_adresses_assu.ligne_01, a1_adresses_assu.ligne_02, a1_adresses_assu.ligne_03, a1_adresses_assu.ligne_04, a1_adresses_assu.ligne_05, a1_adresses_assu.ligne_06, a1_adresses_assu.ligne_07, a1_adresses_assu.ligne_08, a1_adresses_assu.ligne_09, a1_adresses_assu.ligne_10, montant_a_payer.id, paiement.numero, paiement.numero_compte, paiement.numero_swift, paiement.numero_compte_postal, paiement.numero_iban, mut_dossier.statut, ((SubPlan 1)), ((SubPlan 2)), fam_prod.fam_id, prod.pro_id, contrat.contrat_id, a1_part_employeur.nom_ou_raison_sociale, a1_part_affi.sexe, ((SubPlan 3)), a1_part_affi.etat_civil, ((SubPlan 4)), partie_dossier.dte_deb_partie
  • Buffers: shared hit=92349125 read=146949
2. 939.536 1,265,716.132 ↓ 39,079.0 39,079 1

Sort (cost=2,825,089.47..2,825,089.48 rows=1 width=2,419) (actual time=1,265,712.957..1,265,716.132 rows=39,079 loops=1)

  • Output: soc.soc_id, dossier.dossier_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)), a1_part_affi.dte_naissance, (replace(((((btrim((a1_part_assure.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_part_assure.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_part_assure.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), a1_part_assure.dte_naissance, a1_part_dossier_assure.role, partie_dossier.type_partie, type_couv.desc_courte, couv.desc_courte, a1_adresses_assu.ligne_01, a1_adresses_assu.ligne_02, a1_adresses_assu.ligne_03, a1_adresses_assu.ligne_04, a1_adresses_assu.ligne_05, a1_adresses_assu.ligne_06, a1_adresses_assu.ligne_07, a1_adresses_assu.ligne_08, a1_adresses_assu.ligne_09, a1_adresses_assu.ligne_10, montant_a_payer.id, paiement.numero, paiement.numero_compte, paiement.numero_swift, paiement.numero_compte_postal, paiement.numero_iban, mut_dossier.statut, ((SubPlan 1)), ((SubPlan 2)), fam_prod.fam_id, prod.pro_id, contrat.contrat_id, a1_part_employeur.nom_ou_raison_sociale, a1_part_affi.sexe, ((SubPlan 3)), a1_part_affi.etat_civil, ((SubPlan 4)), partie_dossier.dte_deb_partie, dossier.numero_dossier, pa_gen_prest.prest_due, pa_gen_prest.prest_tot, pa_gen_prest.idx, pa_gen_prest.idx_employeur_fine_capi, pa_gen_prest.idx_employeur_fine_perio, soc.desc_courte, fam_prod.desc_courte, prod.desc_courte, contrat.numero_contrat
  • Sort Key: soc.soc_id, dossier.dossier_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)), a1_part_affi.dte_naissance, (replace(((((btrim((a1_part_assure.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_part_assure.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_part_assure.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), a1_part_assure.dte_naissance, a1_part_dossier_assure.role, partie_dossier.type_partie, type_couv.desc_courte, couv.desc_courte, a1_adresses_assu.ligne_01, a1_adresses_assu.ligne_02, a1_adresses_assu.ligne_03, a1_adresses_assu.ligne_04, a1_adresses_assu.ligne_05, a1_adresses_assu.ligne_06, a1_adresses_assu.ligne_07, a1_adresses_assu.ligne_08, a1_adresses_assu.ligne_09, a1_adresses_assu.ligne_10, montant_a_payer.id, paiement.numero, paiement.numero_compte, paiement.numero_swift, paiement.numero_compte_postal, paiement.numero_iban, mut_dossier.statut, ((SubPlan 1)), ((SubPlan 2)), fam_prod.fam_id, prod.pro_id, contrat.contrat_id, a1_part_employeur.nom_ou_raison_sociale, a1_part_affi.sexe, ((SubPlan 3)), a1_part_affi.etat_civil, ((SubPlan 4)), partie_dossier.dte_deb_partie
  • Sort Method: quicksort Memory: 21687kB
  • Buffers: shared hit=92349125 read=146949
3. 176.324 1,264,776.596 ↓ 39,079.0 39,079 1

Nested Loop (cost=25,017.23..2,825,089.46 rows=1 width=2,419) (actual time=1,244,953.668..1,264,776.596 rows=39,079 loops=1)

  • Output: soc.soc_id, dossier.dossier_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), a1_part_affi.dte_naissance, replace(((((btrim((a1_part_assure.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_part_assure.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_part_assure.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text), a1_part_assure.dte_naissance, a1_part_dossier_assure.role, partie_dossier.type_partie, type_couv.desc_courte, couv.desc_courte, a1_adresses_assu.ligne_01, a1_adresses_assu.ligne_02, a1_adresses_assu.ligne_03, a1_adresses_assu.ligne_04, a1_adresses_assu.ligne_05, a1_adresses_assu.ligne_06, a1_adresses_assu.ligne_07, a1_adresses_assu.ligne_08, a1_adresses_assu.ligne_09, a1_adresses_assu.ligne_10, montant_a_payer.id, paiement.numero, paiement.numero_compte, paiement.numero_swift, paiement.numero_compte_postal, paiement.numero_iban, mut_dossier.statut, (SubPlan 1), (SubPlan 2), fam_prod.fam_id, prod.pro_id, contrat.contrat_id, a1_part_employeur.nom_ou_raison_sociale, a1_part_affi.sexe, (SubPlan 3), a1_part_affi.etat_civil, (SubPlan 4), partie_dossier.dte_deb_partie, dossier.numero_dossier, pa_gen_prest.prest_due, pa_gen_prest.prest_tot, pa_gen_prest.idx, pa_gen_prest.idx_employeur_fine_capi, pa_gen_prest.idx_employeur_fine_perio, soc.desc_courte, fam_prod.desc_courte, prod.desc_courte, contrat.numero_contrat
  • Inner Unique: true
  • Buffers: shared hit=92349116 read=146949
4. 7.414 1,264,014.087 ↓ 39,079.0 39,079 1

Nested Loop (cost=25,016.81..2,825,077.24 rows=1 width=322) (actual time=1,244,952.796..1,264,014.087 rows=39,079 loops=1)

  • Output: montant_a_payer.id, paiement.numero, paiement.numero_compte, paiement.numero_swift, paiement.numero_compte_postal, paiement.numero_iban, dossier.dossier_id, dossier.numero_dossier, mut_dossier.statut, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.dte_naissance, a1_part_affi.sexe, a1_part_affi.etat_civil, partie_dossier.type_partie, partie_dossier.dte_deb_partie, couv.desc_courte, type_couv.desc_courte, pa_gen_prest.prest_due, pa_gen_prest.prest_tot, pa_gen_prest.idx, pa_gen_prest.idx_employeur_fine_capi, pa_gen_prest.idx_employeur_fine_perio, a1_part_dossier_assure.role, a1_part_assure.nom_ou_raison_sociale, a1_part_assure.prenom, a1_part_assure.nom_complementaire, a1_part_assure.dte_naissance, a1_adresses_assu.ligne_01, a1_adresses_assu.ligne_02, a1_adresses_assu.ligne_03, a1_adresses_assu.ligne_04, a1_adresses_assu.ligne_05, a1_adresses_assu.ligne_06, a1_adresses_assu.ligne_07, a1_adresses_assu.ligne_08, a1_adresses_assu.ligne_09, a1_adresses_assu.ligne_10, contrat.contrat_id, contrat.numero_contrat, prod.pro_id, prod.desc_courte, fam_prod.fam_id, fam_prod.desc_courte, soc.soc_id, soc.desc_courte, a1_part_contrat_employeur.part_id
  • Buffers: shared hit=91728145 read=146946
5. 1,024,413.121 1,263,889.436 ↓ 39,079.0 39,079 1

Nested Loop (cost=25,016.39..2,825,074.98 rows=1 width=322) (actual time=1,244,951.066..1,263,889.436 rows=39,079 loops=1)

  • Output: montant_a_payer.id, paiement.numero, paiement.numero_compte, paiement.numero_swift, paiement.numero_compte_postal, paiement.numero_iban, dossier.dossier_id, dossier.numero_dossier, mut_dossier.statut, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.dte_naissance, a1_part_affi.sexe, a1_part_affi.etat_civil, partie_dossier.type_partie, partie_dossier.dte_deb_partie, couv.desc_courte, type_couv.desc_courte, pa_gen_prest.prest_due, pa_gen_prest.prest_tot, pa_gen_prest.idx, pa_gen_prest.idx_employeur_fine_capi, pa_gen_prest.idx_employeur_fine_perio, a1_part_dossier_assure.role, a1_part_assure.nom_ou_raison_sociale, a1_part_assure.prenom, a1_part_assure.nom_complementaire, a1_part_assure.dte_naissance, a1_adresses_assu.ligne_01, a1_adresses_assu.ligne_02, a1_adresses_assu.ligne_03, a1_adresses_assu.ligne_04, a1_adresses_assu.ligne_05, a1_adresses_assu.ligne_06, a1_adresses_assu.ligne_07, a1_adresses_assu.ligne_08, a1_adresses_assu.ligne_09, a1_adresses_assu.ligne_10, contrat.contrat_id, contrat.numero_contrat, prod.pro_id, prod.desc_courte, fam_prod.fam_id, fam_prod.desc_courte, soc.soc_id, soc.desc_courte, contrat_gen.contrat_gen_id
  • Join Filter: (mut_contrat.mut_contrat_id = contrat_gen.mut_contrat_id)
  • Rows Removed by Join Filter: 3078917173
  • Buffers: shared hit=91571831 read=146944
6. 354.735 354.735 ↑ 1.0 78,788 1

Index Scan using p_contrat_gen on iliade.contrat_gen (cost=0.29..2,974.06 rows=78,788 width=8) (actual time=0.957..354.735 rows=78,788 loops=1)

  • Output: contrat_gen.contrat_gen_id, contrat_gen.contrat_pilote_fonds_gar_id, contrat_gen.mut_contrat_id, contrat_gen.nouvelle_ip_part_id, contrat_gen.contrat_pilote_regrp_gest_id, contrat_gen.secteur_act_predominant_id, contrat_gen.contrat_id, contrat_gen.no_gen_contrat, contrat_gen.type_rattr, contrat_gen.gest_reservee, contrat_gen.code_voir_dossier, contrat_gen.confidentiel, contrat_gen.effectif_pour_rab, contrat_gen.nombre_affilies_reel, contrat_gen.appliquer_tx_validite, contrat_gen.appliquer_tx_act, contrat_gen.bonif_escomptee, contrat_gen.fonds_gar, contrat_gen.pilote_regrpmnt_gest, contrat_gen.age_terme_vise, contrat_gen.regrpmnt_contrat_fonds_gar, contrat_gen.statut_cmt_pari, contrat_gen.dte_prochaine_maj_effectif, contrat_gen.numero_avenant, contrat_gen.dte_entree_vigueur_avenant, contrat_gen.type_avenant, contrat_gen.suivi_contentieux, contrat_gen.contrat_resilie_cours_annee, contrat_gen.correspondance_employeur_seul, contrat_gen.envoi_liste_annuelle_salaires, contrat_gen.envoi_extrait_rapport_annuel, contrat_gen.envoi_situation_prev, contrat_gen.envoi_courrier_va_logement, contrat_gen.dte_ctrl_cmt_pari, contrat_gen.nombre_repr_employeur_requis, contrat_gen.nombre_repr_empl_requis, contrat_gen.grand_contrat, contrat_gen.tx_coti_laa, contrat_gen.usr_log_i, contrat_gen.dte_log_i, contrat_gen.usr_log_u, contrat_gen.dte_log_u, contrat_gen.idx_particuliere, contrat_gen.fact_rattr, contrat_gen.audit, contrat_gen.envoi_bordereau_fact, contrat_gen.dte_echeance, contrat_gen.choix_plan_complementaire, contrat_gen.declaration_fiscale, contrat_gen.soumis_cct, contrat_gen.groupement_cct, contrat_gen.particularites_contrat, contrat_gen.impression_documents_facturation, contrat_gen.impot_source
  • Buffers: shared hit=2599 read=1761
7. 152,876.629 239,121.580 ↓ 39,079.0 39,079 78,788

Materialize (cost=25,016.10..2,820,919.11 rows=1 width=322) (actual time=0.506..3.035 rows=39,079 loops=78,788)

  • Output: montant_a_payer.id, paiement.numero, paiement.numero_compte, paiement.numero_swift, paiement.numero_compte_postal, paiement.numero_iban, dossier.dossier_id, dossier.numero_dossier, mut_dossier.statut, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.dte_naissance, a1_part_affi.sexe, a1_part_affi.etat_civil, partie_dossier.type_partie, partie_dossier.dte_deb_partie, couv.desc_courte, type_couv.desc_courte, pa_gen_prest.prest_due, pa_gen_prest.prest_tot, pa_gen_prest.idx, pa_gen_prest.idx_employeur_fine_capi, pa_gen_prest.idx_employeur_fine_perio, a1_part_dossier_assure.role, a1_part_assure.nom_ou_raison_sociale, a1_part_assure.prenom, a1_part_assure.nom_complementaire, a1_part_assure.dte_naissance, a1_adresses_assu.ligne_01, a1_adresses_assu.ligne_02, a1_adresses_assu.ligne_03, a1_adresses_assu.ligne_04, a1_adresses_assu.ligne_05, a1_adresses_assu.ligne_06, a1_adresses_assu.ligne_07, a1_adresses_assu.ligne_08, a1_adresses_assu.ligne_09, a1_adresses_assu.ligne_10, contrat.contrat_id, contrat.numero_contrat, prod.pro_id, prod.desc_courte, fam_prod.fam_id, fam_prod.desc_courte, soc.soc_id, soc.desc_courte, mut_contrat.mut_contrat_id
  • Buffers: shared hit=91569232 read=145183
8. 21.117 86,244.951 ↓ 39,079.0 39,079 1

Nested Loop (cost=25,016.10..2,820,919.10 rows=1 width=322) (actual time=39,866.163..86,244.951 rows=39,079 loops=1)

  • Output: montant_a_payer.id, paiement.numero, paiement.numero_compte, paiement.numero_swift, paiement.numero_compte_postal, paiement.numero_iban, dossier.dossier_id, dossier.numero_dossier, mut_dossier.statut, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.dte_naissance, a1_part_affi.sexe, a1_part_affi.etat_civil, partie_dossier.type_partie, partie_dossier.dte_deb_partie, couv.desc_courte, type_couv.desc_courte, pa_gen_prest.prest_due, pa_gen_prest.prest_tot, pa_gen_prest.idx, pa_gen_prest.idx_employeur_fine_capi, pa_gen_prest.idx_employeur_fine_perio, a1_part_dossier_assure.role, a1_part_assure.nom_ou_raison_sociale, a1_part_assure.prenom, a1_part_assure.nom_complementaire, a1_part_assure.dte_naissance, a1_adresses_assu.ligne_01, a1_adresses_assu.ligne_02, a1_adresses_assu.ligne_03, a1_adresses_assu.ligne_04, a1_adresses_assu.ligne_05, a1_adresses_assu.ligne_06, a1_adresses_assu.ligne_07, a1_adresses_assu.ligne_08, a1_adresses_assu.ligne_09, a1_adresses_assu.ligne_10, contrat.contrat_id, contrat.numero_contrat, prod.pro_id, prod.desc_courte, fam_prod.fam_id, fam_prod.desc_courte, soc.soc_id, soc.desc_courte, mut_contrat.mut_contrat_id
  • Join Filter: (contrat.contrat_id = mut_contrat.contrat_id)
  • Buffers: shared hit=91569232 read=145183
9. 60.460 79,424.088 ↓ 39,079.0 39,079 1

Nested Loop (cost=25,015.68..2,820,879.93 rows=1 width=322) (actual time=39,848.810..79,424.088 rows=39,079 loops=1)

  • Output: montant_a_payer.id, paiement.numero, paiement.numero_compte, paiement.numero_swift, paiement.numero_compte_postal, paiement.numero_iban, dossier.dossier_id, dossier.numero_dossier, dossier.contrat_id, mut_dossier.statut, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.dte_naissance, a1_part_affi.sexe, a1_part_affi.etat_civil, partie_dossier.type_partie, partie_dossier.dte_deb_partie, couv.desc_courte, type_couv.desc_courte, pa_gen_prest.prest_due, pa_gen_prest.prest_tot, pa_gen_prest.idx, pa_gen_prest.idx_employeur_fine_capi, pa_gen_prest.idx_employeur_fine_perio, a1_part_dossier_assure.role, a1_part_assure.nom_ou_raison_sociale, a1_part_assure.prenom, a1_part_assure.nom_complementaire, a1_part_assure.dte_naissance, a1_adresses_assu.ligne_01, a1_adresses_assu.ligne_02, a1_adresses_assu.ligne_03, a1_adresses_assu.ligne_04, a1_adresses_assu.ligne_05, a1_adresses_assu.ligne_06, a1_adresses_assu.ligne_07, a1_adresses_assu.ligne_08, a1_adresses_assu.ligne_09, a1_adresses_assu.ligne_10, contrat.contrat_id, contrat.numero_contrat, prod.pro_id, prod.desc_courte, fam_prod.fam_id, fam_prod.desc_courte, soc.soc_id, soc.desc_courte
  • Inner Unique: true
  • Join Filter: (fam_prod.soc_id = soc.soc_id)
  • Rows Removed by Join Filter: 195395
  • Buffers: shared hit=82112150 read=145147
10. 79.414 79,324.549 ↓ 39,079.0 39,079 1

Nested Loop (cost=25,015.68..2,820,878.77 rows=1 width=317) (actual time=39,848.797..79,324.549 rows=39,079 loops=1)

  • Output: montant_a_payer.id, paiement.numero, paiement.numero_compte, paiement.numero_swift, paiement.numero_compte_postal, paiement.numero_iban, dossier.dossier_id, dossier.numero_dossier, dossier.contrat_id, mut_dossier.statut, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.dte_naissance, a1_part_affi.sexe, a1_part_affi.etat_civil, partie_dossier.type_partie, partie_dossier.dte_deb_partie, couv.desc_courte, type_couv.desc_courte, pa_gen_prest.prest_due, pa_gen_prest.prest_tot, pa_gen_prest.idx, pa_gen_prest.idx_employeur_fine_capi, pa_gen_prest.idx_employeur_fine_perio, a1_part_dossier_assure.role, a1_part_assure.nom_ou_raison_sociale, a1_part_assure.prenom, a1_part_assure.nom_complementaire, a1_part_assure.dte_naissance, a1_adresses_assu.ligne_01, a1_adresses_assu.ligne_02, a1_adresses_assu.ligne_03, a1_adresses_assu.ligne_04, a1_adresses_assu.ligne_05, a1_adresses_assu.ligne_06, a1_adresses_assu.ligne_07, a1_adresses_assu.ligne_08, a1_adresses_assu.ligne_09, a1_adresses_assu.ligne_10, contrat.contrat_id, contrat.numero_contrat, prod.pro_id, prod.desc_courte, fam_prod.fam_id, fam_prod.desc_courte, fam_prod.soc_id
  • Inner Unique: true
  • Join Filter: (prod.fam_id = fam_prod.fam_id)
  • Rows Removed by Join Filter: 156316
  • Buffers: shared hit=82073071 read=145147
11. 21.439 79,127.898 ↓ 39,079.0 39,079 1

Nested Loop (cost=25,015.68..2,820,877.06 rows=1 width=304) (actual time=39,848.771..79,127.898 rows=39,079 loops=1)

  • Output: montant_a_payer.id, paiement.numero, paiement.numero_compte, paiement.numero_swift, paiement.numero_compte_postal, paiement.numero_iban, dossier.dossier_id, dossier.numero_dossier, dossier.contrat_id, mut_dossier.statut, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.dte_naissance, a1_part_affi.sexe, a1_part_affi.etat_civil, partie_dossier.type_partie, partie_dossier.dte_deb_partie, couv.desc_courte, type_couv.desc_courte, pa_gen_prest.prest_due, pa_gen_prest.prest_tot, pa_gen_prest.idx, pa_gen_prest.idx_employeur_fine_capi, pa_gen_prest.idx_employeur_fine_perio, a1_part_dossier_assure.role, a1_part_assure.nom_ou_raison_sociale, a1_part_assure.prenom, a1_part_assure.nom_complementaire, a1_part_assure.dte_naissance, a1_adresses_assu.ligne_01, a1_adresses_assu.ligne_02, a1_adresses_assu.ligne_03, a1_adresses_assu.ligne_04, a1_adresses_assu.ligne_05, a1_adresses_assu.ligne_06, a1_adresses_assu.ligne_07, a1_adresses_assu.ligne_08, a1_adresses_assu.ligne_09, a1_adresses_assu.ligne_10, contrat.contrat_id, contrat.numero_contrat, prod.pro_id, prod.desc_courte, prod.fam_id
  • Inner Unique: true
  • Buffers: shared hit=82033992 read=145147
12. 22.145 79,067.380 ↓ 39,079.0 39,079 1

Nested Loop (cost=25,015.54..2,820,874.67 rows=1 width=279) (actual time=39,848.749..79,067.380 rows=39,079 loops=1)

  • Output: montant_a_payer.id, paiement.numero, paiement.numero_compte, paiement.numero_swift, paiement.numero_compte_postal, paiement.numero_iban, dossier.dossier_id, dossier.numero_dossier, dossier.contrat_id, mut_dossier.statut, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.dte_naissance, a1_part_affi.sexe, a1_part_affi.etat_civil, partie_dossier.type_partie, partie_dossier.dte_deb_partie, couv.desc_courte, type_couv.desc_courte, pa_gen_prest.prest_due, pa_gen_prest.prest_tot, pa_gen_prest.idx, pa_gen_prest.idx_employeur_fine_capi, pa_gen_prest.idx_employeur_fine_perio, a1_part_dossier_assure.role, a1_part_assure.nom_ou_raison_sociale, a1_part_assure.prenom, a1_part_assure.nom_complementaire, a1_part_assure.dte_naissance, a1_adresses_assu.ligne_01, a1_adresses_assu.ligne_02, a1_adresses_assu.ligne_03, a1_adresses_assu.ligne_04, a1_adresses_assu.ligne_05, a1_adresses_assu.ligne_06, a1_adresses_assu.ligne_07, a1_adresses_assu.ligne_08, a1_adresses_assu.ligne_09, a1_adresses_assu.ligne_10, contrat.contrat_id, contrat.numero_contrat, contrat.pro_id
  • Buffers: shared hit=81955834 read=145147
13. 14.118 78,849.840 ↓ 39,079.0 39,079 1

Nested Loop (cost=25,015.11..2,820,869.83 rows=1 width=214) (actual time=39,848.688..78,849.840 rows=39,079 loops=1)

  • Output: montant_a_payer.id, paiement.numero, paiement.numero_compte, paiement.numero_swift, paiement.numero_compte_postal, paiement.numero_iban, dossier.dossier_id, dossier.numero_dossier, dossier.contrat_id, mut_dossier.statut, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.dte_naissance, a1_part_affi.sexe, a1_part_affi.etat_civil, partie_dossier.type_partie, partie_dossier.dte_deb_partie, couv.desc_courte, type_couv.desc_courte, pa_gen_prest.prest_due, pa_gen_prest.prest_tot, pa_gen_prest.idx, pa_gen_prest.idx_employeur_fine_capi, pa_gen_prest.idx_employeur_fine_perio, a1_part_dossier_assure.role, a1_part_assure.nom_ou_raison_sociale, a1_part_assure.prenom, a1_part_assure.nom_complementaire, a1_part_assure.dte_naissance, a1_part_assure.numero_contact, contrat.contrat_id, contrat.numero_contrat, contrat.pro_id
  • Inner Unique: true
  • Buffers: shared hit=81638720 read=145133
14. 35.468 78,757.564 ↓ 39,079.0 39,079 1

Nested Loop (cost=25,014.69..2,820,867.67 rows=1 width=192) (actual time=39,848.671..78,757.564 rows=39,079 loops=1)

  • Output: montant_a_payer.id, paiement.numero, paiement.numero_compte, paiement.numero_swift, paiement.numero_compte_postal, paiement.numero_iban, dossier.dossier_id, dossier.numero_dossier, dossier.contrat_id, mut_dossier.statut, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.dte_naissance, a1_part_affi.sexe, a1_part_affi.etat_civil, partie_dossier.type_partie, partie_dossier.dte_deb_partie, couv.desc_courte, type_couv.desc_courte, pa_gen_prest.prest_due, pa_gen_prest.prest_tot, pa_gen_prest.idx, pa_gen_prest.idx_employeur_fine_capi, pa_gen_prest.idx_employeur_fine_perio, a1_part_dossier_assure.role, a1_part_dossier_assure.part_dossier_part_id, contrat.contrat_id, contrat.numero_contrat, contrat.pro_id
  • Inner Unique: true
  • Buffers: shared hit=81482411 read=145126
15. 614.661 78,643.938 ↓ 39,079.0 39,079 1

Nested Loop (cost=25,014.26..2,820,865.49 rows=1 width=187) (actual time=39,846.727..78,643.938 rows=39,079 loops=1)

  • Output: montant_a_payer.id, paiement.numero, paiement.numero_compte, paiement.numero_swift, paiement.numero_compte_postal, paiement.numero_iban, dossier.dossier_id, dossier.numero_dossier, dossier.contrat_id, mut_dossier.statut, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.dte_naissance, a1_part_affi.sexe, a1_part_affi.etat_civil, partie_dossier.type_partie, partie_dossier.dte_deb_partie, pa_gen.assure_part_dossier_id, couv.desc_courte, type_couv.desc_courte, pa_gen_prest.prest_due, pa_gen_prest.prest_tot, pa_gen_prest.idx, pa_gen_prest.idx_employeur_fine_capi, pa_gen_prest.idx_employeur_fine_perio, contrat.contrat_id, contrat.numero_contrat, contrat.pro_id
  • Join Filter: (dossier.contrat_id = contrat.contrat_id)
  • Rows Removed by Join Filter: 7192088
  • Buffers: shared hit=81325985 read=145092
16. 4.850 4.850 ↑ 1.0 1 1

Index Scan using p_contrat on iliade.contrat (cost=0.28..2,309.47 rows=1 width=12) (actual time=3.695..4.850 rows=1 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
  • Filter: (contrat.numero_contrat = 31002)
  • Rows Removed by Filter: 5484
  • Buffers: shared hit=2716 read=13
17. 6,921.158 78,024.427 ↓ 1,518.2 7,231,167 1

Nested Loop (cost=25,013.97..2,818,496.48 rows=4,763 width=175) (actual time=1,802.219..78,024.427 rows=7,231,167 loops=1)

  • Output: montant_a_payer.id, paiement.numero, paiement.numero_compte, paiement.numero_swift, paiement.numero_compte_postal, paiement.numero_iban, dossier.dossier_id, dossier.numero_dossier, dossier.contrat_id, mut_dossier.statut, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.dte_naissance, a1_part_affi.sexe, a1_part_affi.etat_civil, partie_dossier.type_partie, partie_dossier.dte_deb_partie, pa_gen.assure_part_dossier_id, couv.desc_courte, type_couv.desc_courte, pa_gen_prest.prest_due, pa_gen_prest.prest_tot, pa_gen_prest.idx, pa_gen_prest.idx_employeur_fine_capi, pa_gen_prest.idx_employeur_fine_perio
  • Buffers: shared hit=81323269 read=145079
18. 2,289.112 56,640.935 ↓ 1,518.2 7,231,167 1

Hash Join (cost=25,013.41..2,807,324.95 rows=4,763 width=161) (actual time=1,800.739..56,640.935 rows=7,231,167 loops=1)

  • Output: montant_a_payer.id, paiement.numero, paiement.numero_compte, paiement.numero_swift, paiement.numero_compte_postal, paiement.numero_iban, dossier.dossier_id, dossier.numero_dossier, dossier.contrat_id, mut_dossier.statut, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.dte_naissance, a1_part_affi.sexe, a1_part_affi.etat_civil, partie_dossier.type_partie, partie_dossier.dte_deb_partie, pa_gen.pa_gen_id, pa_gen.assure_part_dossier_id, couv.desc_courte, type_couv.desc_courte
  • Inner Unique: true
  • Hash Cond: ((couv.type_nat_type_couv)::text = (type_couv.type_nat_type_couv)::text)
  • Buffers: shared hit=45151621 read=119423
19. 2,205.164 54,351.181 ↓ 1,518.2 7,231,167 1

Hash Join (cost=25,010.97..2,807,309.19 rows=4,763 width=146) (actual time=1,800.066..54,351.181 rows=7,231,167 loops=1)

  • Output: montant_a_payer.id, paiement.numero, paiement.numero_compte, paiement.numero_swift, paiement.numero_compte_postal, paiement.numero_iban, dossier.dossier_id, dossier.numero_dossier, dossier.contrat_id, mut_dossier.statut, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.dte_naissance, a1_part_affi.sexe, a1_part_affi.etat_civil, partie_dossier.type_partie, partie_dossier.dte_deb_partie, pa_gen.pa_gen_id, pa_gen.assure_part_dossier_id, couv.desc_courte, couv.type_nat_type_couv
  • Inner Unique: true
  • Hash Cond: (vcouv.cou_id = couv.cou_id)
  • Buffers: shared hit=45151621 read=119422
20. 2,261.690 52,144.440 ↓ 1,518.2 7,231,167 1

Hash Join (cost=24,976.26..2,807,261.91 rows=4,763 width=122) (actual time=1,798.453..52,144.440 rows=7,231,167 loops=1)

  • Output: montant_a_payer.id, paiement.numero, paiement.numero_compte, paiement.numero_swift, paiement.numero_compte_postal, paiement.numero_iban, dossier.dossier_id, dossier.numero_dossier, dossier.contrat_id, mut_dossier.statut, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.dte_naissance, a1_part_affi.sexe, a1_part_affi.etat_civil, partie_dossier.type_partie, partie_dossier.dte_deb_partie, pa_gen.pa_gen_id, pa_gen.assure_part_dossier_id, vcouv.cou_id
  • Inner Unique: true
  • Hash Cond: (pa_gen.vco_id = vcouv.vco_id)
  • Buffers: shared hit=45151621 read=119405
21. 697.037 49,875.997 ↓ 1,518.2 7,231,167 1

Nested Loop (cost=24,778.84..2,807,051.98 rows=4,763 width=122) (actual time=1,791.656..49,875.997 rows=7,231,167 loops=1)

  • Output: montant_a_payer.id, paiement.numero, paiement.numero_compte, paiement.numero_swift, paiement.numero_compte_postal, paiement.numero_iban, dossier.dossier_id, dossier.numero_dossier, dossier.contrat_id, mut_dossier.statut, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.dte_naissance, a1_part_affi.sexe, a1_part_affi.etat_civil, partie_dossier.type_partie, partie_dossier.dte_deb_partie, pa_gen.vco_id, pa_gen.pa_gen_id, pa_gen.assure_part_dossier_id
  • Buffers: shared hit=45151621 read=119299
22. 1,978.174 29,433.722 ↓ 1,038.5 3,290,873 1

Nested Loop (cost=24,778.27..1,702,179.62 rows=3,169 width=114) (actual time=1,789.933..29,433.722 rows=3,290,873 loops=1)

  • Output: montant_a_payer.id, paiement.numero, paiement.numero_compte, paiement.numero_swift, paiement.numero_compte_postal, paiement.numero_iban, dossier.dossier_id, dossier.numero_dossier, dossier.contrat_id, mut_dossier.statut, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.dte_naissance, a1_part_affi.sexe, a1_part_affi.etat_civil, partie_dossier.type_partie, partie_dossier.dte_deb_partie, plan_partie.plan_partie_id
  • Buffers: shared hit=28007373 read=93982
23. 1,426.919 20,818.260 ↓ 917.0 3,318,644 1

Nested Loop (cost=24,777.84..1,633,907.43 rows=3,619 width=114) (actual time=1,789.903..20,818.260 rows=3,318,644 loops=1)

  • Output: montant_a_payer.id, paiement.numero, paiement.numero_compte, paiement.numero_swift, paiement.numero_compte_postal, paiement.numero_iban, dossier.dossier_id, dossier.numero_dossier, dossier.contrat_id, mut_dossier.statut, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.dte_naissance, a1_part_affi.sexe, a1_part_affi.etat_civil, partie_dossier.type_partie, partie_dossier.dte_deb_partie, partie_dossier.partie_dossier_id
  • Buffers: shared hit=14368812 read=93982
24. 1,042.354 14,130.511 ↓ 124.5 2,630,415 1

Hash Left Join (cost=24,777.41..1,570,278.28 rows=21,123 width=109) (actual time=1,789.866..14,130.511 rows=2,630,415 loops=1)

  • Output: montant_a_payer.id, paiement.numero, paiement.numero_compte, paiement.numero_swift, paiement.numero_compte_postal, paiement.numero_iban, dossier.dossier_id, dossier.numero_dossier, dossier.contrat_id, mut_dossier.statut, dossier_gen.dossier_gen_id, a1_part_dossier_affi.dossier_gen_id, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.dte_naissance, a1_part_affi.sexe, a1_part_affi.etat_civil
  • Hash Cond: (montant_a_payer.adresse_paiement_id = paiement.numero)
  • Join Filter: ((montant_a_payer.adresse_paiement_generation_id = paiement.generation) OR ((montant_a_payer.adresse_paiement_generation_id IS NULL) AND ((paiement.statut)::text = ANY ('{ACTIF,CONTROLER,RADIE}'::text[]))))
  • Rows Removed by Join Filter: 215054
  • Buffers: shared hit=3775104 read=93982
25. 700.788 13,013.659 ↓ 124.5 2,630,415 1

Nested Loop (cost=18,996.89..1,563,512.42 rows=21,123 width=65) (actual time=1,714.697..13,013.659 rows=2,630,415 loops=1)

  • Output: montant_a_payer.id, montant_a_payer.adresse_paiement_id, montant_a_payer.adresse_paiement_generation_id, dossier.dossier_id, dossier.numero_dossier, dossier.contrat_id, mut_dossier.statut, dossier_gen.dossier_gen_id, a1_part_dossier_affi.dossier_gen_id, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.dte_naissance, a1_part_affi.sexe, a1_part_affi.etat_civil
  • Buffers: shared hit=3771801 read=93982
26. 19.434 7,760.071 ↓ 38.0 30,352 1

Nested Loop (cost=18,996.46..1,498,384.34 rows=799 width=57) (actual time=1,713.756..7,760.071 rows=30,352 loops=1)

  • Output: objet_payable.id, dossier.dossier_id, dossier.numero_dossier, dossier.contrat_id, mut_dossier.statut, dossier_gen.dossier_gen_id, a1_part_dossier_affi.dossier_gen_id, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.dte_naissance, a1_part_affi.sexe, a1_part_affi.etat_civil
  • Inner Unique: true
  • Buffers: shared hit=3008007 read=60082
27. 13.333 7,163.949 ↓ 38.0 30,352 1

Nested Loop (cost=18,996.03..1,496,656.53 rows=799 width=33) (actual time=1,713.089..7,163.949 rows=30,352 loops=1)

  • Output: objet_payable.id, dossier.dossier_id, dossier.numero_dossier, dossier.contrat_id, mut_dossier.statut, dossier_gen.dossier_gen_id, a1_part_dossier_affi.dossier_gen_id, a1_part_dossier_affi.part_dossier_part_id
  • Buffers: shared hit=2891602 read=54989
28. 29.591 6,604.280 ↓ 37.9 30,352 1

Nested Loop (cost=18,995.60..1,486,301.42 rows=800 width=25) (actual time=1,711.555..6,604.280 rows=30,352 loops=1)

  • Output: objet_payable.id, dossier.dossier_id, dossier.numero_dossier, dossier.contrat_id, mut_dossier.statut, dossier_gen.dossier_gen_id
  • Buffers: shared hit=2776703 read=47272
29. 16.024 6,453.281 ↓ 37.8 30,352 1

Nested Loop (cost=18,995.17..1,484,268.25 rows=804 width=25) (actual time=1,711.524..6,453.281 rows=30,352 loops=1)

  • Output: objet_payable.id, dossier.dossier_id, dossier.numero_dossier, dossier.contrat_id, mut_dossier.statut, mut_dossier.mut_dossier_id
  • Inner Unique: true
  • Buffers: shared hit=2655133 read=47272
30. 35.610 6,285.497 ↓ 18.7 30,352 1

Hash Join (cost=18,994.75..1,480,755.44 rows=1,625 width=21) (actual time=1,711.476..6,285.497 rows=30,352 loops=1)

  • Output: objet_payable.id, objet_payable.metier_id, mut_dossier.statut, mut_dossier.dossier_id, mut_dossier.mut_dossier_id
  • Hash Cond: (mut_dossier.dossier_id = objet_payable.metier_id)
  • Buffers: shared hit=2533641 read=47272
31. 1,505.228 6,178.811 ↓ 9.8 30,634 1

Bitmap Heap Scan on iliade.mut_dossier (cost=13,295.77..1,475,028.55 rows=3,111 width=13) (actual time=1,639.993..6,178.811 rows=30,634 loops=1)

  • 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
  • Recheck Cond: ((mut_dossier.statut)::text = ANY ('{PENS,MIXT}'::text[]))
  • Filter: (mut_dossier.no_gen_dossier = (SubPlan 6))
  • Rows Removed by Filter: 579118
  • Heap Blocks: exact=73143
  • Buffers: shared hit=2530932 read=47272
32. 1,624.823 1,624.823 ↑ 1.0 609,752 1

Bitmap Index Scan on idx_mut_dossier_statut_no_gen_dossier (cost=0.00..13,294.99 rows=622,211 width=0) (actual time=1,624.823..1,624.823 rows=609,752 loops=1)

  • Index Cond: ((mut_dossier.statut)::text = ANY ('{PENS,MIXT}'::text[]))
  • Buffers: shared hit=5 read=3950
33.          

SubPlan (for Bitmap Heap Scan)

34. 0.000 3,048.760 ↑ 1.0 1 609,752

Result (cost=1.84..1.85 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=609,752)

  • Output: $5
  • Buffers: shared hit=2501106
35.          

Initplan (for Result)

36. 609.752 3,048.760 ↑ 1.0 1 609,752

Limit (cost=0.43..1.84 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=609,752)

  • Output: a_4.no_gen_dossier
  • Buffers: shared hit=2501106
37. 2,439.008 2,439.008 ↑ 30.0 1 609,752

Index Scan Backward using idx_mut_dossier_dossier_id_no_gen_dossier on iliade.mut_dossier a_4 (cost=0.43..42.68 rows=30 width=4) (actual time=0.004..0.004 rows=1 loops=609,752)

  • Output: a_4.no_gen_dossier
  • Index Cond: ((mut_dossier.dossier_id = a_4.dossier_id) AND (a_4.no_gen_dossier IS NOT NULL))
  • Filter: ((a_4.etat)::text = 'VALI'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2501106
38. 20.010 71.076 ↓ 1.5 96,995 1

Hash (cost=4,887.69..4,887.69 rows=64,903 width=8) (actual time=71.075..71.076 rows=96,995 loops=1)

  • Output: objet_payable.id, objet_payable.metier_id
  • Buckets: 131072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 4813kB
  • Buffers: shared hit=2709
39. 51.066 51.066 ↓ 1.5 96,995 1

Seq Scan on hermes.objet_payable (cost=0.00..4,887.69 rows=64,903 width=8) (actual time=0.019..51.066 rows=96,995 loops=1)

  • Output: objet_payable.id, objet_payable.metier_id
  • Filter: (((objet_payable.application)::text = 'PLR2'::text) AND ((objet_payable.type_objet_payable)::text = 'DOSS'::text))
  • Rows Removed by Filter: 48251
  • Buffers: shared hit=2709
40. 151.760 151.760 ↑ 1.0 1 30,352

Index Scan using p_dossier on iliade.dossier (cost=0.42..2.16 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=30,352)

  • 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.dossier_id = objet_payable.metier_id)
  • Buffers: shared hit=121492
41. 121.408 121.408 ↑ 1.0 1 30,352

Index Scan using mu_d_in_d_g_fk on iliade.dossier_gen (cost=0.43..2.52 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=30,352)

  • 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=121570
42. 546.336 546.336 ↑ 5.0 1 30,352

Index Scan using d_g_in_pt_d_fk on iliade.part_dossier a1_part_dossier_affi (cost=0.43..12.89 rows=5 width=8) (actual time=0.016..0.018 rows=1 loops=30,352)

  • 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: 1
  • Buffers: shared hit=114899 read=7717
43. 576.688 576.688 ↑ 1.0 1 30,352

Index Scan using p_part on iliade.part a1_part_affi (cost=0.42..2.16 rows=1 width=32) (actual time=0.019..0.019 rows=1 loops=30,352)

  • 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
  • Index Cond: (a1_part_affi.part_id = a1_part_dossier_affi.part_dossier_part_id)
  • Buffers: shared hit=116405 read=5093
44. 4,552.800 4,552.800 ↑ 1.1 87 30,352

Index Scan using idx4fk_f_objpay_in_mntap on hermes.montant_a_payer (cost=0.43..80.54 rows=97 width=16) (actual time=0.022..0.150 rows=87 loops=30,352)

  • Output: montant_a_payer.id, montant_a_payer.prestation_a_payer_id, montant_a_payer.informations_paiement_interne_id, montant_a_payer.interface_montant_a_payer_id, montant_a_payer.facture_id, montant_a_payer.groupe_factures_id, montant_a_payer.objet_payable_id, montant_a_payer.periode_gestion_id, montant_a_payer.paiement_id, montant_a_payer.beneficiaire_id, montant_a_payer.adresse_paiement_id, montant_a_payer.adresse_paiement_generation_id, montant_a_payer.devise, montant_a_payer.nature, montant_a_payer.type_paiement_particulier, montant_a_payer.montant, montant_a_payer.date_echeance_paiement, montant_a_payer.date_prevue_paiement, montant_a_payer.date_effective_paiement, montant_a_payer.communication_1, montant_a_payer.communication_2, montant_a_payer.communication_3, montant_a_payer.communication_4, montant_a_payer.mode_paiement_interne, montant_a_payer.ligne_codage_bvr, montant_a_payer.numero_reference_bvr, montant_a_payer.numero_adherent_bvr, montant_a_payer.reference_facture, montant_a_payer.secteur, montant_a_payer.deja_paye_hors_paiement, montant_a_payer.deja_comptabilise_hors_paiement, montant_a_payer.etat, montant_a_payer.type_extourne, montant_a_payer.type_calcul_effectue, montant_a_payer.montant_frais_retour, montant_a_payer.visa_mise_a_valider, montant_a_payer.date_mise_a_valider, montant_a_payer.visa_validation, montant_a_payer.date_validation, montant_a_payer.usr_log_i, montant_a_payer.dte_log_i, montant_a_payer.usr_log_u, montant_a_payer.dte_log_u, montant_a_payer.audit, montant_a_payer.version, montant_a_payer.montant_interets, montant_a_payer.nature_interets, montant_a_payer.periode_gestion_bouclement_id, montant_a_payer.nature_bouclement, montant_a_payer.numero_encaissement
  • Index Cond: (montant_a_payer.objet_payable_id = objet_payable.id)
  • Buffers: shared hit=763794 read=33900
45. 32.190 74.498 ↑ 1.0 110,112 1

Hash (cost=4,404.12..4,404.12 rows=110,112 width=62) (actual time=74.498..74.498 rows=110,112 loops=1)

  • Output: paiement.numero, paiement.numero_compte, paiement.numero_swift, paiement.numero_compte_postal, paiement.numero_iban, paiement.generation, paiement.statut
  • Buckets: 131072 Batches: 1 Memory Usage: 9667kB
  • Buffers: shared hit=3303
46. 42.308 42.308 ↑ 1.0 110,112 1

Seq Scan on partner.paiement (cost=0.00..4,404.12 rows=110,112 width=62) (actual time=0.023..42.308 rows=110,112 loops=1)

  • Output: paiement.numero, paiement.numero_compte, paiement.numero_swift, paiement.numero_compte_postal, paiement.numero_iban, paiement.generation, paiement.statut
  • Buffers: shared hit=3303
47. 5,260.830 5,260.830 ↑ 1.0 1 2,630,415

Index Scan using d_g_in_pi_d_fk on iliade.partie_dossier (cost=0.43..3.00 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=2,630,415)

  • 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) AND ((partie_dossier.type_partie)::text = ANY ('{PEDE,PEIN,PERE}'::text[])))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=10593708
48. 6,637.288 6,637.288 ↑ 7.0 1 3,318,644

Index Scan using idx4fk_f_pi_in_p_pi on iliade.plan_partie (cost=0.43..18.79 rows=7 width=8) (actual time=0.002..0.002 rows=1 loops=3,318,644)

  • Output: plan_partie.plan_partie_id, plan_partie.partie_dossier_id, plan_partie.plan_employeur_affilie_id, plan_partie.plan_partie_orig_id, plan_partie.dossier_id, plan_partie.no_gen_dossier, plan_partie.plan_base_id, plan_partie.nom_plan, plan_partie.type_plan, plan_partie.type_prev_lpp, plan_partie.etat, plan_partie.deduc_1, plan_partie.deduc_2, plan_partie.mnt_capital_retraite, plan_partie.prct_capital_retraite, plan_partie.mnt_rte_retraite, plan_partie.prct_rte_retraite, plan_partie.corrections_manuelles, plan_partie.dte_effet, plan_partie.recalcul_prest_av_terme, plan_partie.figer_recalcul_prest_av_terme, plan_partie.propres_coti, plan_partie.int_propres_coti, plan_partie.propres_coti_prov, plan_partie.propres_coti_majorees, plan_partie.rachats, plan_partie.int_rachats, plan_partie.avoir_transfere, plan_partie.freq_paie_coti, plan_partie.surp_paie_frac_inclus, plan_partie.valorisable, plan_partie.dte_entree_theorique, plan_partie.dte_entree_av_va, plan_partie.dte_prevue_deb_inva, plan_partie.deduc_gar, plan_partie.deduc_gar_precedent, plan_partie.sal_cotisant_garanti, plan_partie.sal_cotisant_garanti_precedent, plan_partie.propres_coti_ord, plan_partie.propres_coti_maintien, plan_partie.part_suppltemp_rachats, plan_partie.rachats_sans_int, plan_partie.prest_sortie_gar_base, plan_partie.prop_coti_ord_prestsortgar, plan_partie.prop_coti_maint_prestsortgar, plan_partie.prop_coti_maj_prestsortgar, plan_partie.rachats_prestsortgar, plan_partie.vers_anticipes_prestsortgar, plan_partie.prct_capital_lpp_retraite, plan_partie.prest_payees, plan_partie.usr_log_i, plan_partie.dte_log_i, plan_partie.usr_log_u, plan_partie.dte_log_u, plan_partie.nom_comm, plan_partie.freq_paie_coti_fige, plan_partie.rachats_prevus, plan_partie.part_suppltemp_rachats_prevus, plan_partie.rachats_prevus_prestsortgar, plan_partie.corrections_manuelles_primes, plan_partie.corrections_manuelles_prest, plan_partie.remb_va_prevus, plan_partie.part_suppltemp_remb_va_prevus, plan_partie.remb_va_prevus_prestsortgar, plan_partie.mnt_max_deb_ajournement, plan_partie.remb_va_a_recevoir, plan_partie.audit, plan_partie.max_rachetable_31_12, plan_partie.sal_min_1, plan_partie.sal_min_2, plan_partie.sal_max_1, plan_partie.sal_max_2, plan_partie.duree_gar_sal_retraite, plan_partie.duree_gar_sal_risque, plan_partie.duree_gar_sal_sortie, plan_partie.capital_retraite, plan_partie.correction_ps_rappel, plan_partie.plan_calcul_avant_terme, plan_partie.deduc_3, plan_partie.deduc_4, plan_partie.sal_min_3, plan_partie.sal_min_4, plan_partie.sal_max_3, plan_partie.sal_max_4, plan_partie.prct_cpt_pref_retraite, plan_partie.prct_cpt_exc_lp, plan_partie.pref_retraite, plan_partie.int_pref_retraite, plan_partie.excedents_lp, plan_partie.int_excedents_lp, plan_partie.propres_cotisations_ordinaires_part_epargne, plan_partie.propres_cotisations_ordinaires_interets_part_epargne, plan_partie.propres_cotisations_ordinaires_provisoires_part_epargne, plan_partie.propres_cotisations_maintien_part_epargne, plan_partie.propres_cotisations_maintien_interets_part_epargne, plan_partie.propres_cotisations_maintien_provisoires_part_epargne, plan_partie.max_rachetable_31_12_sans_cpr
  • Index Cond: (plan_partie.partie_dossier_id = partie_dossier.partie_dossier_id)
  • Filter: (((plan_partie.etat)::text = 'VALI'::text) AND ((plan_partie.type_plan)::text = 'ENV'::text))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=13638561
49. 19,745.238 19,745.238 ↑ 37.0 2 3,290,873

Index Scan using p_pi_in_pag_fk on iliade.pa_gen (cost=0.57..347.91 rows=74 width=16) (actual time=0.004..0.006 rows=2 loops=3,290,873)

  • Output: pa_gen.pa_gen_id, pa_gen.assure_part_dossier_id, pa_gen.vco_id, pa_gen.plan_partie_id, pa_gen.pa_gen_orig_id, pa_gen.pa_gen_base_prest_id, pa_gen.pa_gen_base_coti_id, pa_gen.pa_gen_base_plan_base_prest_id, pa_gen.pa_gen_base_plan_base_coti_id, pa_gen.partie_dossier_id, pa_gen.dossier_id, pa_gen.no_gen_dossier, pa_gen.type_couv, pa_gen.etat, pa_gen.dte_effet, pa_gen.dte_echeance, pa_gen.dte_effet_pe, pa_gen.dte_effet_idx, pa_gen.dte_base_calc, pa_gen.dte_fin_differe, pa_gen.dte_deb_inca_gain, pa_gen.age_deb_inca_gain, pa_gen.delai_attente, pa_gen.capital_rte, pa_gen.doublement_rte_enfant, pa_gen.freq_paie_rte, pa_gen.sal, pa_gen.classe_risque, pa_gen.tx_prest_base, pa_gen.usr_log_i, pa_gen.dte_log_i, pa_gen.usr_log_u, pa_gen.dte_log_u, pa_gen.dte_derniere_idx_liee_avs, pa_gen.numero_ub_refact_base, pa_gen.numero_ub_refact_idx, pa_gen.audit, pa_gen.type_fina_avance_avs, pa_gen.prct_avance_avs, pa_gen.type_numerando, pa_gen.vco_precedente_id, pa_gen.delai_lib, pa_gen.categorie_empl_secteur_act_id
  • Index Cond: (pa_gen.plan_partie_id = plan_partie.plan_partie_id)
  • Filter: (((pa_gen.etat)::text = 'VAL'::text) AND ((pa_gen.type_couv)::text = ANY ('{RCCO,RCEI,RCOR,RCRE,RCVF,RCVV,RELE,RENR,RCON}'::text[])))
  • Rows Removed by Filter: 4
  • Buffers: shared hit=17144248 read=25317
50. 1.289 6.753 ↑ 1.0 4,063 1

Hash (cost=146.63..146.63 rows=4,063 width=8) (actual time=6.753..6.753 rows=4,063 loops=1)

  • Output: vcouv.vco_id, vcouv.cou_id
  • Buckets: 4096 Batches: 1 Memory Usage: 191kB
  • Buffers: shared read=106
51. 5.464 5.464 ↑ 1.0 4,063 1

Seq Scan on produit.vcouv (cost=0.00..146.63 rows=4,063 width=8) (actual time=0.698..5.464 rows=4,063 loops=1)

  • Output: vcouv.vco_id, vcouv.cou_id
  • Buffers: shared read=106
52. 0.214 1.577 ↑ 1.0 787 1

Hash (cost=24.87..24.87 rows=787 width=32) (actual time=1.577..1.577 rows=787 loops=1)

  • Output: couv.desc_courte, couv.cou_id, couv.type_nat_type_couv
  • Buckets: 1024 Batches: 1 Memory Usage: 60kB
  • Buffers: shared read=17
53. 1.363 1.363 ↑ 1.0 787 1

Seq Scan on produit.couv (cost=0.00..24.87 rows=787 width=32) (actual time=0.434..1.363 rows=787 loops=1)

  • Output: couv.desc_courte, couv.cou_id, couv.type_nat_type_couv
  • Buffers: shared read=17
54. 0.025 0.642 ↑ 1.0 64 1

Hash (cost=1.64..1.64 rows=64 width=23) (actual time=0.642..0.642 rows=64 loops=1)

  • Output: type_couv.desc_courte, type_couv.type_nat_type_couv
  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared read=1
55. 0.617 0.617 ↑ 1.0 64 1

Seq Scan on produit.type_couv (cost=0.00..1.64 rows=64 width=23) (actual time=0.610..0.617 rows=64 loops=1)

  • Output: type_couv.desc_courte, type_couv.type_nat_type_couv
  • Buffers: shared read=1
56. 14,462.334 14,462.334 ↑ 1.0 1 7,231,167

Index Scan using idx4fk_f_pag_in_pagprest on iliade.pa_gen_prest (cost=0.57..2.34 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=7,231,167)

  • Output: pa_gen_prest.pa_gen_prest_id, pa_gen_prest.pa_gen_id, pa_gen_prest.dossier_id, pa_gen_prest.no_gen_dossier, pa_gen_prest.corrections_manuelles, pa_gen_prest.prest_reglementaire, pa_gen_prest.prest_base, pa_gen_prest.supplement_lpp, pa_gen_prest.prest_tot, pa_gen_prest.avoir_31_12, pa_gen_prest.prest_projetee_avec_int, pa_gen_prest.prest_projetee_sans_int, pa_gen_prest.idx, pa_gen_prest.participation_excedents, pa_gen_prest.reduc_base, pa_gen_prest.reduc_supplement_lpp, pa_gen_prest.reduc_idx, pa_gen_prest.reduc_participation_excedents, pa_gen_prest.supplement_propres_coti, pa_gen_prest.prest_due, pa_gen_prest.prest_reversee_par_reass, pa_gen_prest.allocation_menage, pa_gen_prest.supplement_droits_acquis, pa_gen_prest.compens_retraite_avancee, pa_gen_prest.idx_employeur_fine_capi, pa_gen_prest.idx_employeur_fine_perio, pa_gen_prest.supplement_prest_sortie_gar, pa_gen_prest.reduc_allocation_menage, pa_gen_prest.reduc_idx_employeur_fine_capi, pa_gen_prest.reduc_idx_employeur_fine_perio, pa_gen_prest.reduc_supplement_droits_acquis, pa_gen_prest.usr_log_i, pa_gen_prest.dte_log_i, pa_gen_prest.usr_log_u, pa_gen_prest.dte_log_u, pa_gen_prest.audit, pa_gen_prest.complement_fine_employeur, pa_gen_prest.reduc_compl_fine_employeur, pa_gen_prest.reduc_compens_retraite_avancee, pa_gen_prest.prest_gar, pa_gen_prest.supplement_garanti, pa_gen_prest.reduc_supplement_garanti, pa_gen_prest.reduc_divorce, pa_gen_prest.compl_exc_libre_passage
  • Index Cond: (pa_gen_prest.pa_gen_id = pa_gen.pa_gen_id)
  • Buffers: shared hit=36171648 read=25656
57. 78.158 78.158 ↑ 1.0 1 39,079

Index Scan using p_part_dossier on iliade.part_dossier a1_part_dossier_assure (cost=0.43..2.18 rows=1 width=13) (actual time=0.002..0.002 rows=1 loops=39,079)

  • Output: a1_part_dossier_assure.part_dossier_id, a1_part_dossier_assure.dossier_gen_id, a1_part_dossier_assure.part_dossier_part_id, a1_part_dossier_assure.dossier_id, a1_part_dossier_assure.no_gen_dossier, a1_part_dossier_assure.role, a1_part_dossier_assure.usr_log_i, a1_part_dossier_assure.dte_log_i, a1_part_dossier_assure.usr_log_u, a1_part_dossier_assure.dte_log_u, a1_part_dossier_assure.audit
  • Index Cond: (a1_part_dossier_assure.part_dossier_id = pa_gen.assure_part_dossier_id)
  • Buffers: shared hit=156426 read=34
58. 78.158 78.158 ↑ 1.0 1 39,079

Index Scan using p_part on iliade.part a1_part_assure (cost=0.42..2.16 rows=1 width=30) (actual time=0.002..0.002 rows=1 loops=39,079)

  • Output: a1_part_assure.part_id, a1_part_assure.type_part, a1_part_assure.nom_ou_raison_sociale, a1_part_assure.nom_complementaire, a1_part_assure.nom_alliance, a1_part_assure.prenom, a1_part_assure.dte_naissance, a1_part_assure.dte_deces, a1_part_assure.sexe, a1_part_assure.etat_civil, a1_part_assure.no_avs, a1_part_assure.langue_correspondance, a1_part_assure.usr_log_i, a1_part_assure.dte_log_i, a1_part_assure.usr_log_u, a1_part_assure.dte_log_u, a1_part_assure.audit, a1_part_assure.visa, a1_part_assure.numero_contact
  • Index Cond: (a1_part_assure.part_id = a1_part_dossier_assure.part_dossier_part_id)
  • Buffers: shared hit=156309 read=7
59. 115.721 195.395 ↑ 1.0 1 39,079

Index Scan using adresse_formatee_numero_contact_idx on partner.adresse_formatee a1_adresses_assu (cost=0.42..4.83 rows=1 width=73) (actual time=0.005..0.005 rows=1 loops=39,079)

  • Output: a1_adresses_assu.id, a1_adresses_assu.type_adresse, a1_adresses_assu.adresse_inconnue, a1_adresses_assu.code_canton_etat, a1_adresses_assu.code_iso2_pays, a1_adresses_assu.code_postal, a1_adresses_assu.indice_numero_rue, a1_adresses_assu.langue, a1_adresses_assu.ligne_01, a1_adresses_assu.ligne_02, a1_adresses_assu.ligne_03, a1_adresses_assu.ligne_04, a1_adresses_assu.ligne_05, a1_adresses_assu.ligne_06, a1_adresses_assu.ligne_07, a1_adresses_assu.ligne_08, a1_adresses_assu.ligne_09, a1_adresses_assu.ligne_10, a1_adresses_assu.nom_commune, a1_adresses_assu.nom_district, a1_adresses_assu.nom_localite, a1_adresses_assu.npa_loc_formatee, a1_adresses_assu.numero_rue, a1_adresses_assu.rue, a1_adresses_assu.rue_formatee, a1_adresses_assu.sexe_complement_destinataire, a1_adresses_assu.pas_bella_vita, a1_adresses_assu.pas_correspondance, a1_adresses_assu.version, a1_adresses_assu.usr_log_i, a1_adresses_assu.dte_log_i, a1_adresses_assu.usr_log_u, a1_adresses_assu.dte_log_u, a1_adresses_assu.numero_contact
  • Index Cond: (a1_adresses_assu.numero_contact = a1_part_assure.numero_contact)
  • Filter: (CASE WHEN ((a1_adresses_assu.type_adresse)::text = 'BASE'::text) THEN 0 WHEN ((a1_adresses_assu.type_adresse)::text = 'VIE_COLLECTIVE'::text) THEN 1 ELSE NULL::integer END = (SubPlan 7))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=317114 read=14
60.          

SubPlan (for Index Scan)

61. 0.000 79.674 ↑ 1.0 1 39,837

Aggregate (cost=2.65..2.66 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=39,837)

  • Output: max(CASE WHEN ((b.type_adresse)::text = 'BASE'::text) THEN 0 WHEN ((b.type_adresse)::text = 'VIE_COLLECTIVE'::text) THEN 1 ELSE NULL::integer END)
  • Buffers: shared hit=160300
62. 79.674 79.674 ↑ 1.0 1 39,837

Index Scan using adresse_formatee_numero_contact_idx on partner.adresse_formatee b (cost=0.42..2.64 rows=1 width=5) (actual time=0.001..0.002 rows=1 loops=39,837)

  • Output: b.id, b.type_adresse, b.adresse_inconnue, b.code_canton_etat, b.code_iso2_pays, b.code_postal, b.indice_numero_rue, b.langue, b.ligne_01, b.ligne_02, b.ligne_03, b.ligne_04, b.ligne_05, b.ligne_06, b.ligne_07, b.ligne_08, b.ligne_09, b.ligne_10, b.nom_commune, b.nom_district, b.nom_localite, b.npa_loc_formatee, b.numero_rue, b.rue, b.rue_formatee, b.sexe_complement_destinataire, b.pas_bella_vita, b.pas_correspondance, b.version, b.usr_log_i, b.dte_log_i, b.usr_log_u, b.dte_log_u, b.numero_contact
  • Index Cond: (a1_adresses_assu.numero_contact = b.numero_contact)
  • Buffers: shared hit=160300
63. 39.079 39.079 ↑ 1.0 1 39,079

Index Scan using p_pro on produit.prod (cost=0.14..2.36 rows=1 width=29) (actual time=0.001..0.001 rows=1 loops=39,079)

  • 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
  • Index Cond: (prod.pro_id = contrat.pro_id)
  • Buffers: shared hit=78158
64. 117.237 117.237 ↑ 1.0 5 39,079

Seq Scan on produit.fam_prod (cost=0.00..1.65 rows=5 width=17) (actual time=0.001..0.003 rows=5 loops=39,079)

  • 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 = ANY ('{23,24,9,43,26}'::integer[]))
  • Rows Removed by Filter: 22
  • Buffers: shared hit=39079
65. 39.079 39.079 ↑ 1.2 6 39,079

Seq Scan on produit.soc (cost=0.00..1.07 rows=7 width=9) (actual time=0.000..0.001 rows=6 loops=39,079)

  • Output: soc.soc_id, soc.desc_courte, soc.usr_log_i, soc.dte_log_i, soc.usr_log_u, soc.dte_log_u
  • Buffers: shared hit=39079
66. 1,055.133 6,799.746 ↑ 1.0 1 39,079

Index Scan using idx_mut_dossier_no_gen_contrat_contrat_id on iliade.mut_contrat (cost=0.42..39.16 rows=1 width=8) (actual time=0.174..0.174 rows=1 loops=39,079)

  • Output: mut_contrat.mut_contrat_id, mut_contrat.per_gest_id, mut_contrat.mut_id, mut_contrat.contrat_id, mut_contrat.donnees_mut_contrat_id, mut_contrat.no_gen_contrat, mut_contrat.dte_mut, mut_contrat.description, mut_contrat.etat, mut_contrat.statut, mut_contrat.no_mut, mut_contrat.no_mut_precedente, mut_contrat.no_mut_extournee, mut_contrat.repercussion_auto_dossiers, mut_contrat.visa_cree_par, mut_contrat.dte_creation, mut_contrat.visa_modifie_par, mut_contrat.dte_modification, mut_contrat.visa_ctrl_par, mut_contrat.dte_ctrl, mut_contrat.usr_log_i, mut_contrat.dte_log_i, mut_contrat.usr_log_u, mut_contrat.dte_log_u, mut_contrat.audit, mut_contrat.dte_escompte, mut_contrat.nouveau_cas, mut_contrat.pas_de_cas, mut_contrat.cas_id, mut_contrat.no_mut_bck, mut_contrat.no_mut_precedente_bck, mut_contrat.no_mut_extournee_bck
  • Index Cond: (mut_contrat.contrat_id = dossier.contrat_id)
  • Filter: (mut_contrat.no_gen_contrat = (SubPlan 9))
  • Rows Removed by Filter: 48
  • Buffers: shared hit=9457082 read=36
67.          

SubPlan (for Index Scan)

68. 0.000 5,744.613 ↑ 1.0 1 1,914,871

Result (cost=1.66..1.67 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1,914,871)

  • Output: $8
  • Buffers: shared hit=7659483 read=1
69.          

Initplan (for Result)

70. 1,914.871 5,744.613 ↑ 1.0 1 1,914,871

Limit (cost=0.42..1.66 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=1,914,871)

  • Output: a_5.no_gen_contrat
  • Buffers: shared hit=7659483 read=1
71. 3,829.742 3,829.742 ↑ 14.0 1 1,914,871

Index Scan Backward using idx_mut_dossier_no_gen_contrat_contrat_id on iliade.mut_contrat a_5 (cost=0.42..17.83 rows=14 width=4) (actual time=0.002..0.002 rows=1 loops=1,914,871)

  • 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=7659483 read=1
72. 117.237 117.237 ↑ 1.0 1 39,079

Index Scan using c_g_in_pt_c_fk on iliade.part_contrat a1_part_contrat_employeur (cost=0.42..2.25 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=39,079)

  • Output: a1_part_contrat_employeur.part_contrat_id, a1_part_contrat_employeur.contrat_gen_id, a1_part_contrat_employeur.part_id, a1_part_contrat_employeur.contrat_id, a1_part_contrat_employeur.no_gen_contrat, a1_part_contrat_employeur.role, a1_part_contrat_employeur.usr_log_i, a1_part_contrat_employeur.dte_log_i, a1_part_contrat_employeur.usr_log_u, a1_part_contrat_employeur.dte_log_u, a1_part_contrat_employeur.audit, a1_part_contrat_employeur.adresse_facturation_id, a1_part_contrat_employeur.region, a1_part_contrat_employeur.type_aprt_aff, a1_part_contrat_employeur.type_intmd_ext, a1_part_contrat_employeur.type_intmd_intn
  • Index Cond: (a1_part_contrat_employeur.contrat_gen_id = contrat_gen.contrat_gen_id)
  • Filter: ((a1_part_contrat_employeur.role)::text = 'EMPL'::text)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=156314 read=2
73. 39.079 39.079 ↑ 1.0 1 39,079

Index Scan using p_part on iliade.part a1_part_employeur (cost=0.42..2.17 rows=1 width=13) (actual time=0.001..0.001 rows=1 loops=39,079)

  • Output: a1_part_employeur.part_id, a1_part_employeur.type_part, a1_part_employeur.nom_ou_raison_sociale, a1_part_employeur.nom_complementaire, a1_part_employeur.nom_alliance, a1_part_employeur.prenom, a1_part_employeur.dte_naissance, a1_part_employeur.dte_deces, a1_part_employeur.sexe, a1_part_employeur.etat_civil, a1_part_employeur.no_avs, a1_part_employeur.langue_correspondance, a1_part_employeur.usr_log_i, a1_part_employeur.dte_log_i, a1_part_employeur.usr_log_u, a1_part_employeur.dte_log_u, a1_part_employeur.audit, a1_part_employeur.visa, a1_part_employeur.numero_contact
  • Index Cond: (a1_part_employeur.part_id = a1_part_contrat_employeur.part_id)
  • Buffers: shared hit=156316
74.          

SubPlan (for Nested Loop)

75. 156.316 156.316 ↑ 1.0 1 39,079

Index Scan using idx_code_texte_code_cle_code_type on produit.code_texte a (cost=0.28..2.50 rows=1 width=15) (actual time=0.004..0.004 rows=1 loops=39,079)

  • Output: a.texte
  • Index Cond: (((mut_dossier.statut)::text = (a.code_cle)::text) AND ((a.code_type)::text = 'TypeStatutDossier'::text))
  • Filter: ((a.codet_langue)::text = 'FR'::text)
  • Buffers: shared hit=117236 read=1
76. 156.316 156.316 ↑ 1.0 1 39,079

Index Scan using idx_code_texte_code_cle_code_type on produit.code_texte a_1 (cost=0.28..2.50 rows=1 width=15) (actual time=0.004..0.004 rows=1 loops=39,079)

  • Output: a_1.texte
  • Index Cond: (((a_1.code_cle)::text = (partie_dossier.type_partie)::text) AND ((a_1.code_type)::text = 'TypePartieDossier'::text))
  • Filter: ((a_1.codet_langue)::text = 'FR'::text)
  • Buffers: shared hit=117237
77. 117.237 117.237 ↑ 1.0 1 39,079

Index Scan using idx_code_texte_code_cle_code_type on produit.code_texte a_2 (cost=0.28..2.50 rows=1 width=15) (actual time=0.003..0.003 rows=1 loops=39,079)

  • Output: a_2.texte
  • Index Cond: (((a1_part_affi.sexe)::text = (a_2.code_cle)::text) AND ((a_2.code_type)::text = 'TypeSexe'::text))
  • Filter: ((a_2.codet_langue)::text = 'FR'::text)
  • Buffers: shared hit=117237
78. 117.237 117.237 ↑ 1.0 1 39,079

Index Scan using idx_code_texte_code_cle_code_type on produit.code_texte a_3 (cost=0.28..2.50 rows=1 width=15) (actual time=0.003..0.003 rows=1 loops=39,079)

  • Output: a_3.texte
  • Index Cond: (((a_3.code_cle)::text = (a1_part_affi.etat_civil)::text) AND ((a_3.code_type)::text = 'TypeEtatCivil'::text))
  • Filter: ((a_3.codet_langue)::text = 'FR'::text)
  • Buffers: shared hit=112945 read=2
Planning time : 67.834 ms
Execution time : 1,265,812.933 ms