explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kCsq

Settings
# exclusive inclusive rows x rows loops node
1. 42,301.323 62,758.143 ↓ 5.4 849,947 1

Merge Left Join (cost=2,067,272.86..2,210,837.36 rows=157,180 width=140) (actual time=20,168.280..62,758.143 rows=849,947 loops=1)

  • Merge 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: 229908
2. 779.623 19,736.816 ↓ 5.4 849,947 1

Sort (cost=2,020,441.95..2,020,834.90 rows=157,180 width=136) (actual time=19,572.678..19,736.816 rows=849,947 loops=1)

  • Sort Key: projects.id, (COALESCE(""*SELECT* 1_1"".activity_id, sa.activity_id))
  • Sort Method: quicksort Memory: 144100kB
3. 278.862 18,957.193 ↓ 5.4 849,947 1

Hash Join (cost=1,132,107.56..2,006,875.70 rows=157,180 width=136) (actual time=1,997.644..18,957.193 rows=849,947 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
4. 151.727 18,678.218 ↓ 2.4 1,116,116 1

Append (cost=1,132,088.89..2,000,889.02 rows=471,540 width=136) (actual time=1,997.519..18,678.218 rows=1,116,116 loops=1)

5. 593.715 18,240.037 ↓ 2.7 1,087,210 1

Hash Join (cost=1,132,088.89..1,557,773.60 rows=403,983 width=108) (actual time=1,997.519..18,240.037 rows=1,087,210 loops=1)

  • Hash Cond: (projects.account_id = accounts_1.id)
  • Join Filter: ((((""*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: 13596
6. 490.061 17,646.175 ↑ 1.1 1,100,806 1

Hash Left Join (cost=1,132,069.92..1,539,437.01 rows=1,199,950 width=144) (actual time=1,997.358..17,646.175 rows=1,100,806 loops=1)

  • Hash Cond: ((projects.account_id = sa.account_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))
  • Rows Removed by Join Filter: 258899
7. 341.706 17,153.516 ↑ 1.1 1,100,806 1

Hash Join (cost=1,131,915.40..1,438,239.63 rows=1,199,950 width=120) (actual time=1,994.745..17,153.516 rows=1,100,806 loops=1)

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
8. 147.410 16,809.725 ↑ 1.1 1,100,806 1

Append (cost=1,131,686.63..1,422,859.20 rows=1,199,950 width=116) (actual time=1,992.649..16,809.725 rows=1,100,806 loops=1)

9. 138.532 2,837.576 ↑ 1.2 396,845 1

Result (cost=1,131,686.63..1,226,655.23 rows=495,989 width=116) (actual time=1,992.648..2,837.576 rows=396,845 loops=1)

10. 48.488 2,699.044 ↑ 1.2 396,845 1

Append (cost=1,131,686.63..1,220,455.37 rows=495,989 width=140) (actual time=1,992.643..2,699.044 rows=396,845 loops=1)

11. 64.859 2,326.201 ↑ 1.4 338,629 1

Subquery Scan on *SELECT* 1_1 (cost=1,131,686.63..1,155,495.07 rows=457,239 width=114) (actual time=1,992.642..2,326.201 rows=338,629 loops=1)

12. 99.670 2,261.342 ↑ 1.4 338,629 1

Merge Left Join (cost=1,131,686.63..1,148,636.49 rows=457,239 width=134) (actual time=1,992.641..2,261.342 rows=338,629 loops=1)

  • Merge Cond: ((projects_1.account_id = summary_vacations_by_dates.account_id) AND (generate_series.generate_series = summary_vacations_by_dates.date) AND (plan_rows.user_id = summary_vacations_by_dates.user_id))
13. 117.171 2,029.574 ↑ 1.4 338,629 1

Merge Anti Join (cost=525,089.35..531,687.96 rows=457,239 width=66) (actual time=1,866.338..2,029.574 rows=338,629 loops=1)

  • Merge Cond: ((projects_1.account_id = non_working_intervals_by_dates.account_id) AND (generate_series.generate_series = non_working_intervals_by_dates.date) AND (plan_rows.user_id = non_working_intervals_by_dates.user_id))
14. 1,054.475 1,869.731 ↑ 1.7 346,275 1

Sort (cost=519,071.36..520,519.29 rows=579,175 width=66) (actual time=1,828.317..1,869.731 rows=346,275 loops=1)

  • Sort Key: projects_1.account_id, generate_series.generate_series, plan_rows.user_id
  • Sort Method: quicksort Memory: 60983kB
15. 168.409 815.256 ↑ 1.7 346,275 1

Merge Left Join (cost=12,929.64..463,633.77 rows=579,175 width=66) (actual time=252.095..815.256 rows=346,275 loops=1)

  • Merge Cond: (plan_items.plan_row_uuid = pa.plan_row_uuid)
  • Join Filter: (((pa.epic_id = plan_rows.epic_id) OR (plan_rows.epic_id IS NULL)) AND (pa.project_plan_id = project_plans.id) AND (pa.user_id = plan_rows.user_id) AND ((generate_series.generate_series)::date >= pa.start_date) AND ((generate_series.generate_series)::date <= pa.end_date))
  • Rows Removed by Join Filter: 3247
16. 64.498 410.999 ↑ 1.7 346,275 1

Nested Loop (cost=3,971.09..448,257.03 rows=579,175 width=70) (actual time=49.292..410.999 rows=346,275 loops=1)

17. 17.257 101.143 ↓ 1.2 27,262 1

Merge Join (cost=3,971.09..8,084.02 rows=23,167 width=70) (actual time=49.277..101.143 rows=27,262 loops=1)

  • Merge Cond: (plan_rows.uuid = plan_items.plan_row_uuid)
18. 29.303 29.303 ↑ 1.0 42,344 1

Index Scan using plan_rows_uuid_key on plan_rows (cost=0.29..3,663.60 rows=42,428 width=28) (actual time=0.010..29.303 rows=42,344 loops=1)

  • Filter: (user_id IS NOT NULL)
  • Rows Removed by Filter: 5762
19. 20.185 54.583 ↓ 1.1 29,797 1

Sort (cost=3,970.18..4,035.85 rows=26,267 width=58) (actual time=49.250..54.583 rows=29,797 loops=1)

  • Sort Key: plan_items.plan_row_uuid
  • Sort Method: quicksort Memory: 4959kB
20. 7.724 34.398 ↓ 1.1 29,797 1

Hash Join (cost=430.80..2,042.06 rows=26,267 width=58) (actual time=3.795..34.398 rows=29,797 loops=1)

  • Hash Cond: (project_plans.project_id = projects_1.id)
21. 12.707 24.693 ↓ 1.1 29,797 1

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

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

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

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
23. 0.491 1.734 ↑ 1.0 2,851 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 144kB
24. 1.243 1.243 ↑ 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.243 rows=2,851 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3188
25. 1.059 1.981 ↑ 1.0 5,812 1

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

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

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

27. 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
28. 52.466 235.848 ↓ 77.4 384,529 1

Sort (cost=8,958.54..8,970.97 rows=4,971 width=36) (actual time=202.792..235.848 rows=384,529 loops=1)

  • Sort Key: pa.plan_row_uuid
  • Sort Method: quicksort Memory: 5007kB
29. 6.695 183.382 ↓ 8.9 44,428 1

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

30. 76.122 176.687 ↓ 8.9 44,428 1

HashAggregate (cost=8,529.07..8,603.63 rows=4,971 width=48) (actual time=132.955..176.687 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)
31. 32.976 100.565 ↑ 1.0 44,581 1

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

32. 25.696 67.589 ↑ 1.0 44,581 1

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

  • Sort Key: plan_roles.plan_row_uuid, plan_roles.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 5023kB
33. 16.464 41.893 ↑ 1.0 44,581 1

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

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

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

35. 11.585 20.900 ↑ 1.0 48,106 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3180kB
36. 9.315 9.315 ↑ 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.005..9.315 rows=48,106 loops=1)

37. 32.693 42.672 ↑ 1.0 59,679 1

Sort (cost=6,017.99..6,167.27 rows=59,712 width=12) (actual time=38.014..42.672 rows=59,679 loops=1)

  • Sort Key: non_working_intervals_by_dates.account_id, non_working_intervals_by_dates.date, non_working_intervals_by_dates.user_id
  • Sort Method: quicksort Memory: 4336kB
38. 9.979 9.979 ↑ 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.012..9.979 rows=59,712 loops=1)

39. 42.184 132.098 ↑ 15.5 37,180 1

Sort (cost=606,597.28..608,034.78 rows=575,000 width=44) (actual time=126.296..132.098 rows=37,180 loops=1)

  • Sort Key: summary_vacations_by_dates.account_id, summary_vacations_by_dates.date, summary_vacations_by_dates.user_id
  • Sort Method: quicksort Memory: 2844kB
40. 4.557 89.914 ↑ 17.8 32,266 1

Subquery Scan on summary_vacations_by_dates (cost=537,214.32..551,589.32 rows=575,000 width=44) (actual time=66.924..89.914 rows=32,266 loops=1)

41. 34.460 85.357 ↑ 17.8 32,266 1

HashAggregate (cost=537,214.32..545,839.32 rows=575,000 width=52) (actual time=66.923..85.357 rows=32,266 loops=1)

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
42. 11.758 50.897 ↑ 134.2 32,422 1

Hash Join (cost=138.05..493,700.50 rows=4,351,382 width=21) (actual time=1.472..50.897 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
43. 13.520 37.707 ↑ 634.8 34,330 1

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

44. 2.395 2.395 ↑ 1.0 21,792 1

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

45. 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)

46. 0.734 1.432 ↑ 1.0 4,002 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 229kB
47. 0.698 0.698 ↑ 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.698 rows=4,002 loops=1)

48. 60.534 324.355 ↓ 1.5 58,216 1

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

  • Hash Cond: (project_plans_1.project_id = projects_1_1.id)
  • Join Filter: (NOT (SubPlan 1))
  • Rows Removed by Join Filter: 770
49. 14.055 84.713 ↑ 1.3 58,986 1

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

50. 1.790 30.098 ↑ 1.2 2,535 1

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

  • Hash Cond: (plan_items_1.project_plan_id = project_plans_1.id)
51. 12.820 26.588 ↓ 1.2 7,876 1

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

  • Hash Cond: (plan_items_1.plan_row_uuid = plan_rows_2.uuid)
52. 10.406 10.406 ↓ 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.015..10.406 rows=55,664 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
53. 1.417 3.362 ↓ 1.0 5,762 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 366kB
54. 1.730 1.945 ↓ 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.262..1.945 rows=5,762 loops=1)

  • Recheck Cond: (user_id IS NULL)
  • Heap Blocks: exact=430
55. 0.215 0.215 ↓ 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.215..0.215 rows=5,762 loops=1)

  • Index Cond: (user_id IS NULL)
