explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S9uB

Settings
# exclusive inclusive rows x rows loops node
1. 43.556 5,237,359.907 ↓ 29,425.0 58,850 1

Unique (cost=4,336,419.95..4,336,420.19 rows=2 width=4,560) (actual time=5,237,310.805..5,237,359.907 rows=58,850 loops=1)

  • Output: soc.soc_id, dossier.numero_dossier, dossier.dossier_id, mut_dossier.no_gen_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.numero_contact, a1_part_affi.dte_naissance, a1_part_affi.sexe, 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, mut_dossier.dte_mut, mut_dossier.mut_id, ((SubPlan 1)), mut_dossier.statut, ((SubPlan 2)), dossier_gen.dte_demission, ((dossier_gen.conge)::integer), dossier_gen.dte_deb_conge_effectif, part_comm_dossier.part_comm_dossier_id, part_comm_dossier.type_part_comm, ((SubPlan 3)), part_comm_dossier.type_employeur, part_comm_dossier.numero_ub, part_comm_dossier.numero_decompte, part_comm_dossier.type_sal, ((SubPlan 4)), partie_dossier.partie_dossier_id, partie_dossier.type_partie, ((SubPlan 5)), partie_dossier.type_inva, ((SubPlan 6)), part_comm_plan_partie.part_comm_plan_partie_id, part_comm_plan_partie.tx_travaille, (sum(COALESCE(part_comm_plan_partie.sal_avs, '0'::numeric))), (sum(COALESCE(part_comm_plan_partie.part_sal_cotisant_act, '0'::numeric))), unite_budgetaire_contrat.numero_ub, unite_budgetaire_contrat.desc_courte, unite_budgetaire_contrat.desc_longue
  • Buffers: shared hit=43743454 read=670415 written=307
2. 143.837 5,237,316.351 ↓ 29,425.0 58,850 1

Sort (cost=4,336,419.95..4,336,419.96 rows=2 width=4,560) (actual time=5,237,310.800..5,237,316.351 rows=58,850 loops=1)

  • Output: soc.soc_id, dossier.numero_dossier, dossier.dossier_id, mut_dossier.no_gen_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.numero_contact, a1_part_affi.dte_naissance, a1_part_affi.sexe, 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, mut_dossier.dte_mut, mut_dossier.mut_id, ((SubPlan 1)), mut_dossier.statut, ((SubPlan 2)), dossier_gen.dte_demission, ((dossier_gen.conge)::integer), dossier_gen.dte_deb_conge_effectif, part_comm_dossier.part_comm_dossier_id, part_comm_dossier.type_part_comm, ((SubPlan 3)), part_comm_dossier.type_employeur, part_comm_dossier.numero_ub, part_comm_dossier.numero_decompte, part_comm_dossier.type_sal, ((SubPlan 4)), partie_dossier.partie_dossier_id, partie_dossier.type_partie, ((SubPlan 5)), partie_dossier.type_inva, ((SubPlan 6)), part_comm_plan_partie.part_comm_plan_partie_id, part_comm_plan_partie.tx_travaille, (sum(COALESCE(part_comm_plan_partie.sal_avs, '0'::numeric))), (sum(COALESCE(part_comm_plan_partie.part_sal_cotisant_act, '0'::numeric))), unite_budgetaire_contrat.numero_ub, unite_budgetaire_contrat.desc_courte, unite_budgetaire_contrat.desc_longue
  • Sort Key: soc.soc_id, dossier.numero_dossier, dossier.dossier_id, mut_dossier.no_gen_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.numero_contact, a1_part_affi.dte_naissance, a1_part_affi.sexe, 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, mut_dossier.dte_mut, mut_dossier.mut_id, ((SubPlan 1)), mut_dossier.statut, ((SubPlan 2)), dossier_gen.dte_demission, ((dossier_gen.conge)::integer), dossier_gen.dte_deb_conge_effectif, part_comm_dossier.part_comm_dossier_id, part_comm_dossier.type_part_comm, ((SubPlan 3)), part_comm_dossier.type_employeur, part_comm_dossier.numero_ub, part_comm_dossier.numero_decompte, part_comm_dossier.type_sal, ((SubPlan 4)), partie_dossier.partie_dossier_id, partie_dossier.type_partie, ((SubPlan 5)), partie_dossier.type_inva, ((SubPlan 6)), part_comm_plan_partie.part_comm_plan_partie_id, part_comm_plan_partie.tx_travaille, (sum(COALESCE(part_comm_plan_partie.sal_avs, '0'::numeric))), (sum(COALESCE(part_comm_plan_partie.part_sal_cotisant_act, '0'::numeric))), unite_budgetaire_contrat.numero_ub, unite_budgetaire_contrat.desc_courte, unite_budgetaire_contrat.desc_longue
  • Sort Method: quicksort Memory: 30502kB
  • Buffers: shared hit=43743454 read=670415 written=307
3. 4.704 5,237,172.514 ↓ 29,425.0 58,850 1

Append (cost=2,637,043.25..4,336,419.94 rows=2 width=4,560) (actual time=5,206,341.622..5,237,172.514 rows=58,850 loops=1)

  • Buffers: shared hit=43743445 read=670415 written=307
4. 61.994 5,206,410.228 ↓ 39,886.0 39,886 1

GroupAggregate (cost=2,637,043.25..2,637,043.39 rows=1 width=3,118) (actual time=5,206,341.621..5,206,410.228 rows=39,886 loops=1)

  • Output: soc.soc_id, dossier.numero_dossier, dossier.dossier_id, mut_dossier.no_gen_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.numero_contact, a1_part_affi.dte_naissance, a1_part_affi.sexe, 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, mut_dossier.dte_mut, mut_dossier.mut_id, ((SubPlan 1)), mut_dossier.statut, ((SubPlan 2)), dossier_gen.dte_demission, ((dossier_gen.conge)::integer), dossier_gen.dte_deb_conge_effectif, part_comm_dossier.part_comm_dossier_id, part_comm_dossier.type_part_comm, ((SubPlan 3)), part_comm_dossier.type_employeur, part_comm_dossier.numero_ub, part_comm_dossier.numero_decompte, part_comm_dossier.type_sal, ((SubPlan 4)), partie_dossier.partie_dossier_id, partie_dossier.type_partie, ((SubPlan 5)), partie_dossier.type_inva, ((SubPlan 6)), part_comm_plan_partie.part_comm_plan_partie_id, part_comm_plan_partie.tx_travaille, sum(COALESCE(part_comm_plan_partie.sal_avs, '0'::numeric)), sum(COALESCE(part_comm_plan_partie.part_sal_cotisant_act, '0'::numeric)), unite_budgetaire_contrat.numero_ub, unite_budgetaire_contrat.desc_courte, unite_budgetaire_contrat.desc_longue
  • Group Key: soc.soc_id, dossier.dossier_id, mut_dossier.no_gen_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.numero_contact, a1_part_affi.dte_naissance, a1_part_affi.sexe, 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, mut_dossier.dte_mut, mut_dossier.mut_id, ((SubPlan 1)), mut_dossier.statut, ((SubPlan 2)), dossier_gen.dte_demission, ((dossier_gen.conge)::integer), dossier_gen.dte_deb_conge_effectif, part_comm_dossier.part_comm_dossier_id, ((SubPlan 3)), ((SubPlan 4)), partie_dossier.partie_dossier_id, ((SubPlan 5)), ((SubPlan 6)), part_comm_plan_partie.part_comm_plan_partie_id, unite_budgetaire_contrat.numero_ub, unite_budgetaire_contrat.desc_courte, unite_budgetaire_contrat.desc_longue
  • Buffers: shared hit=39265152 read=277999 written=307
5. 695.269 5,206,348.234 ↓ 39,886.0 39,886 1

Sort (cost=2,637,043.25..2,637,043.26 rows=1 width=3,067) (actual time=5,206,341.547..5,206,348.234 rows=39,886 loops=1)

  • Output: soc.soc_id, dossier.dossier_id, mut_dossier.no_gen_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.numero_contact, a1_part_affi.dte_naissance, a1_part_affi.sexe, 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, mut_dossier.dte_mut, mut_dossier.mut_id, ((SubPlan 1)), mut_dossier.statut, ((SubPlan 2)), dossier_gen.dte_demission, ((dossier_gen.conge)::integer), dossier_gen.dte_deb_conge_effectif, part_comm_dossier.part_comm_dossier_id, ((SubPlan 3)), ((SubPlan 4)), partie_dossier.partie_dossier_id, ((SubPlan 5)), ((SubPlan 6)), part_comm_plan_partie.part_comm_plan_partie_id, unite_budgetaire_contrat.numero_ub, unite_budgetaire_contrat.desc_courte, unite_budgetaire_contrat.desc_longue, dossier.numero_dossier, part_comm_dossier.type_part_comm, part_comm_dossier.type_employeur, part_comm_dossier.numero_ub, part_comm_dossier.numero_decompte, part_comm_dossier.type_sal, partie_dossier.type_partie, partie_dossier.type_inva, part_comm_plan_partie.tx_travaille, part_comm_plan_partie.sal_avs, part_comm_plan_partie.part_sal_cotisant_act
  • Sort Key: dossier.dossier_id, mut_dossier.no_gen_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.numero_contact, a1_part_affi.dte_naissance, a1_part_affi.sexe, 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, mut_dossier.dte_mut, mut_dossier.mut_id, ((SubPlan 1)), mut_dossier.statut, ((SubPlan 2)), dossier_gen.dte_demission, ((dossier_gen.conge)::integer), dossier_gen.dte_deb_conge_effectif, part_comm_dossier.part_comm_dossier_id, ((SubPlan 3)), ((SubPlan 4)), partie_dossier.partie_dossier_id, ((SubPlan 5)), ((SubPlan 6)), part_comm_plan_partie.part_comm_plan_partie_id, unite_budgetaire_contrat.numero_ub, unite_budgetaire_contrat.desc_courte, unite_budgetaire_contrat.desc_longue
  • Sort Method: quicksort Memory: 22103kB
  • Buffers: shared hit=39265152 read=277999 written=307
6. 1,086.797 5,205,652.965 ↓ 39,886.0 39,886 1

Nested Loop (cost=1,362,400.20..2,637,043.24 rows=1 width=3,067) (actual time=30,665.666..5,205,652.965 rows=39,886 loops=1)

  • Output: soc.soc_id, dossier.dossier_id, mut_dossier.no_gen_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.numero_contact, a1_part_affi.dte_naissance, a1_part_affi.sexe, 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, mut_dossier.dte_mut, mut_dossier.mut_id, ((SubPlan 1)), mut_dossier.statut, ((SubPlan 2)), dossier_gen.dte_demission, (dossier_gen.conge)::integer, dossier_gen.dte_deb_conge_effectif, part_comm_dossier.part_comm_dossier_id, ((SubPlan 3)), ((SubPlan 4)), partie_dossier.partie_dossier_id, ((SubPlan 5)), ((SubPlan 6)), part_comm_plan_partie.part_comm_plan_partie_id, unite_budgetaire_contrat.numero_ub, unite_budgetaire_contrat.desc_courte, unite_budgetaire_contrat.desc_longue, dossier.numero_dossier, part_comm_dossier.type_part_comm, part_comm_dossier.type_employeur, part_comm_dossier.numero_ub, part_comm_dossier.numero_decompte, part_comm_dossier.type_sal, partie_dossier.type_partie, partie_dossier.type_inva, part_comm_plan_partie.tx_travaille, part_comm_plan_partie.sal_avs, part_comm_plan_partie.part_sal_cotisant_act
  • Join Filter: ((mut_dossier.dte_mut >= ub_par.dte_deb) AND (mut_dossier.dte_mut <= ub_par.dte_fin) AND (part_comm_dossier.numero_ub >= ub_par.numero_ub_detail_deb) AND (part_comm_dossier.numero_ub <= ub_par.numero_ub_detail_fin))
  • Rows Removed by Join Filter: 9373210
  • Buffers: shared hit=39265152 read=277999 written=307
7. 3,105.022 5,192,121.736 ↓ 6,222,216.0 6,222,216 1

