explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DC37

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 10.481 1,943,793.822 ↓ 4,777.0 4,777 1

Unique (cost=853,580.04..853,580.75 rows=1 width=3,449) (actual time=1,943,781.864..1,943,793.822 rows=4,777 loops=1)

2. 64.803 1,943,783.341 ↓ 4,777.0 4,777 1

Sort (cost=853,580.04..853,580.04 rows=1 width=3,449) (actual time=1,943,781.863..1,943,783.341 rows=4,777 loops=1)

  • Sort Key: fiche.fic_id, personne.per_id, personne.per_datenaissance, personne.per_nom, personne.per_nomjeunefille, personne.per_nomusage, personne.per_prenom, personne.per_sexe, personne.per_version, personne.per_datecreation, personne.per_logincreateur, personne.per_nom_0, personne.per_nom_1, personne.per_nom_2, personne.per_nom_3, personne.per_prenom_0, personne.per_prenom_1, personne.per_prenom_2, personne.per_prenom_3, personne.per_tra_id, personne.per_groupe, personne.per_rolegroupe, personne.per_age, personne.per_moisnaissance, personne.per_anneenaissance, personne.per_dateterme, personne.per_grossesse, personne.per_nom_phonetique, personne.per_prenom_phonetique, personne.per_suivipmi, personne.per_telephone, personne.per_anonyme, personne.per_fusion_115, fiche.fic_datecreation, fiche.fic_datemodification, fiche.fic_informations, fiche.fic_typefiche, fiche.fic_cmp_id, fiche.fic_demandeurprincipal, fiche.fic_ecoutant, fiche.fic_sfa_id, fiche.fic_sso_id, fiche.fic_victimeviolence, fiche.fic_typevictime, fiche.fic_pfa_uid, urg_personne.pur_dso_id, urg_personne.pur_rpe_id, urg_personne.pur_sma_id, urg_personne.pur_sad_id, urg_personne.pur_id, urg_personne.pur_ter_id, territoire.ter_id, territoire.ter_codedepartement, territoire.ter_nom, territoire.ter_version, territoire.ter_sao_insertion_id, territoire.ter_sao_urgence_id, territoire.ter_debutcyclejour, territoire.ter_debutcyclenuit, territoire.ter_journeedivisee, territoire.ter_timezone, urg_situationfamille.sfa_id, urg_situationfamille.sfa_adresse, urg_situationfamille.sfa_capacitelogement, urg_situationfamille.sfa_codeepci, urg_situationfamille.sfa_codepostal, urg_situationfamille.sfa_demandelogement, urg_situationfamille.sfa_enfantanaitre, urg_situationfamille.sfa_nombreenfants, urg_situationfamille.sfa_presenceenfant, urg_situationfamille.sfa_remplissageautomatique, urg_situationfamille.sfa_ville, urg_situationfamille.sfa_situationenfant, ref_situationenfant.sen_id, ref_situationenfant.sen_nom, ref_situationenfant.sen_ordre, urg_situationadministrative.sad_id, urg_situationadministrative.sad_demandetitresejour, urg_situationadministrative.sad_droitsecuritesociale, urg_situationadministrative.sad_nationalite, urg_situationadministrative.sad_nomcaisse, urg_situationadministrative.sad_papieridentite, urg_situationadministrative.sad_remplissageautomatique, urg_situationadministrative.sad_droitsejour, urg_situationadministrative.sad_pays, urg_situationadministrative.sad_securitesociale, urg_situationadministrative.sad_typepapieridentite, ref_pays.pay_id, ref_pays.pay_nom, ref_pays.pay_nationalite, ref_pays.pay_ordre, ref_papieridentite.pid_id, ref_papieridentite.pid_nom, ref_papieridentite.pid_ordre, ref_droitsejour.dse_id, ref_droitsejour.dse_nom, ref_droitsejour.dse_ordre, urg_situationsociale.sso_id, urg_situationsociale.sso_commentaires, urg_situationsociale.sso_expulsion, urg_situationsociale.sso_mailreferent, urg_situationsociale.sso_nomorganisme, urg_situationsociale.sso_nomreferent, urg_situationsociale.sso_remplissageautomatique, urg_situationsociale.sso_suivisocial, urg_situationsociale.sso_telephonereferent, urg_situationsociale.sso_dureederrance, urg_situationsociale.sso_motifdemande, "diagnostiqueSocial".dso_id, "diagnostiqueSocial".dso_demandeur, "diagnostiqueSocial".dso_dossiersurendettement, "diagnostiqueSocial".dso_enemploi, "diagnostiqueSocial".dso_enformation, "diagnostiqueSocial".dso_etudiant, "diagnostiqueSocial".dso_remboursementdettes, "diagnostiqueSocial".dso_remplissageautomatique, "diagnostiqueSocial".dso_retraite, "diagnostiqueSocial".dso_rqth, "diagnostiqueSocial".dso_sanscharge, "diagnostiqueSocial".dso_sansdette, "diagnostiqueSocial".dso_sansressource, "diagnostiqueSocial".dso_tco_id, "demandeHebergement".dhe_id, "demandeHebergement".dhe_date, "demandeHebergement".dhe_presenceanimal, "demandeHebergement".dhe_lieumenageveille, "demandeHebergement".dhe_adr_id, "demandeHebergement".dhe_uti_id, "demandeHebergement".dhe_situationdemande, "demandeHebergement".dhe_appel, "demandeHebergement".dhe_type, "demandeHebergement".dhe_commentaireappelant, "demandeHebergement".dhe_dupliquee, urg_reponse.id, urg_reponse.rep_commentaire, urg_reponse.rep_date, urg_reponse.rep_dateentree, urg_reponse.rep_datefinsejour, urg_reponse.rep_nombrenuits, urg_reponse.rep_refus, urg_reponse.reponsepositive, urg_reponse.rep_groupeplace, urg_reponse.rep_motifrefus, urg_reponse.rep_motifreponsenegative, urg_reponse.rep_structure, urg_reponse.rep_statutdemande, urg_reponse.rep_demandepourvue, urg_reponse.rep_ttr_id, urg_reponse.rep_transferee, urg_reponse.rep_datepremiereentree, urg_reponse.rep_lastgroupe, urg_reponse.rep_mfp_id, urg_reponse.rep_datefinpeca, urg_reponse.rep_renouvellementauto, urg_reponse.rep_datefinrenouvellementauto, urg_reponse.rep_nombrenuitsrenouvellement, urg_reponse.rep_pdf_upload_date, urg_reponse.rep_pdf_upload, urg_reponse.rep_mail_send, urg_reponse.rep_mail_send_date, urg_reponse.rep_laststructure, urg_reponse.rep_nouvellepec, urg_reponse.rep_sms_send, urg_reponse.rep_sms_send_date, urg_reponse.rep_renouvellementpermanent, urg_reponse.rep_participationusager, urg_adresse.adr_id, urg_adresse.adr_codeepci, urg_adresse.adr_codepostal, urg_adresse.adr_lat, urg_adresse.adr_libellevoie, urg_adresse.adr_lng, urg_adresse.adr_ville, urg_structure.str_id, urg_structure.str_adresse, urg_structure.str_affectationplaces, urg_structure.str_animauxacceptes, urg_structure.str_codeepci, urg_structure.str_codeinsee, urg_structure.str_codepostal, urg_structure.str_siret, urg_structure.str_cout, urg_structure.str_coutrepas, urg_structure.str_departement, urg_structure.str_fax, urg_structure.str_mail, urg_structure.str_nom, urg_structure.str_nom_sans_ordre, urg_structure.str_nomreferent, urg_structure.str_observation, urg_structure.str_participationfinanciere, urg_structure.str_prenomreferent, urg_structure.str_restauration, urg_structure.str_telephone, urg_structure.str_totalcout, urg_structure.str_typestructure, urg_structure.str_nomsansordre, urg_structure.str_ville, urg_structure.str_horaire_presentation, urg_structure.str_ephemere, urg_structure.str_supprime, urg_structure.str_active, urg_structure.str_placesvariables, urg_structure.str_structurepartagee, urg_groupeplace.gro_id, urg_groupeplace.gro_active, urg_groupeplace.gro_adresse, urg_groupeplace.gro_adresseidentique, urg_groupeplace.gro_capacitedisponible, urg_groupeplace.gro_capaciteouverte, urg_groupeplace.gro_capacitetheorique, urg_groupeplace.gro_codeepci, urg_groupeplace.gro_codepostal, urg_groupeplace.gro_nom, urg_groupeplace.gro_placefixe, urg_groupeplace.gro_placefermees, urg_groupeplace.gro_placeoccupees, urg_groupeplace.gro_ville, urg_groupeplace.gro_categorieplace, urg_groupeplace.gro_configurationphysique, urg_groupeplace.gro_nombrenuitees, urg_groupeplace.gro_typeplace, urg_groupeplace.str_groupes, urg_groupeplace.gro_supprime, urg_groupeplace.gro_accessible, phrh_hotel.pho_uid, phrh_hotel.pho_nom, phrh_hotel.pho_adresse, phrh_hotel.pho_cp, phrh_hotel.pho_ville, phrh_hotel.pho_tel, phrh_hotel.pho_fax, phrh_hotel.pho_niveau, phrh_hotel.pho_statut, phrh_hotel.pho_date_modification, phrh_hotel.pho_complement_adresse, phrh_hotel.pho_ascenseur, phrh_hotel.pho_droit_visite, phrh_hotel.pho_commentaire_visite, phrh_hotel.pho_reception_h24, phrh_hotel.pho_horaires_ouverture, phrh_hotel.pho_prestataire, phrh_hotel.pho_ascii, ref_composition.cmp_id, ref_composition.cmp_nom, ref_composition.cmp_ordre, ref_composition.cmp_iscomposition, ref_lieumenage.lm_id, ref_lieumenage.lm_nom, ref_lieumenage.lm_ordre, ref_motiffinpeca.mfp_id, ref_motiffinpeca.mfp_nom, ref_motiffinpeca.mfp_ordre, ref_motifrefus.mrf_id, ref_motifrefus.mrf_nom, ref_motifrefus.mrf_ordre, ref_typetransfert.ttr_id, ref_typetransfert.ttr_nom, ref_typetransfert.ttr_ordre, ref_motifreponsenegative.mrn_id, ref_motifreponsenegative.mrn_nom, ref_motifreponsenegative.mrn_ordre, ref_rolepersonne.rpe_id, ref_rolepersonne.rpe_nom, ref_rolepersonne.rpe_ordre, ref_situationdemande.sde_id, ref_situationdemande.sde_nom, ref_situationdemande.sde_ordre, ref_situationmatrimoniale.sma_id, ref_situationmatrimoniale.sma_nom, ref_situationmatrimoniale.sma_ordre, ((SubPlan 1)), ((SubPlan 2)), (sum((urg_reponse_1.rep_datefinsejour - urg_reponse_1.rep_dateentree))), (max(urg_reponse_1.rep_datefinsejour)), ((fiche.fic_demandeurprincipal = personne.per_id)), (sum(urg_ressourcepersonne.rep_montant)), (sum(urg_chargepersonne.chp_montant)), (sum(urg_dettepersonne.dep_montant))
  • Sort Method: quicksort Memory: 9895kB
