explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7jUh

Settings
# exclusive inclusive rows x rows loops node
1. 0.504 1,758,603.882 ↓ 286.0 286 1

GroupAggregate (cost=323,291.30..323,293.89 rows=1 width=1,252) (actual time=1,758,603.389..1,758,603.882 rows=286 loops=1)

  • Output: soc.soc_id, objet_payable.id, objet_payable.metier_id, objet_payable.numero_metier, objet_payable.numero_contact, a1_bi_partenaire_ben.numero_contact, objet_payable.application, (rtrim(concat(concat(a1_bi_partenaire_ben.nom_raison_sociale, ' '), COALESCE(a1_bi_partenaire_ben.complement_raison_sociale, COALESCE(a1_bi_partenaire_ben.prenom, ''::character varying))))), a1_bi_partenaire_ben.numero_avs_upi, a1_bi_partenaire_ben.date_naissance, retenue.type_retenue, ((SubPlan 1)), prestation_a_payer.etat, sum(retenue.montant_imposable), sum(retenue.montant_retenue), retenue.correctif_arrondi_retenue, montant_a_payer.type_paiement_particulier, retenue.taux, montant_a_payer.date_prevue_paiement, montant_a_payer.date_effective_paiement, prestation_generee.type_extourne, sum(prestation_a_payer.montant_prestation), prestation_a_payer.mode_paiement_interne, ((SubPlan 2))
  • Group Key: soc.soc_id, objet_payable.id, a1_bi_partenaire_ben.numero_contact, (rtrim(concat(concat(a1_bi_partenaire_ben.nom_raison_sociale, ' '), COALESCE(a1_bi_partenaire_ben.complement_raison_sociale, COALESCE(a1_bi_partenaire_ben.prenom, ''::character varying))))), a1_bi_partenaire_ben.numero_avs_upi, a1_bi_partenaire_ben.date_naissance, retenue.type_retenue, ((SubPlan 1)), prestation_a_payer.etat, retenue.correctif_arrondi_retenue, montant_a_payer.type_paiement_particulier, retenue.taux, montant_a_payer.date_prevue_paiement, montant_a_payer.date_effective_paiement, prestation_generee.type_extourne, prestation_a_payer.mode_paiement_interne, ((SubPlan 2))
  • Buffers: shared hit=234639548 read=36383
2. 4.201 1,758,603.378 ↓ 400.0 400 1

Sort (cost=323,291.30..323,291.31 rows=1 width=1,174) (actual time=1,758,603.358..1,758,603.378 rows=400 loops=1)

  • Output: soc.soc_id, objet_payable.id, a1_bi_partenaire_ben.numero_contact, (rtrim(concat(concat(a1_bi_partenaire_ben.nom_raison_sociale, ' '), COALESCE(a1_bi_partenaire_ben.complement_raison_sociale, COALESCE(a1_bi_partenaire_ben.prenom, ''::character varying))))), a1_bi_partenaire_ben.numero_avs_upi, a1_bi_partenaire_ben.date_naissance, retenue.type_retenue, ((SubPlan 1)), prestation_a_payer.etat, retenue.correctif_arrondi_retenue, montant_a_payer.type_paiement_particulier, retenue.taux, montant_a_payer.date_prevue_paiement, montant_a_payer.date_effective_paiement, prestation_generee.type_extourne, prestation_a_payer.mode_paiement_interne, ((SubPlan 2)), objet_payable.metier_id, objet_payable.numero_metier, objet_payable.numero_contact, objet_payable.application, retenue.montant_imposable, retenue.montant_retenue, prestation_a_payer.montant_prestation
  • Sort Key: objet_payable.id, a1_bi_partenaire_ben.numero_contact, (rtrim(concat(concat(a1_bi_partenaire_ben.nom_raison_sociale, ' '), COALESCE(a1_bi_partenaire_ben.complement_raison_sociale, COALESCE(a1_bi_partenaire_ben.prenom, ''::character varying))))), a1_bi_partenaire_ben.numero_avs_upi, a1_bi_partenaire_ben.date_naissance, retenue.type_retenue, ((SubPlan 1)), prestation_a_payer.etat, retenue.correctif_arrondi_retenue, montant_a_payer.type_paiement_particulier, retenue.taux, montant_a_payer.date_prevue_paiement, montant_a_payer.date_effective_paiement, prestation_generee.type_extourne, prestation_a_payer.mode_paiement_interne, ((SubPlan 2))
  • Sort Method: quicksort Memory: 131kB
  • Buffers: shared hit=234639548 read=36383
