explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mt8E

Settings
# exclusive inclusive rows x rows loops node
1. 10.540 13.909 ↓ 0.0 0 1

Insert on activity_day (cost=186,011.29..186,040.07 rows=12 width=121) (actual time=13.908..13.909 rows=0 loops=1)

  • Buffers: shared hit=87 read=6 dirtied=15
  • I/O Timings: read=13.586
2.          

CTE dts

3. 0.017 0.052 ↑ 200.0 5 1

WindowAgg (cost=59.83..79.83 rows=1,000 width=20) (actual time=0.042..0.052 rows=5 loops=1)

  • Buffers: shared hit=3
4. 0.035 0.035 ↑ 200.0 5 1

Sort (cost=59.83..62.33 rows=1,000 width=8) (actual time=0.033..0.035 rows=5 loops=1)

  • Sort Key: d.d
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3
  • -> Function Scan on generate_series d (cost=0.00..10.00 rows=1000 width=8) (actual time=0.011..0.012 rows=5
5.          

CTE activities

6. 0.066 3,949.656 ↓ 9.2 55 1

Sort (cost=183,311.32..183,311.34 rows=6 width=54) (actual time=3,949.641..3,949.656 rows=55 loops=1)

  • Sort Key: dt_1.week_date
  • Sort Method: quicksort Memory: 29kB
  • Buffers: shared hit=76692 read=2
  • I/O Timings: read=0.980
7. 0.090 3,949.590 ↓ 9.2 55 1

Nested Loop (cost=8.45..183,311.25 rows=6 width=54) (actual time=3,572.128..3,949.590 rows=55 loops=1)

  • Join Filter: (ti.day_num = dt_1.day_num)
  • Rows Removed by Join Filter: 220
  • Buffers: shared hit=76689 read=2
  • I/O Timings: read=0.980
  • -> Index Scan using activity_date_class_idx on activity aa (cost=0.57..14.02 rows=1 width=0) (never execu
  • Index Cond: ((activity_date = dt_1.week_date) AND (class_id = 1519479))
  • Filter: (timeofday_id = ti.timeofday_id)
  • -> Index Scan using activity_date_class_idx on activity aa_1 (cost=0.57..2774095.59 rows=5717 width=8) (n
  • Index Cond: (class_id = 1519479)
8. 0.010 0.010 ↑ 5.0 5 1

CTE Scan on dts dt_1 (cost=0.00..26.25 rows=25 width=12) (actual time=0.002..0.010 rows=5 loops=1)

  • Filter: (day_num = ANY ('{1,2,3,4,5}'::bigint[]))
9. 0.096 3,949.490 ↓ 1.2 55 5

Materialize (cost=8.45..183,184.07 rows=45 width=31) (actual time=714.424..789.898 rows=55 loops=5)

  • Buffers: shared hit=76689 read=2
  • I/O Timings: read=0.980
10. 3,948.395 3,949.394 ↓ 1.2 55 1

Hash Join (cost=8.45..183,183.85 rows=45 width=31) (actual time=3,572.110..3,949.394 rows=55 loops=1)

  • Hash Cond: (ti.template_id = t.template_id)
  • Buffers: shared hit=76689 read=2
  • I/O Timings: read=0.980
  • -> Seq Scan on template_item ti (cost=0.00..161033.85 rows=8434685 width=35) (actual time=0.007
  • Buffers: shared hit=76687
11. 0.999 0.999 ↑ 1.0 1 1

Hash (cost=8.44..8.44 rows=1 width=4) (actual time=0.999..0.999 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=2 read=2
  • I/O Timings: read=0.980
  • -> Index Scan using template_class_index on template t (cost=0.42..8.44 rows=1 width=4) (
  • Index Cond: (class_id = 1519479)
  • Buffers: shared hit=2 read=2
  • I/O Timings: read=0.980
12.          

CTE ins_activities

13. 0.497 3,950.295 ↓ 18.3 55 1

Insert on activity (cost=0.00..0.13 rows=3 width=137) (actual time=3,949.788..3,950.295 rows=55 loops=1)

  • Buffers: shared hit=77318 read=2 dirtied=18
  • I/O Timings: read=0.980
14. 3,949.798 3,949.798 ↓ 18.3 55 1

CTE Scan on activities (cost=0.00..0.13 rows=3 width=137) (actual time=3,949.708..3,949.798 rows=55 loops=1)

  • Filter: should_be_added
  • Buffers: shared hit=76756 read=2 dirtied=1
  • I/O Timings: read=0.980
15.          

CTE ins_act_metadata

16. 1.499 3,951.915 ↓ 0.0 0 1

Insert on activity_metadata (cost=1.02..1.10 rows=3 width=86) (actual time=3,951.915..3,951.915 rows=0 loops=1)

  • Buffers: shared hit=77564 read=6 dirtied=25
  • I/O Timings: read=2.137
17.          

Initplan (for Insert)

18. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on lang (cost=0.00..1.02 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=1)

  • Filter: (locale = 'en_US'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1
19. 3,950.410 3,950.410 ↓ 18.3 55 1

CTE Scan on ins_activities (cost=0.00..0.07 rows=3 width=86) (actual time=3,949.825..3,950.410 rows=55 loops=1)

  • Buffers: shared hit=77382 read=2 dirtied=19
  • I/O Timings: read=0.980
20. 3.369 3.369 ↑ 2.4 5 1

CTE Scan on dts dt (cost=2,618.89..2,647.67 rows=12 width=121) (actual time=3.344..3.369 rows=5 loops=1)

  • Filter: ((NOT (hashed SubPlan 8)) AND (day_num = ANY ('{1,2,3,4,5}'::bigint[])))
  • Buffers: shared hit=18 read=2 dirtied=1
  • I/O Timings: read=3.214
  • -> Bitmap Heap Scan on activity_day d_1 (cost=13.72..2617.18 rows=682 width=4) (actual time=1.255..1.255 rows=0 l
  • Recheck Cond: (class_id = 1519479)
  • Buffers: shared hit=2 read=1
  • I/O Timings: read=1.245
  • -> Bitmap Index Scan on activity_day_class_idx (cost=0.00..13.55 rows=682 width=0) (actual time=1.253..1.25
  • Index Cond: (class_id = 1519479)
  • Buffers: shared hit=2 read=1
  • I/O Timings: read=1.245
Planning time : 9.390 ms
Execution time : 3,969.835 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint fk_activity_day_study_id on activity_day 0.531 ms 5 0.106 ms
for constraint fk_activity_investigation_item_id on activity 0.110 ms 55 0.002 ms
for constraint fk_activity_investigation_item_option_id on activity 0.077 ms 55 0.001 ms
for constraint fk_activity_timeofday_id on activity 0.940 ms 55 0.017 ms
for constraint fk_activity_metadata_curriculum_asset_id on activity_metadata 0.111 ms 55 0.002 ms
for constraint fk_activity_metadata_custom_asset_id on activity_metadata 0.078 ms 55 0.001 ms
for constraint fk_activity_metadata_lang_id on activity_metadata 0.676 ms 55 0.012 ms
for constraint fk_activity_metadata_activity_id on activity_metadata 0.863 ms 55 0.016 ms