Merge Join (cost=1,362,399.93..2,637,042.19 rows=1 width=3,054) (actual time=30,663.067..5,192,121.736 rows=6,222,216 loops=1)

  • Output: part_comm_plan_partie.part_comm_plan_partie_id, part_comm_plan_partie.tx_travaille, part_comm_plan_partie.sal_avs, part_comm_plan_partie.part_sal_cotisant_act, partie_dossier.partie_dossier_id, partie_dossier.type_partie, partie_dossier.type_inva, dossier_gen.dte_demission, dossier_gen.conge, dossier_gen.dte_deb_conge_effectif, mut_dossier.no_gen_dossier, mut_dossier.dte_mut, mut_dossier.mut_id, mut_dossier.statut, dossier.numero_dossier, dossier.dossier_id, soc.soc_id, part_comm_dossier.part_comm_dossier_id, part_comm_dossier.type_part_comm, part_comm_dossier.type_employeur, part_comm_dossier.numero_ub, part_comm_dossier.numero_decompte, part_comm_dossier.type_sal, unite_budgetaire_contrat.numero_ub, unite_budgetaire_contrat.desc_courte, unite_budgetaire_contrat.desc_longue, unite_budgetaire_contrat.unite_budgetaire_contrat_id, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.numero_contact, a1_part_affi.dte_naissance, a1_part_affi.sexe, 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, ((SubPlan 1)), ((SubPlan 2)), ((SubPlan 3)), ((SubPlan 4)), ((SubPlan 5)), ((SubPlan 6))
  • Merge Cond: (a1_part_affi.numero_contact = a1_adresses_affi.numero_contact)
  • Buffers: shared hit=18803642 read=277991 written=307
8. 1,955.223 5,186,641.192 ↓ 282,828.0 6,222,216 1

Merge Join (cost=395,830.47..3,901,072.14 rows=22 width=2,989) (actual time=28,808.672..5,186,641.192 rows=6,222,216 loops=1)

  • Output: part_comm_plan_partie.part_comm_plan_partie_id, part_comm_plan_partie.tx_travaille, part_comm_plan_partie.sal_avs, part_comm_plan_partie.part_sal_cotisant_act, partie_dossier.partie_dossier_id, partie_dossier.type_partie, partie_dossier.type_inva, dossier_gen.dte_demission, dossier_gen.conge, dossier_gen.dte_deb_conge_effectif, mut_dossier.no_gen_dossier, mut_dossier.dte_mut, mut_dossier.mut_id, mut_dossier.statut, dossier.numero_dossier, dossier.dossier_id, soc.soc_id, part_comm_dossier.part_comm_dossier_id, part_comm_dossier.type_part_comm, part_comm_dossier.type_employeur, part_comm_dossier.numero_ub, part_comm_dossier.numero_decompte, part_comm_dossier.type_sal, unite_budgetaire_contrat.numero_ub, unite_budgetaire_contrat.desc_courte, unite_budgetaire_contrat.desc_longue, unite_budgetaire_contrat.unite_budgetaire_contrat_id, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.numero_contact, a1_part_affi.dte_naissance, a1_part_affi.sexe, a1_bi_partenaire_affi.numero_contact, ((SubPlan 1)), ((SubPlan 2)), ((SubPlan 3)), ((SubPlan 4)), ((SubPlan 5)), ((SubPlan 6))
  • Inner Unique: true
  • Merge Cond: (a1_part_affi.numero_contact = a1_bi_partenaire_affi.numero_contact)
  • Buffers: shared hit=17370438 read=263546 written=307
9. 2,041.898 5,184,046.708 ↓ 109,161.7 6,222,216 1

Nested Loop (cost=395,824.69..9,453,086.06 rows=57 width=2,985) (actual time=28,803.179..5,184,046.708 rows=6,222,216 loops=1)

  • Output: part_comm_plan_partie.part_comm_plan_partie_id, part_comm_plan_partie.tx_travaille, part_comm_plan_partie.sal_avs, part_comm_plan_partie.part_sal_cotisant_act, partie_dossier.partie_dossier_id, partie_dossier.type_partie, partie_dossier.type_inva, dossier_gen.dte_demission, dossier_gen.conge, dossier_gen.dte_deb_conge_effectif, mut_dossier.no_gen_dossier, mut_dossier.dte_mut, mut_dossier.mut_id, mut_dossier.statut, dossier.numero_dossier, dossier.dossier_id, soc.soc_id, part_comm_dossier.part_comm_dossier_id, part_comm_dossier.type_part_comm, part_comm_dossier.type_employeur, part_comm_dossier.numero_ub, part_comm_dossier.numero_decompte, part_comm_dossier.type_sal, unite_budgetaire_contrat.numero_ub, unite_budgetaire_contrat.desc_courte, unite_budgetaire_contrat.desc_longue, unite_budgetaire_contrat.unite_budgetaire_contrat_id, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.numero_contact, a1_part_affi.dte_naissance, a1_part_affi.sexe, ((SubPlan 1)), ((SubPlan 2)), ((SubPlan 3)), ((SubPlan 4)), ((SubPlan 5)), ((SubPlan 6))
  • Buffers: shared hit=17369635 read=261811 written=295
10. 4,125,636.963 5,176,460.656 ↓ 39,886.0 39,886 1

Nested Loop (cost=395,824.42..9,453,075.94 rows=1 width=2,930) (actual time=28,801.653..5,176,460.656 rows=39,886 loops=1)

  • Output: part_comm_plan_partie.part_comm_plan_partie_id, part_comm_plan_partie.tx_travaille, part_comm_plan_partie.sal_avs, part_comm_plan_partie.part_sal_cotisant_act, partie_dossier.partie_dossier_id, partie_dossier.type_partie, partie_dossier.type_inva, dossier_gen.dte_demission, dossier_gen.conge, dossier_gen.dte_deb_conge_effectif, mut_dossier.no_gen_dossier, mut_dossier.dte_mut, mut_dossier.mut_id, mut_dossier.statut, dossier.numero_dossier, dossier.dossier_id, dossier.contrat_id, soc.soc_id, part_comm_dossier.part_comm_dossier_id, part_comm_dossier.type_part_comm, part_comm_dossier.type_employeur, part_comm_dossier.numero_ub, part_comm_dossier.numero_decompte, part_comm_dossier.type_sal, a1_part_affi.nom_ou_raison_sociale, a1_part_affi.prenom, a1_part_affi.nom_complementaire, a1_part_affi.numero_contact, a1_part_affi.dte_naissance, a1_part_affi.sexe, ((SubPlan 1)), ((SubPlan 2)), ((SubPlan 3)), ((SubPlan 4)), ((SubPlan 5)), ((SubPlan 6))
  • Join Filter: (a1_part_dossier_affi.part_dossier_part_id = a1_part_affi.part_id)
  • Rows Removed by Join Filter: 20032344640
  • Buffers: shared hit=16492163 read=261791 written=295
11. 9,678.100 9,678.100 ↑ 1.0 502,241 1

