explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3OMo

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 191.589 ↓ 100.0 100 1

Limit (cost=79,818.06..79,818.07 rows=1 width=649) (actual time=191.579..191.589 rows=100 loops=1)

2. 0.330 191.582 ↓ 100.0 100 1

Sort (cost=79,818.06..79,818.07 rows=1 width=649) (actual time=191.578..191.582 rows=100 loops=1)

  • Sort Key: (natural_sort(attendances.registry_attendance_number, 20)) DESC
  • Sort Method: quicksort Memory: 76kB
3. 1.594 191.252 ↓ 100.0 100 1

WindowAgg (cost=79,572.84..79,818.05 rows=1 width=649) (actual time=190.051..191.252 rows=100 loops=1)

4. 0.558 189.658 ↓ 100.0 100 1

Nested Loop (cost=79,572.84..79,817.78 rows=1 width=565) (actual time=126.375..189.658 rows=100 loops=1)

  • Join Filter: (attendance_periods.id = ap_2.id)
  • Rows Removed by Join Filter: 11,141
5. 16.409 185.900 ↓ 100.0 100 1

Nested Loop (cost=79,133.89..79,360.46 rows=1 width=597) (actual time=124.693..185.900 rows=100 loops=1)

  • Join Filter: (attendance_periods.id = f.attendance_period_id)
  • Rows Removed by Join Filter: 390,691
6. 0.080 6.291 ↓ 100.0 100 1

Nested Loop (cost=777.69..783.53 rows=1 width=577) (actual time=5.078..6.291 rows=100 loops=1)

7. 0.086 6.111 ↓ 100.0 100 1

Nested Loop (cost=777.55..783.36 rows=1 width=400) (actual time=5.070..6.111 rows=100 loops=1)

8. 0.215 5.725 ↓ 100.0 100 1

Nested Loop (cost=777.26..780.21 rows=1 width=385) (actual time=5.057..5.725 rows=100 loops=1)

9. 0.115 5.210 ↓ 100.0 100 1

Merge Join (cost=777.26..777.80 rows=1 width=353) (actual time=5.045..5.210 rows=100 loops=1)

  • Merge Cond: (attendance_periods.id = attendance_periods_1.id)
10. 0.166 1.129 ↓ 33.7 202 1

Sort (cost=240.61..240.63 rows=6 width=337) (actual time=1.094..1.129 rows=202 loops=1)

  • Sort Key: attendance_periods.id
  • Sort Method: quicksort Memory: 81kB
11. 0.170 0.963 ↓ 34.2 205 1

Nested Loop (cost=101.89..240.53 rows=6 width=337) (actual time=0.248..0.963 rows=205 loops=1)

12. 0.093 0.383 ↓ 34.2 205 1

Hash Join (cost=101.61..221.35 rows=6 width=199) (actual time=0.237..0.383 rows=205 loops=1)

  • Hash Cond: (attendances.patient_id = registries_patients.patient_id)
13. 0.082 0.107 ↑ 1.0 205 1

Bitmap Heap Scan on attendances (cost=5.87..125.08 rows=205 width=173) (actual time=0.047..0.107 rows=205 loops=1)

  • Recheck Cond: (registry_id = '6e14145b-ee6e-4fff-8c0b-fe32ab6592fa'::uuid)
  • Heap Blocks: exact=12
14. 0.025 0.025 ↑ 1.0 205 1

Bitmap Index Scan on attendances_registry_id_idx (cost=0.00..5.82 rows=205 width=0) (actual time=0.025..0.025 rows=205 loops=1)

  • Index Cond: (registry_id = '6e14145b-ee6e-4fff-8c0b-fe32ab6592fa'::uuid)
15. 0.060 0.183 ↑ 1.0 197 1

