explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r7UC

Settings
# exclusive inclusive rows x rows loops node
1. 287.791 8,827.889 ↑ 164.8 237 1

GroupAggregate (cost=160,344.48..161,418.85 rows=39,068 width=82) (actual time=7,214.721..8,827.889 rows=237 loops=1)

  • Group Key: (to_char((bi_calendrier.jour)::timestamp with time zone, 'YYYY'::text)), struct_commune.code_uf, struct_commune.lib_uf
2. 3,840.118 8,540.098 ↓ 34.4 1,344,542 1

Sort (cost=160,344.48..160,442.15 rows=39,068 width=66) (actual time=7,214.266..8,540.098 rows=1,344,542 loops=1)

  • Sort Key: (to_char((bi_calendrier.jour)::timestamp with time zone, 'YYYY'::text)), struct_commune.code_uf, struct_commune.lib_uf
  • Sort Method: external merge Disk: 69296kB
3. 714.875 4,699.980 ↓ 34.4 1,344,542 1

Hash Join (cost=2,197.40..157,364.82 rows=39,068 width=66) (actual time=11.344..4,699.980 rows=1,344,542 loops=1)

  • Hash Cond: (dos_gam.id_etat_dossier = etat_dossier.id)
4. 0.000 3,985.096 ↓ 35.0 1,369,227 1

Gather (cost=2,197.22..156,632.12 rows=39,068 width=42) (actual time=11.322..3,985.096 rows=1,369,227 loops=1)

  • Workers Planned: 5
  • Workers Launched: 5
5. 53.409 5,387.366 ↓ 29.2 228,204 6 / 6

Hash Join (cost=1,197.22..151,725.32 rows=7,814 width=42) (actual time=14.107..5,387.366 rows=228,204 loops=6)

  • Hash Cond: (mvt_j_heb_fait.id_elt_struct_heb = struct_commune.id_elt_struct)
6. 107.106 5,325.395 ↓ 31.4 245,741 6 / 6

Hash Join (cost=230.88..150,583.01 rows=7,826 width=16) (actual time=5.511..5,325.395 rows=245,741 loops=6)

  • Hash Cond: (dos_gam.id_type_dossier = type_dossier.id)
7. 213.646 5,218.249 ↓ 62.4 977,353 6 / 6

Nested Loop (cost=229.80..150,444.98 rows=15,652 width=20) (actual time=4.888..5,218.249 rows=977,353 loops=6)

8. 621.742 3,049.897 ↓ 62.4 977,353 6 / 6

Nested Loop (cost=229.38..143,061.93 rows=15,652 width=16) (actual time=4.845..3,049.897 rows=977,353 loops=6)

9. 269.508 473.449 ↓ 62.4 977,353 6 / 6

Hash Join (cost=228.95..135,811.03 rows=15,652 width=16) (actual time=4.796..473.449 rows=977,353 loops=6)

  • Hash Cond: (mvt_j_heb_fait.d_jour = bi_calendrier.jour)
10. 199.242 199.242 ↑ 1.5 1,061,786 6 / 6

Parallel Seq Scan on mvt_j_fait mvt_j_heb_fait (cost=0.00..129,535.59 rows=1,570,659 width=16) (actual time=0.012..199.242 rows=1,061,786 loops=6)

11. 0.096 4.699 ↓ 8.0 730 6 / 6

Hash (cost=227.81..227.81 rows=91 width=4) (actual time=4.699..4.699 rows=730 loops=6)

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
12. 4.603 4.603 ↓ 8.0 730 6 / 6

Seq Scan on bi_calendrier (cost=0.00..227.81 rows=91 width=4) (actual time=0.148..4.603 rows=730 loops=6)

  • Filter: (to_char((jour)::timestamp with time zone, 'YYYY'::text) = ANY ('{2019,2018}'::text[]))
  • Rows Removed by Filter: 8402
13. 1,954.706 1,954.706 ↑ 1.0 1 5,864,119 / 6

Index Scan using pk_mvt_gam on mvt_gam (cost=0.43..0.45 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=5,864,119)

  • Index Cond: (id = mvt_j_heb_fait.id_mvt)
14. 1,954.706 1,954.706 ↑ 1.0 1 5,864,119 / 6

Index Scan using pk_dos_gam on dos_gam (cost=0.43..0.46 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=5,864,119)

  • Index Cond: (id = mvt_gam.id_dos_gam)
15. 0.007 0.040 ↑ 1.0 2 6 / 6

Hash (cost=1.05..1.05 rows=2 width=4) (actual time=0.040..0.040 rows=2 loops=6)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.033 0.033 ↑ 1.0 2 6 / 6

Seq Scan on type_dossier (cost=0.00..1.05 rows=2 width=4) (actual time=0.032..0.033 rows=2 loops=6)

  • Filter: (lb_type_dos = ANY ('{Nouveau-ne,Hospitalise}'::text[]))
  • Rows Removed by Filter: 2
17. 0.431 8.562 ↑ 1.1 2,316 6 / 6

Hash (cost=934.34..934.34 rows=2,560 width=34) (actual time=8.562..8.562 rows=2,316 loops=6)

  • Buckets: 4096 Batches: 1 Memory Usage: 186kB
18. 8.131 8.131 ↑ 1.1 2,316 6 / 6

Seq Scan on struct_commune (cost=0.00..934.34 rows=2,560 width=34) (actual time=0.082..8.131 rows=2,316 loops=6)

  • Filter: (is_defaut_unite AND (lib_uf <> 'UF inconnu'::text) AND (code_uf <> ALL ('{8250,8245,8240}'::text[])))
  • Rows Removed by Filter: 18259
19. 0.001 0.009 ↑ 1.5 2 1

Hash (cost=0.14..0.14 rows=3 width=4) (actual time=0.009..0.009 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.001 0.008 ↑ 1.5 2 1

Subquery Scan on etat_dossier (cost=0.08..0.14 rows=3 width=4) (actual time=0.007..0.008 rows=2 loops=1)

21. 0.005 0.007 ↑ 1.5 2 1

HashAggregate (cost=0.08..0.11 rows=3 width=68) (actual time=0.006..0.007 rows=2 loops=1)

  • Group Key: (1), ('1'::text), ('Dossier en cours'::text)
22. 0.001 0.002 ↑ 1.5 2 1

Append (cost=0.00..0.06 rows=3 width=68) (actual time=0.001..0.002 rows=2 loops=1)

23. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=68) (actual time=0.000..0.000 rows=1 loops=1)

24. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=68) (actual time=0.000..0.000 rows=1 loops=1)

25. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0.00..0.01 rows=1 width=68) (actual time=0.001..0.001 rows=0 loops=1)

  • One-Time Filter: false
Planning time : 1.166 ms
Execution time : 8,839.357 ms