Index Scan using idx_part_numero_contact on iliade.part a1_part_affi (cost=0.42..141,699.55 rows=502,241 width=32) (actual time=0.030..9,678.100 rows=502,241 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=111540 read=24267 written=295
12. 1,012,688.271 1,041,145.593 ↓ 39,886.0 39,886 502,241

Materialize (cost=395,824.00..9,303,842.78 rows=1 width=2,906) (actual time=0.014..2.073 rows=39,886 loops=502,241)

  • Output: part_comm_plan_partie.part_comm_plan_partie_id, part_comm_plan_partie.tx_travaille, part_comm_plan_partie.sal_avs, part_comm_plan_partie.part_sal_cotisant_act, partie_dossier.partie_dossier_id, partie_dossier.type_partie, partie_dossier.type_inva, dossier_gen.dte_demission, dossier_gen.conge, dossier_gen.dte_deb_conge_effectif, mut_dossier.no_gen_dossier, mut_dossier.dte_mut, mut_dossier.mut_id, mut_dossier.statut, dossier.numero_dossier, dossier.dossier_id, dossier.contrat_id, soc.soc_id, part_comm_dossier.part_comm_dossier_id, part_comm_dossier.type_part_comm, part_comm_dossier.type_employeur, part_comm_dossier.numero_ub, part_comm_dossier.numero_decompte, part_comm_dossier.type_sal, a1_part_dossier_affi.part_dossier_part_id, ((SubPlan 1)), ((SubPlan 2)), ((SubPlan 3)), ((SubPlan 4)), ((SubPlan 5)), ((SubPlan 6))
  • Buffers: shared hit=16380623 read=237524
13. 26.347 28,457.322 ↓ 39,886.0 39,886 1

Nested Loop (cost=395,824.00..9,303,842.77 rows=1 width=2,906) (actual time=6,905.952..28,457.322 rows=39,886 loops=1)

  • Output: part_comm_plan_partie.part_comm_plan_partie_id, part_comm_plan_partie.tx_travaille, part_comm_plan_partie.sal_avs, part_comm_plan_partie.part_sal_cotisant_act, partie_dossier.partie_dossier_id, partie_dossier.type_partie, partie_dossier.type_inva, dossier_gen.dte_demission, dossier_gen.conge, dossier_gen.dte_deb_conge_effectif, mut_dossier.no_gen_dossier, mut_dossier.dte_mut, mut_dossier.mut_id, mut_dossier.statut, dossier.numero_dossier, dossier.dossier_id, dossier.contrat_id, soc.soc_id, part_comm_dossier.part_comm_dossier_id, part_comm_dossier.type_part_comm, part_comm_dossier.type_employeur, part_comm_dossier.numero_ub, part_comm_dossier.numero_decompte, part_comm_dossier.type_sal, a1_part_dossier_affi.part_dossier_part_id, ((SubPlan 1)), ((SubPlan 2)), ((SubPlan 3)), ((SubPlan 4)), ((SubPlan 5)), ((SubPlan 6))
  • Buffers: shared hit=16380623 read=237524
14. 61.200 28,111.887 ↓ 39,886.0 39,886 1

Nested Loop (cost=395,823.56..9,303,835.26 rows=1 width=2,914) (actual time=6,905.900..28,111.887 rows=39,886 loops=1)

  • Output: part_comm_plan_partie.part_comm_plan_partie_id, part_comm_plan_partie.tx_travaille, part_comm_plan_partie.sal_avs, part_comm_plan_partie.part_sal_cotisant_act, partie_dossier.partie_dossier_id, partie_dossier.type_partie, partie_dossier.type_inva, partie_dossier.dossier_gen_id, dossier_gen.dte_demission, dossier_gen.conge, dossier_gen.dte_deb_conge_effectif, dossier_gen.dossier_gen_id, mut_dossier.no_gen_dossier, mut_dossier.dte_mut, mut_dossier.mut_id, mut_dossier.statut, dossier.numero_dossier, dossier.dossier_id, dossier.contrat_id, soc.soc_id, part_comm_dossier.part_comm_dossier_id, part_comm_dossier.type_part_comm, part_comm_dossier.type_employeur, part_comm_dossier.numero_ub, part_comm_dossier.numero_decompte, part_comm_dossier.type_sal, part_comm_dossier.dossier_gen_id, ((SubPlan 1)), ((SubPlan 2)), ((SubPlan 3)), ((SubPlan 4)), ((SubPlan 5)), ((SubPlan 6))
  • Inner Unique: true
  • Join Filter: (dossier_gen.dossier_gen_id = part_comm_dossier.dossier_gen_id)
  • Buffers: shared hit=16219068 read=237513
15. 66.805 26,491.547 ↓ 10.4 77,957 1

Nested Loop (cost=395,823.13..9,255,859.55 rows=7,523 width=1,856) (actual time=6,901.117..26,491.547 rows=77,957 loops=1)

  • Output: part_comm_plan_partie.part_comm_plan_partie_id, part_comm_plan_partie.tx_travaille, part_comm_plan_partie.sal_avs, part_comm_plan_partie.part_sal_cotisant_act, part_comm_plan_partie.part_comm_dossier_id, partie_dossier.partie_dossier_id, partie_dossier.type_partie, partie_dossier.type_inva, partie_dossier.dossier_gen_id, dossier_gen.dte_demission, dossier_gen.conge, dossier_gen.dte_deb_conge_effectif, dossier_gen.dossier_gen_id, mut_dossier.no_gen_dossier, mut_dossier.dte_mut, mut_dossier.mut_id, mut_dossier.statut, dossier.numero_dossier, dossier.dossier_id, dossier.contrat_id, soc.soc_id, ((SubPlan 1)), ((SubPlan 2)), ((SubPlan 5)), ((SubPlan 6))
  • Buffers: shared hit=15674812 read=230308
16. 12.644 24,026.269 ↓ 7.0 38,071 1

Nested Loop (cost=395,822.69..9,227,830.37 rows=5,401 width=1,834) (actual time=6,899.461..24,026.269 rows=38,071 loops=1)

  • Output: plan_partie.plan_partie_id, partie_dossier.partie_dossier_id, partie_dossier.type_partie, partie_dossier.type_inva, partie_dossier.dossier_gen_id, dossier_gen.dte_demission, dossier_gen.conge, dossier_gen.dte_deb_conge_effectif, dossier_gen.dossier_gen_id, mut_dossier.no_gen_dossier, mut_dossier.dte_mut, mut_dossier.mut_id, mut_dossier.statut, dossier.numero_dossier, dossier.dossier_id, dossier.contrat_id, soc.soc_id, ((SubPlan 1)), ((SubPlan 2)), ((SubPlan 5)), ((SubPlan 6))
  • Buffers: shared hit=15534701 read=216475
17. 0.003 0.058 ↑ 1.0 1 1

Nested Loop (cost=0.14..3.46 rows=1 width=8) (actual time=0.048..0.058 rows=1 loops=1)

  • Output: prod.pro_id, soc.soc_id
  • Buffers: shared hit=3
18. 0.034 0.034 ↑ 1.0 1 1

Index Scan using p_pro on produit.prod (cost=0.14..2.37 rows=1 width=6) (actual time=0.031..0.034 rows=1 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
  • Index Cond: (prod.pro_id = 87)
  • Filter: (prod.dn_soc_id = 6)
  • Buffers: shared hit=2
19. 0.021 0.021 ↑ 1.0 1 1

Seq Scan on produit.soc (cost=0.00..1.09 rows=1 width=4) (actual time=0.015..0.021 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 = 6)
  • Rows Removed by Filter: 6
  • Buffers: shared hit=1
20. 42.730 24,013.567 ↓ 7.0 38,071 1

Nested Loop (cost=395,822.55..9,227,772.90 rows=5,401 width=1,834) (actual time=6,899.410..24,013.567 rows=38,071 loops=1)

  • Output: plan_partie.plan_partie_id, partie_dossier.partie_dossier_id, partie_dossier.type_partie, partie_dossier.type_inva, partie_dossier.dossier_gen_id, dossier_gen.dte_demission, dossier_gen.conge, dossier_gen.dte_deb_conge_effectif, dossier_gen.dossier_gen_id, mut_dossier.no_gen_dossier, mut_dossier.dte_mut, mut_dossier.mut_id, mut_dossier.statut, dossier.numero_dossier, dossier.dossier_id, dossier.pro_id, dossier.contrat_id, ((SubPlan 1)), ((SubPlan 2)), ((SubPlan 5)), ((SubPlan 6))
  • Buffers: shared hit=15534698 read=216475
21. 20.285 21,572.364 ↓ 6.2 38,071 1

Nested Loop (cost=395,822.11..9,162,856.31 rows=6,133 width=1,830) (actual time=6,896.761..21,572.364 rows=38,071 loops=1)

  • Output: partie_dossier.partie_dossier_id, partie_dossier.type_partie, partie_dossier.type_inva, partie_dossier.dossier_gen_id, dossier_gen.dte_demission, dossier_gen.conge, dossier_gen.dte_deb_conge_effectif, dossier_gen.dossier_gen_id, mut_dossier.no_gen_dossier, mut_dossier.dte_mut, mut_dossier.mut_id, mut_dossier.statut, dossier.numero_dossier, dossier.dossier_id, dossier.pro_id, dossier.contrat_id, ((SubPlan 1)), ((SubPlan 2)), ((SubPlan 5)), ((SubPlan 6))
  • Buffers: shared hit=15390469 read=203534
22. 197.311 20,904.872 ↓ 5.4 38,071 1

Hash Join (cost=395,821.68..9,080,078.92 rows=7,013 width=780) (actual time=6,895.114..20,904.872 rows=38,071 loops=1)

  • Output: dossier_gen.dte_demission, dossier_gen.conge, dossier_gen.dte_deb_conge_effectif, dossier_gen.dossier_gen_id, mut_dossier.no_gen_dossier, mut_dossier.dte_mut, mut_dossier.mut_id, mut_dossier.statut, dossier.numero_dossier, dossier.dossier_id, dossier.pro_id, dossier.contrat_id, ((SubPlan 1)), ((SubPlan 2))
  • Hash Cond: (mut_dossier.mut_dossier_id = dossier_gen.mut_dossier_id)
  • Join Filter: (((mut_dossier.statut)::text = ANY ('{ACTI,MIXT}'::text[])) OR (((mut_dossier.statut)::text = 'PENS'::text) AND (hashed SubPlan 10)))
  • Rows Removed by Join Filter: 17236
  • Buffers: shared hit=15124527 read=202718
23. 96.739 13,359.202 ↓ 7.7 55,307 1

Nested Loop (cost=0.85..8,684,071.26 rows=7,157 width=771) (actual time=80.979..13,359.202 rows=55,307 loops=1)

  • Output: mut_dossier.no_gen_dossier, mut_dossier.dte_mut, mut_dossier.mut_id, mut_dossier.statut, mut_dossier.mut_dossier_id, dossier.numero_dossier, dossier.dossier_id, dossier.pro_id, dossier.contrat_id, ((SubPlan 1)), ((SubPlan 2))
  • Buffers: shared hit=14549407 read=776
24. 178.326 178.326 ↑ 1.0 87,813 1

Index Scan using c_in_d_fk on iliade.dossier (cost=0.42..97,914.54 rows=88,725 width=16) (actual time=77.249..178.326 rows=87,813 loops=1)

  • 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
  • Filter: (dossier.pro_id = 87)
  • Rows Removed by Filter: 164700
  • Buffers: shared hit=73590 read=759
25. 3,929.713 13,084.137 ↑ 1.0 1 87,813

Index Scan using idx_mut_dossier_dossier_id_no_gen_dossier on iliade.mut_dossier (cost=0.43..96.76 rows=1 width=759) (actual time=0.143..0.149 rows=1 loops=87,813)

  • Output: mut_dossier.no_gen_dossier, mut_dossier.dte_mut, mut_dossier.mut_id, mut_dossier.statut, mut_dossier.mut_dossier_id, mut_dossier.dossier_id, (SubPlan 1), (SubPlan 2)
  • Index Cond: (mut_dossier.dossier_id = dossier.dossier_id)
  • Filter: ((((mut_dossier.statut)::text = ANY ('{ACTI,MIXT}'::text[])) OR ((mut_dossier.statut)::text = 'PENS'::text)) AND (mut_dossier.no_gen_dossier = (SubPlan 8)))
  • Rows Removed by Filter: 25
  • Buffers: shared hit=14475817 read=17
26.          

SubPlan (for Index Scan)

27. 165.921 165.921 ↑ 1.0 1 55,307

Index Scan using qsys_mut_lan_00001 on produit.mut_lan a (cost=0.28..2.50 rows=1 width=27) (actual time=0.003..0.003 rows=1 loops=55,307)

  • Output: a.nom_mut
  • Index Cond: ((a.mut_id = mut_dossier.mut_id) AND ((a.lan)::text = 'FR'::text))
  • Buffers: shared hit=165914 read=7
28. 276.535 276.535 ↑ 1.0 1 55,307

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.005..0.005 rows=1 loops=55,307)

  • Output: a_1.texte
  • Index Cond: (((mut_dossier.statut)::text = (a_1.code_cle)::text) AND ((a_1.code_type)::text = 'TypeStatutDossier'::text))
  • Filter: ((a_1.codet_langue)::text = 'FR'::text)
  • Buffers: shared hit=165919 read=2
29. 2,177.992 8,711.968 ↑ 1.0 1 2,177,992

Result (cost=1.87..1.88 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2,177,992)

  • Output: $7
  • Buffers: shared hit=11866652 read=8
30.          

Initplan (for Result)

31. 0.000 6,533.976 ↑ 1.0 1 2,177,992

Limit (cost=0.43..1.87 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=2,177,992)

  • Output: a_6.no_gen_dossier
  • Buffers: shared hit=11866652 read=8
32. 6,533.976 6,533.976 ↑ 30.0 1 2,177,992

Index Scan Backward using idx_mut_dossier_dossier_id_no_gen_dossier on iliade.mut_dossier a_6 (cost=0.43..43.70 rows=30 width=4) (actual time=0.003..0.003 rows=1 loops=2,177,992)

  • 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.dte_mut <= '2019-12-31'::date) AND ((a_6.etat)::text = 'VALI'::text))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=11866652 read=8
33. 1,760.607 6,766.411 ↓ 1.0 4,781,167 1

Hash (cost=240,515.84..240,515.84 rows=4,770,284 width=17) (actual time=6,766.411..6,766.411 rows=4,781,167 loops=1)

  • Output: dossier_gen.dte_demission, dossier_gen.conge, dossier_gen.dte_deb_conge_effectif, dossier_gen.dossier_gen_id, dossier_gen.mut_dossier_id
  • Buckets: 8388608 Batches: 1 Memory Usage: 272424kB
  • Buffers: shared read=192813
34. 5,005.804 5,005.804 ↓ 1.0 4,781,167 1

Seq Scan on iliade.dossier_gen (cost=0.00..240,515.84 rows=4,770,284 width=17) (actual time=3.005..5,005.804 rows=4,781,167 loops=1)

  • Output: dossier_gen.dte_demission, dossier_gen.conge, dossier_gen.dte_deb_conge_effectif, dossier_gen.dossier_gen_id, dossier_gen.mut_dossier_id
  • Buffers: shared read=192813
35.          

SubPlan (for Hash Join)

36. 0.000 581.948 ↓ 10.2 128,869 1

Gather (cost=1,000.87..95,644.87 rows=12,625 width=4) (actual time=39.908..581.948 rows=128,869 loops=1)

  • Output: dossier_gen_2.dossier_gen_id
  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=575120 read=9129
37. 1.879 589.167 ↓ 7.9 32,217 4 / 4

Nested Loop (cost=0.86..93,382.37 rows=4,073 width=4) (actual time=32.639..589.167 rows=32,217 loops=4)

  • Output: dossier_gen_2.dossier_gen_id
  • Inner Unique: true
  • Buffers: shared hit=575120 read=9129
  • Worker 0: actual time=30.534..601.335 rows=34147 loops=1
  • Buffers: shared hit=149757 read=2343
  • Worker 1: actual time=30.824..601.908 rows=32124 loops=1
  • Buffers: shared hit=142876 read=2230
  • Worker 2: actual time=32.037..602.252 rows=33447 loops=1
  • Buffers: shared hit=149985 read=2445
38. 265.116 265.116 ↓ 7.9 32,217 4 / 4