3. 49.541 1,943,718.538 ↓ 4,777.0 4,777 1

Nested Loop Left Join (cost=790,820.08..853,580.03 rows=1 width=3,449) (actual time=7,770.910..1,943,718.538 rows=4,777 loops=1)

4. 20.710 1,942,570.287 ↓ 4,777.0 4,777 1

Nested Loop Left Join (cost=790,819.94..848,942.24 rows=1 width=3,423) (actual time=7,769.730..1,942,570.287 rows=4,777 loops=1)

5. 20.407 1,942,530.469 ↓ 4,777.0 4,777 1

Nested Loop Left Join (cost=790,819.81..848,942.07 rows=1 width=3,387) (actual time=7,769.719..1,942,530.469 rows=4,777 loops=1)

6. 17.538 1,942,490.954 ↓ 4,777.0 4,777 1

Nested Loop Left Join (cost=790,819.67..848,941.91 rows=1 width=3,360) (actual time=7,769.706..1,942,490.954 rows=4,777 loops=1)

7. 15.235 1,942,454.308 ↓ 4,777.0 4,777 1

Nested Loop Left Join (cost=790,819.54..848,941.75 rows=1 width=3,295) (actual time=7,769.694..1,942,454.308 rows=4,777 loops=1)

8. 16.451 1,942,434.296 ↓ 4,777.0 4,777 1