3. 87,713.121 1,758,599.177 ↓ 400.0 400 1

Nested Loop Semi Join (cost=1,007.95..323,291.29 rows=1 width=1,174) (actual time=47,578.342..1,758,599.177 rows=400 loops=1)

  • Output: soc.soc_id, objet_payable.id, a1_bi_partenaire_ben.numero_contact, rtrim(concat(concat(a1_bi_partenaire_ben.nom_raison_sociale, ' '), COALESCE(a1_bi_partenaire_ben.complement_raison_sociale, COALESCE(a1_bi_partenaire_ben.prenom, ''::character varying)))), a1_bi_partenaire_ben.numero_avs_upi, a1_bi_partenaire_ben.date_naissance, retenue.type_retenue, ((SubPlan 1)), prestation_a_payer.etat, retenue.correctif_arrondi_retenue, montant_a_payer.type_paiement_particulier, retenue.taux, montant_a_payer.date_prevue_paiement, montant_a_payer.date_effective_paiement, prestation_generee.type_extourne, prestation_a_payer.mode_paiement_interne, (SubPlan 2), objet_payable.metier_id, objet_payable.numero_metier, objet_payable.numero_contact, objet_payable.application, retenue.montant_imposable, retenue.montant_retenue, prestation_a_payer.montant_prestation
  • Join Filter: (montant_a_payer.date_effective_paiement = montant_a_payer_1.date_effective_paiement)
  • Rows Removed by Join Filter: 1498973815
  • Buffers: shared hit=234639536 read=36383
4. 8.222 1,922.630 ↓ 2,602.0 2,602 1

Nested Loop (cost=1,007.95..165,219.28 rows=1 width=665) (actual time=69.097..1,922.630 rows=2,602 loops=1)

  • Output: prestation_generee.type_extourne, retenue.type_retenue, retenue.montant_imposable, retenue.montant_retenue, retenue.correctif_arrondi_retenue, retenue.taux, objet_payable.id, objet_payable.metier_id, objet_payable.numero_metier, objet_payable.numero_contact, objet_payable.application, soc.soc_id, prestation_a_payer.etat, prestation_a_payer.montant_prestation, prestation_a_payer.mode_paiement_interne, montant_a_payer.type_paiement_particulier, montant_a_payer.date_prevue_paiement, montant_a_payer.date_effective_paiement, a1_bi_partenaire_ben.numero_contact, a1_bi_partenaire_ben.nom_raison_sociale, a1_bi_partenaire_ben.complement_raison_sociale, a1_bi_partenaire_ben.prenom, a1_bi_partenaire_ben.numero_avs_upi, a1_bi_partenaire_ben.date_naissance, ((SubPlan 1))
  • Inner Unique: true
  • Buffers: shared hit=7574331 read=19044
5. 9.880 1,841.552 ↓ 2,602.0 2,602 1