56. 0.511 1.720 ↑ 1.0 2,851 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 144kB
57. 1.209 1.209 ↑ 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.009..1.209 rows=2,851 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3188
58. 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
59. 0.983 2.150 ↑ 1.0 5,812 1

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

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

Seq Scan on projects projects_1_1 (cost=0.00..156.12 rows=5,812 width=12) (actual time=0.019..1.167 rows=5,812 loops=1)

61.          

SubPlan (for Hash Join)

62. 58.986 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)

63. 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: (projects_1_1.client_id = id)
64. 58.986 58.986 ↓ 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.001..0.001 rows=0 loops=58,986)

  • Index Cond: ((account_id = projects_1_1.account_id) AND (office_id = clients_3.office_id) AND (date = generate_series_2.generate_series))
  • Heap Fetches: 770
65. 13,011.715 13,824.739 ↑ 1.0 703,961 1

Hash Right Join (cost=45,029.23..183,164.61 rows=703,961 width=116) (actual time=746.956..13,824.739 rows=703,961 loops=1)

  • Hash Cond: (plan_rows_3.user_id = time_logs.user_id)
  • Join Filter: ((time_logs.date >= (COALESCE(plan_roles_1.start_date, '1980-01-01'::date))) AND (time_logs.date <= (COALESCE(lag((plan_roles_1.start_date - 1), 1) OVER (?), '3000-01-01'::date))) AND (((plan_rows_3.epic_id = time_logs.epic_id) AND (plan_rows_3.project_plan_id = epics.project_plan_id)) OR ((plan_rows_3.epic_id IS NULL) AND (plan_rows_3.project_plan_id = general_epic_plans.id))))
  • Rows Removed by Join Filter: 63215381
