explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ml6B

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 37.102 ↓ 0.0 0 1

GroupAggregate (cost=13,235.26..13,235.37 rows=1 width=128) (actual time=37.102..37.102 rows=0 loops=1)

  • Group Key: (to_char(dossier_pmsi.d_fin, 'YYYY'::text)), (to_char(periode_mois_cumule.date_fin_periode, 'TMMonth'::text)), (CASE WHEN (dossier_pmsi.nb_seances > 0) THEN 'Seance'::text WHEN ((dossier_pmsi.nb_seances = 0) AND (date_part('days'::text, (dossier_pmsi.d_fin - dossier_pmsi.d_deb)) = '0'::double precision)) THEN 'Ambulatoire'::text WHEN (date_part('days'::text, (dossier_pmsi.d_fin - dossier_pmsi.d_deb)) > '0'::double precision) THEN 'Hospitalisation complète'::text ELSE 'Indéterminé'::text END)
2. 0.047 37.097 ↓ 0.0 0 1

Sort (cost=13,235.26..13,235.26 rows=1 width=136) (actual time=37.097..37.097 rows=0 loops=1)

  • Sort Key: (to_char(dossier_pmsi.d_fin, 'YYYY'::text))
  • Sort Method: quicksort Memory: 25kB
3. 0.008 37.050 ↓ 0.0 0 1

Hash Join (cost=12,048.17..13,235.25 rows=1 width=136) (actual time=37.050..37.050 rows=0 loops=1)

  • Hash Cond: ((ghm_carac.id = dossier_pmsi_ghm.id) AND (ghm_carac.verclass_id = dossier_pmsi_ghm.verclass_id))
4. 0.022 0.022 ↑ 41,088.0 1 1

Seq Scan on ghm_carac (cost=0.00..878.88 rows=41,088 width=15) (actual time=0.022..0.022 rows=1 loops=1)

5. 0.000 37.020 ↓ 0.0 0 1

Hash (cost=12,048.16..12,048.16 rows=1 width=49) (actual time=37.020..37.020 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
6. 5.277 37.020 ↓ 0.0 0 1

Gather (cost=3,681.66..12,048.16 rows=1 width=49) (actual time=37.020..37.020 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 0.194 31.743 ↓ 0.0 0 3 / 3

Hash Join (cost=2,681.66..11,048.06 rows=1 width=49) (actual time=31.743..31.743 rows=0 loops=3)

  • Hash Cond: (date_part('year'::text, dossier_pmsi.d_fin) = (periode_mois_cumule.annee)::double precision)
  • Join Filter: (date_part('month'::text, dossier_pmsi.d_fin) <= (periode_mois_cumule.mois)::double precision)
8. 0.000 30.794 ↓ 0.0 0 3 / 3

Nested Loop (cost=2,672.11..11,038.48 rows=1 width=41) (actual time=30.794..30.794 rows=0 loops=3)

9. 0.038 30.794 ↓ 0.0 0 3 / 3

Hash Join (cost=2,671.68..10,302.18 rows=1,390 width=29) (actual time=30.794..30.794 rows=0 loops=3)

  • Hash Cond: (dossier_pmsi_ghm.dossier_pmsi_id = sous_dossier_pmsi.dossier_pmsi_id)
10. 0.014 0.014 ↑ 109,573.0 1 3 / 3

Parallel Seq Scan on dossier_pmsi_ghm (cost=0.00..7,068.73 rows=109,573 width=19) (actual time=0.014..0.014 rows=1 loops=3)

11. 0.001 30.742 ↓ 0.0 0 3 / 3

Hash (cost=2,630.00..2,630.00 rows=3,335 width=10) (actual time=30.742..30.742 rows=0 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 32kB
12. 0.001 30.741 ↓ 0.0 0 3 / 3

Nested Loop (cost=768.12..2,630.00 rows=3,335 width=10) (actual time=30.741..30.741 rows=0 loops=3)

13. 0.000 30.740 ↓ 0.0 0 3 / 3

Nested Loop (cost=767.69..884.07 rows=3,495 width=4) (actual time=30.740..30.740 rows=0 loops=3)

14. 0.002 30.740 ↓ 0.0 0 3 / 3

Nested Loop (cost=767.27..777.30 rows=1 width=8) (actual time=30.740..30.740 rows=0 loops=3)

15. 0.002 30.738 ↓ 0.0 0 3 / 3

HashAggregate (cost=766.98..766.99 rows=1 width=4) (actual time=30.738..30.738 rows=0 loops=3)

  • Group Key: securite_helper.elt_struct_id
16. 30.736 30.736 ↓ 0.0 0 3 / 3

Seq Scan on temp_security_view securite_helper (cost=0.00..766.98 rows=1 width=4) (actual time=30.736..30.736 rows=0 loops=3)

  • Filter: (name = 'root'::text)
  • Rows Removed by Filter: 42798
17. 0.000 0.000 ↓ 0.0 0 / 3

Index Scan using idx_struct_commune_id_elt_struct on struct_commune (cost=0.29..10.31 rows=1 width=4) (never executed)

  • Index Cond: (id_elt_struct = securite_helper.elt_struct_id)
  • Filter: (is_defaut_unite AND (lib_jur = 'CH LE HAVRE'::text))
18. 0.000 0.000 ↓ 0.0 0 / 3

Index Scan using idx_sdpp_elt_struct_autori_elt_struct_id_um on sdpp_elt_struct_autori (cost=0.42..70.36 rows=3,641 width=8) (never executed)

  • Index Cond: (elt_struct_id_um = struct_commune.id_elt_struct)
19. 0.000 0.000 ↓ 0.0 0 / 3

Index Scan using pk_sous_dossier_pmsi on sous_dossier_pmsi (cost=0.42..0.49 rows=1 width=14) (never executed)

  • Index Cond: (id = sdpp_elt_struct_autori.sous_dossier_pmsi_id)
20. 0.000 0.000 ↓ 0.0 0 / 3

Index Scan using pk_dossier_pmsi on dossier_pmsi (cost=0.42..0.52 rows=1 width=24) (never executed)

  • Index Cond: (id = sous_dossier_pmsi.dossier_pmsi_id)
  • Filter: ((champ_code = 'MCO'::text) AND (src_id = 'pmsipilot_mco'::text) AND (to_char(d_fin, 'YYYY'::text) >= to_char((('now'::cstring)::date + '-2 years'::interval), 'YYYY'::text)) AND (CASE WHEN (nb_seances > 0) THEN 'Seance'::text WHEN ((nb_seances = 0) AND (date_part('days'::text, (d_fin - d_deb)) = '0'::double precision)) THEN 'Ambulatoire'::text WHEN (date_part('days'::text, (d_fin - d_deb)) > '0'::double precision) THEN 'Hospitalisation complète'::text ELSE 'Indéterminé'::text END = 'Hospitalisation complète'::text))
21. 0.086 0.755 ↓ 13.0 26 3 / 3

Hash (cost=9.53..9.53 rows=2 width=16) (actual time=0.755..0.755 rows=26 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
22. 0.669 0.669 ↓ 13.0 26 3 / 3

Seq Scan on periode_mois_cumule (cost=0.00..9.53 rows=2 width=16) (actual time=0.309..0.669 rows=26 loops=3)

  • Filter: (to_char(date_fin_periode, 'TMMonth'::text) = to_char((('now'::cstring)::date + '-2 mons'::interval), 'TMMonth'::text))
  • Rows Removed by Filter: 275
Planning time : 2.749 ms
Execution time : 39.098 ms