Nested Loop (cost=1,007.52..165,218.74 rows=1 width=610) (actual time=67.947..1,841.552 rows=2,602 loops=1)

  • Output: prestation_generee.type_extourne, retenue.type_retenue, retenue.montant_imposable, retenue.montant_retenue, retenue.correctif_arrondi_retenue, retenue.taux, objet_payable.id, objet_payable.metier_id, objet_payable.numero_metier, objet_payable.numero_contact, objet_payable.application, soc.soc_id, prestation_a_payer.etat, prestation_a_payer.montant_prestation, prestation_a_payer.mode_paiement_interne, montant_a_payer.type_paiement_particulier, montant_a_payer.date_prevue_paiement, montant_a_payer.date_effective_paiement, beneficiaire.numero_contact, a1_adresses_ben_map.numero_contact, ((SubPlan 1))
  • Buffers: shared hit=7563980 read=18987
6. 8.880 1,769.224 ↓ 21.0 2,602 1

Nested Loop (cost=1,007.10..164,816.99 rows=124 width=606) (actual time=67.875..1,769.224 rows=2,602 loops=1)

  • Output: prestation_generee.type_extourne, retenue.type_retenue, retenue.montant_imposable, retenue.montant_retenue, retenue.correctif_arrondi_retenue, retenue.taux, objet_payable.id, objet_payable.metier_id, objet_payable.numero_metier, objet_payable.numero_contact, objet_payable.application, soc.soc_id, prestation_a_payer.etat, prestation_a_payer.montant_prestation, prestation_a_payer.mode_paiement_interne, montant_a_payer.type_paiement_particulier, montant_a_payer.date_prevue_paiement, montant_a_payer.date_effective_paiement, beneficiaire.numero_contact, ((SubPlan 1))
  • Inner Unique: true
  • Buffers: shared hit=7541305 read=18980
7. 9.438 1,742.130 ↓ 21.0 2,602 1

Nested Loop (cost=1,006.68..164,762.99 rows=124 width=606) (actual time=67.834..1,742.130 rows=2,602 loops=1)

  • Output: prestation_generee.type_extourne, retenue.type_retenue, retenue.montant_imposable, retenue.montant_retenue, retenue.correctif_arrondi_retenue, retenue.taux, objet_payable.id, objet_payable.metier_id, objet_payable.numero_metier, objet_payable.numero_contact, objet_payable.application, soc.soc_id, prestation_a_payer.etat, prestation_a_payer.montant_prestation, prestation_a_payer.mode_paiement_interne, montant_a_payer.type_paiement_particulier, montant_a_payer.date_prevue_paiement, montant_a_payer.date_effective_paiement, montant_a_payer.beneficiaire_id, ((SubPlan 1))
  • Buffers: shared hit=7530891 read=18980
8. 3.103 1,709.274 ↓ 21.0 2,602 1

Nested Loop (cost=1,006.25..164,614.96 rows=124 width=593) (actual time=67.782..1,709.274 rows=2,602 loops=1)

  • Output: prestation_generee.type_extourne, retenue.type_retenue, retenue.montant_imposable, retenue.montant_retenue, retenue.correctif_arrondi_retenue, retenue.taux, objet_payable.id, objet_payable.metier_id, objet_payable.numero_metier, objet_payable.numero_contact, objet_payable.application, soc.soc_id, prestation_a_payer.etat, prestation_a_payer.montant_prestation, prestation_a_payer.mode_paiement_interne, prestation_a_payer.id, ((SubPlan 1))
  • Buffers: shared hit=7520465 read=18980
9. 0.023 0.023 ↑ 1.0 1 1

