explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MG7O

Settings
# exclusive inclusive rows x rows loops node
1. 0.295 214,883.394 ↑ 2.2 731 1

Limit (cost=49,752.08..49,816.88 rows=1,620 width=192) (actual time=209,666.440..214,883.394 rows=731 loops=1)

2. 1,445.368 214,883.099 ↑ 2.2 731 1

GroupAggregate (cost=49,752.08..49,816.88 rows=1,620 width=192) (actual time=209,666.439..214,883.099 rows=731 loops=1)

  • Group Key: (to_char(actes_gam.d_execution, 'YYYY'::text)), struct_commune_1.code_elt_struct, struct_commune_1.libelle_elt_struct, produit.code, produit.libelle, (CASE WHEN (actes_gam.is_acte_facturable = 1) THEN 'OUI'::text WHEN (actes_gam.is_acte_facturable = 0) THEN 'NON'::text ELSE 'Inconnu'::text END)
3. 14,257.815 213,437.731 ↓ 1,707.3 2,765,838 1

Sort (cost=49,752.08..49,756.13 rows=1,620 width=193) (actual time=209,666.382..213,437.731 rows=2,765,838 loops=1)

  • Sort Key: (to_char(actes_gam.d_execution, 'YYYY'::text)), struct_commune_1.code_elt_struct, struct_commune_1.libelle_elt_struct, produit.code, produit.libelle, (CASE WHEN (actes_gam.is_acte_facturable = 1) THEN 'OUI'::text WHEN (actes_gam.is_acte_facturable = 0) THEN 'NON'::text ELSE 'Inconnu'::text END)
  • Sort Method: external merge Disk: 262160kB
4. 1,178.127 199,179.916 ↓ 1,707.3 2,765,838 1

Nested Loop (cost=2,226.11..49,665.72 rows=1,620 width=193) (actual time=3,084.678..199,179.916 rows=2,765,838 loops=1)

5. 75,155.760 198,001.789 ↓ 5,691.0 460,973 1

Nested Loop (cost=2,226.11..49,560.78 rows=81 width=141) (actual time=3,084.606..198,001.789 rows=460,973 loops=1)

  • Join Filter: (actes_gam.id_elt_struct_uf_executrice = struct_commune_1.id_elt_struct)
  • Rows Removed by Join Filter: 1068996387
6. 24.514 46.109 ↑ 1.7 2,320 1

HashAggregate (cost=1,224.25..1,264.83 rows=4,058 width=36) (actual time=34.756..46.109 rows=2,320 loops=1)

  • Group Key: struct_commune_1.id_elt_struct, struct_commune_1.code_elt_struct, struct_commune_1.libelle_elt_struct, struct_commune_1.type_elt_struct
7. 21.595 21.595 ↑ 1.0 18,560 1

Seq Scan on struct_commune struct_commune_1 (cost=0.00..1,038.65 rows=18,560 width=36) (actual time=0.035..21.595 rows=18,560 loops=1)

  • Filter: (type_elt_struct = 'UF'::text)
  • Rows Removed by Filter: 3972
8. 115,143.240 122,799.920 ↓ 115,243.2 460,973 2,320

Materialize (cost=1,001.86..48,011.90 rows=4 width=116) (actual time=0.020..52.931 rows=460,973 loops=2,320)

9. 149.783 7,656.680 ↓ 115,243.2 460,973 1

Nested Loop (cost=1,001.86..48,011.88 rows=4 width=116) (actual time=28.850..7,656.680 rows=460,973 loops=1)

10. 224.072 7,045.924 ↓ 115,243.2 460,973 1

Nested Loop (cost=1,001.58..48,010.63 rows=4 width=120) (actual time=28.812..7,045.924 rows=460,973 loops=1)

11. 49.082 4,516.947 ↓ 115,245.2 460,981 1

Nested Loop (cost=1,001.29..48,008.80 rows=4 width=124) (actual time=28.765..4,516.947 rows=460,981 loops=1)

12. 76.358 2,162.960 ↓ 153,660.3 460,981 1

Nested Loop (cost=1,000.86..48,002.91 rows=3 width=111) (actual time=28.744..2,162.960 rows=460,981 loops=1)

  • Join Filter: (dos_gam.id_type_dossier = type_dossier.id)
  • Rows Removed by Join Filter: 56654
13. 0.038 0.038 ↑ 1.0 1 1

Seq Scan on type_dossier (cost=0.00..1.05 rows=1 width=4) (actual time=0.036..0.038 rows=1 loops=1)

  • Filter: (lb_type_dos = 'Externe'::text)
  • Rows Removed by Filter: 3
