explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RnEO : Optimization for: plan #Xy62

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 654.046 11,097.072 ↑ 94,502.7 1,893 1

GroupAggregate (cost=357,314,407.18..382,915,409.13 rows=178,893,601 width=186) (actual time=10,121.409..11,097.072 rows=1,893 loops=1)

  • Group Key: ufc.institute_name, ufc.user_name
  • Group Key: ufc.institute_name
  • Functions: 93
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 10.914 ms, Inlining 40.794 ms, Optimization 702.485 ms, Emission 433.851 ms, Total 1188.044 ms
2. 2,184.588 10,443.026 ↑ 160.5 1,141,377 1

Sort (cost=357,314,407.18..357,772,331.53 rows=183,169,738 width=126) (actual time=10,121.384..10,443.026 rows=1,141,377 loops=1)

  • Sort Key: ufc.institute_name, ufc.user_name
  • Sort Method: external merge Disk: 243,904kB
3. 71.243 8,258.438 ↑ 160.5 1,141,377 1

Subquery Scan on ufc (cost=274,062,165.46..284,594,425.40 rows=183,169,738 width=126) (actual time=7,345.721..8,258.438 rows=1,141,377 loops=1)

4. 644.431 8,187.195 ↑ 160.5 1,141,377 1

WindowAgg (cost=274,062,165.46..282,762,728.02 rows=183,169,738 width=170) (actual time=7,345.719..8,187.195 rows=1,141,377 loops=1)

5. 1,144.142 7,542.764 ↑ 160.5 1,141,377 1

Sort (cost=274,062,165.46..274,520,089.81 rows=183,169,738 width=150) (actual time=7,345.304..7,542.764 rows=1,141,377 loops=1)

  • Sort Key: tf.attendance_id, tf.target_approval_level
  • Sort Method: external merge Disk: 275,208kB
6. 413.440 6,398.622 ↑ 160.5 1,141,377 1

WindowAgg (cost=189,708,044.57..193,829,363.68 rows=183,169,738 width=150) (actual time=5,685.432..6,398.622 rows=1,141,377 loops=1)

7. 1,406.424 5,985.182 ↑ 160.5 1,141,377 1

Sort (cost=189,708,044.57..190,165,968.92 rows=183,169,738 width=142) (actual time=5,685.351..5,985.182 rows=1,141,377 loops=1)

  • Sort Key: tf.attendance_id, u.name, tf.target_approval_level
  • Sort Method: external merge Disk: 266,256kB
8. 77.481 4,578.758 ↑ 160.5 1,141,377 1

Nested Loop (cost=15,862.72..111,979,520.79 rows=183,169,738 width=142) (actual time=1,297.962..4,578.758 rows=1,141,377 loops=1)

  • Join Filter: ((racl.institute_id = i.id) OR (racl.institute_id IS NULL))
  • Rows Removed by Join Filter: 78,051
9. 279.905 4,433.531 ↑ 137.1 33,873 1

Hash Left Join (cost=15,412.17..103,829,305.67 rows=4,643,739 width=110) (actual time=1,295.295..4,433.531 rows=33,873 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. 110.581 4,042.002 ↑ 257.0 663 1

Nested Loop (cost=0.00..91,296,389.81 rows=170,391 width=214) (actual time=1,183.637..4,042.002 rows=663 loops=1)

  • Join Filter: (SubPlan 1)
  • Rows Removed by Join Filter: 340,119
11. 0.494 0.494 ↑ 1.0 663 1

Seq Scan on registry_acl racl (cost=0.00..19.63 rows=663 width=84) (actual time=0.021..0.494 rows=663 loops=1)

12. 25.459 1,204.671 ↑ 1.0 514 663

Materialize (cost=0.00..21.71 rows=514 width=34) (actual time=1.779..1.817 rows=514 loops=663)

13. 1,179.212 1,179.212 ↑ 1.0 514 1

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

14.          

SubPlan (for Nested Loop)

15. 0.000 2,726.256 ↑ 1,000.0 1 340,782

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

16. 2,726.256 2,726.256 ↑ 1,000.0 1 340,782

ProjectSet (cost=0.00..5.27 rows=1,000 width=16) (actual time=0.006..0.008 rows=1 loops=340,782)

17. 0.000 0.000 ↑ 1.0 1 340,782

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

18. 7.803 111.624 ↓ 1.7 28,399 1

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

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

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

20. 72.288 101.498 ↓ 1.7 28,399 1

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

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

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

22. 0.335 2.303 ↑ 1.0 2,743 1

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

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

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

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

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

25. 0.283 0.746 ↑ 1.0 2,743 1

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

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

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

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

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

28. 0.003 2.658 ↑ 3.2 36 1

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

29. 0.272 2.655 ↑ 3.2 36 1

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

  • Group Key: i_1.id
30. 0.321 2.383 ↑ 1.0 2,743 1

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

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

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

  • Hash Cond: (ap_1.attendance_id = a_1.id)
32. 0.524 0.524 ↑ 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.003..0.524 rows=6,579 loops=1)

33. 0.319 0.775 ↑ 1.0 2,743 1

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

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

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

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

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

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

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

Execution time : 11,189.965 ms