explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3LIi

Settings
# exclusive inclusive rows x rows loops node
1. 1,502.865 14,915.864 ↓ 2.0 2 1

GroupAggregate (cost=395,686.06..396,025.40 rows=1 width=64) (actual time=13,820.296..14,915.864 rows=2 loops=1)

  • Group Key: (to_char((cdg_date.jour)::timestamp with time zone, 'YYYY'::text))
2. 2,258.250 13,412.999 ↓ 89.6 1,736,751 1

Sort (cost=395,686.06..395,734.54 rows=19,390 width=61) (actual time=12,613.776..13,412.999 rows=1,736,751 loops=1)

  • Sort Key: (to_char((cdg_date.jour)::timestamp with time zone, 'YYYY'::text))
  • Sort Method: quicksort Memory: 189382kB
3. 2,648.953 11,154.749 ↓ 89.6 1,736,751 1

Hash Join (cost=9,556.81..394,305.20 rows=19,390 width=61) (actual time=81.138..11,154.749 rows=1,736,751 loops=1)

  • Hash Cond: (rh_personnel_mois_structure_etat.elt_struct_id = unite_cdg.id)
4. 1,761.178 8,504.960 ↓ 89.6 1,736,751 1

Hash Left Join (cost=9,543.20..393,928.03 rows=19,390 width=37) (actual time=80.252..8,504.960 rows=1,736,751 loops=1)

  • Hash Cond: (rh_personnel_mois_structure_etat.rh_type_paie_id = rh_type_paie.id)
5. 1,742.331 6,741.302 ↓ 89.6 1,736,751 1

Hash Left Join (cost=9,507.77..393,626.33 rows=19,390 width=23) (actual time=77.726..6,741.302 rows=1,736,751 loops=1)

  • Hash Cond: (rh_personnel_mois_structure_etat.rh_fonction_id = rh_fonction.id)
6. 2,621.259 4,998.077 ↓ 89.6 1,736,751 1

Nested Loop (cost=9,495.64..393,347.59 rows=19,390 width=24) (actual time=76.768..4,998.077 rows=1,736,751 loops=1)

7. 0.914 0.914 ↓ 16.0 16 1

Seq Scan on cdg_date (cost=0.00..20.81 rows=1 width=12) (actual time=0.102..0.914 rows=16 loops=1)

  • Filter: ((to_char((jour)::timestamp with time zone, 'YYYY'::text) >= to_char((('now'::cstring)::date + '-1 years -1 mons'::interval), 'YYYY'::text)) AND (to_char((jour)::timestamp with time zone, 'YYYY'::text) <= to_char((('now'::cstring)::date)::timestamp with time zone, 'YYYY'::text)) AND (to_char((jour)::timestamp with time zone, 'MM'::text) <= to_char((('now'::cstring)::date + '-1 mons'::interval), 'MM'::text)))
  • Rows Removed by Filter: 285
8. 2,142.480 2,375.904 ↑ 1.5 108,547 16

Bitmap Heap Scan on rh_personnel_mois_structure_etat (cost=9,495.64..391,704.13 rows=162,264 width=28) (actual time=23.687..148.494 rows=108,547 loops=16)

  • Recheck Cond: ((annee = cdg_date.annee) AND (mois = cdg_date.mois))
  • Heap Blocks: exact=719008
9. 233.424 233.424 ↑ 1.5 108,547 16

Bitmap Index Scan on rh_personnel_mois_structure_etat_annee_idx (cost=0.00..9,455.07 rows=162,264 width=0) (actual time=14.589..14.589 rows=108,547 loops=16)

  • Index Cond: ((annee = cdg_date.annee) AND (mois = cdg_date.mois))
10. 0.403 0.894 ↓ 1.0 319 1

Hash (cost=8.17..8.17 rows=317 width=7) (actual time=0.894..0.894 rows=319 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
11. 0.491 0.491 ↓ 1.0 319 1

Seq Scan on rh_fonction (cost=0.00..8.17 rows=317 width=7) (actual time=0.025..0.491 rows=319 loops=1)

12. 1.243 2.480 ↓ 1.0 914 1

Hash (cost=24.08..24.08 rows=908 width=22) (actual time=2.480..2.480 rows=914 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 58kB
13. 1.237 1.237 ↓ 1.0 914 1

Seq Scan on rh_type_paie (cost=0.00..24.08 rows=908 width=22) (actual time=0.017..1.237 rows=914 loops=1)

14. 0.400 0.836 ↑ 1.0 338 1

Hash (cost=9.38..9.38 rows=338 width=4) (actual time=0.836..0.836 rows=338 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
15. 0.436 0.436 ↑ 1.0 338 1

Seq Scan on unite_cdg (cost=0.00..9.38 rows=338 width=4) (actual time=0.021..0.436 rows=338 loops=1)

Planning time : 2.541 ms
Execution time : 14,929.775 ms