explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xGXL

Settings
# exclusive inclusive rows x rows loops node
1. 0.488 215,975.937 ↓ 9.1 731 1

Limit (cost=48,236.12..48,239.32 rows=80 width=193) (actual time=209,367.886..215,975.937 rows=731 loops=1)

2. 1,561.514 215,975.449 ↓ 9.1 731 1

GroupAggregate (cost=48,236.12..48,239.32 rows=80 width=193) (actual time=209,367.880..215,975.449 rows=731 loops=1)

  • Group Key: (to_char(actes_gam.d_execution, 'YYYY'::text)), uf_executrice_actes_gam.code_elt_struct, uf_executrice_actes_gam.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. 16,213.673 214,413.935 ↓ 34,573.0 2,765,838 1

Sort (cost=48,236.12..48,236.32 rows=80 width=194) (actual time=209,367.443..214,413.935 rows=2,765,838 loops=1)

  • Sort Key: (to_char(actes_gam.d_execution, 'YYYY'::text)), uf_executrice_actes_gam.code_elt_struct, uf_executrice_actes_gam.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: 262176kB
4. 1,176.352 198,200.262 ↓ 34,573.0 2,765,838 1

Nested Loop (cost=1,001.86..48,233.59 rows=80 width=194) (actual time=59.776..198,200.262 rows=2,765,838 loops=1)

5. 0.026 0.194 ↑ 3.3 6 1

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

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

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

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

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

8. 0.013 0.013 ↑ 6.0 1 1

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

  • Filter: (mois_libelle = 'Janvier -> Juin'::text)
  • Rows Removed by Filter: 11
9. 512.227 197,023.716 ↓ 115,243.2 460,973 6

Materialize (cost=1,001.86..48,159.51 rows=4 width=142) (actual time=9.963..32,837.286 rows=460,973 loops=6)

10. 101.537 196,511.489 ↓ 115,243.2 460,973 1

Nested Loop (cost=1,001.86..48,159.49 rows=4 width=142) (actual time=59.705..196,511.489 rows=460,973 loops=1)

11. 179.368 195,948.979 ↓ 115,243.2 460,973 1

Nested Loop (cost=1,001.58..48,158.24 rows=4 width=146) (actual time=59.695..195,948.979 rows=460,973 loops=1)

12. 63.349 193,925.719 ↓ 115,243.2 460,973 1

Nested Loop (cost=1,001.29..48,156.41 rows=4 width=150) (actual time=59.670..193,925.719 rows=460,973 loops=1)

13. 76,870.726 191,557.505 ↓ 153,657.7 460,973 1

Nested Loop (cost=1,000.86..48,150.52 rows=3 width=137) (actual time=59.655..191,557.505 rows=460,973 loops=1)

  • Join Filter: (actes_gam.id_elt_struct_uf_executrice = uf_executrice_actes_gam.id_elt_struct)
  • Rows Removed by Join Filter: 1069014947
14. 9.179 9.179 ↑ 1.0 2,320 1

Seq Scan on uf_executrice_actes_gam (cost=0.00..43.20 rows=2,320 width=34) (actual time=0.011..9.179 rows=2,320 loops=1)

15. 112,996.944 114,677.600 ↓ 153,660.3 460,981 2,320

Materialize (cost=1,000.86..48,002.93 rows=3 width=111) (actual time=0.016..49.430 rows=460,981 loops=2,320)

16. 66.199 1,680.656 ↓ 153,660.3 460,981 1

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

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

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

  • Filter: (lb_type_dos = 'Externe'::text)
  • Rows Removed by Filter: 3
18. 186.767 1,614.449 ↓ 39,818.1 517,635 1

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

  • Workers Planned: 1
  • Workers Launched: 1
19. 0.000 1,427.682 ↓ 32,352.2 258,818 2 / 2

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

20. 30.738 417.922 ↓ 32,352.2 258,818 2 / 2

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

21. 23.980 23.980 ↑ 1.4 12 2 / 2

Parallel Seq Scan on produit (cost=0.00..4,706.13 rows=17 width=95) (actual time=6.985..23.980 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
22. 363.204 363.204 ↓ 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.039..30.267 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
23. 1,035.270 1,035.270 ↑ 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.003..0.004 rows=1 loops=517,635)

  • Index Cond: (id = actes_gam.id_dos_gam)
24. 2,304.865 2,304.865 ↑ 1.0 1 460,973

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,973)

  • Index Cond: (id_acte = actes_gam.id)
25. 1,843.892 1,843.892 ↑ 1.0 1 460,973

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.004 rows=1 loops=460,973)

  • 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
26. 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
Planning time : 4.282 ms
Execution time : 216,044.691 ms