explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xy62

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 669.472 9,461.360 ↑ 24,907.1 1,040 1

GroupAggregate (cost=45,021,967.56..48,728,912.58 rows=25,903,393 width=186) (actual time=8,464.332..9,461.360 rows=1,040 loops=1)

  • Group Key: ufc.institute_name, ufc.user_name
  • Group Key: ufc.institute_name
  • Functions: 95
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 8.253 ms, Inlining 176.978 ms, Optimization 993.772 ms, Emission 621.582 ms, Total 1800.585 ms
2. 2,153.032 8,791.888 ↑ 23.4 1,134,295 1

Sort (cost=45,021,967.56..45,088,273.54 rows=26,522,393 width=126) (actual time=8,464.306..8,791.888 rows=1,134,295 loops=1)

  • Sort Key: ufc.institute_name, ufc.user_name
  • Sort Method: external merge Disk: 242,784kB
3. 71.485 6,638.856 ↑ 23.4 1,134,295 1

Subquery Scan on ufc (cost=35,059,417.57..36,584,455.16 rows=26,522,393 width=126) (actual time=5,748.998..6,638.856 rows=1,134,295 loops=1)

4. 626.993 6,567.371 ↑ 23.4 1,134,295 1

WindowAgg (cost=35,059,417.57..36,319,231.23 rows=26,522,393 width=170) (actual time=5,748.996..6,567.371 rows=1,134,295 loops=1)

5. 1,180.946 5,940.378 ↑ 23.4 1,134,295 1

Sort (cost=35,059,417.57..35,125,723.55 rows=26,522,393 width=150) (actual time=5,748.585..5,940.378 rows=1,134,295 loops=1)

  • Sort Key: tf.attendance_id, tf.target_approval_level
  • Sort Method: external merge Disk: 274,072kB
6. 433.212 4,759.432 ↑ 23.4 1,134,295 1

WindowAgg (cost=25,209,269.83..25,806,023.67 rows=26,522,393 width=150) (actual time=4,031.014..4,759.432 rows=1,134,295 loops=1)

7. 1,357.736 4,326.220 ↑ 23.4 1,134,295 1

Sort (cost=25,209,269.83..25,275,575.81 rows=26,522,393 width=142) (actual time=4,030.839..4,326.220 rows=1,134,295 loops=1)

  • Sort Key: tf.attendance_id, u.name, tf.target_approval_level
  • Sort Method: external merge Disk: 265,176kB
8. 88.294 2,968.484 ↑ 23.4 1,134,295 1

Nested Loop (cost=15,862.72..16,227,836.43 rows=26,522,393 width=142) (actual time=2,118.018..2,968.484 rows=1,134,295 loops=1)

  • Join Filter: ((racl.institute_id = i.id) OR (racl.institute_id IS NULL))
  • Rows Removed by Join Filter: 64,721
9. 280.391 2,813.578 ↑ 20.2 33,306 1

Hash Left Join (cost=15,412.17..15,047,323.01 rows=672,399 width=110) (actual time=2,114.162..2,813.578 rows=33,306 loops=1)

  • Hash Cond: (racl.registry_id = tf.registry_id)
  • Join Filter: (((racl.form_type_id IS NULL) OR (racl.form_type_id = tf.form_type_id)) AND ((racl.institute_id IS NULL) OR (racl.institute_id = tf.institute_id)) AND ((racl.physician_id IS NULL) OR (racl.physician_id = tf.physician_id)) AND (((tf.target_approval_level = 0) AND (u.id = tf.owner_id)) OR (('frm_approve_'::text || (tf.target_approval_level)::text) = ANY ((to_registry_perm_flags(racl.permission_mask))))))
  • Rows Removed by Join Filter: 2,693,058
10. 32.337 2,228.960 ↑ 257.0 96 1

Nested Loop (cost=0.00..13,219,421.63 rows=24,672 width=214) (actual time=1,809.476..2,228.960 rows=96 loops=1)

  • Join Filter: (SubPlan 1)
  • Rows Removed by Join Filter: 49,248
11. 0.815 0.815 ↑ 1.0 514 1

Seq Scan on users u (cost=0.00..19.14 rows=514 width=34) (actual time=0.286..0.815 rows=514 loops=1)

12. 3.008 1,801.056 ↑ 1.0 96 514

Materialize (cost=0.00..21.77 rows=96 width=84) (actual time=3.498..3.504 rows=96 loops=514)

13. 1,798.048 1,798.048 ↑ 1.0 96 1

Seq Scan on registry_acl racl (cost=0.00..21.29 rows=96 width=84) (actual time=1,797.932..1,798.048 rows=96 loops=1)

  • Filter: (registry_id = 'ce0f4190-a4dc-4a76-a4c8-a993d44cef90'::uuid)
  • Rows Removed by Filter: 567
14.          

SubPlan (for Nested Loop)

15. 0.000 394.752 ↑ 1,000.0 1 49,344

Result (cost=0.00..267.76 rows=1,000 width=1) (actual time=0.007..0.008 rows=1 loops=49,344)

