explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vDBF : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #P1Js; plan #PQHU; plan #6cOj; plan #J8kif; plan #dMxR; plan #sGcK; plan #Al8j; plan #hUBw; plan #km2t

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 718.759 9,503.273 ↑ 249,068.6 1,040 1

GroupAggregate (cost=387,081,875.13..424,151,190.08 rows=259,031,312 width=186) (actual time=8,444.310..9,503.273 rows=1,040 loops=1)

  • Group Key: ufc.institute_name, ufc.user_name
  • Group Key: ufc.institute_name
  • Functions: 112
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 10.586 ms, Inlining 33.262 ms, Optimization 731.763 ms, Emission 465.012 ms, Total 1240.622 ms
2. 2,673.419 8,784.514 ↑ 221.4 1,197,701 1

Sort (cost=387,081,875.13..387,744,932.86 rows=265,223,091 width=126) (actual time=8,444.287..8,784.514 rows=1,197,701 loops=1)

  • Sort Key: ufc.institute_name, ufc.user_name
  • Sort Method: external merge Disk: 255,088kB
3. 77.430 6,111.095 ↑ 221.4 1,197,701 1

Subquery Scan on ufc (cost=265,827,499.76..281,077,827.50 rows=265,223,091 width=126) (actual time=5,126.646..6,111.095 rows=1,197,701 loops=1)

4. 704.011 6,033.665 ↑ 221.4 1,197,701 1

WindowAgg (cost=265,827,499.76..278,425,596.59 rows=265,223,091 width=170) (actual time=5,126.644..6,033.665 rows=1,197,701 loops=1)

5. 1,226.221 5,329.654 ↑ 221.4 1,197,701 1

Sort (cost=265,827,499.76..266,490,557.49 rows=265,223,091 width=150) (actual time=5,126.227..5,329.654 rows=1,197,701 loops=1)

  • Sort Key: tf.attendance_id, tf.target_approval_level
  • Sort Method: external merge Disk: 288,048kB
6. 443.550 4,103.433 ↑ 221.4 1,197,701 1

WindowAgg (cost=142,977,638.58..148,945,158.13 rows=265,223,091 width=150) (actual time=3,367.595..4,103.433 rows=1,197,701 loops=1)

7. 1,476.809 3,659.883 ↑ 221.4 1,197,701 1

Sort (cost=142,977,638.58..143,640,696.31 rows=265,223,091 width=142) (actual time=3,367.524..3,659.883 rows=1,197,701 loops=1)

  • Sort Key: tf.attendance_id, users.name, tf.target_approval_level
  • Sort Method: external merge Disk: 278,664kB
8. 93.858 2,183.074 ↑ 221.4 1,197,701 1

Nested Loop (cost=70,663.27..29,721,394.95 rows=265,223,091 width=142) (actual time=1,358.402..2,183.074 rows=1,197,701 loops=1)

  • Join Filter: ((racl.institute_id = i.id) OR (racl.institute_id IS NULL))
  • Rows Removed by Join Filter: 223,831
9. 370.408 2,010.242 ↑ 170.3 39,487 1

Merge Left Join (cost=70,212.73..17,920,383.20 rows=6,723,965 width=110) (actual time=1,355.726..2,010.242 rows=39,487 loops=1)

  • Merge 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 = ANY ('{0,5}'::integer[])) AND (users.id = tf.owner_id)) OR (('frm_approve_'::text || (tf.target_approval_level)::text) = ANY (racl.permissions))))
  • Rows Removed by Join Filter: 2,686,870
10. 0.069 1,238.614 ↑ 2,570.0 96 1

Sort (cost=53,835.74..54,452.54 rows=246,720 width=130) (actual time=1,238.574..1,238.614 rows=96 loops=1)

  • Sort Key: racl.registry_id
  • Sort Method: quicksort Memory: 50kB
11. 0.085 1,238.545 ↑ 2,570.0 96 1

Hash Join (cost=47.45..14,872.36 rows=246,720 width=130) (actual time=1,234.724..1,238.545 rows=96 loops=1)

  • Hash Cond: ((get_trustee_list(users.id)) = racl.trustee_id)
12. 1,237.315 1,237.396 ↑ 1,000.0 514 1

ProjectSet (cost=0.00..2,720.21 rows=514,000 width=356) (actual time=1,233.610..1,237.396 rows=514 loops=1)

13. 0.081 0.081 ↑ 1.0 514 1

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

14. 0.019 1.064 ↑ 1.0 96 1

Hash (cost=46.25..46.25 rows=96 width=112) (actual time=1.063..1.064 rows=96 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
15. 0.012 1.045 ↑ 1.0 96 1

Subquery Scan on racl (cost=0.00..46.25 rows=96 width=112) (actual time=0.123..1.045 rows=96 loops=1)

16. 1.033 1.033 ↑ 1.0 96 1

Seq Scan on registry_acl racl_1 (cost=0.00..45.29 rows=96 width=180) (actual time=0.121..1.033 rows=96 loops=1)

  • Filter: (registry_id = 'ce0f4190-a4dc-4a76-a4c8-a993d44cef90'::uuid)
  • Rows Removed by Filter: 567
17. 298.401 401.220 ↓ 162.9 2,726,209 1

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

  • Sort Key: tf.registry_id
  • Sort Method: external sort Disk: 3,960kB
18. 2.435 102.819 ↓ 1.7 28,399 1

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

19. 74.476 100.384 ↓ 1.7 28,399 1

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

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

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

21. 0.356 2.408 ↑ 1.0 2,743 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 290kB
22. 0.793 2.052 ↑ 1.0 2,743 1

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

  • Hash Cond: (ap.attendance_id = a.id)
23. 0.513 0.513 ↑ 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.513 rows=6,579 loops=1)

24. 0.282 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
25. 0.464 0.464 ↑ 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.464 rows=2,743 loops=1)

  • Filter: (registry_id = 'ce0f4190-a4dc-4a76-a4c8-a993d44cef90'::uuid)
  • Rows Removed by Filter: 3,836
26. 76.296 78.974 ↑ 3.2 36 39,487

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

27. 0.004 2.678 ↑ 3.2 36 1

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

28. 0.276 2.674 ↑ 3.2 36 1

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

  • Group Key: i_1.id
29. 0.336 2.398 ↑ 1.0 2,743 1

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

  • Hash Cond: (ap_1.institute_id = i_1.id)
30. 0.730 2.021 ↑ 1.0 2,743 1

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

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

32. 0.259 0.733 ↑ 1.0 2,743 1

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

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

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
35. 0.024 0.024 ↑ 1.0 117 1

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

Execution time : 9,600.266 ms