Nested Loop Left Join (cost=790,819.40..848,941.59 rows=1 width=3,232) (actual time=7,769.691..1,942,434.296 rows=4,777 loops=1)

9. 14.142 1,942,413.068 ↓ 4,777.0 4,777 1

Nested Loop Left Join (cost=790,819.27..848,941.42 rows=1 width=3,182) (actual time=7,769.688..1,942,413.068 rows=4,777 loops=1)

10. 16.341 1,942,394.149 ↓ 4,777.0 4,777 1

Nested Loop Left Join (cost=790,819.13..848,941.26 rows=1 width=3,134) (actual time=7,769.685..1,942,394.149 rows=4,777 loops=1)

11. 35.346 1,942,353.923 ↓ 4,777.0 4,777 1

Nested Loop Left Join (cost=790,818.99..848,941.09 rows=1 width=3,095) (actual time=7,769.673..1,942,353.923 rows=4,777 loops=1)

12. 9,101.761 1,942,256.476 ↓ 4,777.0 4,777 1

Nested Loop Left Join (cost=790,818.86..848,940.93 rows=1 width=3,058) (actual time=7,769.646..1,942,256.476 rows=4,777 loops=1)

  • Join Filter: (urg_dettepersonne.dep_diagnosticsocial_id = "diagnostiqueSocial".dso_id)
  • Rows Removed by Join Filter: 140907068
13. 34.431 1,869,119.030 ↓ 4,777.0 4,777 1

Nested Loop Left Join (cost=790,023.13..847,129.53 rows=1 width=3,026) (actual time=7,739.316..1,869,119.030 rows=4,777 loops=1)

14. 18.959 10,879.369 ↓ 4,777.0 4,777 1

Nested Loop Left Join (cost=769,056.17..815,048.47 rows=1 width=2,848) (actual time=6,970.239..10,879.369 rows=4,777 loops=1)

15. 15.549 10,855.633 ↓ 4,777.0 4,777 1

Nested Loop Left Join (cost=769,056.04..815,048.31 rows=1 width=2,812) (actual time=6,970.235..10,855.633 rows=4,777 loops=1)

16. 15.503 10,835.307 ↓ 4,777.0 4,777 1

Nested Loop Left Join (cost=769,055.89..815,048.13 rows=1 width=2,778) (actual time=6,970.232..10,835.307 rows=4,777 loops=1)

17. 20.634 10,810.250 ↓ 4,777.0 4,777 1

Nested Loop Left Join (cost=769,055.76..815,047.98 rows=1 width=2,745) (actual time=6,970.229..10,810.250 rows=4,777 loops=1)

18. 14.471 10,708.407 ↓ 4,777.0 4,777 1

Nested Loop Left Join (cost=769,055.33..815,047.23 rows=1 width=2,651) (actual time=6,970.205..10,708.407 rows=4,777 loops=1)