Hash (cost=93.27..93.27 rows=197 width=42) (actual time=0.183..0.183 rows=197 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 23kB
16. 0.087 0.123 ↑ 1.0 197 1

Bitmap Heap Scan on registries_patients (cost=5.81..93.27 rows=197 width=42) (actual time=0.053..0.123 rows=197 loops=1)

  • Recheck Cond: (registry_id = '6e14145b-ee6e-4fff-8c0b-fe32ab6592fa'::uuid)
  • Heap Blocks: exact=9
17. 0.036 0.036 ↑ 1.0 197 1

Bitmap Index Scan on registries_patients_registry_id_idx (cost=0.00..5.76 rows=197 width=0) (actual time=0.036..0.036 rows=197 loops=1)

  • Index Cond: (registry_id = '6e14145b-ee6e-4fff-8c0b-fe32ab6592fa'::uuid)
18. 0.410 0.410 ↑ 1.0 1 205

Index Scan using attendance_periods_attendance_id_idx on attendance_periods (cost=0.28..3.19 rows=1 width=138) (actual time=0.002..0.002 rows=1 loops=205)

  • Index Cond: (attendance_id = attendances.id)
19. 0.055 3.966 ↑ 1.0 100 1

Sort (cost=536.65..536.90 rows=100 width=16) (actual time=3.945..3.966 rows=100 loops=1)

  • Sort Key: attendance_periods_1.id
  • Sort Method: quicksort Memory: 29kB
20. 0.009 3.911 ↑ 1.0 100 1

Limit (cost=457.32..532.33 rows=100 width=16) (actual time=2.701..3.911 rows=100 loops=1)

21. 0.549 3.902 ↑ 3.1 100 1

Hash Join (cost=457.32..686.86 rows=306 width=16) (actual time=2.700..3.902 rows=100 loops=1)

  • Hash Cond: (attendance_periods_1.id = reg_perm.id)
22. 0.668 0.668 ↑ 1.1 6,546 1

Seq Scan on attendance_periods attendance_periods_1 (cost=0.00..210.50 rows=7,250 width=16) (actual time=0.007..0.668 rows=6,546 loops=1)

23. 0.027 2.685 ↑ 1.5 205 1

Hash (cost=453.49..453.49 rows=306 width=16) (actual time=2.685..2.685 rows=205 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
24. 0.013 2.658 ↑ 1.5 205 1

Subquery Scan on reg_perm (cost=438.96..453.49 rows=306 width=16) (actual time=2.613..2.658 rows=205 loops=1)

25. 0.117 2.645 ↑ 1.5 205 1

HashAggregate (cost=438.96..450.43 rows=306 width=20) (actual time=2.612..2.645 rows=205 loops=1)

  • Group Key: ap.id
  • Filter: (bit_or(racl.permission_mask) > 0)
26. 0.128 2.528 ↑ 4.5 205 1

Nested Loop (cost=144.89..434.37 rows=918 width=20) (actual time=0.519..2.528 rows=205 loops=1)

  • Join Filter: ((racl.institute_id IS NULL) OR (racl.institute_id = ap.institute_id))
27. 0.954 1.990 ↑ 1.0 205 1

Hash Join (cost=127.64..357.18 rows=205 width=48) (actual time=0.125..1.990 rows=205 loops=1)

  • Hash Cond: (ap.attendance_id = a.id)
28. 0.923 0.923 ↑ 1.0 7,250 1

Seq Scan on attendance_periods ap (cost=0.00..210.50 rows=7,250 width=48) (actual time=0.003..0.923 rows=7,250 loops=1)

29. 0.035 0.113 ↑ 1.0 205 1

Hash (cost=125.08..125.08 rows=205 width=32) (actual time=0.113..0.113 rows=205 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
30. 0.062 0.078 ↑ 1.0 205 1

Bitmap Heap Scan on attendances a (cost=5.87..125.08 rows=205 width=32) (actual time=0.020..0.078 rows=205 loops=1)

  • Recheck Cond: (registry_id = '6e14145b-ee6e-4fff-8c0b-fe32ab6592fa'::uuid)
  • Heap Blocks: exact=12
31. 0.016 0.016 ↑ 1.0 205 1

Bitmap Index Scan on attendances_registry_id_idx (cost=0.00..5.82 rows=205 width=0) (actual time=0.016..0.016 rows=205 loops=1)

  • Index Cond: (registry_id = '6e14145b-ee6e-4fff-8c0b-fe32ab6592fa'::uuid)
32. 0.000 0.410 ↑ 12.0 1 205

Materialize (cost=17.25..40.32 rows=12 width=36) (actual time=0.002..0.002 rows=1 loops=205)

33. 0.008 0.453 ↑ 12.0 1 1

Hash Join (cost=17.25..40.26 rows=12 width=36) (actual time=0.389..0.453 rows=1 loops=1)

  • Hash Cond: (racl.trustee_id = get_trustee_list.trustee_id)
34. 0.106 0.106 ↑ 1.0 23 1

Seq Scan on registry_acl racl (cost=0.00..22.81 rows=23 width=52) (actual time=0.040..0.106 rows=23 loops=1)

  • Filter: (registry_id = '6e14145b-ee6e-4fff-8c0b-fe32ab6592fa'::uuid)
  • Rows Removed by Filter: 682
35. 0.002 0.339 ↑ 200.0 1 1

Hash (cost=14.75..14.75 rows=200 width=16) (actual time=0.339..0.339 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
36. 0.006 0.337 ↑ 200.0 1 1

HashAggregate (cost=12.75..14.75 rows=200 width=16) (actual time=0.336..0.337 rows=1 loops=1)

  • Group Key: get_trustee_list.trustee_id
37. 0.331 0.331 ↑ 1,000.0 1 1

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

38. 0.300 0.300 ↑ 1.0 1 100

Seq Scan on registries (cost=0.00..2.40 rows=1 width=48) (actual time=0.003..0.003 rows=1 loops=100)

  • Filter: (id = '6e14145b-ee6e-4fff-8c0b-fe32ab6592fa'::uuid)
  • Rows Removed by Filter: 31
39. 0.300 0.300 ↑ 1.0 1 100

Index Scan using patients_pkey on patients (cost=0.28..3.15 rows=1 width=47) (actual time=0.003..0.003 rows=1 loops=100)

  • Index Cond: (id = attendances.patient_id)
40. 0.100 0.100 ↑ 1.0 1 100

Index Scan using institutes_pkey on institutes (cost=0.14..0.16 rows=1 width=177) (actual time=0.001..0.001 rows=1 loops=100)

  • Index Cond: (id = attendance_periods.institute_id)
41. 46.445 163.200 ↑ 1.7 3,908 100

HashAggregate (cost=78,356.20..78,424.12 rows=6,792 width=20) (actual time=1.192..1.632 rows=3,908 loops=100)

  • Group Key: f.attendance_period_id
42. 7.345 116.755 ↑ 5.9 7,250 1

Hash Right Join (cost=28,505.18..78,037.30 rows=42,520 width=24) (actual time=64.131..116.755 rows=7,250 loops=1)

  • Hash Cond: (f_1.id = f.id)
43. 59.643 93.832 ↑ 4.6 39,250 1

HashAggregate (cost=17,601.44..64,241.80 rows=179,386 width=40) (actual time=48.535..93.832 rows=39,250 loops=1)

  • Group Key: f_1.id, r.trial_manager_id
44. 21.494 34.189 ↑ 4.4 40,438 1

Hash Join (cost=2,062.16..15,807.58 rows=179,386 width=68) (actual time=5.873..34.189 rows=40,438 loops=1)

  • Hash Cond: (f_1.attendance_period_id = ap_1.id)
  • Join Filter: (((racl_1.form_type_id = f_1.form_type_id) OR (racl_1.form_type_id IS NULL)) AND ((racl_1.physician_id = f_1.physician_id) OR (racl_1.physician_id IS NULL)))
  • Rows Removed by Join Filter: 4,001
45. 6.844 6.844 ↑ 1.0 43,053 1

Seq Scan on forms f_1 (cost=0.00..10,653.53 rows=43,053 width=80) (actual time=0.007..6.844 rows=43,053 loops=1)

46. 0.933 5.851 ↑ 4.4 7,045 1

Hash (cost=1,673.59..1,673.59 rows=31,086 width=68) (actual time=5.851..5.851 rows=7,045 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 743kB
47. 1.269 4.918 ↑ 4.4 7,045 1

Hash Join (cost=329.36..1,673.59 rows=31,086 width=68) (actual time=1.680..4.918 rows=7,045 loops=1)

  • Hash Cond: (a_1.registry_id = r.id)
  • Join Filter: ((racl_1.institute_id = ap_1.institute_id) OR (racl_1.institute_id IS NULL))
  • Rows Removed by Join Filter: 708
48. 1.436 3.316 ↑ 1.0 7,250 1

Hash Join (cost=277.12..506.67 rows=7,250 width=48) (actual time=1.343..3.316 rows=7,250 loops=1)

  • Hash Cond: (ap_1.attendance_id = a_1.id)
49. 0.546 0.546 ↑ 1.0 7,250 1

Seq Scan on attendance_periods ap_1 (cost=0.00..210.50 rows=7,250 width=48) (actual time=0.004..0.546 rows=7,250 loops=1)

50. 0.682 1.334 ↑ 1.0 7,250 1

Hash (cost=186.50..186.50 rows=7,250 width=32) (actual time=1.334..1.334 rows=7,250 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 518kB
51. 0.652 0.652 ↑ 1.0 7,250 1

Seq Scan on attendances a_1 (cost=0.00..186.50 rows=7,250 width=32) (actual time=0.004..0.652 rows=7,250 loops=1)

52. 0.003 0.333 ↑ 27.1 13 1

Hash (cost=47.83..47.83 rows=352 width=100) (actual time=0.333..0.333 rows=13 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
53. 0.006 0.330 ↑ 27.1 13 1

Hash Join (cost=19.97..47.83 rows=352 width=100) (actual time=0.234..0.330 rows=13 loops=1)

  • Hash Cond: (racl_1.registry_id = r.id)
54. 0.053 0.313 ↑ 27.1 13 1

Hash Join (cost=17.25..44.07 rows=352 width=68) (actual time=0.220..0.313 rows=13 loops=1)

  • Hash Cond: (racl_1.trustee_id = get_trustee_list_1.trustee_id)
55. 0.056 0.056 ↑ 1.0 705 1

Seq Scan on registry_acl racl_1 (cost=0.00..21.05 rows=705 width=84) (actual time=0.003..0.056 rows=705 loops=1)

56. 0.001 0.204 ↑ 200.0 1 1

Hash (cost=14.75..14.75 rows=200 width=16) (actual time=0.204..0.204 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
57. 0.004 0.203 ↑ 200.0 1 1

HashAggregate (cost=12.75..14.75 rows=200 width=16) (actual time=0.203..0.203 rows=1 loops=1)

  • Group Key: get_trustee_list_1.trustee_id
58. 0.199 0.199 ↑ 1,000.0 1 1

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

59. 0.003 0.011 ↑ 1.0 32 1

Hash (cost=2.32..2.32 rows=32 width=32) (actual time=0.011..0.011 rows=32 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
60. 0.008 0.008 ↑ 1.0 32 1

Seq Scan on registries r (cost=0.00..2.32 rows=32 width=32) (actual time=0.004..0.008 rows=32 loops=1)

61. 1.351 15.578 ↑ 1.4 7,250 1

Hash (cost=10,776.18..10,776.18 rows=10,205 width=32) (actual time=15.578..15.578 rows=7,250 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 582kB
62. 7.613 14.227 ↑ 1.4 7,250 1

Hash Join (cost=6.09..10,776.18 rows=10,205 width=32) (actual time=0.036..14.227 rows=7,250 loops=1)

  • Hash Cond: (f.form_type_id = ft.id)
63. 6.587 6.587 ↑ 1.0 43,053 1

Seq Scan on forms f (cost=0.00..10,653.53 rows=43,053 width=48) (actual time=0.004..6.587 rows=43,053 loops=1)

64. 0.004 0.027 ↑ 1.0 32 1

Hash (cost=5.69..5.69 rows=32 width=16) (actual time=0.027..0.027 rows=32 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
65. 0.023 0.023 ↑ 1.0 32 1

Seq Scan on form_types ft (cost=0.00..5.69 rows=32 width=16) (actual time=0.005..0.023 rows=32 loops=1)

  • Filter: (behavior_type = 'attendance_start'::text)
  • Rows Removed by Filter: 103
66. 1.587 3.200 ↑ 2.7 112 100

HashAggregate (cost=438.96..450.43 rows=306 width=20) (actual time=0.017..0.032 rows=112 loops=100)

  • Group Key: ap_2.id
  • Filter: (bit_or(racl_2.permission_mask) > 0)
67. 0.000 1.613 ↑ 4.5 205 1

Nested Loop (cost=144.89..434.37 rows=918 width=20) (actual time=0.389..1.613 rows=205 loops=1)

  • Join Filter: ((racl_2.institute_id IS NULL) OR (racl_2.institute_id = ap_2.institute_id))
68. 0.586 1.256 ↑ 1.0 205 1

Hash Join (cost=127.64..357.18 rows=205 width=48) (actual time=0.114..1.256 rows=205 loops=1)

  • Hash Cond: (ap_2.attendance_id = a_2.id)
69. 0.576 0.576 ↑ 1.0 7,250 1

Seq Scan on attendance_periods ap_2 (cost=0.00..210.50 rows=7,250 width=48) (actual time=0.011..0.576 rows=7,250 loops=1)

70. 0.022 0.094 ↑ 1.0 205 1

Hash (cost=125.08..125.08 rows=205 width=32) (actual time=0.094..0.094 rows=205 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
71. 0.050 0.072 ↑ 1.0 205 1

Bitmap Heap Scan on attendances a_2 (cost=5.87..125.08 rows=205 width=32) (actual time=0.027..0.072 rows=205 loops=1)

  • Recheck Cond: (registry_id = '6e14145b-ee6e-4fff-8c0b-fe32ab6592fa'::uuid)
  • Heap Blocks: exact=12
72. 0.022 0.022 ↑ 1.0 205 1

Bitmap Index Scan on attendances_registry_id_idx (cost=0.00..5.82 rows=205 width=0) (actual time=0.022..0.022 rows=205 loops=1)

  • Index Cond: (registry_id = '6e14145b-ee6e-4fff-8c0b-fe32ab6592fa'::uuid)
73. 0.106 0.410 ↑ 12.0 1 205

Materialize (cost=17.25..40.32 rows=12 width=36) (actual time=0.001..0.002 rows=1 loops=205)

74. 0.005 0.304 ↑ 12.0 1 1

Hash Join (cost=17.25..40.26 rows=12 width=36) (actual time=0.271..0.304 rows=1 loops=1)

  • Hash Cond: (racl_2.trustee_id = get_trustee_list_2.trustee_id)
75. 0.061 0.061 ↑ 1.0 23 1

Seq Scan on registry_acl racl_2 (cost=0.00..22.81 rows=23 width=52) (actual time=0.026..0.061 rows=23 loops=1)

  • Filter: (registry_id = '6e14145b-ee6e-4fff-8c0b-fe32ab6592fa'::uuid)
  • Rows Removed by Filter: 682
76. 0.001 0.238 ↑ 200.0 1 1

Hash (cost=14.75..14.75 rows=200 width=16) (actual time=0.238..0.238 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
77. 0.003 0.237 ↑ 200.0 1 1

HashAggregate (cost=12.75..14.75 rows=200 width=16) (actual time=0.237..0.237 rows=1 loops=1)

  • Group Key: get_trustee_list_2.trustee_id
78. 0.234 0.234 ↑ 1,000.0 1 1

Function Scan on get_trustee_list get_trustee_list_2 (cost=0.25..10.25 rows=1,000 width=16) (actual time=0.233..0.234 rows=1 loops=1)

Planning time : 6.144 ms
Execution time : 194.494 ms