Seq Scan on produit.soc (cost=0.00..1.09 rows=1 width=4) (actual time=0.021..0.023 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
10. 34.679 1,706.148 ↓ 21.0 2,602 1

Nested Loop Left Join (cost=1,006.25..164,612.63 rows=124 width=591) (actual time=67.758..1,706.148 rows=2,602 loops=1)

  • Output: prestation_generee.type_extourne, retenue.type_retenue, retenue.montant_imposable, retenue.montant_retenue, retenue.correctif_arrondi_retenue, retenue.taux, objet_payable.id, objet_payable.metier_id, objet_payable.numero_metier, objet_payable.numero_contact, objet_payable.application, prod.dn_soc_id, prestation_a_payer.etat, prestation_a_payer.montant_prestation, prestation_a_payer.mode_paiement_interne, prestation_a_payer.id, ((SubPlan 1))
  • Filter: ((((SubPlan 1)))::text = ANY ('{"Impôt source rente cantonal","Impôt source rente"}'::text[]))
  • Rows Removed by Filter: 67641
  • Buffers: shared hit=7520464 read=18980
11. 0.000 1,460.740 ↓ 5.6 70,243 1

Gather (cost=1,005.84..127,818.86 rows=12,447 width=54) (actual time=2.217..1,460.740 rows=70,243 loops=1)

  • Output: prestation_generee.type_extourne, prestation_generee.id, objet_payable.id, objet_payable.metier_id, objet_payable.numero_metier, objet_payable.numero_contact, objet_payable.application, prod.dn_soc_id, prestation_a_payer.etat, prestation_a_payer.montant_prestation, prestation_a_payer.mode_paiement_interne, prestation_a_payer.id
  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=7298770 read=18809
12. 2,203.303 5,387.296 ↓ 4.8 35,122 2

Nested Loop (cost=5.83..125,574.16 rows=7,322 width=54) (actual time=0.925..5,387.296 rows=35,122 loops=2)

  • Output: prestation_generee.type_extourne, prestation_generee.id, objet_payable.id, objet_payable.metier_id, objet_payable.numero_metier, objet_payable.numero_contact, objet_payable.application, prod.dn_soc_id, prestation_a_payer.etat, prestation_a_payer.montant_prestation, prestation_a_payer.mode_paiement_interne, prestation_a_payer.id
  • Buffers: shared hit=7298770 read=18809
  • Worker 0: actual time=0.746..9333.481 rows=65180 loops=1
  • Buffers: shared hit=6552369 read=16290
13. 3,145.887 3,183.991 ↓ 4.4 840,884 2

Nested Loop (cost=5.40..31,713.98 rows=190,212 width=34) (actual time=0.384..3,183.991 rows=840,884 loops=2)

  • Output: prestation_generee.type_extourne, prestation_generee.id, objet_payable.id, objet_payable.metier_id, objet_payable.numero_metier, objet_payable.numero_contact, objet_payable.application, prod.dn_soc_id
  • Buffers: shared hit=536152 read=14793
  • Worker 0: actual time=0.599..5444.205 rows=1509470 loops=1
  • Buffers: shared hit=481666 read=12843
14. 26.701 37.945 ↓ 2.4 18,048 2

Hash Join (cost=4.97..3,799.19 rows=7,660 width=25) (actual time=0.321..37.945 rows=18,048 loops=2)

  • Output: objet_payable.id, objet_payable.metier_id, objet_payable.numero_metier, objet_payable.numero_contact, objet_payable.application, prod.dn_soc_id
  • Inner Unique: true
  • Hash Cond: (objet_payable.produit_id = prod.pro_id)
  • Buffers: shared hit=2759
  • Worker 0: actual time=0.530..63.003 rows=32815 loops=1
  • Buffers: shared hit=2256
15. 11.173 11.173 ↑ 1.2 72,623 2

Parallel Seq Scan on hermes.objet_payable (cost=0.00..3,563.39 rows=85,439 width=27) (actual time=0.010..11.173 rows=72,623 loops=2)

  • Output: objet_payable.id, objet_payable.interface_objet_payable_id, objet_payable.numero_metier, objet_payable.metier_id, objet_payable.contrat_id, objet_payable.societe_id, objet_payable.famille_id, objet_payable.produit_id, objet_payable.application, objet_payable.type_objet_payable, objet_payable.confidentiel, objet_payable.personnel_rp, objet_payable.etat, objet_payable.date_blocage, objet_payable.date_dernier_bouclement, objet_payable.usr_log_i, objet_payable.dte_log_i, objet_payable.usr_log_u, objet_payable.dte_log_u, objet_payable.audit, objet_payable.version, objet_payable.numero_contrat, objet_payable.date_migration, objet_payable.numero_contact
  • Buffers: shared hit=2709
  • Worker 0: actual time=0.014..17.435 rows=118162 loops=1
  • Buffers: shared hit=2209
16. 0.008 0.071 ↑ 1.0 13 2

Hash (cost=4.81..4.81 rows=13 width=6) (actual time=0.071..0.071 rows=13 loops=2)

  • Output: prod.pro_id, prod.dn_soc_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=6
  • Worker 0: actual time=0.074..0.074 rows=13 loops=1
  • Buffers: shared hit=3
17. 0.063 0.063 ↑ 1.0 13 2

Seq Scan on produit.prod (cost=0.00..4.81 rows=13 width=6) (actual time=0.025..0.063 rows=13 loops=2)

  • Output: prod.pro_id, prod.dn_soc_id
  • Filter: (prod.dn_soc_id = 6)
  • Rows Removed by Filter: 132
  • Buffers: shared hit=6
  • Worker 0: actual time=0.030..0.068 rows=13 loops=1
  • Buffers: shared hit=3
18. 0.159 0.159 ↑ 1.9 47 36,096

Index Scan using prestation_generee_objet_payable_id_idx on hermes.prestation_generee (cost=0.43..2.74 rows=90 width=13) (actual time=0.013..0.159 rows=47 loops=36,096)

  • Output: prestation_generee.id, prestation_generee.periode_gestion_id, prestation_generee.objet_payable_id, prestation_generee.beneficiaire_id, prestation_generee.devise, prestation_generee.nature, prestation_generee.frequence_paiement, prestation_generee.date_debut_periode, prestation_generee.date_fin_periode, prestation_generee.montant_prestation, prestation_generee.montant_part_lpp, prestation_generee.date_echeance_paiement, prestation_generee.date_prevue_paiement, prestation_generee.communication_1, prestation_generee.communication_2, prestation_generee.communication_3, prestation_generee.communication_4, prestation_generee.avec_interet, prestation_generee.type_interet, prestation_generee.retenue_active, prestation_generee.secteur, prestation_generee.etat, prestation_generee.etat_fort, prestation_generee.type_extourne, prestation_generee.type_calcul_effectue, prestation_generee.usr_log_i, prestation_generee.dte_log_i, prestation_generee.usr_log_u, prestation_generee.dte_log_u, prestation_generee.audit, prestation_generee.version, prestation_generee.periode_gestion_bouclement_id, prestation_generee.montant_prestation_bouclement, prestation_generee.nature_bouclement
  • Index Cond: (prestation_generee.objet_payable_id = objet_payable.id)
  • Buffers: shared hit=533393 read=14793
  • Worker 0: actual time=0.013..0.149 rows=46 loops=32815
  • Buffers: shared hit=479410 read=12843
19. 0.002 0.002 ↓ 0.0 0 1,681,768

Index Scan using idx4fk_f_pge_in_pap on hermes.prestation_a_payer (cost=0.43..0.48 rows=1 width=24) (actual time=0.002..0.002 rows=0 loops=1,681,768)

  • Output: prestation_a_payer.id, prestation_a_payer.prestation_a_payer_origine_id, prestation_a_payer.prestation_generee_id, prestation_a_payer.informations_paiement_interne_id, prestation_a_payer.objet_payable_id, prestation_a_payer.periode_gestion_id, prestation_a_payer.etat_repartition, prestation_a_payer.beneficiaire_id, prestation_a_payer.adresse_paiement_id, prestation_a_payer.adresse_paiement_generation_id, prestation_a_payer.adresse_paiement_principale, prestation_a_payer.nature, prestation_a_payer.type_paiement_particulier, prestation_a_payer.montant_prestation, prestation_a_payer.montant_part_lpp, prestation_a_payer.motif_paiement, prestation_a_payer.date_prevue_paiement, prestation_a_payer.communication_1, prestation_a_payer.communication_2, prestation_a_payer.communication_3, prestation_a_payer.communication_4, prestation_a_payer.mode_paiement_interne, prestation_a_payer.ligne_codage_bvr, prestation_a_payer.numero_reference_bvr, prestation_a_payer.numero_adherent_bvr, prestation_a_payer.etat, prestation_a_payer.etat_fort, prestation_a_payer.type_extourne, prestation_a_payer.visa_mise_a_valider, prestation_a_payer.date_mise_a_valider, prestation_a_payer.visa_validation, prestation_a_payer.date_validation, prestation_a_payer.usr_log_i, prestation_a_payer.dte_log_i, prestation_a_payer.usr_log_u, prestation_a_payer.dte_log_u, prestation_a_payer.audit, prestation_a_payer.version, prestation_a_payer.prestation_a_payer_extourne_origine_id, prestation_a_payer.periode_gestion_bouclement_id, prestation_a_payer.nature_bouclement, prestation_a_payer.fiscalite
  • Index Cond: (prestation_a_payer.prestation_generee_id = prestation_generee.id)
  • Filter: (((prestation_a_payer.etat)::text = ANY ('{4PAY,2APA}'::text[])) AND ((prestation_a_payer.mode_paiement_interne)::text <> 'REVE'::text))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=6762618 read=4016
  • Worker 0: actual time=0.002..0.002 rows=0 loops=1509470
  • Buffers: shared hit=6070703 read=3447
20. 178.521 210.729 ↓ 0.0 0 70,243

Index Scan using idx4fk_f_pge_in_ret on hermes.retenue (cost=0.42..2.94 rows=1 width=545) (actual time=0.003..0.003 rows=0 loops=70,243)

  • Output: retenue.type_retenue, retenue.montant_imposable, retenue.montant_retenue, retenue.correctif_arrondi_retenue, retenue.taux, retenue.prestation_generee_id, (SubPlan 1)
  • Index Cond: (prestation_generee.id = retenue.prestation_generee_id)
  • Buffers: shared hit=221694 read=171
21.          

SubPlan (for Index Scan)

22. 32.208 32.208 ↑ 1.0 1 2,684

Index Scan using idx_code_texte_code_cle_code_type on produit.code_texte a (cost=0.28..2.50 rows=1 width=15) (actual time=0.012..0.012 rows=1 loops=2,684)

  • Output: a.texte
  • Index Cond: (((retenue.type_retenue)::text = (a.code_cle)::text) AND ((a.code_type)::text = 'TypeRetenue'::text))
  • Filter: ((a.codet_langue)::text = 'FR'::text)
  • Buffers: shared hit=8052
23. 23.418 23.418 ↑ 1.0 1 2,602

Index Scan using idx4fk_f_pap_in_mntap on hermes.montant_a_payer (cost=0.43..1.18 rows=1 width=21) (actual time=0.008..0.009 rows=1 loops=2,602)

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

Index Scan using p_beneficiaire on hermes.beneficiaire (cost=0.42..0.44 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=2,602)

  • Output: beneficiaire.id, beneficiaire.usr_log_i, beneficiaire.dte_log_i, beneficiaire.usr_log_u, beneficiaire.dte_log_u, beneficiaire.audit, beneficiaire.version, beneficiaire.numero_contact
  • Index Cond: (beneficiaire.id = montant_a_payer.beneficiaire_id)
  • Buffers: shared hit=10414
25. 41.966 62.448 ↑ 1.0 1 2,602

Index Scan using adresse_formatee_numero_contact_idx on partner.adresse_formatee a1_adresses_ben_map (cost=0.42..3.23 rows=1 width=4) (actual time=0.020..0.024 rows=1 loops=2,602)

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

SubPlan (for Index Scan)

27. 11.704 20.482 ↑ 1.0 1 2,926

Aggregate (cost=2.65..2.66 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=2,926)

  • Output: max(CASE WHEN ((b.type_adresse)::text = 'BASE'::text) THEN 0 WHEN ((b.type_adresse)::text = 'BASE'::text) THEN 1 ELSE NULL::integer END)
  • Buffers: shared hit=12034
28. 8.778 8.778 ↑ 1.0 1 2,926

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.003..0.003 rows=1 loops=2,926)

  • 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_ben_map.numero_contact = b.numero_contact)
  • Buffers: shared hit=12034