66. 100.856 205.905 ↓ 8.9 44,428 1

HashAggregate (cost=8,529.07..8,603.63 rows=4,971 width=48) (actual time=135.760..205.905 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)
67. 31.712 105.049 ↑ 1.0 44,581 1

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

68. 26.772 73.337 ↑ 1.0 44,581 1

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

  • Sort Key: plan_roles_1.plan_row_uuid, plan_roles_1.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 5023kB
69. 18.993 46.565 ↑ 1.0 44,581 1

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

  • Hash Cond: (plan_roles_1.plan_row_uuid = plan_rows_3.uuid)
70. 4.873 4.873 ↑ 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.009..4.873 rows=44,581 loops=1)

71. 12.884 22.699 ↑ 1.0 48,106 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3180kB
72. 9.815 9.815 ↑ 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.006..9.815 rows=48,106 loops=1)

73. 216.486 607.119 ↑ 1.0 703,961 1

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

  • Buckets: 1048576 Batches: 1 Memory Usage: 62366kB
74. 276.260 390.633 ↑ 1.0 703,961 1

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

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

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

76. 6.140 19.558 ↑ 1.0 26,435 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1381kB
77. 8.872 13.418 ↑ 1.0 26,435 1

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

  • Hash Cond: (epics.project_id = general_epic_plans.project_id)
  • Join Filter: (epics.project_plan_id IS NULL)
  • Rows Removed by Join Filter: 11144
