explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YFfa

Settings
# exclusive inclusive rows x rows loops node
1. 0.181 0.741 ↓ 0.0 0 1

Insert on activity_day (cost=1,342.98..1,371.76 rows=12 width=121) (actual time=0.741..0.741 rows=0 loops=1)

  • Buffers: shared hit=134 read=1 dirtied=13
2.          

CTE dts

3. 0.014 0.045 ↑ 200.0 5 1

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

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

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

  • Sort Key: d.d
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3
5. 0.013 0.013 ↑ 200.0 5 1

Function Scan on generate_series d (cost=0.00..10.00 rows=1,000 width=8) (actual time=0.012..0.013 rows=5 loops=1)

6.          

CTE activities

7. 0.050 0.205 ↓ 9.5 57 1

Sort (cost=145.12..145.14 rows=6 width=54) (actual time=0.190..0.205 rows=57 loops=1)

  • Sort Key: dt_1.week_date
  • Sort Method: quicksort Memory: 29kB
  • Buffers: shared hit=16 read=1 dirtied=2
  • I/O Timings: read=0.006
8. 0.037 0.155 ↓ 9.5 57 1

Hash Join (cost=19.58..145.05 rows=6 width=54) (actual time=0.111..0.155 rows=57 loops=1)

  • Hash Cond: (dt_1.day_num = ti.day_num)
  • Buffers: shared hit=13 read=1 dirtied=2
  • I/O Timings: read=0.006
9. 0.004 0.004 ↑ 5.0 5 1

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

  • Filter: (day_num = ANY ('{1,2,3,4,5}'::bigint[]))
10. 0.022 0.095 ↓ 1.2 57 1

Hash (cost=18.98..18.98 rows=48 width=30) (actual time=0.095..0.095 rows=57 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=5 read=1
  • I/O Timings: read=0.006
11. 0.028 0.073 ↓ 1.2 57 1

Nested Loop (cost=0.57..18.98 rows=48 width=30) (actual time=0.025..0.073 rows=57 loops=1)

  • Buffers: shared hit=5 read=1
  • I/O Timings: read=0.006
12. 0.007 0.007 ↑ 1.0 1 1

Index Scan using template_class_index on template t (cost=0.28..8.29 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1)

  • Index Cond: (class_id = 1,519,479)
  • Buffers: shared hit=3
13. 0.038 0.038 ↓ 1.2 57 1

Index Scan using template_item_template_id on template_item ti (cost=0.29..10.21 rows=48 width=34) (actual time=0.016..0.038 rows=57 loops=1)

  • Index Cond: (template_id = t.template_id)
  • Buffers: shared hit=2 read=1
  • I/O Timings: read=0.006
14.          

SubPlan (for Hash Join)

15. 0.019 0.019 ↑ 1.0 1 1

Index Scan using activity_date_class_idx on activity aa (cost=0.43..16.46 rows=1 width=0) (actual time=0.019..0.019 rows=1 loops=1)

  • Index Cond: ((activity_date = dt_1.week_date) AND (class_id = 1,519,479))
  • Filter: (timeofday_id = ti.timeofday_id)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=5 dirtied=2
16. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on activity aa_1 (cost=32,034.81..45,648.16 rows=4,950 width=8) (never executed)

  • Recheck Cond: (class_id = 1,519,479)
17. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on activity_date_class_idx (cost=0.00..32,033.57 rows=4,950 width=0) (never executed)

  • Index Cond: (class_id = 1,519,479)
18.          

CTE ins_activities

19. 1.199 1.497 ↓ 18.7 56 1

Insert on activity (cost=0.00..0.13 rows=3 width=137) (actual time=0.254..1.497 rows=56 loops=1)

  • Buffers: shared hit=554 read=3 dirtied=18
  • I/O Timings: read=0.752
20. 0.298 0.298 ↓ 18.7 56 1

CTE Scan on activities (cost=0.00..0.13 rows=3 width=137) (actual time=0.216..0.298 rows=56 loops=1)

  • Filter: should_be_added
  • Rows Removed by Filter: 1
  • Buffers: shared hit=80 read=1 dirtied=3
  • I/O Timings: read=0.006
21.          

CTE ins_act_metadata

22. 0.265 1.897 ↓ 0.0 0 1

Insert on activity_metadata (cost=12.64..12.72 rows=3 width=86) (actual time=1.897..1.897 rows=0 loops=1)

  • Buffers: shared hit=851 read=4 dirtied=23
  • I/O Timings: read=0.752
23.          

Initplan (for Insert)

24. 0.003 0.009 ↑ 4.0 1 1

Bitmap Heap Scan on lang (cost=4.18..12.64 rows=4 width=4) (actual time=0.008..0.009 rows=1 loops=1)

  • Recheck Cond: (locale = 'en_US'::text)
  • Heap Blocks: exact=1
  • Buffers: shared hit=2
25. 0.006 0.006 ↑ 4.0 1 1

Bitmap Index Scan on lang_locale_variant_key (cost=0.00..4.18 rows=4 width=0) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (locale = 'en_US'::text)
  • Buffers: shared hit=1
26. 1.623 1.623 ↓ 18.7 56 1

CTE Scan on ins_activities (cost=0.00..0.07 rows=3 width=86) (actual time=0.283..1.623 rows=56 loops=1)

  • Buffers: shared hit=620 read=3 dirtied=19
  • I/O Timings: read=0.752
27. 0.284 0.560 ↑ 6.0 2 1

CTE Scan on dts dt (cost=1,105.16..1,133.94 rows=12 width=121) (actual time=0.543..0.560 rows=2 loops=1)

  • Filter: ((NOT (hashed SubPlan 8)) AND (day_num = ANY ('{1,2,3,4,5}'::bigint[])))
  • Rows Removed by Filter: 3
  • Buffers: shared hit=99 dirtied=2
28.          

SubPlan (for CTE Scan)

29. 0.243 0.276 ↓ 1.1 450 1

Bitmap Heap Scan on activity_day d_1 (cost=11.72..1,104.10 rows=426 width=4) (actual time=0.046..0.276 rows=450 loops=1)

  • Recheck Cond: (class_id = 1,519,479)
  • Heap Blocks: exact=80
  • Buffers: shared hit=85 dirtied=1
30. 0.033 0.033 ↓ 1.1 471 1

Bitmap Index Scan on activity_day_class_id_activity_date_key (cost=0.00..11.62 rows=426 width=0) (actual time=0.033..0.033 rows=471 loops=1)

  • Index Cond: (class_id = 1,519,479)
  • Buffers: shared hit=5
Planning time : 1.030 ms
Execution time : 6.063 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint fk_activity_day_study_id on activity_day 0.060 ms 2 0.030 ms
for constraint fk_activity_timeofday_id on activity 0.893 ms 56 0.016 ms
for constraint fk_activity_investigation_item_id on activity 0.095 ms 56 0.002 ms
for constraint fk_activity_investigation_item_option_id on activity 0.111 ms 56 0.002 ms
for constraint fk_activity_metadata_activity_id on activity_metadata 0.930 ms 56 0.017 ms
for constraint fk_activity_metadata_curriculum_asset_id on activity_metadata 0.118 ms 56 0.002 ms
for constraint fk_activity_metadata_custom_asset_id on activity_metadata 0.078 ms 56 0.001 ms
for constraint fk_activity_metadata_lang_id on activity_metadata 0.825 ms 56 0.015 ms