explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PQHU : Optimization for: plan #P1Js

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 637.262 5,582.871 ↑ 193.4 1,134,295 1

WindowAgg (cost=231,321,766.22..242,838,251.27 rows=219,361,620 width=170) (actual time=4,756.863..5,582.871 rows=1,134,295 loops=1)

  • Functions: 88
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 6.555 ms, Inlining 26.595 ms, Optimization 685.222 ms, Emission 390.813 ms, Total 1109.185 ms
2. 1,139.158 4,945.609 ↑ 193.4 1,134,295 1

Sort (cost=231,321,766.22..231,870,170.27 rows=219,361,620 width=166) (actual time=4,756.425..4,945.609 rows=1,134,295 loops=1)

  • Sort Key: tf.attendance_id, tf.target_approval_level
  • Sort Method: external merge Disk: 291,832kB
3. 396.342 3,806.451 ↑ 193.4 1,134,295 1

WindowAgg (cost=124,016,931.80..128,952,568.25 rows=219,361,620 width=166) (actual time=3,139.750..3,806.451 rows=1,134,295 loops=1)

4. 1,352.232 3,410.109 ↑ 193.4 1,134,295 1

Sort (cost=124,016,931.80..124,565,335.85 rows=219,361,620 width=158) (actual time=3,139.682..3,410.109 rows=1,134,295 loops=1)

  • Sort Key: tf.attendance_id, uracl.user_name, tf.target_approval_level
  • Sort Method: external merge Disk: 282,944kB
5. 83.695 2,057.877 ↑ 193.4 1,134,295 1

Nested Loop (cost=123,186.31..24,646,813.84 rows=219,361,620 width=158) (actual time=1,270.077..2,057.877 rows=1,134,295 loops=1)

  • Join Filter: ((uracl.institute_id = i.id) OR (uracl.institute_id IS NULL))
  • Rows Removed by Join Filter: 64,721
6. 352.059 1,907.570 ↑ 167.0 33,306 1

Merge Left Join (cost=122,735.77..14,886,314.26 rows=5,561,280 width=126) (actual time=1,267.389..1,907.570 rows=33,306 loops=1)

  • Merge Cond: (uracl.registry_id = tf.registry_id)
  • Join Filter: (((uracl.form_type_id IS NULL) OR (uracl.form_type_id = tf.form_type_id)) AND ((uracl.institute_id IS NULL) OR (uracl.institute_id = tf.institute_id)) AND ((uracl.physician_id IS NULL) OR (uracl.physician_id = tf.physician_id)) AND (((tf.target_approval_level = 0) AND (uracl.user_id = tf.owner_id)) OR (('frm_approve_'::text || (tf.target_approval_level)::text) = ANY (uracl.permissions))))
  • Rows Removed by Join Filter: 2,693,058
7. 0.107 1,138.111 ↑ 2,125.6 96 1

Sort (cost=106,358.77..106,868.92 rows=204,058 width=130) (actual time=1,138.068..1,138.111 rows=96 loops=1)

  • Sort Key: uracl.registry_id
  • Sort Method: quicksort Memory: 50kB
8. 0.026 1,138.004 ↑ 2,125.6 96 1

Subquery Scan on uracl (cost=22.75..74,411.26 rows=204,058 width=130) (actual time=1,104.569..1,138.004 rows=96 loops=1)

9. 2.440 1,137.978 ↑ 2,125.6 96 1

Nested Loop (cost=22.75..72,370.68 rows=204,058 width=214) (actual time=1,104.567..1,137.978 rows=96 loops=1)

10. 0.112 0.112 ↑ 1.0 514 1

Seq Scan on users (cost=0.00..19.14 rows=514 width=34) (actual time=0.010..0.112 rows=514 loops=1)

11. 4.626 1,135.426 ↓ 0.0 0 514

Hash Join (cost=22.75..60.25 rows=397 width=68) (actual time=2.207..2.209 rows=0 loops=514)

  • Hash Cond: (racl.trustee_id = trustee_id.trustee_id)
12. 1,124.118 1,124.118 ↑ 1.0 96 514

Seq Scan on registry_acl racl (cost=0.00..21.29 rows=96 width=84) (actual time=2.173..2.187 rows=96 loops=514)

  • Filter: (registry_id = 'ce0f4190-a4dc-4a76-a4c8-a993d44cef90'::uuid)
  • Rows Removed by Filter: 567
13. 0.000 6.682 ↑ 1,000.0 1 514

Hash (cost=10.25..10.25 rows=1,000 width=16) (actual time=0.013..0.013 rows=1 loops=514)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 6.682 6.682 ↑ 1,000.0 1 514

Function Scan on get_trustee_list trustee_id (cost=0.25..10.25 rows=1,000 width=16) (actual time=0.013..0.013 rows=1 loops=514)

15. 305.522 417.400 ↓ 162.9 2,726,209 1

Sort (cost=16,377.00..16,418.83 rows=16,735 width=124) (actual time=128.678..417.400 rows=2,726,209 loops=1)

  • Sort Key: tf.registry_id
  • Sort Method: external sort Disk: 3,960kB
16. 2.583 111.878 ↓ 1.7 28,399 1

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

17. 79.235 109.295 ↓ 1.7 28,399 1

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

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

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

19. 0.334 2.310 ↑ 1.0 2,743 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 290kB
20. 0.726 1.976 ↑ 1.0 2,743 1

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

  • Hash Cond: (ap.attendance_id = a.id)
21. 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)

22. 0.282 0.759 ↑ 1.0 2,743 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 204kB
23. 0.477 0.477 ↑ 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.477 rows=2,743 loops=1)

  • Filter: (registry_id = 'ce0f4190-a4dc-4a76-a4c8-a993d44cef90'::uuid)
  • Rows Removed by Filter: 3,836
24. 63.936 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)

25. 0.003 2.676 ↑ 3.2 36 1

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

26. 0.273 2.673 ↑ 3.2 36 1

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

  • Group Key: i_1.id
27. 0.325 2.400 ↑ 1.0 2,743 1

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

  • Hash Cond: (ap_1.institute_id = i_1.id)
28. 0.750 2.035 ↑ 1.0 2,743 1

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

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

30. 0.261 0.732 ↑ 1.0 2,743 1

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

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

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

  • Filter: (registry_id = 'ce0f4190-a4dc-4a76-a4c8-a993d44cef90'::uuid)
  • Rows Removed by Filter: 3,836
32. 0.017 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
33. 0.023 0.023 ↑ 1.0 117 1

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

Execution time : 5,696.913 ms