Parallel Index Scan using idx_partie_dossier_type_partie_dossier_id on iliade.partie_dossier partie_dossier_2 (cost=0.43..88,524.21 rows=4,073 width=4) (actual time=31.512..265.116 rows=32,217 loops=4)

  • Output: partie_dossier_2.partie_dossier_id, partie_dossier_2.partie_liee_partie_dossier_id, partie_dossier_2.dossier_gen_id, partie_dossier_2.partie_dossier_orig_id, partie_dossier_2.dossier_id, partie_dossier_2.no_gen_dossier, partie_dossier_2.etat, partie_dossier_2.type_partie, partie_dossier_2.type_deces, partie_dossier_2.dte_deb_partie, partie_dossier_2.dte_effet_idx, partie_dossier_2.avoir_50_ans, partie_dossier_2.type_inva, partie_dossier_2.dte_deb_inca_gain, partie_dossier_2.age_deb_inca_gain, partie_dossier_2.dte_fin_inca_gain, partie_dossier_2.dte_reexamen, partie_dossier_2.tx_inca_gain_reel, partie_dossier_2.tx_inca_gain_calc, partie_dossier_2.lib_passee, partie_dossier_2.inva_passee, partie_dossier_2.rechute, partie_dossier_2.dte_rechute, partie_dossier_2.faute_grave, partie_dossier_2.tx_reduc_faute_grave, partie_dossier_2.reticence, partie_dossier_2.accident, partie_dossier_2.cause_medicale_sinistre, partie_dossier_2.cause_risque_special_sinistre, partie_dossier_2.sal_actif_reparti, partie_dossier_2.delai_attente_lib_consomme, partie_dossier_2.delai_attente_ri_consomme, partie_dossier_2.dte_age_terme, partie_dossier_2.dte_deb_mise_en_pension, partie_dossier_2.tx_retraite, partie_dossier_2.suri_avec_pe, partie_dossier_2.suri_avec_idx, partie_dossier_2.dte_revision_ai, partie_dossier_2.dte_revision_caisse_pension, partie_dossier_2.tx_sal_suri, partie_dossier_2.tot_rev_recu, partie_dossier_2.reprise_ancienne_ip, partie_dossier_2.deb_paie_effectif, partie_dossier_2.sal_presume_perdu, partie_dossier_2.usr_log_i, partie_dossier_2.dte_log_i, partie_dossier_2.usr_log_u, partie_dossier_2.dte_log_u, partie_dossier_2.audit, partie_dossier_2.dte_echeance_partie, partie_dossier_2.calc_av_terme, partie_dossier_2.mnt_cpt_droits_transitoires, partie_dossier_2.int_cpt_droits_transitoires, partie_dossier_2.somme_risquee_reass_deces, partie_dossier_2.somme_risquee_reass_inva, partie_dossier_2.annonce_reass_deces, partie_dossier_2.annonce_reass_inva, partie_dossier_2.annonce_reass_deces_facultatif, partie_dossier_2.annonce_reass_inva_facultatif, partie_dossier_2.annonce_reass_deces_sinistre, partie_dossier_2.annonce_reass_inva_sinistre, partie_dossier_2.reass_deces, partie_dossier_2.reass_inva, partie_dossier_2.inva_proportion_reassuree, partie_dossier_2.fo_reass_id, partie_dossier_2.avoir_50_ans_lpp, partie_dossier_2.annonce_deces, partie_dossier_2.annonce_invalidite
  • Index Cond: ((partie_dossier_2.type_partie)::text = 'PEIN'::text)
  • Filter: (((partie_dossier_2.etat)::text = 'VALI'::text) AND ((partie_dossier_2.type_inva)::text = 'TEMP'::text))
  • Rows Removed by Filter: 76337
  • Buffers: shared hit=180056 read=1709
  • Worker 0: actual time=29.727..267.590 rows=34147 loops=1
  • Buffers: shared hit=45050 read=427
  • Worker 1: actual time=29.720..272.245 rows=32124 loops=1
  • Buffers: shared hit=44318 read=426
  • Worker 2: actual time=30.079..274.002 rows=33447 loops=1
  • Buffers: shared hit=47313 read=451
39. 322.173 322.173 ↑ 1.0 1 128,869 / 4

Index Only Scan using p_dossier_gen on iliade.dossier_gen dossier_gen_2 (cost=0.43..1.19 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=128,869)

  • Output: dossier_gen_2.dossier_gen_id
  • Index Cond: (dossier_gen_2.dossier_gen_id = partie_dossier_2.dossier_gen_id)
  • Heap Fetches: 0
  • Buffers: shared hit=395064 read=7420
  • Worker 0: actual time=0.009..0.009 rows=1 loops=34147
  • Buffers: shared hit=104707 read=1916
  • Worker 1: actual time=0.010..0.010 rows=1 loops=32124
  • Buffers: shared hit=98558 read=1804
  • Worker 2: actual time=0.009..0.009 rows=1 loops=33447
  • Buffers: shared hit=102672 read=1994
40. 456.852 647.207 ↑ 2.0 1 38,071

Index Scan using d_g_in_pi_d_fk on iliade.partie_dossier (cost=0.43..11.78 rows=2 width=1,050) (actual time=0.016..0.017 rows=1 loops=38,071)

  • Output: partie_dossier.partie_dossier_id, partie_dossier.type_partie, partie_dossier.type_inva, partie_dossier.dossier_gen_id, (SubPlan 5), (SubPlan 6)
  • 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 = 'ACTI'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=265942 read=816
41.          

SubPlan (for Index Scan)

42. 190.355 190.355 ↑ 1.0 1 38,071

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.005..0.005 rows=1 loops=38,071)

  • 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=114212 read=1
43. 0.000 0.000 ↓ 0.0 0 38,071

Index Scan using idx_code_texte_code_cle_code_type on produit.code_texte a_5 (cost=0.28..2.50 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=38,071)

  • Output: a_5.texte
  • Index Cond: (((partie_dossier.type_inva)::text = (a_5.code_cle)::text) AND ((a_5.code_type)::text = 'TypeInvalidite'::text))
  • Filter: ((a_5.codet_langue)::text = 'FR'::text)
44. 2,398.473 2,398.473 ↑ 8.0 1 38,071

Index Scan using idx4fk_f_pi_in_p_pi on iliade.plan_partie (cost=0.43..10.50 rows=8 width=8) (actual time=0.061..0.063 rows=1 loops=38,071)

  • 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: 2
  • Buffers: shared hit=144229 read=12941
45. 2,398.473 2,398.473 ↑ 4.5 2 38,071

Index Scan using idx4fk_f_p_pi_in_ptc_p on iliade.part_comm_plan_partie (cost=0.44..5.10 rows=9 width=30) (actual time=0.061..0.063 rows=2 loops=38,071)

  • Output: part_comm_plan_partie.part_comm_plan_partie_id, part_comm_plan_partie.part_comm_dossier_id, part_comm_plan_partie.plan_partie_id, part_comm_plan_partie.nom_plan, part_comm_plan_partie.dossier_id, part_comm_plan_partie.no_gen_dossier, part_comm_plan_partie.nom_comm, part_comm_plan_partie.sal_avs, part_comm_plan_partie.sal_cotisant, part_comm_plan_partie.sal_avs_fige, part_comm_plan_partie.sal_cotisant_fige, part_comm_plan_partie.sal_base, part_comm_plan_partie.supplement_assure, part_comm_plan_partie.provision_13e_sal, part_comm_plan_partie.part_sal_cotisant_act, part_comm_plan_partie.part_sal_cotisant_maintien_deg, part_comm_plan_partie.part_sal_cotisant_maintien_sal, part_comm_plan_partie.part_deduc_coord, part_comm_plan_partie.part_sal_cotisant_act_preced, part_comm_plan_partie.deg_ass, part_comm_plan_partie.tx_maintenu, part_comm_plan_partie.tx_travaille, part_comm_plan_partie.tx_travaille_precedent, part_comm_plan_partie.tx_maintenu_precedent, part_comm_plan_partie.usr_log_i, part_comm_plan_partie.dte_log_i, part_comm_plan_partie.usr_log_u, part_comm_plan_partie.dte_log_u, part_comm_plan_partie.audit, part_comm_plan_partie.bonus
  • Index Cond: (part_comm_plan_partie.plan_partie_id = plan_partie.plan_partie_id)
  • Buffers: shared hit=140111 read=13833
46. 1,160.280 1,559.140 ↑ 1.0 1 77,957

Index Scan using p_part_comm_d on iliade.part_comm_dossier (cost=0.43..6.36 rows=1 width=1,062) (actual time=0.020..0.020 rows=1 loops=77,957)

  • Output: part_comm_dossier.part_comm_dossier_id, part_comm_dossier.type_part_comm, part_comm_dossier.type_employeur, part_comm_dossier.numero_ub, part_comm_dossier.numero_decompte, part_comm_dossier.type_sal, part_comm_dossier.dossier_gen_id, (SubPlan 3), (SubPlan 4)
  • Index Cond: (part_comm_dossier.part_comm_dossier_id = part_comm_plan_partie.part_comm_dossier_id)
  • Filter: ((part_comm_dossier.type_part_comm)::text = 'UB'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=544256 read=7205
47.          

SubPlan (for Index Scan)

48. 199.430 199.430 ↑ 1.0 1 39,886

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.005..0.005 rows=1 loops=39,886)

  • Output: a_2.texte
  • Index Cond: (((part_comm_dossier.type_part_comm)::text = (a_2.code_cle)::text) AND ((a_2.code_type)::text = 'TypePartenaireCommDossier'::text))
  • Filter: ((a_2.codet_langue)::text = 'FR'::text)
  • Buffers: shared hit=119658
49. 199.430 199.430 ↑ 1.0 1 39,886

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.005..0.005 rows=1 loops=39,886)

  • Output: a_3.texte
  • Index Cond: (((part_comm_dossier.type_sal)::text = (a_3.code_cle)::text) AND ((a_3.code_type)::text = 'TypeSalairePCD'::text))
  • Filter: ((a_3.codet_langue)::text = 'FR'::text)
  • Buffers: shared hit=119654 read=4
50. 319.088 319.088 ↑ 5.0 1 39,886

Index Scan using d_g_in_pt_d_fk on iliade.part_dossier a1_part_dossier_affi (cost=0.43..7.47 rows=5 width=8) (actual time=0.007..0.008 rows=1 loops=39,886)

  • 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: 2
  • Buffers: shared hit=161555 read=11
51. 5,544.154 5,544.154 ↓ 11.1 156 39,886

Index Scan using c_in_ub_c_fk on iliade.unite_budgetaire_contrat (cost=0.27..9.98 rows=14 width=63) (actual time=0.046..0.139 rows=156 loops=39,886)

  • Output: unite_budgetaire_contrat.unite_budgetaire_contrat_id, unite_budgetaire_contrat.contrat_id, unite_budgetaire_contrat.coordonnees_ub_part_id, unite_budgetaire_contrat.numero_ub, unite_budgetaire_contrat.type_ub, unite_budgetaire_contrat.desc_courte, unite_budgetaire_contrat.desc_longue, unite_budgetaire_contrat.dte_deb, unite_budgetaire_contrat.dte_fin, unite_budgetaire_contrat.usr_log_i, unite_budgetaire_contrat.dte_log_i, unite_budgetaire_contrat.usr_log_u, unite_budgetaire_contrat.dte_log_u, unite_budgetaire_contrat.dte_entree, unite_budgetaire_contrat.quotite_apport_initial, unite_budgetaire_contrat.freq_paie_coti, unite_budgetaire_contrat.dte_derniere_fact, unite_budgetaire_contrat.type_employeur, unite_budgetaire_contrat.blocage_fact, unite_budgetaire_contrat.raison_blocage_fact, unite_budgetaire_contrat.visa_blocage_fact, unite_budgetaire_contrat.dte_operation_blocage_fact, unite_budgetaire_contrat.fonds_gar, unite_budgetaire_contrat.objet_facturable_cree, unite_budgetaire_contrat.ged_copie_affiliation, unite_budgetaire_contrat.ged_copie_retraite, unite_budgetaire_contrat.ged_copie_changement_plan, unite_budgetaire_contrat.envoi_liste_annuelle_salaires, unite_budgetaire_contrat.hospices, unite_budgetaire_contrat.ged_no_ub_copie_supplementaire, unite_budgetaire_contrat.dossier_ged_cree, unite_budgetaire_contrat.envoi_bordereau_fact, unite_budgetaire_contrat.audit, unite_budgetaire_contrat.impression_documents_facturation, unite_budgetaire_contrat.type_affiliation, unite_budgetaire_contrat.type_affiliation_description, unite_budgetaire_contrat.blocage_extranet
  • Index Cond: (unite_budgetaire_contrat.contrat_id = dossier.contrat_id)
  • Filter: ((unite_budgetaire_contrat.type_ub)::text = 'CORR'::text)
  • Rows Removed by Filter: 183
  • Buffers: shared hit=877472 read=20
52. 639.261 639.261 ↑ 1.0 345,227 1

Index Only Scan using bi_partenaire_uk1 on biinfra.bi_partenaire a1_bi_partenaire_affi (cost=0.42..8,597.40 rows=345,872 width=4) (actual time=0.053..639.261 rows=345,227 loops=1)

  • Output: a1_bi_partenaire_affi.numero_contact
  • Heap Fetches: 0
  • Buffers: shared hit=803 read=1735 written=12
53. 742.822 2,375.522 ↓ 3,663.7 6,528,768 1

Sort (cost=966,569.45..966,573.90 rows=1,782 width=73) (actual time=1,854.143..2,375.522 rows=6,528,768 loops=1)

  • Output: 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.numero_contact
  • Sort Key: a1_adresses_affi.numero_contact
  • Sort Method: quicksort Memory: 61629kB
  • Buffers: shared hit=1433204 read=14445