29. 72.856 72.856 ↑ 1.0 1 2,602

Index Scan using bi_partenaire_uk1 on biinfra.bi_partenaire a1_bi_partenaire_ben (cost=0.42..0.55 rows=1 width=63) (actual time=0.028..0.028 rows=1 loops=2,602)

  • Output: a1_bi_partenaire_ben.contact_id, a1_bi_partenaire_ben.numero_contact, a1_bi_partenaire_ben.partenaire_id, a1_bi_partenaire_ben.part_pid_id, a1_bi_partenaire_ben.oc_principal_bkp, a1_bi_partenaire_ben.oc_secondaire_bkp, a1_bi_partenaire_ben.prenom, a1_bi_partenaire_ben.nom_raison_sociale, a1_bi_partenaire_ben.complement_raison_sociale, a1_bi_partenaire_ben.flag_personne_physique, a1_bi_partenaire_ben.flag_collaborateur, a1_bi_partenaire_ben.date_naissance, a1_bi_partenaire_ben.titre_civil, a1_bi_partenaire_ben.date_deces, a1_bi_partenaire_ben.etat_civil, a1_bi_partenaire_ben.numero_avs_upi, a1_bi_partenaire_ben.sexe, a1_bi_partenaire_ben.curatelle, a1_bi_partenaire_ben.statut, a1_bi_partenaire_ben.designation_complementaire, a1_bi_partenaire_ben.apporteur_affaire, a1_bi_partenaire_ben.cause_radiation, a1_bi_partenaire_ben.classe, a1_bi_partenaire_ben.code_succession, a1_bi_partenaire_ben.numero_agent, a1_bi_partenaire_ben.statut_agent, a1_bi_partenaire_ben.numero_immobilier, a1_bi_partenaire_ben.role_relation, a1_bi_partenaire_ben.type_contact, a1_bi_partenaire_ben.texte_libre_1, a1_bi_partenaire_ben.texte_libre_2, a1_bi_partenaire_ben.email, a1_bi_partenaire_ben.email_confidentiel, a1_bi_partenaire_ben.email_mailing, a1_bi_partenaire_ben.email_mailing_confidentiel, a1_bi_partenaire_ben.email_delegue_cip, a1_bi_partenaire_ben.email_delegue_cip_confidentiel, a1_bi_partenaire_ben.email_professionnel, a1_bi_partenaire_ben.email_professionnel_confidentiel, a1_bi_partenaire_ben.fax, a1_bi_partenaire_ben.mobile, a1_bi_partenaire_ben.telephone, a1_bi_partenaire_ben.nationalite_1, a1_bi_partenaire_ben.nationalite_2, a1_bi_partenaire_ben.cause_exemption_fatca, a1_bi_partenaire_ben.declaration_fatca, a1_bi_partenaire_ben.etranger_soumis_avs, a1_bi_partenaire_ben.code_fiscalite_fatca, a1_bi_partenaire_ben.intermediaire_lba, a1_bi_partenaire_ben.niveau_lba, a1_bi_partenaire_ben.numero_contribuable_etranger, a1_bi_partenaire_ben.numero_contribuable_usa, a1_bi_partenaire_ben.dte_log_u, a1_bi_partenaire_ben.dte_log_i, a1_bi_partenaire_ben.certificat_vie_internet, a1_bi_partenaire_ben.dte_extraction, a1_bi_partenaire_ben.texte_recherche, a1_bi_partenaire_ben.courrier_ear
  • Index Cond: (a1_bi_partenaire_ben.numero_contact = beneficiaire.numero_contact)
  • Buffers: shared hit=10351 read=57
