explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WWN2

Settings
# exclusive inclusive rows x rows loops node
1. 54,135.334 71,342.861 ↓ 34.2 772,167 1

Hash Left Join (cost=1,717,262.65..10,891,264.57 rows=22,581 width=188) (actual time=2,757.150..71,342.861 rows=772,167 loops=1)

  • Hash Cond: ((projects.id = ""*SELECT* 1"".project_id) AND ((COALESCE(""*SELECT* 1_1"".activity_id, sa.activity_id)) = ""*SELECT* 1"".activity_id))
  • Join Filter: (("*SELECT* 1_1".date >= "*SELECT* 1".start_date) AND (("*SELECT* 1_1".date <= "*SELECT* 1".end_date) OR ("*SELECT* 1".end_date IS NULL)))
  • Rows Removed by Join Filter: 213626
2. 257.723 16,845.909 ↓ 34.2 772,167 1

Hash Join (cost=1,687,340.29..9,293,655.17 rows=22,581 width=136) (actual time=2,378.567..16,845.909 rows=772,167 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
3. 142.093 16,588.069 ↓ 14.9 1,010,188 1

Append (cost=1,687,321.62..9,292,779.12 rows=67,743 width=136) (actual time=2,378.436..16,588.069 rows=1,010,188 loops=1)

4. 741.898 16,169.075 ↓ 5,275.7 981,282 1

Hash Join (cost=1,687,321.62..8,855,720.66 rows=186 width=108) (actual time=2,378.435..16,169.075 rows=981,282 loops=1)

  • Hash Cond: ((projects.account_id = accounts_1.id) AND ("*SELECT* 1_1".user_id = sa.user_id))
  • Join Filter: ((""*SELECT* 1_1"".date >= sa.start_date) AND (""*SELECT* 1_1"".date <= sa.end_date) AND ((((""*SELECT* 1_1"".vacation_user_id)::double precision) IS NULL) OR (((""*SELECT* 1_1"".vacation_hours)::double precision) < (COALESCE(sa.capacity, accounts_1.default_capacity))::double precision)))
  • Rows Removed by Join Filter: 272688
5. 343.729 15,423.427 ↑ 1.1 1,101,438 1

Hash Join (cost=1,687,136.92..8,813,431.19 rows=1,199,950 width=120) (actual time=2,374.186..15,423.427 rows=1,101,438 loops=1)

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
6. 154.863 15,077.537 ↑ 1.1 1,101,438 1

Append (cost=1,686,908.15..8,798,050.76 rows=1,199,950 width=116) (actual time=2,372.013..15,077.537 rows=1,101,438 loops=1)

7. 147.312 4,918.849 ↑ 1.2 396,845 1

Result (cost=1,686,908.15..8,590,768.79 rows=495,989 width=116) (actual time=2,372.012..4,918.849 rows=396,845 loops=1)

8. 52.344 4,771.537 ↑ 1.2 396,845 1

Append (cost=1,686,908.15..8,584,568.93 rows=495,989 width=140) (actual time=2,372.010..4,771.537 rows=396,845 loops=1)

9. 71.194 4,401.349 ↑ 1.4 338,629 1

Subquery Scan on *SELECT* 1_1 (cost=1,686,908.15..8,519,608.63 rows=457,239 width=114) (actual time=2,372.009..4,401.349 rows=338,629 loops=1)

10. 1,979.227 4,330.155 ↑ 1.4 338,629 1

Hash Right Join (cost=1,686,908.15..8,512,750.05 rows=457,239 width=134) (actual time=2,372.006..4,330.155 rows=338,629 loops=1)

  • Hash Cond: ((summary_vacations_by_dates.account_id = projects_1.account_id) AND (summary_vacations_by_dates.user_id = plan_rows.user_id))
  • Join Filter: (generate_series.generate_series = summary_vacations_by_dates.date)
  • Rows Removed by Join Filter: 14110732
11. 4.839 92.951 ↑ 17.8 32,266 1

Subquery Scan on summary_vacations_by_dates (cost=1,151,985.56..1,220,752.83 rows=575,000 width=44) (actual time=65.454..92.951 rows=32,266 loops=1)

12. 19.079 88.112 ↑ 17.8 32,266 1

GroupAggregate (cost=1,151,985.56..1,215,002.83 rows=575,000 width=52) (actual time=65.453..88.112 rows=32,266 loops=1)

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
13. 18.864 69.033 ↑ 134.2 32,422 1

Sort (cost=1,151,985.56..1,162,864.01 rows=4,351,382 width=21) (actual time=65.442..69.033 rows=32,422 loops=1)

  • Sort Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
  • Sort Method: quicksort Memory: 2855kB
14. 12.121 50.169 ↑ 134.2 32,422 1

Hash Join (cost=138.05..493,700.50 rows=4,351,382 width=21) (actual time=1.540..50.169 rows=32,422 loops=1)

  • Hash Cond: (vacations.staff_membership_id = staff_memberships.id)
  • Join Filter: ((generate_series_1.generate_series >= staff_memberships.joined_at) AND ((staff_memberships.archived_at IS NULL) OR (generate_series_1.generate_series <= staff_memberships.archived_at)))
  • Rows Removed by Join Filter: 1908
15. 12.363 36.564 ↑ 634.8 34,330 1

Nested Loop (cost=0.01..436,340.93 rows=21,792,000 width=17) (actual time=0.023..36.564 rows=34,330 loops=1)

16. 2.409 2.409 ↑ 1.0 21,792 1

Seq Scan on vacations (cost=0.00..500.92 rows=21,792 width=17) (actual time=0.010..2.409 rows=21,792 loops=1)

17. 21.792 21.792 ↑ 500.0 2 21,792

Function Scan on generate_series generate_series_1 (cost=0.01..10.01 rows=1,000 width=8) (actual time=0.001..0.001 rows=2 loops=21,792)

18. 0.762 1.484 ↑ 1.0 4,002 1

Hash (cost=88.02..88.02 rows=4,002 width=20) (actual time=1.484..1.484 rows=4,002 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 229kB
19. 0.722 0.722 ↑ 1.0 4,002 1

Seq Scan on staff_memberships (cost=0.00..88.02 rows=4,002 width=20) (actual time=0.005..0.722 rows=4,002 loops=1)

20. 153.947 2,257.977 ↑ 1.4 338,629 1

Hash (cost=522,705.01..522,705.01 rows=457,239 width=66) (actual time=2,257.977..2,257.977 rows=338,629 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 2931kB
21. 222.801 2,104.030 ↑ 1.4 338,629 1

Hash Right Join (cost=514,123.83..522,705.01 rows=457,239 width=66) (actual time=1,835.972..2,104.030 rows=338,629 loops=1)

  • Hash Cond: ((pa.project_plan_id = project_plans.id) AND (pa.plan_row_uuid = plan_items.plan_row_uuid) AND (pa.user_id = plan_rows.user_id))
  • Join Filter: (((pa.epic_id = plan_rows.epic_id) OR (plan_rows.epic_id IS NULL)) AND ((generate_series.generate_series)::date >= pa.start_date) AND ((generate_series.generate_series)::date <= pa.end_date))
  • Rows Removed by Join Filter: 3146
22. 6.305 184.434 ↓ 8.9 44,428 1

Subquery Scan on pa (cost=8,529.07..8,653.34 rows=4,971 width=36) (actual time=137.839..184.434 rows=44,428 loops=1)

23. 70.531 178.129 ↓ 8.9 44,428 1

HashAggregate (cost=8,529.07..8,603.63 rows=4,971 width=48) (actual time=137.838..178.129 rows=44,428 loops=1)

  • Group Key: plan_rows_1.project_plan_id, plan_rows_1.epic_id, plan_rows_1.user_id, plan_roles.activity_id, COALESCE(plan_roles.start_date, '1980-01-01'::date), COALESCE(lag((plan_roles.start_date - 1), 1) OVER (?), '3000-01-01'::date)
24. 30.869 107.598 ↑ 1.0 44,581 1

WindowAgg (cost=6,077.00..7,080.12 rows=44,583 width=68) (actual time=70.609..107.598 rows=44,581 loops=1)

25. 35.172 76.729 ↑ 1.0 44,581 1

Sort (cost=6,077.00..6,188.46 rows=44,583 width=52) (actual time=70.600..76.729 rows=44,581 loops=1)

  • Sort Key: plan_roles.plan_row_uuid, plan_roles.start_date DESC NULLS LAST
  • Sort Method: external merge Disk: 2408kB
26. 17.673 41.557 ↑ 1.0 44,581 1

Hash Join (cost=1,631.38..2,634.26 rows=44,583 width=52) (actual time=19.397..41.557 rows=44,581 loops=1)

  • Hash Cond: (plan_roles.plan_row_uuid = plan_rows_1.uuid)
27. 4.737 4.737 ↑ 1.0 44,581 1

Seq Scan on plan_roles (cost=0.00..885.83 rows=44,583 width=24) (actual time=0.007..4.737 rows=44,581 loops=1)

28. 10.815 19.147 ↑ 1.0 48,106 1

Hash (cost=1,030.06..1,030.06 rows=48,106 width=28) (actual time=19.147..19.147 rows=48,106 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3180kB
29. 8.332 8.332 ↑ 1.0 48,106 1

Seq Scan on plan_rows plan_rows_1 (cost=0.00..1,030.06 rows=48,106 width=28) (actual time=0.006..8.332 rows=48,106 loops=1)

30. 193.484 1,696.795 ↑ 1.4 338,629 1

Hash (cost=492,234.08..492,234.08 rows=457,239 width=70) (actual time=1,696.795..1,696.795 rows=338,629 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 2569kB
31. 1,091.336 1,503.311 ↑ 1.4 338,629 1

Hash Anti Join (cost=4,168.02..492,234.08 rows=457,239 width=70) (actual time=46.541..1,503.311 rows=338,629 loops=1)

  • Hash Cond: ((projects_1.account_id = non_working_intervals_by_dates.account_id) AND (plan_rows.user_id = non_working_intervals_by_dates.user_id))
  • Join Filter: (generate_series.generate_series = non_working_intervals_by_dates.date)
  • Rows Removed by Join Filter: 11449923
32. 66.573 388.506 ↑ 1.7 346,275 1

Nested Loop (cost=1,991.21..443,836.29 rows=579,175 width=70) (actual time=22.823..388.506 rows=346,275 loops=1)

33. 9.585 76.575 ↓ 1.2 27,262 1

Hash Join (cost=1,991.21..3,663.28 rows=23,167 width=70) (actual time=22.794..76.575 rows=27,262 loops=1)

  • Hash Cond: (project_plans.project_id = projects_1.id)
34. 13.756 65.124 ↓ 1.2 27,262 1

Hash Join (cost=1,762.44..3,373.65 rows=23,167 width=66) (actual time=20.855..65.124 rows=27,262 loops=1)

  • Hash Cond: (plan_items.plan_row_uuid = plan_rows.uuid)
35. 16.258 32.532 ↓ 1.1 29,797 1

Hash Join (cost=202.03..1,744.28 rows=26,267 width=54) (actual time=1.753..32.532 rows=29,797 loops=1)

  • Hash Cond: (plan_items.project_plan_id = project_plans.id)
36. 14.561 14.561 ↓ 1.0 55,664 1

Seq Scan on plan_items (cost=0.00..1,396.10 rows=55,639 width=46) (actual time=0.008..14.561 rows=55,664 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
37. 0.531 1.713 ↑ 1.0 2,851 1

Hash (cost=166.39..166.39 rows=2,851 width=8) (actual time=1.713..1.713 rows=2,851 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 144kB
38. 1.182 1.182 ↑ 1.0 2,851 1

Seq Scan on project_plans (cost=0.00..166.39 rows=2,851 width=8) (actual time=0.006..1.182 rows=2,851 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3188
39. 9.787 18.836 ↑ 1.0 42,344 1

Hash (cost=1,030.06..1,030.06 rows=42,428 width=28) (actual time=18.836..18.836 rows=42,344 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2816kB
40. 9.049 9.049 ↑ 1.0 42,344 1

Seq Scan on plan_rows (cost=0.00..1,030.06 rows=42,428 width=28) (actual time=0.011..9.049 rows=42,344 loops=1)

  • Filter: (user_id IS NOT NULL)
  • Rows Removed by Filter: 5762
41. 0.981 1.866 ↑ 1.0 5,812 1

Hash (cost=156.12..156.12 rows=5,812 width=8) (actual time=1.865..1.866 rows=5,812 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 292kB
42. 0.885 0.885 ↑ 1.0 5,812 1

Seq Scan on projects projects_1 (cost=0.00..156.12 rows=5,812 width=8) (actual time=0.023..0.885 rows=5,812 loops=1)

43. 245.358 245.358 ↑ 1.9 13 27,262

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.004..0.009 rows=13 loops=27,262)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 5
44. 12.298 23.469 ↑ 1.0 58,975 1

Hash (cost=1,281.12..1,281.12 rows=59,712 width=12) (actual time=23.469..23.469 rows=58,975 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3047kB
45. 11.171 11.171 ↑ 1.0 59,712 1

Seq Scan on non_working_intervals_by_dates (cost=0.00..1,281.12 rows=59,712 width=12) (actual time=0.011..11.171 rows=59,712 loops=1)

46. 57.136 317.844 ↓ 1.5 58,216 1

Hash Join (cost=1,215.85..62,092.86 rows=38,750 width=114) (actual time=6.484..317.844 rows=58,216 loops=1)

  • Hash Cond: (project_plans_1.project_id = project.id)
  • Join Filter: (NOT (SubPlan 1))
  • Rows Removed by Join Filter: 770
47. 13.166 81.644 ↑ 1.3 58,986 1

Nested Loop (cost=987.08..61,446.50 rows=77,500 width=58) (actual time=4.292..81.644 rows=58,986 loops=1)

48. 1.594 27.918 ↑ 1.2 2,535 1

Hash Join (cost=987.08..2,546.49 rows=3,100 width=58) (actual time=4.278..27.918 rows=2,535 loops=1)

  • Hash Cond: (plan_items_1.project_plan_id = project_plans_1.id)
49. 12.031 24.884 ↓ 1.2 7,876 1

Hash Join (cost=785.05..2,327.21 rows=6,567 width=54) (actual time=2.803..24.884 rows=7,876 loops=1)

  • Hash Cond: (plan_items_1.plan_row_uuid = plan_rows_2.uuid)
50. 10.099 10.099 ↓ 1.0 55,664 1

Seq Scan on plan_items plan_items_1 (cost=0.00..1,396.10 rows=55,639 width=46) (actual time=0.006..10.099 rows=55,664 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
51. 1.112 2.754 ↓ 1.0 5,762 1

Hash (cost=714.07..714.07 rows=5,678 width=24) (actual time=2.753..2.754 rows=5,762 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 366kB
52. 1.371 1.642 ↓ 1.0 5,762 1

Bitmap Heap Scan on plan_rows plan_rows_2 (cost=108.29..714.07 rows=5,678 width=24) (actual time=0.309..1.642 rows=5,762 loops=1)

  • Recheck Cond: (user_id IS NULL)
  • Heap Blocks: exact=430
53. 0.271 0.271 ↓ 1.0 5,762 1

Bitmap Index Scan on index_plan_rows_on_user_id (cost=0.00..106.88 rows=5,678 width=0) (actual time=0.271..0.271 rows=5,762 loops=1)

  • Index Cond: (user_id IS NULL)
54. 0.431 1.440 ↑ 1.0 2,851 1

Hash (cost=166.39..166.39 rows=2,851 width=8) (actual time=1.439..1.440 rows=2,851 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 144kB
55. 1.009 1.009 ↑ 1.0 2,851 1

Seq Scan on project_plans project_plans_1 (cost=0.00..166.39 rows=2,851 width=8) (actual time=0.006..1.009 rows=2,851 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3188
56. 40.560 40.560 ↑ 1.1 23 2,535

Function Scan on generate_series generate_series_2 (cost=0.01..18.76 rows=25 width=8) (actual time=0.006..0.016 rows=23 loops=2,535)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 9
57. 0.972 2.106 ↑ 1.0 5,812 1

Hash (cost=156.12..156.12 rows=5,812 width=12) (actual time=2.106..2.106 rows=5,812 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 314kB
58. 1.134 1.134 ↑ 1.0 5,812 1

Seq Scan on projects project (cost=0.00..156.12 rows=5,812 width=12) (actual time=0.009..1.134 rows=5,812 loops=1)

59.          

SubPlan (for Hash Join)

60. 0.000 176.958 ↓ 0.0 0 58,986

Nested Loop (cost=0.69..16.74 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=58,986)

61. 58.986 58.986 ↑ 1.0 1 58,986

Index Scan using clients_pkey on clients clients_3 (cost=0.28..8.29 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=58,986)

  • Index Cond: (project.client_id = id)
62. 117.972 117.972 ↓ 0.0 0 58,986

Index Only Scan using non_working_intervals_by_office_id_idx on non_working_intervals_by_dates non_working_intervals_by_dates_2 (cost=0.41..8.44 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=58,986)

  • Index Cond: ((account_id = project.account_id) AND (office_id = clients_3.office_id) AND (date = generate_series_2.generate_series))
  • Heap Fetches: 770
63. 9,160.406 10,003.825 ↓ 1.0 704,593 1

Hash Right Join (cost=50,529.23..194,242.61 rows=703,961 width=116) (actual time=796.339..10,003.825 rows=704,593 loops=1)

  • Hash Cond: (pa_1.user_id = time_logs.user_id)
  • Join Filter: ((time_logs.date >= pa_1.start_date) AND (time_logs.date <= pa_1.end_date) AND (((pa_1.epic_id = time_logs.epic_id) AND (pa_1.project_plan_id = epics.project_plan_id)) OR ((pa_1.epic_id IS NULL) AND (pa_1.project_plan_id = active_plan.id))))
  • Rows Removed by Join Filter: 62978265
64. 6.759 185.746 ↓ 8.9 44,428 1

Subquery Scan on pa_1 (cost=8,529.07..8,653.34 rows=4,971 width=40) (actual time=135.956..185.746 rows=44,428 loops=1)

65. 71.544 178.987 ↓ 8.9 44,428 1

HashAggregate (cost=8,529.07..8,603.63 rows=4,971 width=48) (actual time=135.955..178.987 rows=44,428 loops=1)

  • Group Key: plan_rows_3.project_plan_id, plan_rows_3.epic_id, plan_rows_3.user_id, plan_roles_1.activity_id, COALESCE(plan_roles_1.start_date, '1980-01-01'::date), COALESCE(lag((plan_roles_1.start_date - 1), 1) OVER (?), '3000-01-01'::date)
66. 30.305 107.443 ↑ 1.0 44,581 1

WindowAgg (cost=6,077.00..7,080.12 rows=44,583 width=68) (actual time=71.166..107.443 rows=44,581 loops=1)

67. 34.271 77.138 ↑ 1.0 44,581 1

Sort (cost=6,077.00..6,188.46 rows=44,583 width=52) (actual time=71.151..77.138 rows=44,581 loops=1)

  • Sort Key: plan_roles_1.plan_row_uuid, plan_roles_1.start_date DESC NULLS LAST
  • Sort Method: external merge Disk: 2408kB
68. 16.303 42.867 ↑ 1.0 44,581 1

Hash Join (cost=1,631.38..2,634.26 rows=44,583 width=52) (actual time=22.623..42.867 rows=44,581 loops=1)

  • Hash Cond: (plan_roles_1.plan_row_uuid = plan_rows_3.uuid)
69. 4.241 4.241 ↑ 1.0 44,581 1

Seq Scan on plan_roles plan_roles_1 (cost=0.00..885.83 rows=44,583 width=24) (actual time=0.007..4.241 rows=44,581 loops=1)

70. 12.442 22.323 ↑ 1.0 48,106 1

Hash (cost=1,030.06..1,030.06 rows=48,106 width=28) (actual time=22.323..22.323 rows=48,106 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3180kB
71. 9.881 9.881 ↑ 1.0 48,106 1

Seq Scan on plan_rows plan_rows_3 (cost=0.00..1,030.06 rows=48,106 width=28) (actual time=0.007..9.881 rows=48,106 loops=1)

72. 266.697 657.673 ↑ 1.0 703,961 1

Hash (cost=27,700.65..27,700.65 rows=703,961 width=40) (actual time=657.673..657.673 rows=703,961 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 32 (originally 16) Memory Usage: 3585kB
73. 262.961 390.976 ↑ 1.0 703,961 1

Hash Join (cost=1,826.58..27,700.65 rows=703,961 width=40) (actual time=21.743..390.976 rows=703,961 loops=1)

  • Hash Cond: (time_logs.epic_id = epics.id)
74. 106.447 106.447 ↑ 1.0 703,961 1

Seq Scan on time_logs (cost=0.00..16,194.61 rows=703,961 width=28) (actual time=0.042..106.447 rows=703,961 loops=1)

75. 5.522 21.568 ↑ 1.0 26,435 1

Hash (cost=1,496.14..1,496.14 rows=26,435 width=16) (actual time=21.568..21.568 rows=26,435 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1424kB
76. 7.551 16.046 ↑ 1.0 26,435 1

Hash Left Join (cost=202.03..1,496.14 rows=26,435 width=16) (actual time=1.606..16.046 rows=26,435 loops=1)

  • Hash Cond: (epics.project_id = active_plan.project_id)
77. 6.934 6.934 ↑ 1.0 26,435 1

Seq Scan on epics (cost=0.00..619.35 rows=26,435 width=12) (actual time=0.006..6.934 rows=26,435 loops=1)

78. 0.425 1.561 ↑ 1.0 2,851 1

Hash (cost=166.39..166.39 rows=2,851 width=8) (actual time=1.561..1.561 rows=2,851 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 144kB
79. 1.136 1.136 ↑ 1.0 2,851 1

Seq Scan on project_plans active_plan (cost=0.00..166.39 rows=2,851 width=8) (actual time=0.007..1.136 rows=2,851 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3188
80. 1.118 2.161 ↑ 1.0 5,812 1

Hash (cost=156.12..156.12 rows=5,812 width=8) (actual time=2.161..2.161 rows=5,812 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 292kB
81. 1.043 1.043 ↑ 1.0 5,812 1

Seq Scan on projects (cost=0.00..156.12 rows=5,812 width=8) (actual time=0.007..1.043 rows=5,812 loops=1)

82. 1.614 3.750 ↑ 1.0 4,221 1

Hash (cost=121.39..121.39 rows=4,221 width=49) (actual time=3.750..3.750 rows=4,221 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 429kB
83. 1.536 2.136 ↑ 1.0 4,221 1

Hash Join (cost=18.96..121.39 rows=4,221 width=49) (actual time=0.155..2.136 rows=4,221 loops=1)

  • Hash Cond: (sa.account_id = accounts_1.id)
84. 0.465 0.465 ↑ 1.0 4,221 1

Seq Scan on staff_activities_with_dates sa (cost=0.00..91.21 rows=4,221 width=40) (actual time=0.005..0.465 rows=4,221 loops=1)

85. 0.057 0.135 ↑ 1.0 354 1

Hash (cost=14.54..14.54 rows=354 width=9) (actual time=0.135..0.135 rows=354 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
86. 0.078 0.078 ↑ 1.0 354 1

Seq Scan on accounts accounts_1 (cost=0.00..14.54 rows=354 width=9) (actual time=0.002..0.078 rows=354 loops=1)

87. 10.418 276.901 ↑ 2.3 28,906 1

Subquery Scan on *SELECT* 2_1 (cost=2,320.78..436,717.88 rows=67,557 width=136) (actual time=26.173..276.901 rows=28,906 loops=1)

88. 165.795 266.483 ↑ 2.3 28,906 1

Hash Anti Join (cost=2,320.78..435,873.42 rows=67,557 width=143) (actual time=26.171..266.483 rows=28,906 loops=1)

  • Hash Cond: ((sa_1.account_id = non_working_intervals_by_dates_1.account_id) AND (sa_1.user_id = non_working_intervals_by_dates_1.user_id))
  • Join Filter: (generate_series_3.generate_series = non_working_intervals_by_dates_1.date)
  • Rows Removed by Join Filter: 1153172
89. 18.604 76.807 ↑ 2.6 29,394 1

Hash Join (cost=143.98..429,184.39 rows=77,078 width=64) (actual time=2.052..76.807 rows=29,394 loops=1)

  • Hash Cond: (vacations_1.staff_membership_id = sa_1.staff_membership_id)
  • Join Filter: ((generate_series_3.generate_series >= sa_1.start_date) AND (generate_series_3.generate_series <= sa_1.end_date))
  • Rows Removed by Join Filter: 10924
90. 9.608 56.221 ↑ 17.5 31,132 1

Nested Loop (cost=0.01..414,548.93 rows=544,800 width=44) (actual time=0.024..56.221 rows=31,132 loops=1)

91. 3.029 3.029 ↑ 1.0 21,792 1

Seq Scan on vacations vacations_1 (cost=0.00..500.92 rows=21,792 width=44) (actual time=0.009..3.029 rows=21,792 loops=1)

92. 43.584 43.584 ↑ 25.0 1 21,792

Function Scan on generate_series generate_series_3 (cost=0.01..18.76 rows=25 width=8) (actual time=0.001..0.002 rows=1 loops=21,792)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 0
93. 1.141 1.982 ↑ 1.0 4,221 1

Hash (cost=91.21..91.21 rows=4,221 width=36) (actual time=1.981..1.982 rows=4,221 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 350kB
94. 0.841 0.841 ↑ 1.0 4,221 1

Seq Scan on staff_activities_with_dates sa_1 (cost=0.00..91.21 rows=4,221 width=36) (actual time=0.007..0.841 rows=4,221 loops=1)

95. 12.578 23.881 ↑ 1.0 58,975 1

Hash (cost=1,281.12..1,281.12 rows=59,712 width=12) (actual time=23.881..23.881 rows=58,975 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3047kB
96. 11.303 11.303 ↑ 1.0 59,712 1

Seq Scan on non_working_intervals_by_dates non_working_intervals_by_dates_1 (cost=0.00..1,281.12 rows=59,712 width=12) (actual time=0.007..11.303 rows=59,712 loops=1)

97. 0.004 0.117 ↑ 11.8 10 1

Hash (cost=17.20..17.20 rows=118 width=4) (actual time=0.117..0.117 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
98. 0.113 0.113 ↑ 11.8 10 1

Seq Scan on accounts (cost=0.00..17.20 rows=118 width=4) (actual time=0.015..0.113 rows=10 loops=1)

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 344
99. 97.760 361.618 ↑ 1.0 263,033 1

Hash (cost=23,435.32..23,435.32 rows=272,669 width=44) (actual time=361.618..361.618 rows=263,033 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3066kB
100. 27.836 263.858 ↑ 1.0 263,033 1

Append (cost=109.73..23,435.32 rows=272,669 width=44) (actual time=1.818..263.858 rows=263,033 loops=1)

101. 0.114 17.657 ↓ 9.7 474 1

Subquery Scan on *SELECT* 1 (cost=109.73..1,692.67 rows=49 width=44) (actual time=1.817..17.657 rows=474 loops=1)

102. 0.247 17.543 ↓ 9.7 474 1

Nested Loop (cost=109.73..1,692.18 rows=49 width=612) (actual time=1.816..17.543 rows=474 loops=1)

103. 0.163 16.822 ↓ 9.7 474 1

Nested Loop (cost=109.59..1,684.02 rows=49 width=52) (actual time=1.809..16.822 rows=474 loops=1)

104. 0.089 1.594 ↓ 5.0 5 1

Nested Loop (cost=109.17..306.98 rows=1 width=32) (actual time=1.259..1.594 rows=5 loops=1)

  • Join Filter: (clients.brand_id = rate_cards.rateable_id)
  • Rows Removed by Join Filter: 570
105. 0.175 0.700 ↑ 1.0 115 1

WindowAgg (cost=108.61..111.49 rows=115 width=45) (actual time=0.520..0.700 rows=115 loops=1)

106. 0.046 0.525 ↑ 1.0 115 1

Sort (cost=108.61..108.90 rows=115 width=29) (actual time=0.513..0.525 rows=115 loops=1)

  • Sort Key: rate_cards.rateable_id, rate_cards.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 33kB
107. 0.479 0.479 ↑ 1.0 115 1

Seq Scan on rate_cards (cost=0.00..104.68 rows=115 width=29) (actual time=0.113..0.479 rows=115 loops=1)

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4739
108. 0.094 0.805 ↑ 1.0 5 115

Materialize (cost=0.56..185.73 rows=5 width=8) (actual time=0.004..0.007 rows=5 loops=115)

109. 0.008 0.711 ↑ 1.0 5 1

Nested Loop (cost=0.56..185.71 rows=5 width=8) (actual time=0.405..0.711 rows=5 loops=1)

110. 0.006 0.693 ↑ 1.0 5 1

Nested Loop (cost=0.28..184.13 rows=5 width=8) (actual time=0.399..0.693 rows=5 loops=1)

111. 0.667 0.667 ↑ 1.0 5 1

Seq Scan on pricing_models (cost=0.00..142.62 rows=5 width=4) (actual time=0.387..0.667 rows=5 loops=1)

  • Filter: ((rates_type)::text = 'brand'::text)
  • Rows Removed by Filter: 5805
112. 0.020 0.020 ↑ 1.0 1 5

Index Scan using projects_pkey on projects projects_2 (cost=0.28..8.30 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=5)

  • Index Cond: (id = pricing_models.project_id)
113. 0.010 0.010 ↑ 1.0 1 5

Index Scan using clients_pkey on clients (cost=0.28..0.32 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=5)

  • Index Cond: (id = projects_2.client_id)
114. 15.065 15.065 ↓ 1.9 95 5

Index Scan using index_rates_on_activity_id_and_rate_card_id on rates (cost=0.42..1,376.55 rows=49 width=28) (actual time=0.593..3.013 rows=95 loops=5)

  • Index Cond: (rate_card_id = rate_cards.id)
115. 0.474 0.474 ↑ 1.0 1 474

Index Only Scan using brands_pkey on brands (cost=0.14..0.17 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=474)

  • Index Cond: (id = clients.brand_id)
  • Heap Fetches: 474
116. 12.564 67.771 ↓ 2.6 91,600 1

Subquery Scan on *SELECT* 2 (cost=583.00..5,484.31 rows=35,240 width=44) (actual time=11.002..67.771 rows=91,600 loops=1)

117. 34.996 55.207 ↓ 2.6 91,600 1

Hash Join (cost=583.00..5,131.91 rows=35,240 width=612) (actual time=11.001..55.207 rows=91,600 loops=1)

  • Hash Cond: (rates_1.rate_card_id = rate_cards_1.id)
118. 15.364 15.364 ↑ 1.0 174,437 1

Seq Scan on rates rates_1 (cost=0.00..3,542.37 rows=174,437 width=28) (actual time=0.013..15.364 rows=174,437 loops=1)

119. 0.175 4.847 ↑ 1.1 657 1

Hash (cost=574.00..574.00 rows=720 width=24) (actual time=4.847..4.847 rows=657 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
120. 0.213 4.672 ↑ 1.1 657 1

Hash Join (cost=378.98..574.00 rows=720 width=24) (actual time=3.048..4.672 rows=657 loops=1)

  • Hash Cond: (clients_1.office_id = offices.id)
121. 0.162 2.978 ↑ 1.0 623 1

Hash Join (cost=214.02..399.80 rows=623 width=8) (actual time=1.503..2.978 rows=623 loops=1)

  • Hash Cond: (projects_3.client_id = clients_1.id)
122. 0.806 2.146 ↑ 1.0 623 1

Hash Join (cost=150.41..334.56 rows=623 width=8) (actual time=0.821..2.146 rows=623 loops=1)

  • Hash Cond: (projects_3.id = pricing_models_1.project_id)
123. 0.558 0.558 ↑ 1.0 5,812 1

Seq Scan on projects projects_3 (cost=0.00..156.12 rows=5,812 width=8) (actual time=0.006..0.558 rows=5,812 loops=1)

124. 0.099 0.782 ↑ 1.0 623 1

Hash (cost=142.62..142.62 rows=623 width=4) (actual time=0.782..0.782 rows=623 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
125. 0.683 0.683 ↑ 1.0 623 1

Seq Scan on pricing_models pricing_models_1 (cost=0.00..142.62 rows=623 width=4) (actual time=0.013..0.683 rows=623 loops=1)

  • Filter: ((rates_type)::text = 'office'::text)
  • Rows Removed by Filter: 5187
126. 0.300 0.670 ↑ 1.0 1,849 1

Hash (cost=40.49..40.49 rows=1,849 width=8) (actual time=0.670..0.670 rows=1,849 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 89kB
127. 0.370 0.370 ↑ 1.0 1,849 1

Seq Scan on clients clients_1 (cost=0.00..40.49 rows=1,849 width=8) (actual time=0.009..0.370 rows=1,849 loops=1)

128. 0.108 1.481 ↑ 1.1 450 1

Hash (cost=158.85..158.85 rows=489 width=28) (actual time=1.481..1.481 rows=450 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
129. 0.109 1.373 ↑ 1.1 450 1

Hash Join (cost=140.44..158.85 rows=489 width=28) (actual time=0.864..1.373 rows=450 loops=1)

  • Hash Cond: (rate_cards_1.rateable_id = offices.id)
130. 0.368 1.127 ↑ 1.0 489 1

WindowAgg (cost=126.52..138.74 rows=489 width=45) (actual time=0.720..1.127 rows=489 loops=1)

131. 0.202 0.759 ↑ 1.0 489 1

Sort (cost=126.52..127.74 rows=489 width=29) (actual time=0.712..0.759 rows=489 loops=1)

  • Sort Key: rate_cards_1.rateable_id, rate_cards_1.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 63kB
132. 0.557 0.557 ↑ 1.0 489 1

Seq Scan on rate_cards rate_cards_1 (cost=0.00..104.68 rows=489 width=29) (actual time=0.124..0.557 rows=489 loops=1)

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 4365
133. 0.076 0.137 ↑ 1.0 441 1

Hash (cost=8.41..8.41 rows=441 width=4) (actual time=0.137..0.137 rows=441 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
134. 0.061 0.061 ↑ 1.0 441 1

Seq Scan on offices (cost=0.00..8.41 rows=441 width=4) (actual time=0.009..0.061 rows=441 loops=1)

135. 13.694 76.273 ↑ 2.2 94,593 1

Subquery Scan on *SELECT* 3 (cost=840.78..9,363.71 rows=205,419 width=44) (actual time=9.329..76.273 rows=94,593 loops=1)

136. 37.468 62.579 ↑ 2.2 94,593 1

Hash Join (cost=840.78..7,309.52 rows=205,419 width=612) (actual time=9.328..62.579 rows=94,593 loops=1)

  • Hash Cond: (rates_2.rate_card_id = rate_cards_2.id)
137. 15.820 15.820 ↑ 1.0 174,437 1

Seq Scan on rates rates_2 (cost=0.00..3,542.37 rows=174,437 width=28) (actual time=0.008..15.820 rows=174,437 loops=1)

138. 0.823 9.291 ↑ 1.0 4,004 1

Hash (cost=788.31..788.31 rows=4,197 width=24) (actual time=9.291..9.291 rows=4,004 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 299kB
139. 0.920 8.468 ↑ 1.0 4,004 1

Hash Join (cost=586.95..788.31 rows=4,197 width=24) (actual time=5.752..8.468 rows=4,004 loops=1)

  • Hash Cond: (projects_4.client_id = clients_2.id)
140. 1.015 3.723 ↑ 1.0 3,620 1

Hash Join (cost=228.77..380.91 rows=3,620 width=8) (actual time=1.920..3.723 rows=3,620 loops=1)

  • Hash Cond: (pricing_models_2.project_id = projects_4.id)
141. 0.841 0.841 ↑ 1.0 3,621 1

Seq Scan on pricing_models pricing_models_2 (cost=0.00..142.62 rows=3,621 width=4) (actual time=0.007..0.841 rows=3,621 loops=1)

  • Filter: ((rates_type)::text = 'client'::text)
  • Rows Removed by Filter: 2189
142. 0.816 1.867 ↑ 1.0 5,812 1

Hash (cost=156.12..156.12 rows=5,812 width=8) (actual time=1.867..1.867 rows=5,812 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 292kB
143. 1.051 1.051 ↑ 1.0 5,812 1

Seq Scan on projects projects_4 (cost=0.00..156.12 rows=5,812 width=8) (actual time=0.006..1.051 rows=5,812 loops=1)

144. 0.499 3.825 ↑ 1.0 1,820 1

Hash (cost=335.42..335.42 rows=1,821 width=28) (actual time=3.825..3.825 rows=1,820 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 123kB
145. 0.418 3.326 ↑ 1.0 1,820 1

Hash Join (cost=266.89..335.42 rows=1,821 width=28) (actual time=1.595..3.326 rows=1,820 loops=1)

  • Hash Cond: (rate_cards_2.rateable_id = clients_2.id)
146. 1.161 2.469 ↑ 1.0 1,821 1

WindowAgg (cost=203.29..248.81 rows=1,821 width=45) (actual time=1.151..2.469 rows=1,821 loops=1)

147. 0.685 1.308 ↑ 1.0 1,821 1

Sort (cost=203.29..207.84 rows=1,821 width=29) (actual time=1.140..1.308 rows=1,821 loops=1)

  • Sort Key: rate_cards_2.rateable_id, rate_cards_2.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 191kB
148. 0.623 0.623 ↑ 1.0 1,821 1

Seq Scan on rate_cards rate_cards_2 (cost=0.00..104.68 rows=1,821 width=29) (actual time=0.006..0.623 rows=1,821 loops=1)

  • Filter: ((rateable_type)::text = 'Client'::text)
  • Rows Removed by Filter: 3033
149. 0.248 0.439 ↑ 1.0 1,849 1

Hash (cost=40.49..40.49 rows=1,849 width=4) (actual time=0.439..0.439 rows=1,849 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 82kB
150. 0.191 0.191 ↑ 1.0 1,849 1

Seq Scan on clients clients_2 (cost=0.00..40.49 rows=1,849 width=4) (actual time=0.005..0.191 rows=1,849 loops=1)

151. 12.518 74.321 ↓ 2.4 76,366 1

Subquery Scan on *SELECT* 4 (cost=695.56..5,531.29 rows=31,961 width=44) (actual time=6.995..74.321 rows=76,366 loops=1)

152. 37.939 61.803 ↓ 2.4 76,366 1

Hash Join (cost=695.56..5,211.68 rows=31,961 width=612) (actual time=6.994..61.803 rows=76,366 loops=1)

  • Hash Cond: (rates_3.rate_card_id = rate_cards_3.id)
153. 16.922 16.922 ↑ 1.0 174,437 1

Seq Scan on rates rates_3 (cost=0.00..3,542.37 rows=174,437 width=28) (actual time=0.008..16.922 rows=174,437 loops=1)

154. 0.383 6.942 ↓ 2.4 1,591 1

Hash (cost=687.40..687.40 rows=653 width=24) (actual time=6.942..6.942 rows=1,591 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 110kB
155. 0.872 6.559 ↓ 2.4 1,591 1

Hash Join (cost=502.95..687.40 rows=653 width=24) (actual time=5.157..6.559 rows=1,591 loops=1)

  • Hash Cond: (projects_5.id = pricing_models_3.project_id)
156. 0.549 0.549 ↑ 1.0 5,812 1

Seq Scan on projects projects_5 (cost=0.00..156.12 rows=5,812 width=4) (actual time=0.009..0.549 rows=5,812 loops=1)

157. 0.381 5.138 ↓ 2.4 1,591 1

Hash (cost=494.79..494.79 rows=653 width=24) (actual time=5.138..5.138 rows=1,591 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 110kB
158. 0.530 4.757 ↓ 2.4 1,591 1

Hash Join (cost=403.40..494.79 rows=653 width=24) (actual time=2.608..4.757 rows=1,591 loops=1)

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
159. 1.532 3.286 ↑ 1.0 2,429 1

WindowAgg (cost=241.26..301.98 rows=2,429 width=45) (actual time=1.607..3.286 rows=2,429 loops=1)

160. 1.026 1.754 ↑ 1.0 2,429 1

Sort (cost=241.26..247.33 rows=2,429 width=29) (actual time=1.595..1.754 rows=2,429 loops=1)

  • Sort Key: rate_cards_3.rateable_id, rate_cards_3.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 286kB
161. 0.728 0.728 ↑ 1.0 2,429 1

Seq Scan on rate_cards rate_cards_3 (cost=0.00..104.68 rows=2,429 width=29) (actual time=0.007..0.728 rows=2,429 loops=1)

  • Filter: ((rateable_type)::text = 'PricingModel'::text)
  • Rows Removed by Filter: 2425
162. 0.223 0.941 ↑ 1.0 1,561 1

Hash (cost=142.62..142.62 rows=1,561 width=8) (actual time=0.940..0.941 rows=1,561 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 77kB
163. 0.718 0.718 ↑ 1.0 1,561 1

Seq Scan on pricing_models pricing_models_3 (cost=0.00..142.62 rows=1,561 width=8) (actual time=0.024..0.718 rows=1,561 loops=1)

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 4249
Planning time : 9.217 ms
Execution time : 71,431.818 ms