78. 2.976 2.976 ↑ 1.0 26,435 1

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

79. 0.467 1.570 ↑ 1.0 2,851 1

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

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

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

  • Filter: active
  • Rows Removed by Filter: 3188
81. 1.092 2.085 ↑ 1.0 5,812 1

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

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

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

83. 1.468 2.598 ↑ 1.0 4,221 1

Hash (cost=91.21..91.21 rows=4,221 width=40) (actual time=2.598..2.598 rows=4,221 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 370kB
84. 1.130 1.130 ↑ 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.015..1.130 rows=4,221 loops=1)

85. 0.068 0.147 ↑ 1.0 354 1

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

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

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

87. 10.991 286.454 ↑ 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.487..286.454 rows=28,906 loops=1)

88. 174.841 275.463 ↑ 2.3 28,906 1

Hash Anti Join (cost=2,320.78..435,873.42 rows=67,557 width=143) (actual time=26.484..275.463 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. 17.952 76.728 ↑ 2.6 29,394 1

Hash Join (cost=143.98..429,184.39 rows=77,078 width=64) (actual time=2.443..76.728 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.874 56.406 ↑ 17.5 31,132 1

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

91. 2.948 2.948 ↑ 1.0 21,792 1

Seq Scan on vacations vacations_1 (cost=0.00..500.92 rows=21,792 width=44) (actual time=0.012..2.948 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.369 2.370 ↑ 1.0 4,221 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 350kB
94. 1.001 1.001 ↑ 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.006..1.001 rows=4,221 loops=1)

95. 12.485 23.894 ↑ 1.0 58,975 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3047kB
96. 11.409 11.409 ↑ 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.012..11.409 rows=59,712 loops=1)

97. 0.003 0.113 ↑ 11.8 10 1

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

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

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

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 344
99. 426.647 720.004 ↓ 4.1 1,115,234 1

Sort (cost=46,830.92..47,512.59 rows=272,669 width=44) (actual time=595.017..720.004 rows=1,115,234 loops=1)

  • Sort Key: ""*SELECT* 1"".project_id, ""*SELECT* 1"".activity_id
  • Sort Method: quicksort Memory: 32838kB
100. 31.631 293.357 ↑ 1.0 263,033 1

Append (cost=300.88..22,213.28 rows=272,669 width=44) (actual time=2.356..293.357 rows=263,033 loops=1)

101. 0.107 18.072 ↓ 9.7 474 1

Subquery Scan on *SELECT* 1 (cost=300.88..470.62 rows=49 width=44) (actual time=2.354..18.072 rows=474 loops=1)

102. 0.150 17.965 ↓ 9.7 474 1

Merge Join (cost=300.88..470.13 rows=49 width=612) (actual time=2.352..17.965 rows=474 loops=1)

  • Merge Cond: (clients.brand_id = brands.id)
103. 0.150 17.757 ↓ 9.7 474 1

Nested Loop (cost=294.80..1,671.71 rows=49 width=52) (actual time=2.290..17.757 rows=474 loops=1)

104. 0.006 1.602 ↓ 5.0 5 1

Merge Join (cost=294.38..294.67 rows=1 width=32) (actual time=1.590..1.602 rows=5 loops=1)

  • Merge Cond: (rate_cards.rateable_id = clients.brand_id)
105. 0.022 0.843 ↑ 28.8 4 1

WindowAgg (cost=108.61..111.49 rows=115 width=45) (actual time=0.832..0.843 rows=4 loops=1)

106. 0.052 0.821 ↑ 23.0 5 1

Sort (cost=108.61..108.90 rows=115 width=29) (actual time=0.820..0.821 rows=5 loops=1)

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

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

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4739
108. 0.006 0.753 ↑ 1.0 5 1

Sort (cost=185.76..185.78 rows=5 width=8) (actual time=0.751..0.753 rows=5 loops=1)

  • Sort Key: clients.brand_id
  • Sort Method: quicksort Memory: 25kB
109. 0.005 0.747 ↑ 1.0 5 1

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

110. 0.006 0.732 ↑ 1.0 5 1

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

111. 0.706 0.706 ↑ 1.0 5 1

Seq Scan on pricing_models (cost=0.00..142.62 rows=5 width=4) (actual time=0.466..0.706 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. 16.005 16.005 ↓ 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.759..3.201 rows=95 loops=5)

  • Index Cond: (rate_card_id = rate_cards.id)
115. 0.032 0.058 ↑ 38.3 3 1

Sort (cost=6.09..6.37 rows=115 width=4) (actual time=0.057..0.058 rows=3 loops=1)

  • Sort Key: brands.id
  • Sort Method: quicksort Memory: 30kB
116. 0.026 0.026 ↑ 1.0 115 1

Seq Scan on brands (cost=0.00..2.15 rows=115 width=4) (actual time=0.008..0.026 rows=115 loops=1)

117. 13.365 76.615 ↓ 2.6 91,600 1

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

118. 39.806 63.250 ↓ 2.6 91,600 1

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

  • Hash Cond: (rates_1.rate_card_id = rate_cards_1.id)
119. 18.228 18.228 ↑ 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.008..18.228 rows=174,437 loops=1)

