explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tFyG : SLOC metric by month -- FIS DB02

Settings
# exclusive inclusive rows x rows loops node
1. 4.166 616.757 ↓ 193.0 193 1

GroupAggregate (cost=2,432,400.05..2,432,400.10 rows=1 width=77) (actual time=612.110..616.757 rows=193 loops=1)

  • Group Key: (date_trunc('month'::text, c."time")), k.preferred_name_id, m.language_id, c.on_trunk
2. 6.619 612.591 ↓ 5,468.0 5,468 1

Sort (cost=2,432,400.05..2,432,400.06 rows=1 width=49) (actual time=612.003..612.591 rows=5,468 loops=1)

  • Sort Key: (date_trunc('month'::text, c."time")), k.preferred_name_id, m.language_id, c.on_trunk
  • Sort Method: quicksort Memory: 961kB
3. 11.664 605.972 ↓ 5,468.0 5,468 1

Nested Loop (cost=4.01..2,432,400.04 rows=1 width=49) (actual time=15.035..605.972 rows=5,468 loops=1)

  • Join Filter: (ass.sloc_set_id = m.sloc_set_id)
4. 16.435 507.984 ↓ 12,332.0 12,332 1

Nested Loop (cost=3.30..2,432,393.21 rows=1 width=37) (actual time=12.184..507.984 rows=12,332 loops=1)

  • Join Filter: (c.code_set_id = f.code_set_id)
5. 10.013 269.573 ↓ 16.6 12,332 1

Nested Loop (cost=2.72..2,430,681.61 rows=743 width=53) (actual time=7.750..269.573 rows=12,332 loops=1)

6. 5.037 35.635 ↓ 54.7 3,445 1

Nested Loop (cost=2.01..120,623.84 rows=63 width=37) (actual time=5.836..35.635 rows=3,445 loops=1)

7. 1.778 9.928 ↑ 1.1 3,445 1

Nested Loop (cost=1.44..111,887.93 rows=3,820 width=37) (actual time=0.085..9.928 rows=3,445 loops=1)

8. 0.011 0.062 ↑ 18.0 1 1

Nested Loop (cost=0.86..60.47 rows=18 width=16) (actual time=0.044..0.062 rows=1 loops=1)

9. 0.031 0.031 ↑ 18.0 1 1

Index Scan using index_analysis_sloc_sets_on_analysis_id_sloc_set_id on analysis_sloc_sets ass (cost=0.43..16.41 rows=18 width=8) (actual time=0.020..0.031 rows=1 loops=1)

  • Index Cond: (analysis_id = 39,993,914)
  • Filter: (as_of IS NOT NULL)
10. 0.020 0.020 ↑ 1.0 1 1

Index Scan using sloc_sets_pkey on sloc_sets ss (cost=0.43..2.45 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=1)

  • Index Cond: (id = ass.sloc_set_id)
11. 8.088 8.088 ↑ 2.2 3,445 1

Index Scan using index_on_commits_code_set_id_position on commits c (cost=0.57..6,137.59 rows=7,505 width=29) (actual time=0.039..8.088 rows=3,445 loops=1)

  • Index Cond: ((code_set_id = ss.code_set_id) AND ("position" <= ass.as_of))
  • Filter: (("time" >= '1971-01-01 00:00:00'::timestamp without time zone) AND ("time" < timezone('utc'::text, now())))
12. 20.670 20.670 ↑ 1.0 1 3,445

Index Scan using analysis_aliases_analysis_id_commit_name_id on analysis_aliases k (cost=0.57..2.29 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=3,445)

  • Index Cond: ((analysis_id = 39,993,914) AND (commit_name_id = c.name_id))
13. 223.925 223.925 ↑ 10,433.2 4 3,445

Index Scan using index_diffs_on_commit_id on diffs d (cost=0.71..36,250.25 rows=41,733 width=24) (actual time=0.051..0.065 rows=4 loops=3,445)

  • Index Cond: (commit_id = c.id)
14. 221.976 221.976 ↑ 1.0 1 12,332

Index Scan using fyles_pkey on fyles f (cost=0.58..2.29 rows=1 width=12) (actual time=0.018..0.018 rows=1 loops=12,332)

  • Index Cond: (id = d.fyle_id)
15. 86.324 86.324 ↓ 0.0 0 12,332

Index Scan using index_sloc_metrics_on_diff_id on sloc_metrics m (cost=0.71..5.59 rows=99 width=40) (actual time=0.007..0.007 rows=0 loops=12,332)

  • Index Cond: (diff_id = d.id)
Planning time : 100.362 ms
Execution time : 617.177 ms