54. 563.779 1,632.700 ↓ 193.7 345,113 1

Seq Scan on partner.adresse_formatee a1_adresses_affi (cost=0.00..966,473.22 rows=1,782 width=73) (actual time=1.157..1,632.700 rows=345,113 loops=1)

  • Output: 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.numero_contact
  • Filter: (CASE WHEN ((a1_adresses_affi.type_adresse)::text = 'BASE'::text) THEN 0 WHEN ((a1_adresses_affi.type_adresse)::text = 'VIE_COLLECTIVE'::text) THEN 1 ELSE NULL::integer END = (SubPlan 9))
  • Rows Removed by Filter: 11194
  • Buffers: shared hit=1433204 read=14445
55.          

SubPlan (for Seq Scan)

56. 0.000 1,068.921 ↑ 1.0 1 356,307

Aggregate (cost=2.65..2.66 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=356,307)

  • 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=1431483 read=2814
57. 1,068.921 1,068.921 ↑ 1.0 1 356,307

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.002..0.003 rows=1 loops=356,307)

  • 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_affi.numero_contact = b.numero_contact)
  • Buffers: shared hit=1431483 read=2814
58. 12,444.432 12,444.432 ↓ 2.0 2 6,222,216

Index Scan using ub_c_in_ub_par_fk on iliade.ub_par (cost=0.28..1.01 rows=1 width=20) (actual time=0.001..0.002 rows=2 loops=6,222,216)

  • Output: ub_par.ub_par_id, ub_par.unite_budgetaire_contrat_id, ub_par.contrat_id, ub_par.defaut, ub_par.numero_ub_detail_deb, ub_par.numero_ub_detail_fin, ub_par.dte_deb, ub_par.dte_fin, ub_par.usr_log_i, ub_par.dte_log_i, ub_par.usr_log_u, ub_par.dte_log_u, ub_par.audit
  • Index Cond: (ub_par.unite_budgetaire_contrat_id = unite_budgetaire_contrat.unite_budgetaire_contrat_id)
  • Buffers: shared hit=20461510 read=8
59. 25.986 30,757.582 ↓ 18,964.0 18,964 1