14. 0.000 2,086.564 ↓ 39,818.1 517,635 1

Gather (cost=1,000.86..48,001.70 rows=13 width=115) (actual time=28.702..2,086.564 rows=517,635 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
15. 10.944 2,244.083 ↓ 32,352.2 258,818 2 / 2

Nested Loop (cost=0.86..47,000.40 rows=8 width=115) (actual time=24.431..2,244.083 rows=258,818 loops=2)

16. 37.999 680.234 ↓ 32,352.2 258,818 2 / 2

Nested Loop (cost=0.43..46,990.67 rows=8 width=115) (actual time=24.383..680.234 rows=258,818 loops=2)

17. 34.915 34.915 ↑ 1.4 12 2 / 2

Parallel Seq Scan on produit (cost=0.00..4,706.13 rows=17 width=95) (actual time=10.232..34.915 rows=12 loops=2)

  • Filter: (code = ANY ('{APC,CS,CSF,CSC,CSAVC,CSP,SF,EPH,APY,CSPP,CSPR,CD,APU,CSE,G,CCAVC,CNPSY,MPAVC,CDE,CGP,COE,CCP,CMJ,SP}'::text[]))
  • Rows Removed by Filter: 44892
18. 607.320 607.320 ↓ 539.2 21,568 24 / 2

Index Scan using idx_actes_produit on actes actes_gam (cost=0.43..2,486.93 rows=40 width=28) (actual time=0.055..50.610 rows=21,568 loops=24)

  • Index Cond: (id_produit = produit.id)
  • Filter: (to_char(d_execution, 'YYYY'::text) = ANY ('{2018,2019}'::text[]))
  • Rows Removed by Filter: 1207
19. 1,552.905 1,552.905 ↑ 1.0 1 517,635 / 2

Index Scan using pk_dos_gam on dos_gam (cost=0.43..1.21 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=517,635)

  • Index Cond: (id = actes_gam.id_dos_gam)
20. 2,304.905 2,304.905 ↑ 1.0 1 460,981

Index Scan using idx_f_actes_actes on f_actes f_actes_uf_executrice (cost=0.43..1.95 rows=1 width=17) (actual time=0.005..0.005 rows=1 loops=460,981)

  • Index Cond: (id_acte = actes_gam.id)
21. 2,304.905 2,304.905 ↑ 1.0 1 460,981

Index Scan using idx_struct_commune_id_elt_struct on struct_commune (cost=0.29..0.45 rows=1 width=4) (actual time=0.002..0.005 rows=1 loops=460,981)

  • Index Cond: (id_elt_struct = f_actes_uf_executrice.id_elt_struct_uf_executrice)
  • Filter: (is_defaut_unite AND (lib_jur = 'CH LE HAVRE'::text))
  • Rows Removed by Filter: 7
22. 460.973 460.973 ↑ 1.0 1 460,973

Index Only Scan using idx_bi_calendrier_jour on bi_calendrier (cost=0.29..0.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=460,973)

  • Index Cond: (jour = f_actes_uf_executrice.d_jour)
  • Heap Fetches: 0
23. 0.000 0.000 ↑ 3.3 6 460,973

Materialize (cost=0.00..72.59 rows=20 width=0) (actual time=0.000..0.000 rows=6 loops=460,973)

24. 0.007 0.073 ↑ 3.3 6 1

Nested Loop (cost=0.00..72.49 rows=20 width=0) (actual time=0.062..0.073 rows=6 loops=1)

  • Join Filter: (bi_calendrier_comparaison.mois <= bi_calendrier_mois_cumul.mois)
  • Rows Removed by Join Filter: 6
25. 0.054 0.054 ↓ 1.2 12 1

Seq Scan on bi_calendrier_comparaison (cost=0.00..45.70 rows=10 width=4) (actual time=0.048..0.054 rows=12 loops=1)

  • Filter: (to_char((jour)::timestamp with time zone, 'YYYY'::text) = '2019'::text)
  • Rows Removed by Filter: 37
26. 0.001 0.012 ↑ 6.0 1 12

Materialize (cost=0.00..25.91 rows=6 width=4) (actual time=0.001..0.001 rows=1 loops=12)

27. 0.011 0.011 ↑ 6.0 1 1

Seq Scan on bi_calendrier_mois_cumul (cost=0.00..25.88 rows=6 width=4) (actual time=0.010..0.011 rows=1 loops=1)

  • Filter: (mois_libelle = 'Janvier -> Juin'::text)
  • Rows Removed by Filter: 11
Planning time : 6.445 ms
Execution time : 214,939.446 ms