120. 0.201 5.216 ↑ 1.1 657 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
121. 0.198 5.015 ↑ 1.1 657 1

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

  • Hash Cond: (clients_1.office_id = offices.id)
122. 0.207 3.437 ↑ 1.0 623 1

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

  • Hash Cond: (projects_3.client_id = clients_1.id)
123. 0.938 2.569 ↑ 1.0 623 1

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

  • Hash Cond: (projects_3.id = pricing_models_1.project_id)
124. 0.642 0.642 ↑ 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.642 rows=5,812 loops=1)

125. 0.132 0.989 ↑ 1.0 623 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
126. 0.857 0.857 ↑ 1.0 623 1

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

  • Filter: ((rates_type)::text = 'office'::text)
  • Rows Removed by Filter: 5187
127. 0.304 0.661 ↑ 1.0 1,849 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 89kB
128. 0.357 0.357 ↑ 1.0 1,849 1

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

129. 0.115 1.380 ↑ 1.1 450 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
130. 0.128 1.265 ↑ 1.1 450 1

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

  • Hash Cond: (rate_cards_1.rateable_id = offices.id)
131. 0.372 1.015 ↑ 1.0 489 1

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

132. 0.149 0.643 ↑ 1.0 489 1

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

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

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

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 4365
134. 0.070 0.122 ↑ 1.0 441 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
135. 0.052 0.052 ↑ 1.0 441 1

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