GroupAggregate (cost=1,699,361.38..1,699,376.53 rows=1 width=3,093) (actual time=30,728.993..30,757.582 rows=18,964 loops=1)

  • Output: soc_1.soc_id, dossier_1.numero_dossier, dossier_1.dossier_id, mut_dossier_1.no_gen_dossier, (replace(((((btrim((a1_part_affi_1.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_part_affi_1.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_part_affi_1.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), a1_part_affi_1.numero_contact, a1_part_affi_1.dte_naissance, a1_part_affi_1.sexe, a1_adresses_affi_1.ligne_01, a1_adresses_affi_1.ligne_02, a1_adresses_affi_1.ligne_03, a1_adresses_affi_1.ligne_04, a1_adresses_affi_1.ligne_05, a1_adresses_affi_1.ligne_06, a1_adresses_affi_1.ligne_07, a1_adresses_affi_1.ligne_08, a1_adresses_affi_1.ligne_09, a1_adresses_affi_1.ligne_10, mut_dossier_1.dte_mut, mut_dossier_1.mut_id, ((SubPlan 11)), mut_dossier_1.statut, ((SubPlan 12)), dossier_gen_1.dte_demission, ((dossier_gen_1.conge)::integer), dossier_gen_1.dte_deb_conge_effectif, part_comm_dossier_1.part_comm_dossier_id, part_comm_dossier_1.type_part_comm, ((SubPlan 13)), part_comm_dossier_1.type_employeur, part_comm_dossier_1.numero_ub, part_comm_dossier_1.numero_decompte, part_comm_dossier_1.type_sal, ((SubPlan 14)), partie_dossier_1.partie_dossier_id, partie_dossier_1.type_partie, ((SubPlan 15)), partie_dossier_1.type_inva, ((SubPlan 16)), part_comm_plan_partie_1.part_comm_plan_partie_id, part_comm_plan_partie_1.tx_travaille, sum(COALESCE(part_comm_plan_partie_1.sal_avs, '0'::numeric)), sum(COALESCE(part_comm_plan_partie_1.part_sal_cotisant_act, '0'::numeric)), prod_1.pro_id, soc_1.desc_courte, prod_1.desc_courte
  • Group Key: soc_1.soc_id, dossier_1.dossier_id, mut_dossier_1.no_gen_dossier, (replace(((((btrim((a1_part_affi_1.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_part_affi_1.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_part_affi_1.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), a1_part_affi_1.numero_contact, a1_part_affi_1.dte_naissance, a1_part_affi_1.sexe, a1_adresses_affi_1.ligne_01, a1_adresses_affi_1.ligne_02, a1_adresses_affi_1.ligne_03, a1_adresses_affi_1.ligne_04, a1_adresses_affi_1.ligne_05, a1_adresses_affi_1.ligne_06, a1_adresses_affi_1.ligne_07, a1_adresses_affi_1.ligne_08, a1_adresses_affi_1.ligne_09, a1_adresses_affi_1.ligne_10, mut_dossier_1.dte_mut, mut_dossier_1.mut_id, ((SubPlan 11)), mut_dossier_1.statut, ((SubPlan 12)), dossier_gen_1.dte_demission, ((dossier_gen_1.conge)::integer), dossier_gen_1.dte_deb_conge_effectif, part_comm_dossier_1.part_comm_dossier_id, ((SubPlan 13)), ((SubPlan 14)), partie_dossier_1.partie_dossier_id, ((SubPlan 15)), ((SubPlan 16)), part_comm_plan_partie_1.part_comm_plan_partie_id, prod_1.pro_id
  • Buffers: shared hit=4478293 read=392416
60. 52.731 30,731.596 ↓ 18,964.0 18,964 1

Sort (cost=1,699,361.38..1,699,361.38 rows=1 width=3,042) (actual time=30,728.963..30,731.596 rows=18,964 loops=1)

  • Output: soc_1.soc_id, dossier_1.dossier_id, mut_dossier_1.no_gen_dossier, (replace(((((btrim((a1_part_affi_1.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_part_affi_1.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_part_affi_1.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), a1_part_affi_1.numero_contact, a1_part_affi_1.dte_naissance, a1_part_affi_1.sexe, a1_adresses_affi_1.ligne_01, a1_adresses_affi_1.ligne_02, a1_adresses_affi_1.ligne_03, a1_adresses_affi_1.ligne_04, a1_adresses_affi_1.ligne_05, a1_adresses_affi_1.ligne_06, a1_adresses_affi_1.ligne_07, a1_adresses_affi_1.ligne_08, a1_adresses_affi_1.ligne_09, a1_adresses_affi_1.ligne_10, mut_dossier_1.dte_mut, mut_dossier_1.mut_id, ((SubPlan 11)), mut_dossier_1.statut, ((SubPlan 12)), dossier_gen_1.dte_demission, ((dossier_gen_1.conge)::integer), dossier_gen_1.dte_deb_conge_effectif, part_comm_dossier_1.part_comm_dossier_id, ((SubPlan 13)), ((SubPlan 14)), partie_dossier_1.partie_dossier_id, ((SubPlan 15)), ((SubPlan 16)), part_comm_plan_partie_1.part_comm_plan_partie_id, prod_1.pro_id, dossier_1.numero_dossier, part_comm_dossier_1.type_part_comm, part_comm_dossier_1.type_employeur, part_comm_dossier_1.numero_ub, part_comm_dossier_1.numero_decompte, part_comm_dossier_1.type_sal, partie_dossier_1.type_partie, partie_dossier_1.type_inva, part_comm_plan_partie_1.tx_travaille, part_comm_plan_partie_1.sal_avs, part_comm_plan_partie_1.part_sal_cotisant_act, soc_1.desc_courte, prod_1.desc_courte
  • Sort Key: dossier_1.dossier_id, mut_dossier_1.no_gen_dossier, (replace(((((btrim((a1_part_affi_1.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_part_affi_1.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_part_affi_1.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text)), a1_part_affi_1.numero_contact, a1_part_affi_1.dte_naissance, a1_part_affi_1.sexe, a1_adresses_affi_1.ligne_01, a1_adresses_affi_1.ligne_02, a1_adresses_affi_1.ligne_03, a1_adresses_affi_1.ligne_04, a1_adresses_affi_1.ligne_05, a1_adresses_affi_1.ligne_06, a1_adresses_affi_1.ligne_07, a1_adresses_affi_1.ligne_08, a1_adresses_affi_1.ligne_09, a1_adresses_affi_1.ligne_10, mut_dossier_1.dte_mut, mut_dossier_1.mut_id, ((SubPlan 11)), ((SubPlan 12)), dossier_gen_1.dte_demission, ((dossier_gen_1.conge)::integer), dossier_gen_1.dte_deb_conge_effectif, part_comm_dossier_1.part_comm_dossier_id, ((SubPlan 13)), ((SubPlan 14)), partie_dossier_1.partie_dossier_id, ((SubPlan 15)), ((SubPlan 16)), part_comm_plan_partie_1.part_comm_plan_partie_id
  • Sort Method: quicksort Memory: 9100kB
  • Buffers: shared hit=4478293 read=392416
61. 102.713 30,678.865 ↓ 18,964.0 18,964 1

Nested Loop (cost=1,436,245.49..1,699,361.37 rows=1 width=3,042) (actual time=21,936.417..30,678.865 rows=18,964 loops=1)

  • Output: soc_1.soc_id, dossier_1.dossier_id, mut_dossier_1.no_gen_dossier, replace(((((btrim((a1_part_affi_1.nom_ou_raison_sociale)::text) || ' '::text) || btrim((COALESCE(a1_part_affi_1.prenom, ' '::character varying))::text)) || ' '::text) || btrim((COALESCE(a1_part_affi_1.nom_complementaire, ' '::character varying))::text)), ' '::text, ' '::text), a1_part_affi_1.numero_contact, a1_part_affi_1.dte_naissance, a1_part_affi_1.sexe, a1_adresses_affi_1.ligne_01, a1_adresses_affi_1.ligne_02, a1_adresses_affi_1.ligne_03, a1_adresses_affi_1.ligne_04, a1_adresses_affi_1.ligne_05, a1_adresses_affi_1.ligne_06, a1_adresses_affi_1.ligne_07, a1_adresses_affi_1.ligne_08, a1_adresses_affi_1.ligne_09, a1_adresses_affi_1.ligne_10, mut_dossier_1.dte_mut, mut_dossier_1.mut_id, (SubPlan 11), mut_dossier_1.statut, (SubPlan 12), dossier_gen_1.dte_demission, (dossier_gen_1.conge)::integer, dossier_gen_1.dte_deb_conge_effectif, part_comm_dossier_1.part_comm_dossier_id, (SubPlan 13), (SubPlan 14), partie_dossier_1.partie_dossier_id, (SubPlan 15), (SubPlan 16), part_comm_plan_partie_1.part_comm_plan_partie_id, prod_1.pro_id, dossier_1.numero_dossier, part_comm_dossier_1.type_part_comm, part_comm_dossier_1.type_employeur, part_comm_dossier_1.numero_ub, part_comm_dossier_1.numero_decompte, part_comm_dossier_1.type_sal, partie_dossier_1.type_partie, partie_dossier_1.type_inva, part_comm_plan_partie_1.tx_travaille, part_comm_plan_partie_1.sal_avs, part_comm_plan_partie_1.part_sal_cotisant_act, soc_1.desc_courte, prod_1.desc_courte
  • Inner Unique: true
  • Buffers: shared hit=4478293 read=392416
62. 16.046 30,064.124 ↓ 18,964.0 18,964 1

Nested Loop (cost=1,436,245.06..1,699,345.44 rows=1 width=231) (actual time=21,934.448..30,064.124 rows=18,964 loops=1)

  • Output: part_comm_plan_partie_1.part_comm_plan_partie_id, part_comm_plan_partie_1.tx_travaille, part_comm_plan_partie_1.sal_avs, part_comm_plan_partie_1.part_sal_cotisant_act, partie_dossier_1.partie_dossier_id, partie_dossier_1.type_partie, partie_dossier_1.type_inva, dossier_gen_1.dte_demission, dossier_gen_1.conge, dossier_gen_1.dte_deb_conge_effectif, mut_dossier_1.no_gen_dossier, mut_dossier_1.dte_mut, mut_dossier_1.mut_id, mut_dossier_1.statut, dossier_1.numero_dossier, dossier_1.dossier_id, prod_1.pro_id, prod_1.desc_courte, soc_1.soc_id, soc_1.desc_courte, part_comm_dossier_1.part_comm_dossier_id, part_comm_dossier_1.type_part_comm, part_comm_dossier_1.type_employeur, part_comm_dossier_1.numero_ub, part_comm_dossier_1.numero_decompte, part_comm_dossier_1.type_sal, a1_part_affi_1.nom_ou_raison_sociale, a1_part_affi_1.prenom, a1_part_affi_1.nom_complementaire, a1_part_affi_1.numero_contact, a1_part_affi_1.dte_naissance, a1_part_affi_1.sexe, a1_adresses_affi_1.ligne_01, a1_adresses_affi_1.ligne_02, a1_adresses_affi_1.ligne_03, a1_adresses_affi_1.ligne_04, a1_adresses_affi_1.ligne_05, a1_adresses_affi_1.ligne_06, a1_adresses_affi_1.ligne_07, a1_adresses_affi_1.ligne_08, a1_adresses_affi_1.ligne_09, a1_adresses_affi_1.ligne_10, a1_adresses_affi_1.numero_contact
  • Buffers: shared hit=4182516 read=391027
63. 11.892 29,156.770 ↓ 18,964.0 18,964 1

Nested Loop (cost=1,436,244.64..1,699,341.43 rows=1 width=158) (actual time=21,932.079..29,156.770 rows=18,964 loops=1)

  • Output: part_comm_plan_partie_1.part_comm_plan_partie_id, part_comm_plan_partie_1.tx_travaille, part_comm_plan_partie_1.sal_avs, part_comm_plan_partie_1.part_sal_cotisant_act, partie_dossier_1.partie_dossier_id, partie_dossier_1.type_partie, partie_dossier_1.type_inva, dossier_gen_1.dte_demission, dossier_gen_1.conge, dossier_gen_1.dte_deb_conge_effectif, mut_dossier_1.no_gen_dossier, mut_dossier_1.dte_mut, mut_dossier_1.mut_id, mut_dossier_1.statut, dossier_1.numero_dossier, dossier_1.dossier_id, prod_1.pro_id, prod_1.desc_courte, soc_1.soc_id, soc_1.desc_courte, part_comm_dossier_1.part_comm_dossier_id, part_comm_dossier_1.type_part_comm, part_comm_dossier_1.type_employeur, part_comm_dossier_1.numero_ub, part_comm_dossier_1.numero_decompte, part_comm_dossier_1.type_sal, a1_part_affi_1.nom_ou_raison_sociale, a1_part_affi_1.prenom, a1_part_affi_1.nom_complementaire, a1_part_affi_1.numero_contact, a1_part_affi_1.dte_naissance, a1_part_affi_1.sexe
  • Inner Unique: true
  • Buffers: shared hit=4033822 read=385531
64. 3.239 28,955.238 ↓ 18,964.0 18,964 1

Nested Loop (cost=1,436,244.22..1,699,340.10 rows=1 width=134) (actual time=21,931.381..28,955.238 rows=18,964 loops=1)

  • Output: part_comm_plan_partie_1.part_comm_plan_partie_id, part_comm_plan_partie_1.tx_travaille, part_comm_plan_partie_1.sal_avs, part_comm_plan_partie_1.part_sal_cotisant_act, partie_dossier_1.partie_dossier_id, partie_dossier_1.type_partie, partie_dossier_1.type_inva, dossier_gen_1.dte_demission, dossier_gen_1.conge, dossier_gen_1.dte_deb_conge_effectif, mut_dossier_1.no_gen_dossier, mut_dossier_1.dte_mut, mut_dossier_1.mut_id, mut_dossier_1.statut, dossier_1.numero_dossier, dossier_1.dossier_id, prod_1.pro_id, prod_1.desc_courte, soc_1.soc_id, soc_1.desc_courte, part_comm_dossier_1.part_comm_dossier_id, part_comm_dossier_1.type_part_comm, part_comm_dossier_1.type_employeur, part_comm_dossier_1.numero_ub, part_comm_dossier_1.numero_decompte, part_comm_dossier_1.type_sal, a1_part_dossier_affi_1.part_dossier_part_id
  • Buffers: shared hit=3960981 read=382432
65. 8.652 28,572.719 ↓ 18,964.0 18,964 1

Nested Loop (cost=1,436,243.78..1,699,332.57 rows=1 width=142) (actual time=21,929.017..28,572.719 rows=18,964 loops=1)

  • Output: part_comm_plan_partie_1.part_comm_plan_partie_id, part_comm_plan_partie_1.tx_travaille, part_comm_plan_partie_1.sal_avs, part_comm_plan_partie_1.part_sal_cotisant_act, partie_dossier_1.partie_dossier_id, partie_dossier_1.type_partie, partie_dossier_1.type_inva, partie_dossier_1.dossier_gen_id, dossier_gen_1.dte_demission, dossier_gen_1.conge, dossier_gen_1.dte_deb_conge_effectif, dossier_gen_1.dossier_gen_id, mut_dossier_1.no_gen_dossier, mut_dossier_1.dte_mut, mut_dossier_1.mut_id, mut_dossier_1.statut, dossier_1.numero_dossier, dossier_1.dossier_id, prod_1.pro_id, prod_1.desc_courte, soc_1.soc_id, soc_1.desc_courte, part_comm_dossier_1.part_comm_dossier_id, part_comm_dossier_1.type_part_comm, part_comm_dossier_1.type_employeur, part_comm_dossier_1.numero_ub, part_comm_dossier_1.numero_decompte, part_comm_dossier_1.type_sal, part_comm_dossier_1.dossier_gen_id
  • Inner Unique: true
  • Join Filter: (dossier_gen_1.dossier_gen_id = part_comm_dossier_1.dossier_gen_id)
  • Buffers: shared hit=3887616 read=379194
66. 27.609 28,190.743 ↓ 251.6 26,666 1

Nested Loop (cost=1,436,243.35..1,699,187.66 rows=106 width=116) (actual time=21,925.913..28,190.743 rows=26,666 loops=1)

  • Output: part_comm_plan_partie_1.part_comm_plan_partie_id, part_comm_plan_partie_1.tx_travaille, part_comm_plan_partie_1.sal_avs, part_comm_plan_partie_1.part_sal_cotisant_act, part_comm_plan_partie_1.part_comm_dossier_id, partie_dossier_1.partie_dossier_id, partie_dossier_1.type_partie, partie_dossier_1.type_inva, partie_dossier_1.dossier_gen_id, dossier_gen_1.dte_demission, dossier_gen_1.conge, dossier_gen_1.dte_deb_conge_effectif, dossier_gen_1.dossier_gen_id, mut_dossier_1.no_gen_dossier, mut_dossier_1.dte_mut, mut_dossier_1.mut_id, mut_dossier_1.statut, dossier_1.numero_dossier, dossier_1.dossier_id, prod_1.pro_id, prod_1.desc_courte, soc_1.soc_id, soc_1.desc_courte
  • Buffers: shared hit=3783960 read=376089
67. 5.369 27,594.214 ↓ 249.5 18,964 1

Nested Loop (cost=1,436,242.91..1,698,793.25 rows=76 width=94) (actual time=21,923.082..27,594.214 rows=18,964 loops=1)

  • Output: plan_partie_1.plan_partie_id, partie_dossier_1.partie_dossier_id, partie_dossier_1.type_partie, partie_dossier_1.type_inva, partie_dossier_1.dossier_gen_id, dossier_gen_1.dte_demission, dossier_gen_1.conge, dossier_gen_1.dte_deb_conge_effectif, dossier_gen_1.dossier_gen_id, mut_dossier_1.no_gen_dossier, mut_dossier_1.dte_mut, mut_dossier_1.mut_id, mut_dossier_1.statut, dossier_1.numero_dossier, dossier_1.dossier_id, prod_1.pro_id, prod_1.desc_courte, soc_1.soc_id, soc_1.desc_courte
  • Buffers: shared hit=3713596 read=370190
68. 0.003 0.125 ↑ 1.0 1 1

Nested Loop (cost=0.14..3.46 rows=1 width=34) (actual time=0.118..0.125 rows=1 loops=1)

  • Output: prod_1.pro_id, prod_1.desc_courte, soc_1.soc_id, soc_1.desc_courte
  • Buffers: shared hit=3
69. 0.091 0.091 ↑ 1.0 1 1

Index Scan using p_pro on produit.prod prod_1 (cost=0.14..2.37 rows=1 width=27) (actual time=0.087..0.091 rows=1 loops=1)

  • Output: prod_1.pro_id, prod_1.fam_id, prod_1.dte_deb_app, prod_1.dte_fin_app, prod_1.dn_soc_id, prod_1.desc_courte, prod_1.usr_log_i, prod_1.dte_log_i, prod_1.usr_log_u, prod_1.dte_log_u, prod_1.default_nbr_bvr, prod_1.ged_definition1, prod_1.ged_definition2, prod_1.dernier_num_police_utilise, prod_1.type_prod, prod_1.ged_type_caisse
  • Index Cond: (prod_1.pro_id = 87)
  • Filter: (prod_1.dn_soc_id = 6)
  • Buffers: shared hit=2
70. 0.031 0.031 ↑ 1.0 1 1

Seq Scan on produit.soc soc_1 (cost=0.00..1.09 rows=1 width=9) (actual time=0.028..0.031 rows=1 loops=1)

  • Output: soc_1.soc_id, soc_1.desc_courte, soc_1.usr_log_i, soc_1.dte_log_i, soc_1.usr_log_u, soc_1.dte_log_u
  • Filter: (soc_1.soc_id = 6)
  • Rows Removed by Filter: 6
  • Buffers: shared hit=1
71. 17.740 27,588.720 ↓ 249.5 18,964 1

Nested Loop (cost=1,436,242.77..1,698,789.03 rows=76 width=64) (actual time=21,922.952..27,588.720 rows=18,964 loops=1)

  • Output: plan_partie_1.plan_partie_id, partie_dossier_1.partie_dossier_id, partie_dossier_1.type_partie, partie_dossier_1.type_inva, partie_dossier_1.dossier_gen_id, dossier_gen_1.dte_demission, dossier_gen_1.conge, dossier_gen_1.dte_deb_conge_effectif, dossier_gen_1.dossier_gen_id, mut_dossier_1.no_gen_dossier, mut_dossier_1.dte_mut, mut_dossier_1.mut_id, mut_dossier_1.statut, dossier_1.numero_dossier, dossier_1.dossier_id, dossier_1.pro_id
  • Buffers: shared hit=3713593 read=370190
72. 23.469 26,869.312 ↓ 220.5 18,964 1

Nested Loop (cost=1,436,242.33..1,697,869.06 rows=86 width=60) (actual time=21,919.574..26,869.312 rows=18,964 loops=1)

  • Output: partie_dossier_1.partie_dossier_id, partie_dossier_1.type_partie, partie_dossier_1.type_inva, partie_dossier_1.dossier_gen_id, dossier_gen_1.dte_demission, dossier_gen_1.conge, dossier_gen_1.dte_deb_conge_effectif, dossier_gen_1.dossier_gen_id, mut_dossier_1.no_gen_dossier, mut_dossier_1.dte_mut, mut_dossier_1.mut_id, mut_dossier_1.statut, dossier_1.numero_dossier, dossier_1.dossier_id, dossier_1.pro_id
  • Inner Unique: true
  • Buffers: shared hit=3642107 read=364073
73. 17.200 26,585.843 ↓ 132.1 32,500 1

Nested Loop (cost=1,436,241.91..1,697,426.92 rows=246 width=52) (actual time=21,527.814..26,585.843 rows=32,500 loops=1)

  • Output: partie_dossier_1.partie_dossier_id, partie_dossier_1.type_partie, partie_dossier_1.type_inva, partie_dossier_1.dossier_gen_id, dossier_gen_1.dte_demission, dossier_gen_1.conge, dossier_gen_1.dte_deb_conge_effectif, dossier_gen_1.dossier_gen_id, mut_dossier_1.no_gen_dossier, mut_dossier_1.dte_mut, mut_dossier_1.mut_id, mut_dossier_1.statut, mut_dossier_1.dossier_id
  • Buffers: shared hit=3515825 read=360213
74. 1,228.467 26,419.463 ↓ 21.4 29,836 1

Hash Join (cost=1,436,241.48..1,694,944.03 rows=1,395 width=34) (actual time=21,526.575..26,419.463 rows=29,836 loops=1)

  • Output: dossier_gen_1.dte_demission, dossier_gen_1.conge, dossier_gen_1.dte_deb_conge_effectif, dossier_gen_1.dossier_gen_id, mut_dossier_1.no_gen_dossier, mut_dossier_1.dte_mut, mut_dossier_1.mut_id, mut_dossier_1.statut, mut_dossier_1.dossier_id
  • Inner Unique: true
  • Hash Cond: (dossier_gen_1.mut_dossier_id = mut_dossier_1.mut_dossier_id)
  • Buffers: shared hit=3397627 read=358716
75. 4,115.496 5,690.039 ↓ 2.0 4,707,877 1

Seq Scan on iliade.dossier_gen dossier_gen_1 (cost=95,676.44..348,117.99 rows=2,385,142 width=17) (actual time=1,622.942..5,690.039 rows=4,707,877 loops=1)

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

SubPlan (for Seq Scan)

77. 0.000 1,574.543 ↓ 10.2 128,869 1

Gather (cost=1,000.87..95,644.87 rows=12,625 width=4) (actual time=482.889..1,574.543 rows=128,869 loops=1)

  • Output: dossier_gen_3.dossier_gen_id
  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=515600 read=68642
78. 16.462 1,576.558 ↓ 7.9 32,217 4 / 4

Nested Loop (cost=0.86..93,382.37 rows=4,073 width=4) (actual time=506.652..1,576.558 rows=32,217 loops=4)

  • Output: dossier_gen_3.dossier_gen_id
  • Inner Unique: true
  • Buffers: shared hit=515600 read=68642
  • Worker 0: actual time=510.785..1586.500 rows=33088 loops=1
  • Buffers: shared hit=132845 read=17497
  • Worker 1: actual time=525.404..1587.674 rows=31292 loops=1
  • Buffers: shared hit=126385 read=17128
  • Worker 2: actual time=510.414..1586.668 rows=33897 loops=1
  • Buffers: shared hit=134326 read=17522
79. 1,270.141 1,270.141 ↓ 7.9 32,217 4 / 4

Parallel Index Scan using idx_partie_dossier_type_partie_dossier_id on iliade.partie_dossier partie_dossier_3 (cost=0.43..88,524.21 rows=4,073 width=4) (actual time=505.170..1,270.141 rows=32,217 loops=4)

  • Output: partie_dossier_3.partie_dossier_id, partie_dossier_3.partie_liee_partie_dossier_id, partie_dossier_3.dossier_gen_id, partie_dossier_3.partie_dossier_orig_id, partie_dossier_3.dossier_id, partie_dossier_3.no_gen_dossier, partie_dossier_3.etat, partie_dossier_3.type_partie, partie_dossier_3.type_deces, partie_dossier_3.dte_deb_partie, partie_dossier_3.dte_effet_idx, partie_dossier_3.avoir_50_ans, partie_dossier_3.type_inva, partie_dossier_3.dte_deb_inca_gain, partie_dossier_3.age_deb_inca_gain, partie_dossier_3.dte_fin_inca_gain, partie_dossier_3.dte_reexamen, partie_dossier_3.tx_inca_gain_reel, partie_dossier_3.tx_inca_gain_calc, partie_dossier_3.lib_passee, partie_dossier_3.inva_passee, partie_dossier_3.rechute, partie_dossier_3.dte_rechute, partie_dossier_3.faute_grave, partie_dossier_3.tx_reduc_faute_grave, partie_dossier_3.reticence, partie_dossier_3.accident, partie_dossier_3.cause_medicale_sinistre, partie_dossier_3.cause_risque_special_sinistre, partie_dossier_3.sal_actif_reparti, partie_dossier_3.delai_attente_lib_consomme, partie_dossier_3.delai_attente_ri_consomme, partie_dossier_3.dte_age_terme, partie_dossier_3.dte_deb_mise_en_pension, partie_dossier_3.tx_retraite, partie_dossier_3.suri_avec_pe, partie_dossier_3.suri_avec_idx, partie_dossier_3.dte_revision_ai, partie_dossier_3.dte_revision_caisse_pension, partie_dossier_3.tx_sal_suri, partie_dossier_3.tot_rev_recu, partie_dossier_3.reprise_ancienne_ip, partie_dossier_3.deb_paie_effectif, partie_dossier_3.sal_presume_perdu, partie_dossier_3.usr_log_i, partie_dossier_3.dte_log_i, partie_dossier_3.usr_log_u, partie_dossier_3.dte_log_u, partie_dossier_3.audit, partie_dossier_3.dte_echeance_partie, partie_dossier_3.calc_av_terme, partie_dossier_3.mnt_cpt_droits_transitoires, partie_dossier_3.int_cpt_droits_transitoires, partie_dossier_3.somme_risquee_reass_deces, partie_dossier_3.somme_risquee_reass_inva, partie_dossier_3.annonce_reass_deces, partie_dossier_3.annonce_reass_inva, partie_dossier_3.annonce_reass_deces_facultatif, partie_dossier_3.annonce_reass_inva_facultatif, partie_dossier_3.annonce_reass_deces_sinistre, partie_dossier_3.annonce_reass_inva_sinistre, partie_dossier_3.reass_deces, partie_dossier_3.reass_inva, partie_dossier_3.inva_proportion_reassuree, partie_dossier_3.fo_reass_id, partie_dossier_3.avoir_50_ans_lpp, partie_dossier_3.annonce_deces, partie_dossier_3.annonce_invalidite
  • Index Cond: ((partie_dossier_3.type_partie)::text = 'PEIN'::text)
  • Filter: (((partie_dossier_3.etat)::text = 'VALI'::text) AND ((partie_dossier_3.type_inva)::text = 'TEMP'::text))
  • Rows Removed by Filter: 76337
  • Buffers: shared hit=120533 read=61217
  • Worker 0: actual time=509.124..1266.771 rows=33088 loops=1
  • Buffers: shared hit=31367 read=15634
  • Worker 1: actual time=525.313..1283.060 rows=31292 loops=1
  • Buffers: shared hit=30395 read=15263
  • Worker 2: actual time=509.469..1274.204 rows=33897 loops=1
  • Buffers: shared hit=30375 read=15616
80. 289.955 289.955 ↑ 1.0 1 128,869 / 4

Index Only Scan using p_dossier_gen on iliade.dossier_gen dossier_gen_3 (cost=0.43..1.19 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=128,869)

  • Output: dossier_gen_3.dossier_gen_id
  • Index Cond: (dossier_gen_3.dossier_gen_id = partie_dossier_3.dossier_gen_id)
  • Heap Fetches: 0
  • Buffers: shared hit=395067 read=7425
  • Worker 0: actual time=0.009..0.009 rows=1 loops=33088
  • Buffers: shared hit=101478 read=1863
  • Worker 1: actual time=0.009..0.009 rows=1 loops=31292
  • Buffers: shared hit=95990 read=1865
  • Worker 2: actual time=0.009..0.009 rows=1 loops=33897
  • Buffers: shared hit=103951 read=1906
81. 11.007 19,500.957 ↓ 10.7 29,975 1

Hash (cost=1,340,529.88..1,340,529.88 rows=2,813 width=25) (actual time=19,500.957..19,500.957 rows=29,975 loops=1)

  • Output: mut_dossier_1.no_gen_dossier, mut_dossier_1.dte_mut, mut_dossier_1.mut_id, mut_dossier_1.statut, mut_dossier_1.mut_dossier_id, mut_dossier_1.dossier_id
  • Buckets: 32768 (originally 4096) Batches: 1 (originally 1) Memory Usage: 2013kB
  • Buffers: shared hit=2882027 read=97261
82. 9,662.562 19,489.950 ↓ 10.7 29,975 1

Index Scan using idx_mut_dossier_statut_no_gen_dossier on iliade.mut_dossier mut_dossier_1 (cost=0.56..1,340,529.88 rows=2,813 width=25) (actual time=3.239..19,489.950 rows=29,975 loops=1)

  • Output: mut_dossier_1.no_gen_dossier, mut_dossier_1.dte_mut, mut_dossier_1.mut_id, mut_dossier_1.statut, mut_dossier_1.mut_dossier_id, mut_dossier_1.dossier_id
  • Index Cond: ((mut_dossier_1.statut)::text = 'PENS'::text)
  • Filter: (mut_dossier_1.no_gen_dossier = (SubPlan 18))
  • Rows Removed by Filter: 515991
  • Buffers: shared hit=2882027 read=97261
83.          

SubPlan (for Index Scan)

84. 0.000 9,827.388 ↑ 1.0 1 545,966

Result (cost=1.87..1.88 rows=1 width=4) (actual time=0.018..0.018 rows=1 loops=545,966)

  • Output: $19
  • Buffers: shared hit=2575421 read=38267
85.          

Initplan (for Result)

86. 545.966 9,827.388 ↑ 1.0 1 545,966

Limit (cost=0.43..1.87 rows=1 width=4) (actual time=0.018..0.018 rows=1 loops=545,966)

  • Output: a_13.no_gen_dossier
  • Buffers: shared hit=2575421 read=38267
87. 9,281.422 9,281.422 ↑ 30.0 1 545,966

Index Scan Backward using idx_mut_dossier_dossier_id_no_gen_dossier on iliade.mut_dossier a_13 (cost=0.43..43.70 rows=30 width=4) (actual time=0.017..0.017 rows=1 loops=545,966)

  • Output: a_13.no_gen_dossier
  • Index Cond: ((mut_dossier_1.dossier_id = a_13.dossier_id) AND (a_13.no_gen_dossier IS NOT NULL))
  • Filter: ((a_13.dte_mut <= '2019-12-31'::date) AND ((a_13.etat)::text = 'VALI'::text))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=2575421 read=38267
88. 149.180 149.180 ↑ 1.0 1 29,836

Index Scan using d_g_in_pi_d_fk on iliade.partie_dossier partie_dossier_1 (cost=0.43..1.77 rows=1 width=18) (actual time=0.005..0.005 rows=1 loops=29,836)

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

Index Scan using p_dossier on iliade.dossier dossier_1 (cost=0.42..1.80 rows=1 width=12) (actual time=0.008..0.008 rows=1 loops=32,500)

  • Output: dossier_1.dossier_id, dossier_1.contrat_id, dossier_1.dossier_parent_id, dossier_1.pro_id, dossier_1.numero_dossier, dossier_1.dte_affiliation, dossier_1.mnt_epa_tot, dossier_1.mnt_epa_min_lpp, dossier_1.dte_blocage_fact, dossier_1.raison_blocage_fact, dossier_1.visa_blocage_fact, dossier_1.dte_operation_blocage_fact, dossier_1.envoi_quest_mise_pension, dossier_1.objet_facturable_cree, dossier_1.deposant_epa, dossier_1.usr_log_i, dossier_1.dte_log_i, dossier_1.usr_log_u, dossier_1.dte_log_u, dossier_1.audit, dossier_1.blocage_extranet, dossier_1.responsable_gest, dossier_1.responsable_gest_medicale, dossier_1.id_mut_blocage_fact, dossier_1.raison_blocage_mutations, dossier_1.visa_blocage_mutations, dossier_1.blocage_mutations, dossier_1.dte_blocage_mutations, dossier_1.business_warning, dossier_1.sexe_affilie, dossier_1.dte_naissance_affilie
  • Index Cond: (dossier_1.dossier_id = mut_dossier_1.dossier_id)
  • Filter: (dossier_1.pro_id = 87)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=126282 read=3860
90. 701.668 701.668 ↑ 8.0 1 18,964

Index Scan using idx4fk_f_pi_in_p_pi on iliade.plan_partie plan_partie_1 (cost=0.43..10.62 rows=8 width=8) (actual time=0.035..0.037 rows=1 loops=18,964)

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

Index Scan using idx4fk_f_p_pi_in_ptc_p on iliade.part_comm_plan_partie part_comm_plan_partie_1 (cost=0.44..5.10 rows=9 width=30) (actual time=0.029..0.030 rows=1 loops=18,964)

  • Output: part_comm_plan_partie_1.part_comm_plan_partie_id, part_comm_plan_partie_1.part_comm_dossier_id, part_comm_plan_partie_1.plan_partie_id, part_comm_plan_partie_1.nom_plan, part_comm_plan_partie_1.dossier_id, part_comm_plan_partie_1.no_gen_dossier, part_comm_plan_partie_1.nom_comm, part_comm_plan_partie_1.sal_avs, part_comm_plan_partie_1.sal_cotisant, part_comm_plan_partie_1.sal_avs_fige, part_comm_plan_partie_1.sal_cotisant_fige, part_comm_plan_partie_1.sal_base, part_comm_plan_partie_1.supplement_assure, part_comm_plan_partie_1.provision_13e_sal, part_comm_plan_partie_1.part_sal_cotisant_act, part_comm_plan_partie_1.part_sal_cotisant_maintien_deg, part_comm_plan_partie_1.part_sal_cotisant_maintien_sal, part_comm_plan_partie_1.part_deduc_coord, part_comm_plan_partie_1.part_sal_cotisant_act_preced, part_comm_plan_partie_1.deg_ass, part_comm_plan_partie_1.tx_maintenu, part_comm_plan_partie_1.tx_travaille, part_comm_plan_partie_1.tx_travaille_precedent, part_comm_plan_partie_1.tx_maintenu_precedent, part_comm_plan_partie_1.usr_log_i, part_comm_plan_partie_1.dte_log_i, part_comm_plan_partie_1.usr_log_u, part_comm_plan_partie_1.dte_log_u, part_comm_plan_partie_1.audit, part_comm_plan_partie_1.bonus
  • Index Cond: (part_comm_plan_partie_1.plan_partie_id = plan_partie_1.plan_partie_id)
  • Buffers: shared hit=70364 read=5899
92. 373.324 373.324 ↑ 1.0 1 26,666

Index Scan using p_part_comm_d on iliade.part_comm_dossier part_comm_dossier_1 (cost=0.43..1.35 rows=1 width=30) (actual time=0.014..0.014 rows=1 loops=26,666)

  • Output: part_comm_dossier_1.part_comm_dossier_id, part_comm_dossier_1.unite_budgetaire_contrat_id, part_comm_dossier_1.categorie_empl_id, part_comm_dossier_1.dossier_gen_id, part_comm_dossier_1.part_comm_dossier_orig_id, part_comm_dossier_1.dossier_id, part_comm_dossier_1.no_gen_dossier, part_comm_dossier_1.numero_ub, part_comm_dossier_1.numero_decompte, part_comm_dossier_1.type_part_comm, part_comm_dossier_1.type_employeur, part_comm_dossier_1.tx_coti_maintien_sal_dossier, part_comm_dossier_1.tx_coti_maintien_deg_dossier, part_comm_dossier_1.maintien_sal_cotisant, part_comm_dossier_1.sal_cotisant_garanti, part_comm_dossier_1.sal_avs, part_comm_dossier_1.sal_avs_precedent, part_comm_dossier_1.type_sal, part_comm_dossier_1.dte_embauche, part_comm_dossier_1.tx_act, part_comm_dossier_1.tx_retraite, part_comm_dossier_1.sal_base, part_comm_dossier_1.supplement_assure, part_comm_dossier_1.provision_13e_sal, part_comm_dossier_1.maintien_coti_conge, part_comm_dossier_1.maintien_deg_ass, part_comm_dossier_1.deg_ass, part_comm_dossier_1.tx_maintenu, part_comm_dossier_1.tx_act_precedent, part_comm_dossier_1.usr_log_i, part_comm_dossier_1.dte_log_i, part_comm_dossier_1.usr_log_u, part_comm_dossier_1.dte_log_u, part_comm_dossier_1.independant, part_comm_dossier_1.rev_trois_ans, part_comm_dossier_1.subside_fonds_gar, part_comm_dossier_1.part_facture_maintien_sal, part_comm_dossier_1.part_facture_maintien_deg, part_comm_dossier_1.audit, part_comm_dossier_1.type_act, part_comm_dossier_1.dte_fin_maintien, part_comm_dossier_1.bonus
  • Index Cond: (part_comm_dossier_1.part_comm_dossier_id = part_comm_plan_partie_1.part_comm_dossier_id)
  • Filter: ((part_comm_dossier_1.type_part_comm)::text = 'EMPL'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=103656 read=3105
93. 379.280 379.280 ↑ 5.0 1 18,964

Index Scan using d_g_in_pt_d_fk on iliade.part_dossier a1_part_dossier_affi_1 (cost=0.43..7.48 rows=5 width=8) (actual time=0.018..0.020 rows=1 loops=18,964)

  • Output: a1_part_dossier_affi_1.part_dossier_id, a1_part_dossier_affi_1.dossier_gen_id, a1_part_dossier_affi_1.part_dossier_part_id, a1_part_dossier_affi_1.dossier_id, a1_part_dossier_affi_1.no_gen_dossier, a1_part_dossier_affi_1.role, a1_part_dossier_affi_1.usr_log_i, a1_part_dossier_affi_1.dte_log_i, a1_part_dossier_affi_1.usr_log_u, a1_part_dossier_affi_1.dte_log_u, a1_part_dossier_affi_1.audit
  • Index Cond: (a1_part_dossier_affi_1.dossier_gen_id = dossier_gen_1.dossier_gen_id)
  • Filter: ((a1_part_dossier_affi_1.role)::text = 'AFFI'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=73365 read=3238
94. 189.640 189.640 ↑ 1.0 1 18,964

Index Scan using p_part on iliade.part a1_part_affi_1 (cost=0.42..1.33 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=18,964)

  • Output: a1_part_affi_1.part_id, a1_part_affi_1.type_part, a1_part_affi_1.nom_ou_raison_sociale, a1_part_affi_1.nom_complementaire, a1_part_affi_1.nom_alliance, a1_part_affi_1.prenom, a1_part_affi_1.dte_naissance, a1_part_affi_1.dte_deces, a1_part_affi_1.sexe, a1_part_affi_1.etat_civil, a1_part_affi_1.no_avs, a1_part_affi_1.langue_correspondance, a1_part_affi_1.usr_log_i, a1_part_affi_1.dte_log_i, a1_part_affi_1.usr_log_u, a1_part_affi_1.dte_log_u, a1_part_affi_1.audit, a1_part_affi_1.visa, a1_part_affi_1.numero_contact
  • Index Cond: (a1_part_affi_1.part_id = a1_part_dossier_affi_1.part_dossier_part_id)
  • Buffers: shared hit=72841 read=3099
95. 813.868 891.308 ↑ 1.0 1 18,964

Index Scan using adresse_formatee_numero_contact_idx on partner.adresse_formatee a1_adresses_affi_1 (cost=0.42..4.00 rows=1 width=73) (actual time=0.047..0.047 rows=1 loops=18,964)

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

SubPlan (for Index Scan)

97. 19.360 77.440 ↑ 1.0 1 19,360

Aggregate (cost=2.65..2.66 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=19,360)

  • Output: max(CASE WHEN ((b_1.type_adresse)::text = 'BASE'::text) THEN 0 WHEN ((b_1.type_adresse)::text = 'VIE_COLLECTIVE'::text) THEN 1 ELSE NULL::integer END)
  • Buffers: shared hit=77914 read=86
98. 58.080 58.080 ↑ 1.0 1 19,360

Index Scan using adresse_formatee_numero_contact_idx on partner.adresse_formatee b_1 (cost=0.42..2.64 rows=1 width=5) (actual time=0.002..0.003 rows=1 loops=19,360)

  • Output: b_1.id, b_1.type_adresse, b_1.adresse_inconnue, b_1.code_canton_etat, b_1.code_iso2_pays, b_1.code_postal, b_1.indice_numero_rue, b_1.langue, b_1.ligne_01, b_1.ligne_02, b_1.ligne_03, b_1.ligne_04, b_1.ligne_05, b_1.ligne_06, b_1.ligne_07, b_1.ligne_08, b_1.ligne_09, b_1.ligne_10, b_1.nom_commune, b_1.nom_district, b_1.nom_localite, b_1.npa_loc_formatee, b_1.numero_rue, b_1.rue, b_1.rue_formatee, b_1.sexe_complement_destinataire, b_1.pas_bella_vita, b_1.pas_correspondance, b_1.version, b_1.usr_log_i, b_1.dte_log_i, b_1.usr_log_u, b_1.dte_log_u, b_1.numero_contact
  • Index Cond: (a1_adresses_affi_1.numero_contact = b_1.numero_contact)
  • Buffers: shared hit=77914 read=86
99. 151.712 151.712 ↑ 1.0 1 18,964

Index Only Scan using bi_partenaire_uk1 on biinfra.bi_partenaire a1_bi_partenaire_affi_1 (cost=0.42..0.89 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=18,964)

  • Output: a1_bi_partenaire_affi_1.numero_contact
  • Index Cond: (a1_bi_partenaire_affi_1.numero_contact = a1_part_affi_1.numero_contact)
  • Heap Fetches: 0
  • Buffers: shared hit=55513 read=1380
100.          

SubPlan (for Nested Loop)

101. 56.892 56.892 ↑ 1.0 1 18,964

Index Scan using qsys_mut_lan_00001 on produit.mut_lan a_7 (cost=0.28..2.50 rows=1 width=27) (actual time=0.003..0.003 rows=1 loops=18,964)

  • Output: a_7.nom_mut
  • Index Cond: ((a_7.mut_id = mut_dossier_1.mut_id) AND ((a_7.lan)::text = 'FR'::text))
  • Buffers: shared hit=56889 read=3
102. 94.820 94.820 ↑ 1.0 1 18,964

Index Scan using idx_code_texte_code_cle_code_type on produit.code_texte a_8 (cost=0.28..2.50 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=18,964)

  • Output: a_8.texte
  • Index Cond: (((mut_dossier_1.statut)::text = (a_8.code_cle)::text) AND ((a_8.code_type)::text = 'TypeStatutDossier'::text))
  • Filter: ((a_8.codet_langue)::text = 'FR'::text)
  • Buffers: shared hit=56892
103. 94.820 94.820 ↑ 1.0 1 18,964

Index Scan using idx_code_texte_code_cle_code_type on produit.code_texte a_9 (cost=0.28..2.50 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=18,964)

  • Output: a_9.texte
  • Index Cond: (((part_comm_dossier_1.type_part_comm)::text = (a_9.code_cle)::text) AND ((a_9.code_type)::text = 'TypePartenaireCommDossier'::text))
  • Filter: ((a_9.codet_langue)::text = 'FR'::text)
  • Buffers: shared hit=56891 read=1
104. 0.000 0.000 ↓ 0.0 0 18,964

Index Scan using idx_code_texte_code_cle_code_type on produit.code_texte a_10 (cost=0.28..2.50 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=18,964)

  • Output: a_10.texte
  • Index Cond: (((part_comm_dossier_1.type_sal)::text = (a_10.code_cle)::text) AND ((a_10.code_type)::text = 'TypeSalairePCD'::text))
  • Filter: ((a_10.codet_langue)::text = 'FR'::text)
  • Buffers: shared hit=2109 read=3
105. 94.820 94.820 ↑ 1.0 1 18,964

Index Scan using idx_code_texte_code_cle_code_type on produit.code_texte a_11 (cost=0.28..2.50 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=18,964)

  • Output: a_11.texte
  • Index Cond: (((a_11.code_cle)::text = (partie_dossier_1.type_partie)::text) AND ((a_11.code_type)::text = 'TypePartieDossier'::text))
  • Filter: ((a_11.codet_langue)::text = 'FR'::text)
  • Buffers: shared hit=56891 read=1
106. 18.964 18.964 ↓ 0.0 0 18,964

Index Scan using idx_code_texte_code_cle_code_type on produit.code_texte a_12 (cost=0.28..2.50 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=18,964)

  • Output: a_12.texte
  • Index Cond: (((partie_dossier_1.type_inva)::text = (a_12.code_cle)::text) AND ((a_12.code_type)::text = 'TypeInvalidite'::text))
  • Filter: ((a_12.codet_langue)::text = 'FR'::text)
  • Buffers: shared hit=10592 read=1
Planning time : 102.782 ms
Execution time : 5,237,391.500 ms