16. 394.752 394.752 ↑ 1,000.0 1 49,344

ProjectSet (cost=0.00..5.27 rows=1,000 width=16) (actual time=0.007..0.008 rows=1 loops=49,344)

17. 0.000 0.000 ↑ 1.0 1 49,344

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=49,344)

18. 13.685 304.227 ↓ 1.7 28,399 1

Hash (cost=15,202.99..15,202.99 rows=16,735 width=124) (actual time=304.227..304.227 rows=28,399 loops=1)

  • Buckets: 32,768 (originally 32768) Batches: 2 (originally 1) Memory Usage: 4,694kB
19. 3.489 290.542 ↓ 1.7 28,399 1

Subquery Scan on tf (cost=461.88..15,202.99 rows=16,735 width=124) (actual time=27.753..290.542 rows=28,399 loops=1)

20. 120.004 287.053 ↓ 1.7 28,399 1

Hash Join (cost=461.88..15,035.64 rows=16,735 width=124) (actual time=27.750..287.053 rows=28,399 loops=1)

  • Hash Cond: (f.attendance_period_id = ap.id)
21. 149.431 149.431 ↑ 1.0 40,138 1

Seq Scan on forms f (cost=0.00..10,009.38 rows=40,138 width=178) (actual time=2.673..149.431 rows=40,138 loops=1)

22. 1.724 17.618 ↑ 1.0 2,743 1

Hash (cost=427.60..427.60 rows=2,743 width=64) (actual time=17.618..17.618 rows=2,743 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 290kB
23. 3.857 15.894 ↑ 1.0 2,743 1

Hash Join (cost=220.53..427.60 rows=2,743 width=64) (actual time=7.103..15.894 rows=2,743 loops=1)

  • Hash Cond: (ap.attendance_id = a.id)
24. 5.769 5.769 ↑ 1.0 6,579 1

Seq Scan on attendance_periods ap (cost=0.00..189.79 rows=6,579 width=48) (actual time=0.746..5.769 rows=6,579 loops=1)

25. 1.352 6.268 ↑ 1.0 2,743 1

Hash (cost=186.24..186.24 rows=2,743 width=32) (actual time=6.268..6.268 rows=2,743 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 204kB
26. 4.916 4.916 ↑ 1.0 2,743 1

Seq Scan on attendances a (cost=0.00..186.24 rows=2,743 width=32) (actual time=0.481..4.916 rows=2,743 loops=1)

  • Filter: (registry_id = 'ce0f4190-a4dc-4a76-a4c8-a993d44cef90'::uuid)
  • Rows Removed by Filter: 3,836
27. 62.781 66.612 ↑ 3.2 36 33,306

Materialize (cost=450.54..453.47 rows=117 width=64) (actual time=0.000..0.002 rows=36 loops=33,306)

28. 0.005 3.831 ↑ 3.2 36 1

Subquery Scan on i (cost=450.54..452.88 rows=117 width=64) (actual time=3.819..3.831 rows=36 loops=1)

29. 0.371 3.826 ↑ 3.2 36 1

HashAggregate (cost=450.54..451.71 rows=117 width=528) (actual time=3.818..3.826 rows=36 loops=1)

  • Group Key: i_1.id
30. 0.420 3.455 ↑ 1.0 2,743 1

Hash Join (cost=229.16..443.68 rows=2,743 width=64) (actual time=1.523..3.455 rows=2,743 loops=1)

  • Hash Cond: (ap_1.institute_id = i_1.id)
31. 0.882 2.319 ↑ 1.0 2,743 1

Hash Join (cost=220.53..427.60 rows=2,743 width=16) (actual time=0.800..2.319 rows=2,743 loops=1)

  • Hash Cond: (ap_1.attendance_id = a_1.id)
32. 0.666 0.666 ↑ 1.0 6,579 1

Seq Scan on attendance_periods ap_1 (cost=0.00..189.79 rows=6,579 width=32) (actual time=0.004..0.666 rows=6,579 loops=1)

33. 0.306 0.771 ↑ 1.0 2,743 1

Hash (cost=186.24..186.24 rows=2,743 width=16) (actual time=0.771..0.771 rows=2,743 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 161kB
34. 0.465 0.465 ↑ 1.0 2,743 1

Seq Scan on attendances a_1 (cost=0.00..186.24 rows=2,743 width=16) (actual time=0.008..0.465 rows=2,743 loops=1)

  • Filter: (registry_id = 'ce0f4190-a4dc-4a76-a4c8-a993d44cef90'::uuid)
  • Rows Removed by Filter: 3,836
35. 0.027 0.716 ↑ 1.0 117 1

Hash (cost=7.17..7.17 rows=117 width=64) (actual time=0.716..0.716 rows=117 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
36. 0.689 0.689 ↑ 1.0 117 1

Seq Scan on institutes i_1 (cost=0.00..7.17 rows=117 width=64) (actual time=0.253..0.689 rows=117 loops=1)

Execution time : 9,736.028 ms