30. 1,668,956.626 1,668,956.626 ↑ 1.2 576,085 2,602

Seq Scan on hermes.montant_a_payer montant_a_payer_1 (cost=0.00..158,065.42 rows=669,769 width=4) (actual time=1.559..641.413 rows=576,085 loops=2,602)

  • Output: montant_a_payer_1.id, montant_a_payer_1.prestation_a_payer_id, montant_a_payer_1.informations_paiement_interne_id, montant_a_payer_1.interface_montant_a_payer_id, montant_a_payer_1.facture_id, montant_a_payer_1.groupe_factures_id, montant_a_payer_1.objet_payable_id, montant_a_payer_1.periode_gestion_id, montant_a_payer_1.paiement_id, montant_a_payer_1.beneficiaire_id, montant_a_payer_1.adresse_paiement_id, montant_a_payer_1.adresse_paiement_generation_id, montant_a_payer_1.devise, montant_a_payer_1.nature, montant_a_payer_1.type_paiement_particulier, montant_a_payer_1.montant, montant_a_payer_1.date_echeance_paiement, montant_a_payer_1.date_prevue_paiement, montant_a_payer_1.date_effective_paiement, montant_a_payer_1.communication_1, montant_a_payer_1.communication_2, montant_a_payer_1.communication_3, montant_a_payer_1.communication_4, montant_a_payer_1.mode_paiement_interne, montant_a_payer_1.ligne_codage_bvr, montant_a_payer_1.numero_reference_bvr, montant_a_payer_1.numero_adherent_bvr, montant_a_payer_1.reference_facture, montant_a_payer_1.secteur, montant_a_payer_1.deja_paye_hors_paiement, montant_a_payer_1.deja_comptabilise_hors_paiement, montant_a_payer_1.etat, montant_a_payer_1.type_extourne, montant_a_payer_1.type_calcul_effectue, montant_a_payer_1.montant_frais_retour, montant_a_payer_1.visa_mise_a_valider, montant_a_payer_1.date_mise_a_valider, montant_a_payer_1.visa_validation, montant_a_payer_1.date_validation, montant_a_payer_1.usr_log_i, montant_a_payer_1.dte_log_i, montant_a_payer_1.usr_log_u, montant_a_payer_1.dte_log_u, montant_a_payer_1.audit, montant_a_payer_1.version, montant_a_payer_1.montant_interets, montant_a_payer_1.nature_interets, montant_a_payer_1.periode_gestion_bouclement_id, montant_a_payer_1.nature_bouclement, montant_a_payer_1.numero_encaissement
  • Filter: ((montant_a_payer_1.date_effective_paiement >= '2018-01-01'::date) AND (montant_a_payer_1.date_effective_paiement <= '2018-12-31'::date))
  • Rows Removed by Filter: 2757141
  • Buffers: shared hit=227064005 read=17339
31.          

SubPlan (for Nested Loop Semi Join)

32. 6.800 6.800 ↑ 1.0 1 400

Index Scan using idx_code_texte_code_cle_code_type on produit.code_texte a_1 (cost=0.28..2.50 rows=1 width=15) (actual time=0.017..0.017 rows=1 loops=400)

  • Output: a_1.texte
  • Index Cond: (((prestation_a_payer.mode_paiement_interne)::text = (a_1.code_cle)::text) AND ((a_1.code_type)::text = 'TypeModePaiementInterne'::text))
  • Filter: ((a_1.codet_langue)::text = 'FR'::text)
  • Buffers: shared hit=1200
Planning time : 24.084 ms
Execution time : 1,758,604.998 ms