explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BGsA

Settings
# exclusive inclusive rows x rows loops node
1. 48.665 121,600.734 ↓ 105.7 15,221 1

Nested Loop (cost=0.71..132,441.09 rows=144 width=12) (actual time=8.265..121,600.734 rows=15,221 loops=1)

2. 0.050 0.050 ↑ 1.0 7 1

Index Only Scan using time_hierarchy_date_val_idx on time_hierarchy (cost=0.28..4.42 rows=7 width=4) (actual time=0.016..0.050 rows=7 loops=1)

  • Index Cond: ((date_val >= '2019-12-30'::date) AND (date_val <= '2020-01-05'::date))
  • Heap Fetches: 7
3. 12.334 12.334 ↓ 98.8 2,174 7

Index Scan using itemcoding_stage_date_complete_idx on itemcoding_stage (cost=0.43..46.92 rows=22 width=16) (actual time=0.025..1.762 rows=2,174 loops=7)

  • Index Cond: ((date_complete = time_hierarchy.date_val) AND (date_complete >= '2019-12-30'::date) AND (date_complete <= '2020-01-05'::date))
4.          

SubPlan (for Nested Loop)

5. 15.221 40,761.838 ↑ 73.0 1 15,221

Unique (cost=305.43..305.80 rows=73 width=4) (actual time=2.677..2.678 rows=1 loops=15,221)

6. 60.884 40,746.617 ↑ 10.6 7 15,221

Sort (cost=305.43..305.62 rows=74 width=4) (actual time=2.677..2.677 rows=7 loops=15,221)

  • Sort Key: iri_week.weekstarting
  • Sort Method: quicksort Memory: 25kB
7. 40,685.733 40,685.733 ↑ 10.6 7 15,221

Seq Scan on iri_week (cost=0.00..303.13 rows=74 width=4) (actual time=2.616..2.673 rows=7 loops=15,221)

  • Filter: ((iri_week)::numeric = itemcoding_stage.week_first_moved)
  • Rows Removed by Filter: 14802
8. 15.221 40,640.070 ↑ 73.0 1 15,221

Unique (cost=305.43..305.80 rows=73 width=4) (actual time=2.669..2.670 rows=1 loops=15,221)

9. 60.884 40,624.849 ↑ 12.3 6 15,221

Sort (cost=305.43..305.62 rows=74 width=4) (actual time=2.669..2.669 rows=6 loops=15,221)

  • Sort Key: iri_week_1.weekstarting
  • Sort Method: quicksort Memory: 25kB
10. 40,563.965 40,563.965 ↑ 12.3 6 15,221

Seq Scan on iri_week iri_week_1 (cost=0.00..303.13 rows=74 width=4) (actual time=2.622..2.665 rows=6 loops=15,221)

  • Filter: ((iri_week)::numeric = itemcoding_stage.wk_1st_eligible)
  • Rows Removed by Filter: 14803
11. 15.221 40,137.777 ↑ 73.0 1 15,221

Unique (cost=305.43..305.80 rows=73 width=4) (actual time=2.636..2.637 rows=1 loops=15,221)

12. 45.663 40,122.556 ↑ 18.5 4 15,221

Sort (cost=305.43..305.62 rows=74 width=4) (actual time=2.636..2.636 rows=4 loops=15,221)

  • Sort Key: iri_week_2.weekstarting
  • Sort Method: quicksort Memory: 25kB
13. 40,076.893 40,076.893 ↑ 18.5 4 15,221

Seq Scan on iri_week iri_week_2 (cost=0.00..303.13 rows=74 width=4) (actual time=2.616..2.633 rows=4 loops=15,221)

  • Filter: ((iri_week)::numeric = itemcoding_stage.week_first_important)
  • Rows Removed by Filter: 14805
Planning time : 0.618 ms
Execution time : 121,603.521 ms