explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XE4N : Count Lines Added & Removed Last Year -- FIS DB02

Settings
# exclusive inclusive rows x rows loops node
1. 1.051 613.047 ↑ 1.0 1 1

Aggregate (cost=3,802,262.00..3,802,262.01 rows=1 width=16) (actual time=613.047..613.047 rows=1 loops=1)

2. 2.128 611.996 ↓ 2,335.0 2,335 1

Nested Loop (cost=2.86..3,802,261.98 rows=1 width=24) (actual time=12.288..611.996 rows=2,335 loops=1)

  • Join Filter: (ass.sloc_set_id = sm.sloc_set_id)
3. 2.652 410.008 ↓ 5.4 6,662 1

Nested Loop (cost=2.15..3,793,893.86 rows=1,229 width=16) (actual time=7.385..410.008 rows=6,662 loops=1)

4. 1.293 17.876 ↓ 13.4 1,391 1

Nested Loop (cost=1.44..7,815.52 rows=104 width=16) (actual time=3.491..17.876 rows=1,391 loops=1)

  • Join Filter: (c."position" <= ass.as_of)
  • Rows Removed by Join Filter: 16
5. 0.009 0.053 ↑ 18.0 1 1

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

6. 0.033 0.033 ↑ 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.033 rows=1 loops=1)

  • Index Cond: (analysis_id = 39,993,914)
  • Filter: (as_of IS NOT NULL)
7. 0.011 0.011 ↑ 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.010..0.011 rows=1 loops=1)

  • Index Cond: (id = ass.sloc_set_id)
8. 16.530 16.530 ↓ 2.3 1,407 1

Index Scan using index_commits_on_code_set_id_time on commits c (cost=0.58..423.16 rows=614 width=16) (actual time=3.450..16.530 rows=1,407 loops=1)

  • Index Cond: ((code_set_id = ss.code_set_id) AND ("time" < timezone('utc'::text, now())) AND ("time" >= '2019-06-15 11:46:38'::timestamp without time zone))
9. 389.480 389.480 ↑ 8,346.6 5 1,391

Index Scan using index_diffs_on_commit_id on diffs d (cost=0.71..35,987.27 rows=41,733 width=16) (actual time=0.210..0.280 rows=5 loops=1,391)

  • Index Cond: (commit_id = c.id)
10. 199.860 199.860 ↓ 0.0 0 6,662

Index Scan using index_sloc_metrics_on_diff_id on sloc_metrics sm (cost=0.71..5.57 rows=99 width=36) (actual time=0.029..0.030 rows=0 loops=6,662)

  • Index Cond: (diff_id = d.id)
Planning time : 14.323 ms
Execution time : 613.213 ms