136. 14.900 88.441 ↑ 2.2 94,593 1

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

137. 43.497 73.541 ↑ 2.2 94,593 1

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

  • Hash Cond: (rates_2.rate_card_id = rate_cards_2.id)
138. 18.512 18.512 ↑ 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.010..18.512 rows=174,437 loops=1)

139. 0.924 11.532 ↑ 1.0 4,004 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 299kB
140. 1.057 10.608 ↑ 1.0 4,004 1

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

  • Hash Cond: (projects_4.client_id = clients_2.id)
141. 1.100 4.229 ↑ 1.0 3,620 1

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

  • Hash Cond: (pricing_models_2.project_id = projects_4.id)
142. 1.006 1.006 ↑ 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.009..1.006 rows=3,621 loops=1)

  • Filter: ((rates_type)::text = 'client'::text)
  • Rows Removed by Filter: 2189
143. 0.934 2.123 ↑ 1.0 5,812 1

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

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

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

145. 0.608 5.322 ↑ 1.0 1,820 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 123kB
146. 0.611 4.714 ↑ 1.0 1,820 1

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

  • Hash Cond: (rate_cards_2.rateable_id = clients_2.id)
147. 1.676 3.487 ↑ 1.0 1,821 1

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

148. 0.887 1.811 ↑ 1.0 1,821 1

Sort (cost=203.29..207.84 rows=1,821 width=29) (actual time=1.629..1.811 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
149. 0.924 0.924 ↑ 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.009..0.924 rows=1,821 loops=1)

  • Filter: ((rateable_type)::text = 'Client'::text)
  • Rows Removed by Filter: 3033
150. 0.344 0.616 ↑ 1.0 1,849 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 82kB
151. 0.272 0.272 ↑ 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.272 rows=1,849 loops=1)

152. 12.118 78.598 ↓ 2.4 76,366 1

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

153. 38.974 66.480 ↓ 2.4 76,366 1

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

  • Hash Cond: (rates_3.rate_card_id = rate_cards_3.id)
154. 17.676 17.676 ↑ 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.009..17.676 rows=174,437 loops=1)

155. 0.617 9.830 ↓ 2.4 1,591 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 110kB
156. 1.532 9.213 ↓ 2.4 1,591 1

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

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

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

158. 0.539 6.790 ↓ 2.4 1,591 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 110kB
159. 0.736 6.251 ↓ 2.4 1,591 1

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

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
160. 2.363 4.227 ↑ 1.0 2,429 1

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

161. 1.065 1.864 ↑ 1.0 2,429 1

Sort (cost=241.26..247.33 rows=2,429 width=29) (actual time=1.621..1.864 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
162. 0.799 0.799 ↑ 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.799 rows=2,429 loops=1)

  • Filter: ((rateable_type)::text = 'PricingModel'::text)
  • Rows Removed by Filter: 2425
163. 0.309 1.288 ↑ 1.0 1,561 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 77kB
164. 0.979 0.979 ↑ 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.010..0.979 rows=1,561 loops=1)

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 4249
Planning time : 9.112 ms
Execution time : 62,879.422 ms