explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A0RU

Settings
# exclusive inclusive rows x rows loops node
1. 165.375 17,629.176 ↑ 24.9 104 1

Subquery Scan on claims_with_adjustments (cost=1,879,749.27..1,895,310.81 rows=2,594 width=1,831) (actual time=16,493.269..17,629.176 rows=104 loops=1)

  • Filter: (claims_with_adjustments.staffing_users_id = 27529)
  • Rows Removed by Filter: 519537
2. 727.592 17,463.801 ↓ 1.0 519,641 1

WindowAgg (cost=1,879,749.27..1,888,826.84 rows=518,718 width=1,823) (actual time=16,436.203..17,463.801 rows=519,641 loops=1)

3.          

CTE claims

4. 610.999 14,665.991 ↓ 1.0 519,641 1

Unique (cost=920,623.38..1,001,024.67 rows=518,718 width=533) (actual time=13,743.189..14,665.991 rows=519,641 loops=1)

5. 1,544.251 14,054.992 ↓ 1.0 519,641 1

Sort (cost=920,623.38..921,920.17 rows=518,718 width=533) (actual time=13,743.186..14,054.992 rows=519,641 loops=1)

  • Sort Key: claims_1.id, claims_1.id, (NULL::integer), claims_1.claim_categories_id, claims_1.claim_types_id, claims_1.claim_statuses_id, claims_1.payroll_periods_id, claims_1.staffing_users_id, claims_1.events_id, claims_1.events_staffing_days_positions_id, claims_1.receipt_documents_id, claims_1.claim_date, claims_1.description, claims_1.units, claims_1.unit_rate, claims_1.amount, claims_1.created_at, claims_1.updated_at, claims_1.auto_approved, claims_1.approved_at, claims_1.approved_by, claims_1.approved_note, claims_1.declined_at, claims_1.declined_by, claims_1.declined_note, claims_1.claim_comments, claims_1.pay_department_comments, (NULL::text), claims_1.withdrawn_at, claims_1.processed_time, claims_1.claim_start_time, claims_1.claim_end_time, claims_1.legal_minimum_rate, claims_1.exception, claims_1.exception_type, claims_1.eds_claim_id, claims_1.units_approved, claims_1.unit_rate_approved, events.clients_id, events.programs_id, events.parent_id, events.name, programs.name, venues.venue_name, venues_profiles.id, programs.agency_fee_percentage, ((claims_1.pay_date)::character varying), claims_1.primary_claim, claims_1.route_segments_id, claims_1.app_uuid, claims_1.no_receipt_explanation, claims_1.vendor_name, claims_1.transaction_date, ('Standard'::text), ((((staffing_users.first_name)::text || ' '::text) || (staffing_users.last_name)::text)), payroll_periods.end_date, (NULL::text), events_staffing_days_positions.staffing_positions_id, claims_1.paid_amount, claims_1.paid_units, claims_1.paid_rate
  • Sort Method: external merge Disk: 174088kB
6. 234.173 12,510.741 ↓ 1.0 519,641 1

Append (cost=264,412.32..623,165.13 rows=518,718 width=533) (actual time=5,426.739..12,510.741 rows=519,641 loops=1)

7. 739.639 9,114.982 ↑ 1.0 427,978 1

Hash Join (cost=264,412.32..435,442.42 rows=428,000 width=581) (actual time=5,426.738..9,114.982 rows=427,978 loops=1)

  • Hash Cond: (claims_1.staffing_users_id = staffing_users.id)
8. 312.096 8,341.117 ↑ 1.0 427,978 1

Hash Join (cost=261,347.64..422,212.74 rows=428,000 width=567) (actual time=5,392.218..8,341.117 rows=427,978 loops=1)

  • Hash Cond: (claims_1.payroll_periods_id = payroll_periods.id)
9. 327.620 8,028.939 ↑ 1.0 427,978 1

Hash Join (cost=261,341.94..416,322.04 rows=428,000 width=559) (actual time=5,392.121..8,028.939 rows=427,978 loops=1)

  • Hash Cond: (events.programs_id = programs.id)