19. 20.273 10,689.159 ↓ 4,777.0 4,777 1

Nested Loop Left Join (cost=769,055.20..815,047.07 rows=1 width=2,619) (actual time=6,970.202..10,689.159 rows=4,777 loops=1)

20. 18.571 10,587.677 ↓ 4,777.0 4,777 1

Nested Loop Left Join (cost=769,054.78..815,046.57 rows=1 width=2,532) (actual time=6,970.180..10,587.677 rows=4,777 loops=1)

21. 13.784 10,483.120 ↓ 4,777.0 4,777 1

Nested Loop Left Join (cost=769,054.35..815,046.07 rows=1 width=2,373) (actual time=6,970.157..10,483.120 rows=4,777 loops=1)

22. 12.286 10,464.559 ↓ 4,777.0 4,777 1

Nested Loop Left Join (cost=769,054.07..815,045.76 rows=1 width=2,161) (actual time=6,970.154..10,464.559 rows=4,777 loops=1)

23. 55.993 10,447.496 ↓ 4,777.0 4,777 1

Hash Left Join (cost=769,053.79..815,045.39 rows=1 width=2,161) (actual time=6,970.151..10,447.496 rows=4,777 loops=1)

  • Hash Cond: ("demandeHebergement".dhe_id = phrh_demandehebergement.pdh_dhe_id)
  • Filter: (((phrh_demandehebergement.pdh_uid IS NULL) OR ((phrh_demandehebergement.pdh_uid IS NOT NULL) AND (phrh_demandehebergement.pdh_uid_type_demande = 1)) OR ((phrh_dossierhebergement.pdo_date_debut <= '2018-11-01'::date) AND (phrh_dossierhebergement.pdo_date_fin >= '2019-02-06'::date))) AND ("demandeHebergement".dhe_id = (SubPlan 3)))
  • Rows Removed by Filter: 26987
24. 34.281 7,856.362 ↓ 4.4 31,764 1

Hash Left Join (cost=766,838.59..793,921.22 rows=7,229 width=2,153) (actual time=6,943.341..7,856.362 rows=31,764 loops=1)

  • Hash Cond: (urg_personne.pur_ter_id = territoire.ter_id)
25. 27.319 7,822.013 ↓ 4.4 31,764 1

Hash Left Join (cost=766,833.91..793,820.15 rows=7,229 width=2,078) (actual time=6,943.246..7,822.013 rows=31,764 loops=1)

  • Hash Cond: (urg_reponse.rep_groupeplace = urg_groupeplace.gro_id)
26. 26.915 7,783.976 ↓ 4.4 31,764 1

Hash Left Join (cost=766,185.95..793,098.29 rows=7,229 width=1,932) (actual time=6,932.436..7,783.976 rows=31,764 loops=1)

  • Hash Cond: (urg_reponse.rep_structure = urg_structure.str_id)
27. 44.811 7,753.170 ↓ 4.4 31,764 1

Nested Loop Left Join (cost=765,995.93..792,834.38 rows=7,229 width=1,111) (actual time=6,928.511..7,753.170 rows=31,764 loops=1)

28. 46.848 7,422.483 ↓ 4.4 31,764 1

Nested Loop (cost=765,995.50..774,715.57 rows=7,229 width=1,045) (actual time=6,928.485..7,422.483 rows=31,764 loops=1)

29. 132.682 7,248.579 ↓ 4.4 31,764 1

Hash Right Join (cost=765,995.07..766,948.66 rows=7,229 width=857) (actual time=6,928.436..7,248.579 rows=31,764 loops=1)

  • Hash Cond: (urg_reponse_1.rep_personne = personne.per_id)
30. 2,207.233 3,594.325 ↓ 9.7 388,344 1

HashAggregate (cost=287,853.60..288,253.83 rows=40,023 width=16) (actual time=3,406.805..3,594.325 rows=388,344 loops=1)

  • Group Key: urg_reponse_1.rep_personne
31. 1,387.092 1,387.092 ↑ 1.0 7,470,373 1

Seq Scan on urg_reponse urg_reponse_1 (cost=0.00..211,167.80 rows=7,668,580 width=16) (actual time=0.009..1,387.092 rows=7,470,373 loops=1)

32. 45.115 3,521.572 ↓ 4.4 31,764 1

Hash (cost=478,051.11..478,051.11 rows=7,229 width=845) (actual time=3,521.572..3,521.572 rows=31,764 loops=1)

  • Buckets: 32768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 22057kB
33. 22.904 3,476.457 ↓ 4.4 31,764 1

Nested Loop Left Join (cost=77,560.94..478,051.11 rows=7,229 width=845) (actual time=278.348..3,476.457 rows=31,764 loops=1)

34. 20.984 3,390.025 ↓ 4.4 31,764 1

Nested Loop (cost=77,560.52..474,614.56 rows=7,229 width=797) (actual time=278.322..3,390.025 rows=31,764 loops=1)

35. 298.138 3,273.749 ↓ 4.4 31,764 1

