explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1AueE

Settings
# exclusive inclusive rows x rows loops node
1. 144.628 14,257.506 ↓ 123.0 123 1

GroupAggregate (cost=19,602.80..19,602.90 rows=1 width=183) (actual time=14,084.342..14,257.506 rows=123 loops=1)

  • Group Key: struct_commune_et.code_pole, (date_part('year'::text, dossier_pmsi.d_fin)), (CASE WHEN (date_part('days'::text, (dossier_pmsi.d_fin - dossier_pmsi.d_deb)) > '0'::double precision) THEN 'Séjours > 24h'::text ELSE 'Séjours < 24h'::text END), struct_commune_et.lib_pole, struct_commune_et.lib_ssserv1
2. 178.037 14,112.878 ↓ 58,853.0 58,853 1

Sort (cost=19,602.80..19,602.81 rows=1 width=138) (actual time=14,082.245..14,112.878 rows=58,853 loops=1)

  • Sort Key: struct_commune_et.code_pole, (date_part('year'::text, dossier_pmsi.d_fin)), (CASE WHEN (date_part('days'::text, (dossier_pmsi.d_fin - dossier_pmsi.d_deb)) > '0'::double precision) THEN 'Séjours > 24h'::text ELSE 'Séjours < 24h'::text END), struct_commune_et.lib_pole, struct_commune_et.lib_ssserv1
  • Sort Method: quicksort Memory: 15968kB
3. 225.123 13,934.841 ↓ 58,853.0 58,853 1

Nested Loop (cost=1,060.75..19,602.79 rows=1 width=138) (actual time=5.818..13,934.841 rows=58,853 loops=1)

4. 561.358 13,533.159 ↓ 58,853.0 58,853 1

Nested Loop (cost=1,060.33..19,602.09 rows=1 width=107) (actual time=5.791..13,533.159 rows=58,853 loops=1)

5. 559.024 10,797.311 ↓ 434,898.0 434,898 1

Nested Loop Left Join (cost=1,059.91..19,601.33 rows=1 width=91) (actual time=5.745..10,797.311 rows=434,898 loops=1)

6. 848.302 9,368.491 ↓ 434,898.0 434,898 1

Nested Loop (cost=1,059.62..19,601.02 rows=1 width=83) (actual time=5.734..9,368.491 rows=434,898 loops=1)

7. 1,312.626 5,875.725 ↓ 440,744.0 440,744 1

Nested Loop (cost=1,059.35..19,600.60 rows=1 width=22) (actual time=5.674..5,875.725 rows=440,744 loops=1)

8. 1,863.842 2,800.123 ↓ 440,744.0 440,744 1

Gather (cost=1,058.93..19,600.05 rows=1 width=14) (actual time=5.651..2,800.123 rows=440,744 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 448.163 936.281 ↓ 146,915.0 146,915 3

Hash Left Join (cost=58.93..18,599.95 rows=1 width=14) (actual time=5.134..936.281 rows=146,915 loops=3)

  • Hash Cond: (sous_dossier_pmsi.id = sous_dossier_international.sous_dossier_pmsi_id)
  • Filter: (sous_dossier_international.code_type_debiteur IS NULL)
  • Rows Removed by Filter: 503
10. 483.500 483.500 ↑ 1.3 147,417 3

Parallel Seq Scan on sous_dossier_pmsi (cost=0.00..17,843.72 rows=184,272 width=14) (actual time=0.052..483.500 rows=147,417 loops=3)

11. 2.142 4.618 ↑ 1.0 1,508 3

Hash (cost=40.08..40.08 rows=1,508 width=6) (actual time=4.618..4.618 rows=1,508 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 72kB
12. 2.476 2.476 ↑ 1.0 1,508 3

Seq Scan on sous_dossier_international (cost=0.00..40.08 rows=1,508 width=6) (actual time=0.048..2.476 rows=1,508 loops=3)

13. 1,762.976 1,762.976 ↑ 1.0 1 440,744

Index Scan using idx_sdpp_elt_struct_autori_sous_dossier_pmsi_id on sdpp_elt_struct_autori (cost=0.42..0.55 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=440,744)

  • Index Cond: (sous_dossier_pmsi_id = sous_dossier_pmsi.id)
14. 2,644.464 2,644.464 ↑ 1.0 1 440,744

Index Scan using idx_struct_commune_et_id_elt_struct on struct_commune_et (cost=0.27..0.41 rows=1 width=69) (actual time=0.005..0.006 rows=1 loops=440,744)

  • Index Cond: (id_elt_struct = sdpp_elt_struct_autori.elt_struct_id_etm)
  • Filter: ((lib_pole <> 'Pôle non défini'::text) AND (code_hie = 'PMSIPILOT_15'::text))
  • Rows Removed by Filter: 5
15. 869.796 869.796 ↓ 0.0 0 434,898

Index Scan using valo_sc_plafond_sous_dossier_pmsi_id_idx on valo_sc_plafond (cost=0.28..0.30 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=434,898)

  • Index Cond: (sous_dossier_pmsi_id = sous_dossier_pmsi.id)
16. 2,174.490 2,174.490 ↓ 0.0 0 434,898

Index Scan using pk_dossier_pmsi on dossier_pmsi (cost=0.42..0.75 rows=1 width=20) (actual time=0.005..0.005 rows=0 loops=434,898)

  • Index Cond: (id = sous_dossier_pmsi.dossier_pmsi_id)
  • Filter: ((date_part('year'::text, d_fin) >= date_part('year'::text, (('now'::cstring)::date + '-1 years -1 mons'::interval))) AND (date_part('month'::text, d_fin) < date_part('month'::text, (('now'::cstring)::date)::timestamp without time zone)))
  • Rows Removed by Filter: 1
17. 176.559 176.559 ↑ 1.0 1 58,853

Index Scan using pk_valorisation_mco_t2a on valorisation_mco_t2a (cost=0.42..0.68 rows=1 width=19) (actual time=0.003..0.003 rows=1 loops=58,853)

  • Index Cond: (sous_dossier_pmsi_id = sous_dossier_pmsi.id)