10. 1,076.957 7,700.680 ↑ 1.0 427,978 1

Hash Join (cost=261,273.55..410,368.65 rows=428,000 width=527) (actual time=5,391.469..7,700.680 rows=427,978 loops=1)

  • Hash Cond: (claims_1.events_id = events.id)
11. 932.762 2,045.036 ↑ 1.0 427,978 1

Hash Left Join (cost=52,372.27..131,604.42 rows=428,000 width=456) (actual time=808.264..2,045.036 rows=427,978 loops=1)

  • Hash Cond: (claims_1.events_staffing_days_positions_id = events_staffing_days_positions.id)
12. 304.949 304.949 ↑ 1.0 427,978 1

Seq Scan on claims claims_1 (cost=0.00..18,745.00 rows=428,000 width=452) (actual time=0.005..304.949 rows=427,978 loops=1)

13. 411.663 807.325 ↑ 1.0 1,138,095 1

Hash (cost=33,675.12..33,675.12 rows=1,139,612 width=8) (actual time=807.325..807.325 rows=1,138,095 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 3812kB
14. 395.662 395.662 ↑ 1.0 1,138,095 1

Seq Scan on events_staffing_days_positions (cost=0.00..33,675.12 rows=1,139,612 width=8) (actual time=0.004..395.662 rows=1,138,095 loops=1)

15. 579.028 4,578.687 ↓ 1.0 1,095,856 1

Hash (cost=181,407.27..181,407.27 rows=1,091,201 width=79) (actual time=4,578.687..4,578.687 rows=1,095,856 loops=1)

  • Buckets: 65536 Batches: 64 Memory Usage: 1960kB
16. 1,162.744 3,999.659 ↓ 1.0 1,095,856 1

Hash Join (cost=99,768.23..181,407.27 rows=1,091,201 width=79) (actual time=1,675.528..3,999.659 rows=1,095,856 loops=1)

  • Hash Cond: (events_venues.events_id = events.id)
17. 831.077 1,885.959 ↓ 1.0 1,096,231 1

Hash Join (cost=37,637.29..80,742.32 rows=1,091,201 width=24) (actual time=723.987..1,885.959 rows=1,096,231 loops=1)

  • Hash Cond: (events_venues.venues_profiles_id = venues_profiles.id)
18. 331.246 331.246 ↓ 1.0 1,096,232 1

Seq Scan on events_venues (cost=0.00..18,447.01 rows=1,091,201 width=8) (actual time=0.004..331.246 rows=1,096,232 loops=1)

19. 88.156 723.636 ↑ 1.0 191,999 1

Hash (cost=34,104.93..34,104.93 rows=192,349 width=20) (actual time=723.636..723.636 rows=191,999 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 3065kB
20. 330.261 635.480 ↑ 1.0 191,999 1

Hash Join (cost=6,758.85..34,104.93 rows=192,349 width=20) (actual time=126.334..635.480 rows=191,999 loops=1)

  • Hash Cond: (venues.id = venues_profiles.venues_id)
21. 179.531 179.531 ↑ 1.0 447,269 1

Seq Scan on venues (cost=0.00..17,658.79 rows=453,279 width=20) (actual time=0.005..179.531 rows=447,269 loops=1)

22. 67.804 125.688 ↑ 1.0 192,138 1

Hash (cost=3,602.49..3,602.49 rows=192,349 width=8) (actual time=125.688..125.688 rows=192,138 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2895kB
23. 57.884 57.884 ↑ 1.0 192,138 1

Seq Scan on venues_profiles (cost=0.00..3,602.49 rows=192,349 width=8) (actual time=0.004..57.884 rows=192,138 loops=1)

24. 456.453 950.956 ↑ 1.0 1,095,857 1

Hash (cost=37,639.53..37,639.53 rows=1,099,953 width=55) (actual time=950.956..950.956 rows=1,095,857 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 2675kB
25. 494.503 494.503 ↑ 1.0 1,095,857 1

Seq Scan on events (cost=0.00..37,639.53 rows=1,099,953 width=55) (actual time=0.015..494.503 rows=1,095,857 loops=1)

26. 0.230 0.639 ↑ 1.0 683 1

Hash (cost=59.84..59.84 rows=684 width=36) (actual time=0.639..0.639 rows=683 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 56kB
27. 0.409 0.409 ↑ 1.0 683 1

Seq Scan on programs (cost=0.00..59.84 rows=684 width=36) (actual time=0.002..0.409 rows=683 loops=1)

28. 0.038 0.082 ↓ 1.0 121 1

Hash (cost=4.20..4.20 rows=120 width=12) (actual time=0.082..0.082 rows=121 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
29. 0.044 0.044 ↓ 1.0 121 1

Seq Scan on payroll_periods (cost=0.00..4.20 rows=120 width=12) (actual time=0.004..0.044 rows=121 loops=1)

30. 16.921 34.226 ↓ 1.0 42,126 1

Hash (cost=2,538.19..2,538.19 rows=42,119 width=18) (actual time=34.226..34.226 rows=42,126 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2673kB
31. 17.305 17.305 ↓ 1.0 42,126 1

Seq Scan on staffing_users (cost=0.00..2,538.19 rows=42,119 width=18) (actual time=0.006..17.305 rows=42,126 loops=1)

32. 45.229 2,143.562 ↓ 1.0 41,326 1

Subquery Scan on *SELECT* 2 (cost=106,891.58..134,778.62 rows=41,285 width=229) (actual time=1,556.985..2,143.562 rows=41,326 loops=1)

33. 76.501 2,098.333 ↓ 1.0 41,326 1

Hash Join (cost=106,891.58..134,365.77 rows=41,285 width=229) (actual time=1,556.968..2,098.333 rows=41,326 loops=1)

  • Hash Cond: (adjustments.programs_id = programs_1.id)
34. 29.892 2,021.210 ↓ 1.0 41,326 1

Hash Join (cost=106,823.19..133,523.29 rows=41,285 width=193) (actual time=1,556.307..2,021.210 rows=41,326 loops=1)

  • Hash Cond: (adjustments.claim_types_id = claim_types.id)
35. 262.204 1,991.294 ↓ 1.0 41,326 1

Hash Right Join (cost=106,821.47..132,953.90 rows=41,285 width=189) (actual time=1,556.266..1,991.294 rows=41,326 loops=1)

  • Hash Cond: (venues_1.id = venues_profiles_1.venues_id)
36. 172.997 172.997 ↑ 1.0 447,269 1

Seq Scan on venues venues_1 (cost=0.00..17,658.79 rows=453,279 width=20) (actual time=0.011..172.997 rows=447,269 loops=1)

37. 42.179 1,556.093 ↓ 1.0 41,326 1

Hash (cost=105,256.40..105,256.40 rows=41,285 width=177) (actual time=1,556.093..1,556.093 rows=41,326 loops=1)

  • Buckets: 32768 Batches: 4 Memory Usage: 1921kB
38. 112.909 1,513.914 ↓ 1.0 41,326 1

Hash Left Join (cost=67,816.22..105,256.40 rows=41,285 width=177) (actual time=1,043.398..1,513.914 rows=41,326 loops=1)

  • Hash Cond: (events_venues_1.venues_profiles_id = venues_profiles_1.id)
39. 40.819 1,278.782 ↓ 1.0 41,326 1

Nested Loop Left Join (cost=61,057.37..95,079.88 rows=41,285 width=177) (actual time=920.764..1,278.782 rows=41,326 loops=1)

40. 170.211 1,113.985 ↓ 1.0 41,326 1

Hash Left Join (cost=61,056.94..74,446.46 rows=41,285 width=177) (actual time=920.733..1,113.985 rows=41,326 loops=1)

  • Hash Cond: (adjustments.events_id = events_1.id)
41. 23.469 23.469 ↓ 1.0 41,326 1

Seq Scan on adjustments (cost=0.00..1,539.85 rows=41,285 width=130) (actual time=0.008..23.469 rows=41,326 loops=1)

42. 433.694 920.305 ↑ 1.0 1,095,857 1

Hash (cost=37,639.53..37,639.53 rows=1,099,953 width=47) (actual time=920.305..920.305 rows=1,095,857 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 2417kB
43. 486.611 486.611 ↑ 1.0 1,095,857 1

Seq Scan on events events_1 (cost=0.00..37,639.53 rows=1,099,953 width=47) (actual time=0.007..486.611 rows=1,095,857 loops=1)

44. 123.978 123.978 ↑ 1.0 1 41,326

Index Scan using events_venues_events_id on events_venues events_venues_1 (cost=0.43..0.49 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=41,326)

  • Index Cond: (events_1.id = events_id)
45. 66.345 122.223 ↑ 1.0 192,138 1

Hash (cost=3,602.49..3,602.49 rows=192,349 width=8) (actual time=122.223..122.223 rows=192,138 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2917kB
46. 55.878 55.878 ↑ 1.0 192,138 1

Seq Scan on venues_profiles venues_profiles_1 (cost=0.00..3,602.49 rows=192,349 width=8) (actual time=0.005..55.878 rows=192,138 loops=1)

47. 0.017 0.024 ↑ 1.0 32 1

Hash (cost=1.32..1.32 rows=32 width=8) (actual time=0.024..0.024 rows=32 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
48. 0.007 0.007 ↑ 1.0 32 1

Seq Scan on claim_types (cost=0.00..1.32 rows=32 width=8) (actual time=0.005..0.007 rows=32 loops=1)

49. 0.221 0.622 ↑ 1.0 683 1

Hash (cost=59.84..59.84 rows=684 width=40) (actual time=0.622..0.622 rows=683 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 59kB
50. 0.401 0.401 ↑ 1.0 683 1

Seq Scan on programs programs_1 (cost=0.00..59.84 rows=684 width=40) (actual time=0.015..0.401 rows=683 loops=1)

51. 52.706 1,018.024 ↓ 1.0 50,337 1

Subquery Scan on *SELECT* 3 (cost=17,517.68..48,664.09 rows=49,433 width=374) (actual time=272.872..1,018.024 rows=50,337 loops=1)

52. 127.811 965.318 ↓ 1.0 50,337 1

Hash Left Join (cost=17,517.68..48,169.76 rows=49,433 width=374) (actual time=272.855..965.318 rows=50,337 loops=1)

  • Hash Cond: (manual_claims.submitted_by = contact_info.user_id)
53. 35.291 836.976 ↓ 1.0 50,337 1

Hash Join (cost=17,488.75..46,720.77 rows=49,433 width=365) (actual time=272.291..836.976 rows=50,337 loops=1)

  • Hash Cond: (manual_claims.payroll_periods_id = payroll_periods_1.id)
54. 34.975 801.561 ↓ 1.0 50,337 1

Hash Join (cost=17,483.05..46,035.37 rows=49,433 width=357) (actual time=272.149..801.561 rows=50,337 loops=1)

  • Hash Cond: (manual_claims.claim_types_id = claim_types_1.id)
55. 36.709 766.561 ↓ 1.0 50,337 1

Hash Join (cost=17,481.33..45,353.95 rows=49,433 width=353) (actual time=272.112..766.561 rows=50,337 loops=1)

  • Hash Cond: (manual_claims.programs_id = programs_2.id)
56. 302.788 728.749 ↓ 1.0 50,337 1

Hash Join (cost=17,399.80..44,592.72 rows=49,433 width=317) (actual time=270.995..728.749 rows=50,337 loops=1)

  • Hash Cond: (venues_2.id = venues_profiles_2.venues_id)
57. 156.621 156.621 ↑ 1.0 447,269 1

Seq Scan on venues venues_2 (cost=0.00..17,658.79 rows=453,279 width=20) (actual time=0.005..156.621 rows=447,269 loops=1)

58. 51.618 269.340 ↓ 1.0 50,337 1

Hash (cost=14,753.89..14,753.89 rows=49,433 width=305) (actual time=269.340..269.340 rows=50,337 loops=1)

  • Buckets: 16384 (originally 16384) Batches: 16 (originally 8) Memory Usage: 3969kB
59. 63.742 217.722 ↓ 1.0 50,337 1

Hash Join (cost=6,758.85..14,753.89 rows=49,433 width=305) (actual time=122.051..217.722 rows=50,337 loops=1)

  • Hash Cond: (manual_claims.venues_profiles_id = venues_profiles_2.id)
60. 32.332 32.332 ↓ 1.0 50,346 1

Seq Scan on manual_claims (cost=0.00..2,603.33 rows=49,433 width=301) (actual time=0.007..32.332 rows=50,346 loops=1)

61. 65.474 121.648 ↑ 1.0 192,138 1

Hash (cost=3,602.49..3,602.49 rows=192,349 width=8) (actual time=121.648..121.648 rows=192,138 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2916kB
62. 56.174 56.174 ↑ 1.0 192,138 1

Seq Scan on venues_profiles venues_profiles_2 (cost=0.00..3,602.49 rows=192,349 width=8) (actual time=0.004..56.174 rows=192,138 loops=1)

63. 0.266 1.103 ↑ 1.0 683 1

Hash (cost=72.98..72.98 rows=684 width=40) (actual time=1.103..1.103 rows=683 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 59kB
64. 0.398 0.837 ↑ 1.0 683 1

Hash Join (cost=3.73..72.98 rows=684 width=40) (actual time=0.081..0.837 rows=683 loops=1)

  • Hash Cond: (programs_2.clients_id = clients.id)
65. 0.378 0.378 ↑ 1.0 683 1

Seq Scan on programs programs_2 (cost=0.00..59.84 rows=684 width=40) (actual time=0.003..0.378 rows=683 loops=1)

66. 0.031 0.061 ↑ 1.0 77 1

Hash (cost=2.77..2.77 rows=77 width=4) (actual time=0.061..0.061 rows=77 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
67. 0.030 0.030 ↑ 1.0 77 1

Seq Scan on clients (cost=0.00..2.77 rows=77 width=4) (actual time=0.005..0.030 rows=77 loops=1)

68. 0.011 0.025 ↑ 1.0 32 1

Hash (cost=1.32..1.32 rows=32 width=8) (actual time=0.025..0.025 rows=32 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
69. 0.014 0.014 ↑ 1.0 32 1

Seq Scan on claim_types claim_types_1 (cost=0.00..1.32 rows=32 width=8) (actual time=0.004..0.014 rows=32 loops=1)

70. 0.042 0.124 ↓ 1.0 121 1

Hash (cost=4.20..4.20 rows=120 width=12) (actual time=0.124..0.124 rows=121 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
71. 0.082 0.082 ↓ 1.0 121 1

Seq Scan on payroll_periods payroll_periods_1 (cost=0.00..4.20 rows=120 width=12) (actual time=0.009..0.082 rows=121 loops=1)

72. 0.275 0.531 ↓ 1.0 798 1

Hash (cost=18.97..18.97 rows=797 width=17) (actual time=0.531..0.531 rows=798 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 49kB
73. 0.256 0.256 ↓ 1.0 798 1

Seq Scan on contact_info (cost=0.00..18.97 rows=797 width=17) (actual time=0.006..0.256 rows=798 loops=1)

74. 1,234.601 16,736.209 ↓ 1.0 519,641 1

Sort (cost=878,724.61..880,021.40 rows=518,718 width=1,823) (actual time=16,436.185..16,736.209 rows=519,641 loops=1)

  • Sort Key: claims.id
  • Sort Method: external sort Disk: 174168kB
75. 15,501.608 15,501.608 ↓ 1.0 519,641 1

CTE Scan on claims (cost=0.00..10,374.36 rows=518,718 width=1,823) (actual time=13,743.203..15,501.608 rows=519,641 loops=1)