Hash Join (cost=77,560.09..469,780.78 rows=7,229 width=308) (actual time=278.277..3,273.749 rows=31,764 loops=1)

  • Hash Cond: (urg_reponse.dhe_reponses = "demandeHebergement".dhe_id)
  • Join Filter: ((((urg_reponse.rep_demandepourvue)::text = 'OUI'::text) AND (((urg_reponse.rep_datepremiereentree IS NOT NULL) AND (urg_reponse.rep_datepremiereentree <= '2019-02-06'::date)) OR (urg_reponse.rep_dateentree <= '2019-02-06'::date)) AND (urg_reponse.rep_datefinsejour > '2018-11-01'::date)) OR (("demandeHebergement".dhe_date >= '2018-11-01 00:00:00'::timestamp without time zone) AND ("demandeHebergement".dhe_date <= '2019-02-06 00:00:00'::timestamp without time zone)))
  • Rows Removed by Join Filter: 31485
36. 2,817.904 2,817.904 ↓ 1.0 2,027,876 1

Seq Scan on urg_reponse (cost=0.00..383,710.85 rows=2,014,956 width=210) (actual time=12.408..2,817.904 rows=2,027,876 loops=1)

  • Filter: ((((rep_demandepourvue)::text = 'OUI'::text) AND (rep_structure = ANY ('{33161232,34083411,35394269,33157838,33117706,37165786,35250646,34092214,35242752,33379068,35393589,33380724}'::bigint[]))) OR ((rep_demandepourvue)::text = 'NON'::text) OR ((rep_demandepourvue)::text = 'SANS_TRAITEMENT'::text))
  • Rows Removed by Filter: 5442497
37. 27.406 157.707 ↑ 1.1 72,343 1

