explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1CxK

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 150,920.931 ↓ 0.0 0 1

Limit (cost=47,143,933.98..47,143,934.98 rows=1 width=292) (actual time=150,920.931..150,920.931 rows=0 loops=1)

  • Buffers: shared hit=19,392,100, temp read=955,808 written=1,048,019
2. 0.005 150,920.930 ↓ 0.0 0 1

Sort (cost=47,143,933.98..47,143,934.98 rows=1 width=292) (actual time=150,920.929..150,920.930 rows=0 loops=1)

  • Sort Key: v.source_pk1
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=19,392,100, temp read=955,808 written=1,048,019
3. 0.004 150,920.925 ↓ 0.0 0 1

Merge Join (cost=47,083,291.48..47,143,931.98 rows=1 width=292) (actual time=150,920.925..150,920.925 rows=0 loops=1)

  • Merge Cond: ((((v.source_pk1)::numeric) = sync.source_pk1) AND (v."timestamp" = sync.event_time))
  • Buffers: shared hit=19,392,100, temp read=955,808 written=1,048,019
4. 13,232.722 150,920.263 ↑ 28,973.0 1 1

Sort (cost=47,066,439.60..47,080,926.10 rows=28,973 width=247) (actual time=150,920.263..150,920.263 rows=1 loops=1)

  • Sort Key: ((v.source_pk1)::numeric), v."timestamp
  • Sort Method: external merge Disk: 745,032kB
  • Buffers: shared hit=19,392,028, temp read=955,808 written=1,048,019
5. 1,763.362 137,687.541 ↓ 102.8 2,979,857 1

Subquery Scan on v (cost=46,608,016.59..46,636,989.59 rows=28,973 width=247) (actual time=131,157.852..137,687.541 rows=2,979,857 loops=1)

  • Buffers: shared hit=19,392,024, temp read=893,955 written=893,953
6. 10,610.988 135,924.179 ↓ 102.8 2,979,857 1

HashAggregate (cost=46,608,016.59..46,622,503.09 rows=28,973 width=255) (actual time=131,157.844..135,924.179 rows=2,979,857 loops=1)

  • Group Key: aa.event_type, aa."timestamp", u.user_id, u.firstname, u.lastname, u.email, 'S'::text, aa.course_pk1, cm.batch_uid, cm.course_name, cm.course_name, 0, aa.session_id, 1
  • Buffers: shared hit=19,392,024, temp read=893,955 written=893,953
7. 5,552.795 125,313.191 ↓ 10.3 2,983,745 1

Unique (cost=42,551,824.59..44,290,192.59 rows=289,728 width=255) (actual time=114,028.601..125,313.191 rows=2,983,745 loops=1)

  • Buffers: shared hit=19,392,024, temp read=893,955 written=893,953
8. 71,993.083 119,760.396 ↓ 37.7 10,930,186 1

Sort (cost=42,551,824.59..42,696,688.59 rows=289,728 width=255) (actual time=114,028.601..119,760.396 rows=10,930,186 loops=1)

  • Sort Key: aa.pk1, aa.event_type, aa."timestamp", u.user_id, u.firstname, u.lastname, u.email, aa.course_pk1, cm.batch_uid, cm.course_name, aa.session_id
  • Sort Method: external merge Disk: 2,739,240kB
  • Buffers: shared hit=19,392,024, temp read=893,955 written=893,953
9. 9,523.127 47,767.313 ↓ 37.7 10,930,186 1

Hash Join (cost=839,451.04..37,260,240.87 rows=289,728 width=255) (actual time=470.771..47,767.313 rows=10,930,186 loops=1)

  • Hash Cond: (aa.user_pk1 = u.pk1)
  • Buffers: shared hit=19,392,021, temp read=29,825 written=29,823
10. 1,266.094 37,797.012 ↓ 4.5 3,299,390 1

Merge Join (cost=637.82..35,727,435.64 rows=732,172 width=121) (actual time=0.578..37,797.012 rows=3,299,390 loops=1)

  • Merge Cond: (cm.pk1 = aa.course_pk1)
  • Buffers: shared hit=19,177,704
11. 36.484 36.484 ↓ 1.0 41,283 1

Index Scan using course_main_pk on course_main cm (cost=58.00..26,660.63 rows=41,275 width=76) (actual time=0.009..36.484 rows=41,283 loops=1)

  • Buffers: shared hit=13,435
12. 36,494.434 36,494.434 ↑ 1.0 3,299,413 1

Index Scan using activity_accumulator_ie4 on activity_accumulator aa (cost=114.00..159,009,627.92 rows=3,330,074 width=53) (actual time=0.558..36,494.434 rows=3,299,413 loops=1)

  • Filter: ((data)::text = '/webapps/blackboard/execute/modulepage/view'::text)
  • Rows Removed by Filter: 34,197,847
  • Buffers: shared hit=19,164,269
13. 94.184 447.174 ↓ 2.5 182,375 1

Hash (cost=765,060.22..765,060.22 rows=73,753 width=61) (actual time=447.174..447.174 rows=182,375 loops=1)

  • Buckets: 131,072 (originally 131072) Batches: 2 (originally 1) Memory Usage: 11,265kB
  • Buffers: shared hit=214,317, temp written=974
14. 88.717 352.990 ↓ 2.5 182,375 1

Merge Left Join (cost=168.00..765,060.22 rows=73,753 width=61) (actual time=0.042..352.990 rows=182,375 loops=1)

  • Merge Cond: (u.pk1 = ur.users_pk1)
  • Join Filter: ((u.institution_roles_pk1 = ANY ('{345,138,139,140,141,3}'::bigint[])) OR (ur.institution_roles_pk1 = ANY ('{345,138,139,140,141,3}'::bigint[])))
  • Rows Removed by Join Filter: 50
  • Buffers: shared hit=214,317
15. 116.518 116.518 ↑ 1.0 62,116 1

Index Scan using users_ie2 on users u (cost=84.00..245,420.24 rows=63,574 width=69) (actual time=0.020..116.518 rows=62,116 loops=1)

  • Index Cond: (row_status = '0'::numeric)
  • Filter: (((user_id)::text <> 'bbsupport'::text) AND (available_ind = 'Y'::bpchar))
  • Rows Removed by Filter: 3,383
  • Buffers: shared hit=39,567
16. 147.755 147.755 ↑ 1.0 211,728 1

Index Only Scan using users_inst_roles_uk1 on user_roles ur (cost=84.00..116,429.98 rows=212,470 width=16) (actual time=0.020..147.755 rows=211,728 loops=1)

  • Heap Fetches: 174,433
  • Buffers: shared hit=174,750
17. 0.011 0.658 ↓ 0.0 0 1

Sort (cost=16,851.87..17,525.37 rows=1,347 width=61) (actual time=0.658..0.658 rows=0 loops=1)

  • Sort Key: sync.source_pk1, sync.event_time
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=72
18. 0.647 0.647 ↓ 0.0 0 1

Index Scan using bbc_course_access_sync_id3 on bbc_course_access_sync sync (cost=87.00..2,849.09 rows=1,347 width=61) (actual time=0.647..0.647 rows=0 loops=1)

  • Index Cond: (status = 2)
  • Filter: ((retries < 5) AND ((event_type)::text = 'COURSE_ACCESS'::text))
  • Rows Removed by Filter: 1,740
  • Buffers: shared hit=72
Planning time : 4.022 ms
Execution time : 151,947.394 ms