Hash (cost=76,600.07..76,600.07 rows=76,802 width=98) (actual time=157.707..157.707 rows=72,343 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 9883kB
38. 123.374 130.301 ↑ 1.1 72,343 1

Bitmap Heap Scan on urg_demandehebergement "demandeHebergement" (cost=1,443.65..76,600.07 rows=76,802 width=98) (actual time=9.404..130.301 rows=72,343 loops=1)

  • Recheck Cond: (dhe_ter_id = '60'::bigint)
  • Heap Blocks: exact=15974
39. 6.927 6.927 ↑ 1.1 72,718 1

Bitmap Index Scan on dhe_ter_id_idx (cost=0.00..1,424.44 rows=76,802 width=0) (actual time=6.927..6.927 rows=72,718 loops=1)

  • Index Cond: (dhe_ter_id = '60'::bigint)
40. 95.292 95.292 ↑ 1.0 1 31,764

Index Scan using personne_pkey on personne (cost=0.42..0.66 rows=1 width=489) (actual time=0.003..0.003 rows=1 loops=31,764)

  • Index Cond: (per_id = urg_reponse.rep_personne)
41. 63.528 63.528 ↑ 1.0 1 31,764

Index Scan using urg_personne_pkey on urg_personne (cost=0.42..0.47 rows=1 width=48) (actual time=0.002..0.002 rows=1 loops=31,764)

  • Index Cond: (personne.per_id = pur_id)
42. 127.056 127.056 ↑ 1.0 1 31,764

Index Scan using urg_fiche_pkey on urg_fiche fiche (cost=0.42..1.06 rows=1 width=188) (actual time=0.004..0.004 rows=1 loops=31,764)

  • Index Cond: (fic_id = "demandeHebergement".dhe_fic_id)
43. 285.876 285.876 ↑ 1.0 1 31,764

Index Scan using urg_adresse_pkey on urg_adresse (cost=0.43..2.50 rows=1 width=66) (actual time=0.009..0.009 rows=1 loops=31,764)

  • Index Cond: ("demandeHebergement".dhe_adr_id = adr_id)
44. 2.441 3.891 ↑ 1.0 3,167 1

Hash (cost=150.01..150.01 rows=3,201 width=821) (actual time=3.891..3.891 rows=3,167 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 954kB
45. 1.450 1.450 ↑ 1.0 3,167 1

Seq Scan on urg_structure (cost=0.00..150.01 rows=3,201 width=821) (actual time=0.009..1.450 rows=3,167 loops=1)

46. 6.096 10.718 ↓ 1.0 12,359 1

Hash (cost=493.54..493.54 rows=12,354 width=146) (actual time=10.718..10.718 rows=12,359 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 2377kB
47. 4.622 4.622 ↓ 1.0 12,359 1

Seq Scan on urg_groupeplace (cost=0.00..493.54 rows=12,354 width=146) (actual time=0.012..4.622 rows=12,359 loops=1)

48. 0.044 0.068 ↑ 1.0 119 1

Hash (cost=3.19..3.19 rows=119 width=75) (actual time=0.068..0.068 rows=119 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
49. 0.024 0.024 ↑ 1.0 119 1

Seq Scan on territoire (cost=0.00..3.19 rows=119 width=75) (actual time=0.008..0.024 rows=119 loops=1)

50. 5.617 25.785 ↑ 1.1 25,332 1

Hash (cost=1,851.48..1,851.48 rows=29,097 width=36) (actual time=25.785..25.785 rows=25,332 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1966kB
51. 10.522 20.168 ↓ 1.0 29,137 1

Hash Left Join (cost=575.49..1,851.48 rows=29,097 width=36) (actual time=5.375..20.168 rows=29,137 loops=1)

  • Hash Cond: (phrh_demandehebergement.pdh_pdo_uid = phrh_dossierhebergement.pdo_uid)
52. 4.451 4.451 ↓ 1.0 29,137 1

Seq Scan on phrh_demandehebergement (cost=0.00..875.97 rows=29,097 width=28) (actual time=0.011..4.451 rows=29,137 loops=1)

53. 2.567 5.195 ↑ 1.0 15,857 1

Hash (cost=368.55..368.55 rows=16,555 width=16) (actual time=5.195..5.195 rows=15,857 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1000kB
54. 2.628 2.628 ↑ 1.0 15,857 1

Seq Scan on phrh_dossierhebergement (cost=0.00..368.55 rows=16,555 width=16) (actual time=0.006..2.628 rows=15,857 loops=1)

55.          

SubPlan (forHash Left Join)

56. 31.764 2,509.356 ↑ 1.0 1 31,764

Limit (cost=11.07..415.56 rows=1 width=8) (actual time=0.079..0.079 rows=1 loops=31,764)

57. 0.000 2,477.592 ↑ 1.0 1 31,764

Result (cost=11.07..415.56 rows=1 width=8) (actual time=0.078..0.078 rows=1 loops=31,764)

  • One-Time Filter: ((phrh_demandehebergement.pdh_uid IS NULL) OR ((phrh_demandehebergement.pdh_uid IS NOT NULL) AND (phrh_demandehebergement.pdh_uid_type_demande = 1)) OR ((phrh_dossierhebergement.pdo_date_debut <= '2018-11-01'::date) AND (phrh_dossierhebergement.pdo_date_fin >= '2019-02-06'::date)))
58. 121.776 2,477.592 ↑ 1.0 1 31,764

Nested Loop (cost=11.07..415.56 rows=1 width=8) (actual time=0.078..0.078 rows=1 loops=31,764)

59. 285.876 285.876 ↓ 3.5 7 31,764

Index Scan using dhe_fic_id_idx on urg_demandehebergement "demandeHebergement_1" (cost=0.43..386.17 rows=2 width=16) (actual time=0.005..0.009 rows=7 loops=31,764)

  • Index Cond: (dhe_fic_id = fiche.fic_id)
  • Filter: (dhe_ter_id = '60'::bigint)
  • Rows Removed by Filter: 0
60. 413.988 2,069.940 ↓ 0.0 0 206,994

Bitmap Heap Scan on urg_reponse urg_reponse_4 (cost=10.63..14.68 rows=1 width=24) (actual time=0.010..0.010 rows=0 loops=206,994)

  • Recheck Cond: ((dhe_reponses = "demandeHebergement_1".dhe_id) AND (rep_personne = personne.per_id))
  • Filter: (((((rep_demandepourvue)::text = 'OUI'::text) AND (((rep_datepremiereentree IS NOT NULL) AND (rep_datepremiereentree <= '2019-02-06'::date)) OR (rep_dateentree <= '2019-02-06'::date)) AND (rep_datefinsejour > '2018-11-01'::date)) OR (("demandeHebergement_1".dhe_date >= '2018-11-01 00:00:00'::timestamp without time zone) AND ("demandeHebergement_1".dhe_date <= '2019-02-06 00:00:00'::timestamp without time zone))) AND ((((rep_demandepourvue)::text = 'OUI'::text) AND (rep_structure = ANY ('{33161232,34083411,35394269,33157838,33117706,37165786,35250646,34092214,35242752,33379068,35393589,33380724}'::bigint[]))) OR ((rep_demandepourvue)::text = 'NON'::text) OR ((rep_demandepourvue)::text = 'SANS_TRAITEMENT'::text)))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=203922
61. 206.994 1,655.952 ↓ 0.0 0 206,994

BitmapAnd (cost=10.63..10.63 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=206,994)

62. 413.988 413.988 ↑ 2.0 4 206,994

Bitmap Index Scan on dhe_reponses_idx (cost=0.00..4.49 rows=8 width=0) (actual time=0.002..0.002 rows=4 loops=206,994)

  • Index Cond: (dhe_reponses = "demandeHebergement_1".dhe_id)
63. 1,034.970 1,034.970 ↑ 2.9 66 206,994

Bitmap Index Scan on urg_reponse_per_id_idx (cost=0.00..5.87 rows=192 width=0) (actual time=0.005..0.005 rows=66 loops=206,994)

  • Index Cond: (rep_personne = personne.per_id)
64. 4.777 4.777 ↓ 0.0 0 4,777

Index Scan using prv_pdo_uid_idx on phrh_reservation (cost=0.29..0.36 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=4,777)

  • Index Cond: (prv_pdo_uid = phrh_dossierhebergement.pdo_uid)
65. 4.777 4.777 ↓ 0.0 0 4,777

Index Scan using pho_uid_idx on phrh_hotel (cost=0.28..0.30 rows=1 width=220) (actual time=0.001..0.001 rows=0 loops=4,777)

  • Index Cond: (pho_uid = phrh_reservation.prv_pho_uid)
66. 85.986 85.986 ↑ 1.0 1 4,777

Index Scan using urg_situationsociale_pkey on urg_situationsociale (cost=0.42..0.49 rows=1 width=159) (actual time=0.017..0.018 rows=1 loops=4,777)

  • Index Cond: (fiche.fic_sso_id = sso_id)
67. 81.209 81.209 ↑ 1.0 1 4,777

Index Scan using urg_situationfamille_pkey on urg_situationfamille (cost=0.42..0.49 rows=1 width=87) (actual time=0.016..0.017 rows=1 loops=4,777)

  • Index Cond: (fiche.fic_sfa_id = sfa_id)
68. 4.777 4.777 ↓ 0.0 0 4,777

Index Scan using ref_situationenfant_pkey on ref_situationenfant (cost=0.13..0.15 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=4,777)

  • Index Cond: (urg_situationfamille.sfa_situationenfant = sen_id)
69. 81.209 81.209 ↑ 1.0 1 4,777

Index Scan using urg_situationadministrative_pkey on urg_situationadministrative (cost=0.43..0.73 rows=1 width=94) (actual time=0.016..0.017 rows=1 loops=4,777)

  • Index Cond: (urg_personne.pur_sad_id = sad_id)
70. 9.554 9.554 ↓ 0.0 0 4,777

Index Scan using ref_droitsejour_pkey on ref_droitsejour (cost=0.13..0.15 rows=1 width=33) (actual time=0.001..0.002 rows=0 loops=4,777)

  • Index Cond: (urg_situationadministrative.sad_droitsejour = dse_id)
71. 4.777 4.777 ↓ 0.0 0 4,777

Index Scan using ref_pays_pkey on ref_pays (cost=0.14..0.16 rows=1 width=34) (actual time=0.001..0.001 rows=0 loops=4,777)

  • Index Cond: (urg_situationadministrative.sad_pays = pay_id)
72. 4.777 4.777 ↓ 0.0 0 4,777

Index Scan using ref_papieridentite_pkey on ref_papieridentite (cost=0.13..0.15 rows=1 width=36) (actual time=0.001..0.001 rows=0 loops=4,777)

  • Index Cond: (urg_situationadministrative.sad_typepapieridentite = pid_id)
73. 144,685.909 1,858,205.230 ↑ 1.0 1 4,777

Hash Right Join (cost=20,966.96..32,081.06 rows=1 width=178) (actual time=386.646..388.990 rows=1 loops=4,777)

  • Hash Cond: (urg_chargepersonne.chp_diagnosticsocial_id = "diagnostiqueSocial".dso_id)
74. 1,151,340.441 1,151,385.846 ↓ 1.1 461,125 4,751

HashAggregate (cost=10,705.20..15,997.63 rows=423,394 width=16) (actual time=0.062..242.346 rows=461,125 loops=4,751)

  • Group Key: urg_chargepersonne.chp_diagnosticsocial_id
75. 45.405 45.405 ↑ 1.0 478,839 1

Seq Scan on urg_chargepersonne (cost=0.00..8,310.80 rows=478,880 width=16) (actual time=0.024..45.405 rows=478,839 loops=1)

76. 28.662 562,133.475 ↑ 1.0 1 4,777

Hash (cost=10,261.74..10,261.74 rows=1 width=146) (actual time=117.675..117.675 rows=1 loops=4,777)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
77. 60,308.450 562,104.813 ↑ 1.0 1 4,777

Hash Right Join (cost=7,703.64..10,261.74 rows=1 width=146) (actual time=106.985..117.669 rows=1 loops=4,777)

  • Hash Cond: (urg_ressourcepersonne.rep_diagnosticsocial_id = "diagnostiqueSocial".dso_id)
78. 501,660.512 501,705.600 ↓ 1.9 188,016 4,751

HashAggregate (cost=7,702.79..8,920.93 rows=97,451 width=16) (actual time=0.041..105.600 rows=188,016 loops=4,751)

  • Group Key: urg_ressourcepersonne.rep_diagnosticsocial_id
79. 45.088 45.088 ↑ 1.0 343,308 1

Seq Scan on urg_ressourcepersonne (cost=0.00..5,979.86 rows=344,586 width=16) (actual time=0.009..45.088 rows=343,308 loops=1)

80. 9.554 90.763 ↑ 1.0 1 4,777

Hash (cost=0.84..0.84 rows=1 width=114) (actual time=0.019..0.019 rows=1 loops=4,777)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
81. 81.209 81.209 ↑ 1.0 1 4,777

Index Scan using urg_diagnosticsocial_pkey on urg_diagnosticsocial "diagnostiqueSocial" (cost=0.43..0.84 rows=1 width=114) (actual time=0.017..0.017 rows=1 loops=4,777)

  • Index Cond: (urg_personne.pur_dso_id = dso_id)
82. 64,031.974 64,035.685 ↓ 1.0 29,497 4,777

HashAggregate (cost=795.73..1,158.47 rows=29,019 width=16) (actual time=0.007..13.405 rows=29,497 loops=4,777)

  • Group Key: urg_dettepersonne.dep_diagnosticsocial_id
83. 3.711 3.711 ↑ 1.0 35,537 1

Seq Scan on urg_dettepersonne (cost=0.00..617.82 rows=35,582 width=16) (actual time=0.023..3.711 rows=35,537 loops=1)

84. 62.101 62.101 ↑ 1.0 1 4,777

Index Scan using ref_composition_pkey on ref_composition (cost=0.14..0.15 rows=1 width=37) (actual time=0.013..0.013 rows=1 loops=4,777)

  • Index Cond: (fiche.fic_cmp_id = cmp_id)
85. 23.885 23.885 ↑ 1.0 1 4,777

Index Scan using ref_lieumenage_pkey on ref_lieumenage (cost=0.14..0.16 rows=1 width=39) (actual time=0.004..0.005 rows=1 loops=4,777)

  • Index Cond: ("demandeHebergement".dhe_lieumenageveille = lm_id)
86. 4.777 4.777 ↓ 0.0 0 4,777

Index Scan using ref_motiffinpeca_pkey on ref_motiffinpeca (cost=0.14..0.16 rows=1 width=48) (actual time=0.001..0.001 rows=0 loops=4,777)

  • Index Cond: (urg_reponse.rep_mfp_id = mfp_id)
87. 4.777 4.777 ↓ 0.0 0 4,777

Index Scan using ref_motifrefus_pkey on ref_motifrefus (cost=0.14..0.15 rows=1 width=50) (actual time=0.001..0.001 rows=0 loops=4,777)

  • Index Cond: (urg_reponse.rep_motifrefus = mrf_id)
88. 4.777 4.777 ↓ 0.0 0 4,777

Index Scan using ref_typetransfert_pkey on ref_typetransfert (cost=0.14..0.15 rows=1 width=63) (actual time=0.001..0.001 rows=0 loops=4,777)

  • Index Cond: (urg_reponse.rep_ttr_id = ttr_id)
89. 19.108 19.108 ↑ 1.0 1 4,777

Index Scan using ref_motifreponsenegative_pkey on ref_motifreponsenegative (cost=0.14..0.15 rows=1 width=65) (actual time=0.004..0.004 rows=1 loops=4,777)

  • Index Cond: (urg_reponse.rep_motifreponsenegative = mrn_id)
90. 19.108 19.108 ↑ 1.0 1 4,777

Index Scan using ref_rolepersonne_pkey on ref_rolepersonne (cost=0.13..0.15 rows=1 width=27) (actual time=0.004..0.004 rows=1 loops=4,777)

  • Index Cond: (urg_personne.pur_rpe_id = rpe_id)
91. 19.108 19.108 ↑ 1.0 1 4,777

Index Scan using ref_situationdemande_pkey on ref_situationdemande (cost=0.14..0.16 rows=1 width=36) (actual time=0.004..0.004 rows=1 loops=4,777)

  • Index Cond: ("demandeHebergement".dhe_situationdemande = sde_id)
92. 19.108 19.108 ↑ 1.0 1 4,777

Index Scan using ref_situationmatrimoniale_pkey on ref_situationmatrimoniale (cost=0.14..0.15 rows=1 width=26) (actual time=0.004..0.004 rows=1 loops=4,777)

  • Index Cond: (urg_personne.pur_sma_id = sma_id)
93.          

SubPlan (forNested Loop Left Join)

94. 23.885 859.860 ↑ 1.0 1 4,777

Aggregate (cost=2,318.80..2,318.81 rows=1 width=8) (actual time=0.180..0.180 rows=1 loops=4,777)

95. 31.286 835.975 ↑ 13.7 14 4,777

Nested Loop (cost=0.86..2,318.32 rows=192 width=8) (actual time=0.027..0.175 rows=14 loops=4,777)

96. 272.289 272.289 ↑ 13.7 14 4,777

Index Scan using urg_reponse_per_id_idx on urg_reponse urg_reponse_2 (cost=0.43..698.48 rows=192 width=8) (actual time=0.012..0.057 rows=14 loops=4,777)

  • Index Cond: (rep_personne = personne.per_id)
97. 532.400 532.400 ↑ 1.0 1 66,550

Index Scan using urg_demandehebergement_pkey on urg_demandehebergement (cost=0.43..8.43 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=66,550)

  • Index Cond: (dhe_id = urg_reponse_2.dhe_reponses)
98. 19.108 219.742 ↑ 1.0 1 4,777

Aggregate (cost=2,318.80..2,318.81 rows=1 width=8) (actual time=0.046..0.046 rows=1 loops=4,777)

99. 19.764 200.634 ↑ 13.7 14 4,777

Nested Loop (cost=0.86..2,318.32 rows=192 width=8) (actual time=0.007..0.042 rows=14 loops=4,777)

100. 47.770 47.770 ↑ 13.7 14 4,777

Index Scan using urg_reponse_per_id_idx on urg_reponse urg_reponse_3 (cost=0.43..698.48 rows=192 width=8) (actual time=0.003..0.010 rows=14 loops=4,777)

  • Index Cond: (rep_personne = personne.per_id)
101. 133.100 133.100 ↑ 1.0 1 66,550

Index Scan using urg_demandehebergement_pkey on urg_demandehebergement urg_demandehebergement_1 (cost=0.43..8.43 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=66,550)

  • Index Cond: (dhe_id = urg_reponse_3.dhe_reponses)