explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ih8c

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 2,951.121 ↑ 3.0 1 1

Subquery Scan on report_items_live (cost=1,327,414.34..1,327,414.45 rows=3 width=140) (actual time=2,951.119..2,951.121 rows=1 loops=1)

2. 0.038 2,951.118 ↑ 3.0 1 1

HashAggregate (cost=1,327,414.34..1,327,414.42 rows=3 width=184) (actual time=2,951.117..2,951.118 rows=1 loops=1)

  • Group Key: items.account_id, items.project_id, items.project_plan_id, items.epic_id, items.activity_id, items.user_id, items.date, items.value, items.reportable_type, items.reportable_id, items.plan_row_uuid, ""*SELECT* 1"".currency, COALESCE(exchange_rates_by_months.rate, '1'::double precision), ""*SELECT* 1"".value, items.cost, COALESCE(cost_exchange_rates.rate, '1'::double precision), COALESCE(original_exchange_rates.rate, '1'::double precision), GREATEST(items.updated_at, ""*SELECT* 1"".updated_at, ""*SELECT* 1"".rate_card_updated_at)
3. 0.007 2,951.080 ↑ 3.0 1 1

Nested Loop (cost=1,223,193.75..1,327,414.18 rows=3 width=184) (actual time=1,902.020..2,951.080 rows=1 loops=1)

4. 0.008 2,951.051 ↑ 3.0 1 1

Nested Loop Left Join (cost=1,223,193.48..1,327,389.25 rows=3 width=184) (actual time=1,901.994..2,951.051 rows=1 loops=1)

5. 0.003 2,951.026 ↑ 1.0 1 1

Nested Loop Left Join (cost=1,223,189.05..1,327,375.02 rows=1 width=692) (actual time=1,901.972..2,951.026 rows=1 loops=1)

6. 0.011 2,951.004 ↑ 1.0 1 1

Nested Loop Left Join (cost=1,223,184.62..1,327,360.80 rows=1 width=684) (actual time=1,901.951..2,951.004 rows=1 loops=1)

7. 209.432 2,950.956 ↑ 1.0 1 1

Nested Loop Left Join (cost=1,223,180.19..1,327,346.58 rows=1 width=676) (actual time=1,901.905..2,950.956 rows=1 loops=1)

  • Join Filter: ((items.date >= "*SELECT* 1".start_date) AND ((items.date <= "*SELECT* 1".end_date) OR ("*SELECT* 1".end_date IS NULL)) AND ("*SELECT* 1".project_id = items.project_id) AND ("*SELECT* 1".activity_id = items.activity_id))
  • Rows Removed by Join Filter: 261901
8. 0.008 1,034.550 ↑ 1.0 1 1

Nested Loop Left Join (cost=1,222,871.65..1,299,860.56 rows=1 width=648) (actual time=1,010.485..1,034.550 rows=1 loops=1)

9. 0.006 1,034.530 ↑ 1.0 1 1

Subquery Scan on items (cost=1,222,871.38..1,299,850.59 rows=1 width=644) (actual time=1,010.467..1,034.530 rows=1 loops=1)

  • Filter: (items.reportable_type = 'TimeLog'::text)
10. 0.003 1,034.524 ↑ 2.0 1 1

Append (cost=1,222,871.38..1,299,850.57 rows=2 width=112) (actual time=1,010.463..1,034.524 rows=1 loops=1)

11. 0.076 1,034.500 ↑ 1.0 1 1

Hash Left Join (cost=1,222,871.38..1,298,766.92 rows=1 width=161) (actual time=1,010.462..1,034.500 rows=1 loops=1)

  • Hash Cond: (items_1.project_plan_id = pa.project_plan_id)
  • Join Filter: ((items_1.date >= pa.start_date) AND (items_1.date <= pa.end_date) AND ((pa.plan_row_uuid = items_1.plan_row_uuid) OR (items_1.plan_row_uuid IS NULL)) AND ((pa.user_id = items_1.user_id) OR (items_1.user_id IS NULL)) AND ((pa.epic_id = items_1.epic_id) OR ((items_1.reportable_type = 'PlanItem'::text) AND (items_1.epic_id IS NULL)) OR ((items_1.reportable_type = 'TimeLog'::text) AND (pa.epic_id IS NULL))))
  • Rows Removed by Join Filter: 30
12. 0.166 109.155 ↑ 1.0 1 1

Merge Left Join (cost=1,212,652.41..1,288,546.50 rows=1 width=149) (actual time=85.141..109.155 rows=1 loops=1)

  • Merge Cond: (projects.account_id = staff_memberships_1.account_id)
  • Join Filter: ((items_1.date >= (COALESCE(staff_membership_activity_links.start_date, staff_memberships_1.joined_at))) AND (items_1.date <= (COALESCE(lag((staff_membership_activity_links.start_date - 1), 1) OVER (?), staff_memberships_1.archived_at, '3000-01-01'::date))) AND (staff_memberships_1.user_id = items_1.user_id))
  • Rows Removed by Join Filter: 194
  • Filter: ((items_1.vacation_hours IS NULL) OR (items_1.vacation_hours < (COALESCE(staff_membership_activity_links.capacity, accounts_1.default_capacity))::double precision))
13. 6.249 39.537 ↑ 2.0 1 1

Nested Loop (cost=1,211,763.22..1,287,551.25 rows=2 width=125) (actual time=15.694..39.537 rows=1 loops=1)

  • Join Filter: (projects.id = items_1.project_id)
  • Rows Removed by Join Filter: 5803
14. 7.487 27.484 ↓ 1.0 5,804 1

Merge Left Join (cost=664.30..778.31 rows=5,709 width=13) (actual time=14.915..27.484 rows=5,804 loops=1)

  • Merge Cond: (projects.account_id = accounts_1.id)
15. 9.772 17.008 ↓ 1.0 5,804 1

Sort (cost=625.30..639.58 rows=5,709 width=8) (actual time=14.401..17.008 rows=5,804 loops=1)

  • Sort Key: projects.account_id
  • Sort Method: quicksort Memory: 465kB
16. 7.236 7.236 ↓ 1.0 5,804 1

Seq Scan on projects (cost=0.00..269.09 rows=5,709 width=8) (actual time=0.009..7.236 rows=5,804 loops=1)

17. 2.712 2.989 ↓ 17.2 5,918 1

Sort (cost=38.99..39.86 rows=345 width=9) (actual time=0.510..2.989 rows=5,918 loops=1)

  • Sort Key: accounts_1.id
  • Sort Method: quicksort Memory: 41kB
18. 0.277 0.277 ↓ 1.0 354 1

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

19. 5.715 5.804 ↑ 2.0 1 5,804

Materialize (cost=1,211,098.92..1,286,601.67 rows=2 width=116) (actual time=0.000..0.001 rows=1 loops=5,804)

20. 0.002 0.089 ↑ 2.0 1 1

Subquery Scan on items_1 (cost=1,211,098.92..1,286,601.66 rows=2 width=116) (actual time=0.081..0.089 rows=1 loops=1)

21. 0.001 0.087 ↑ 2.0 1 1

Append (cost=1,211,098.92..1,286,601.64 rows=2 width=73) (actual time=0.079..0.087 rows=1 loops=1)

22. 0.002 0.031 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1_1 (cost=1,211,098.92..1,286,584.54 rows=1 width=106) (actual time=0.031..0.031 rows=0 loops=1)

23. 0.001 0.029 ↓ 0.0 0 1

Nested Loop Left Join (cost=1,211,098.92..1,286,584.53 rows=1 width=106) (actual time=0.029..0.029 rows=0 loops=1)

  • Join Filter: (nw_intervals_by_dates_roles.office_id = clients.office_id)
  • Filter: (COALESCE(non_working_intervals_by_dates.id, nw_intervals_by_dates_roles.id) IS NULL)
24. 0.002 0.028 ↓ 0.0 0 1

Nested Loop Left Join (cost=1,211,098.51..1,284,636.02 rows=25 width=110) (actual time=0.028..0.028 rows=0 loops=1)

  • Join Filter: (generate_series.generate_series = non_working_intervals_by_dates.date)
25. 0.009 0.026 ↓ 0.0 0 1

Hash Right Join (cost=1,211,098.09..1,284,605.58 rows=25 width=106) (actual time=0.026..0.026 rows=0 loops=1)

  • Hash Cond: ((staff_memberships.account_id = projects_1.account_id) AND (staff_memberships.user_id = plan_rows.user_id))
  • Join Filter: (generate_series.generate_series = ((generate_series_1.generate_series)::date))
26. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=1,211,052.76..1,274,595.35 rows=569,400 width=21) (never executed)

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
27. 0.000 0.000 ↓ 0.0 0

Sort (cost=1,211,052.76..1,222,053.08 rows=4,400,127 width=21) (never executed)

  • Sort Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
28. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=145.40..545,044.25 rows=4,400,127 width=21) (never executed)

  • 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)))
29. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=145.39..994.24 rows=21,762 width=29) (never executed)

  • Hash Cond: (vacations.staff_membership_id = staff_memberships.id)
30. 0.000 0.000 ↓ 0.0 0

Seq Scan on vacations (cost=0.00..549.62 rows=21,762 width=17) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Hash (cost=95.73..95.73 rows=3,973 width=20) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Seq Scan on staff_memberships (cost=0.00..95.73 rows=3,973 width=20) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Function Scan on generate_series generate_series_1 (cost=0.01..10.01 rows=1,000 width=8) (never executed)

34. 0.002 0.017 ↓ 0.0 0 1

Hash (cost=44.95..44.95 rows=25 width=70) (actual time=0.017..0.017 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
35. 0.000 0.015 ↓ 0.0 0 1

Nested Loop (cost=1.43..44.95 rows=25 width=70) (actual time=0.015..0.015 rows=0 loops=1)

36. 0.001 0.015 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.42..25.95 rows=1 width=70) (actual time=0.015..0.015 rows=0 loops=1)

37. 0.002 0.014 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.15..25.62 rows=1 width=70) (actual time=0.014..0.014 rows=0 loops=1)

38. 0.000 0.012 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.86..24.94 rows=1 width=62) (actual time=0.012..0.012 rows=0 loops=1)

39. 0.002 0.012 ↓ 0.0 0 1

Nested Loop (cost=0.58..16.63 rows=1 width=58) (actual time=0.012..0.012 rows=0 loops=1)

40. 0.010 0.010 ↓ 0.0 0 1

Index Scan using plan_items_pkey on plan_items (cost=0.29..8.31 rows=1 width=46) (actual time=0.010..0.010 rows=0 loops=1)

  • Index Cond: (id = 1245749)
  • Filter: (utilization > '0'::numeric)
41. 0.000 0.000 ↓ 0.0 0

Index Scan using index_plan_rows_on_uuid on plan_rows (cost=0.29..8.31 rows=1 width=28) (never executed)

  • Index Cond: (uuid = plan_items.plan_row_uuid)
  • Filter: ((user_id IS NOT NULL) OR (activity_id IS NOT NULL))
42. 0.000 0.000 ↓ 0.0 0

Index Scan using project_plans_pkey on project_plans (cost=0.28..8.30 rows=1 width=8) (never executed)

  • Index Cond: (id = plan_items.project_plan_id)
  • Filter: active
43. 0.000 0.000 ↓ 0.0 0

Index Scan using projects_pkey on projects projects_1 (cost=0.28..0.67 rows=1 width=12) (never executed)

  • Index Cond: (project_plans.project_id = id)
44. 0.000 0.000 ↓ 0.0 0

Index Scan using clients_pkey on clients (cost=0.28..0.32 rows=1 width=8) (never executed)

  • Index Cond: (projects_1.client_id = id)
45. 0.000 0.000 ↓ 0.0 0

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (never executed)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
46. 0.000 0.000 ↓ 0.0 0

Index Scan using non_working_intervals_by_dates_idx on non_working_intervals_by_dates (cost=0.41..1.19 rows=2 width=16) (never executed)

  • Index Cond: ((projects_1.account_id = account_id) AND (user_id = plan_rows.user_id))
47. 0.000 0.000 ↓ 0.0 0

Index Scan using non_working_intervals_by_dates_idx on non_working_intervals_by_dates nw_intervals_by_dates_roles (cost=0.41..77.79 rows=12 width=16) (never executed)

  • Index Cond: ((projects_1.account_id = account_id) AND (generate_series.generate_series = date))
48. 0.014 0.055 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.99..17.09 rows=1 width=40) (actual time=0.048..0.055 rows=1 loops=1)

  • Join Filter: (epics.project_plan_id IS NULL)
49. 0.008 0.027 ↑ 1.0 1 1

Nested Loop (cost=0.71..16.76 rows=1 width=36) (actual time=0.024..0.027 rows=1 loops=1)

50. 0.012 0.012 ↑ 1.0 1 1

Index Scan using time_logs_pkey on time_logs (cost=0.42..8.44 rows=1 width=28) (actual time=0.012..0.012 rows=1 loops=1)

  • Index Cond: (id = 1245749)
51. 0.007 0.007 ↑ 1.0 1 1

Index Scan using epics_pkey on epics (cost=0.29..8.30 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: (id = time_logs.epic_id)
52. 0.014 0.014 ↑ 1.0 1 1

Index Scan using index_project_plans_on_project_id on project_plans general_epic_plans (cost=0.28..0.32 rows=1 width=8) (actual time=0.011..0.014 rows=1 loops=1)

  • Index Cond: (project_id = epics.project_id)
  • Filter: active
  • Rows Removed by Filter: 3
53. 0.152 69.452 ↑ 21.5 196 1

Materialize (cost=889.19..983.92 rows=4,210 width=40) (actual time=69.022..69.452 rows=196 loops=1)

54. 0.173 69.300 ↑ 21.5 196 1

Unique (cost=889.19..931.29 rows=4,210 width=56) (actual time=69.019..69.300 rows=196 loops=1)

55. 17.563 69.127 ↑ 21.4 197 1

Sort (cost=889.19..899.72 rows=4,210 width=56) (actual time=69.017..69.127 rows=197 loops=1)

  • Sort Key: staff_memberships_1.account_id, staff_memberships_1.user_id, (COALESCE(staff_membership_activity_links.start_date, staff_memberships_1.joined_at)), (COALESCE(lag((staff_membership_activity_links.start_date - 1), 1) OVER (?), staff_memberships_1.archived_at, '3000-01-01'::date)) DESC
  • Sort Method: quicksort Memory: 790kB
56. 8.305 51.564 ↓ 1.0 4,249 1

WindowAgg (cost=541.03..635.76 rows=4,210 width=56) (actual time=37.236..51.564 rows=4,249 loops=1)

57. 18.365 43.259 ↓ 1.0 4,249 1

Sort (cost=541.03..551.56 rows=4,210 width=56) (actual time=37.227..43.259 rows=4,249 loops=1)

  • Sort Key: staff_membership_activity_links.staff_membership_id, staff_membership_activity_links.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 790kB
58. 9.607 24.894 ↓ 1.0 4,249 1

Hash Join (cost=145.39..287.60 rows=4,210 width=56) (actual time=13.424..24.894 rows=4,249 loops=1)

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships_1.id)
59. 1.887 1.887 ↓ 1.0 4,256 1

Seq Scan on staff_membership_activity_links (cost=0.00..84.26 rows=4,226 width=28) (actual time=0.008..1.887 rows=4,256 loops=1)

60. 6.610 13.400 ↓ 1.0 4,002 1

Hash (cost=95.73..95.73 rows=3,973 width=32) (actual time=13.400..13.400 rows=4,002 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 276kB
61. 6.790 6.790 ↓ 1.0 4,002 1

Seq Scan on staff_memberships staff_memberships_1 (cost=0.00..95.73 rows=3,973 width=32) (actual time=0.007..6.790 rows=4,002 loops=1)

62. 41.358 925.269 ↓ 8.3 44,370 1

Hash (cost=10,152.11..10,152.11 rows=5,349 width=48) (actual time=925.269..925.269 rows=44,370 loops=1)

  • Buckets: 65536 (originally 8192) Batches: 1 (originally 1) Memory Usage: 3907kB
63. 46.308 883.911 ↓ 8.3 44,370 1

Subquery Scan on pa (cost=10,018.38..10,152.11 rows=5,349 width=48) (actual time=726.144..883.911 rows=44,370 loops=1)

64. 205.778 837.603 ↓ 8.3 44,370 1

HashAggregate (cost=10,018.38..10,098.62 rows=5,349 width=48) (actual time=726.142..837.603 rows=44,370 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)
65. 100.942 631.825 ↑ 1.1 44,523 1

WindowAgg (cost=7,287.19..8,355.92 rows=47,499 width=60) (actual time=495.986..631.825 rows=44,523 loops=1)

66. 234.590 530.883 ↑ 1.1 44,523 1

Sort (cost=7,287.19..7,405.94 rows=47,499 width=60) (actual time=495.977..530.883 rows=44,523 loops=1)

  • Sort Key: plan_roles.plan_row_uuid, plan_roles.start_date DESC NULLS LAST
  • Sort Method: external merge Disk: 2760kB
67. 120.428 296.293 ↑ 1.1 44,523 1

Hash Join (cost=1,651.66..3,597.56 rows=47,499 width=60) (actual time=121.881..296.293 rows=44,523 loops=1)

  • Hash Cond: (plan_roles.plan_row_uuid = plan_rows_1.uuid)
68. 54.081 54.081 ↑ 1.2 44,525 1

Seq Scan on plan_roles (cost=0.00..1,278.12 rows=51,412 width=32) (actual time=0.010..54.081 rows=44,525 loops=1)

69. 54.987 121.784 ↓ 1.0 48,052 1

Hash (cost=1,060.18..1,060.18 rows=47,318 width=28) (actual time=121.784..121.784 rows=48,052 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3177kB
70. 66.797 66.797 ↓ 1.0 48,052 1

Seq Scan on plan_rows plan_rows_1 (cost=0.00..1,060.18 rows=47,318 width=28) (actual time=0.005..66.797 rows=48,052 loops=1)

71. 0.002 0.021 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_1 (cost=890.59..1,083.64 rows=1 width=64) (actual time=0.021..0.021 rows=0 loops=1)

72. 0.000 0.019 ↓ 0.0 0 1

Nested Loop Semi Join (cost=890.59..1,083.63 rows=1 width=64) (actual time=0.019..0.019 rows=0 loops=1)

  • Join Filter: (staff_membership_activity_links_1.id = staff_activities_with_dates.link_id)
73. 0.002 0.019 ↓ 0.0 0 1

Nested Loop (cost=1.40..37.72 rows=1 width=68) (actual time=0.019..0.019 rows=0 loops=1)

74. 0.017 0.017 ↓ 0.0 0 1

Index Scan using vacations_pkey on vacations vacations_1 (cost=0.41..8.43 rows=1 width=44) (actual time=0.017..0.017 rows=0 loops=1)

  • Index Cond: (id = 1245749)
75. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.98..29.28 rows=1 width=44) (never executed)

76. 0.000 0.000 ↓ 0.0 0

Index Scan using index_staff_membership_activity_links_on_staff_membership_id on staff_membership_activity_links staff_membership_activity_links_1 (cost=0.28..8.30 rows=1 width=20) (never executed)

  • Index Cond: (staff_membership_id = vacations_1.staff_membership_id)
77. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.70..20.97 rows=1 width=24) (never executed)

  • Join Filter: (generate_series_2.generate_series = non_working_intervals_by_dates_1.date)
  • Filter: (non_working_intervals_by_dates_1.id IS NULL)
78. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.29..19.43 rows=1 width=24) (never executed)

  • Join Filter: (vacations_1.staff_membership_id = staff_memberships_2.id)
79. 0.000 0.000 ↓ 0.0 0

Index Scan using memberships_pkey on staff_memberships staff_memberships_2 (cost=0.28..0.36 rows=1 width=16) (never executed)

  • Index Cond: (id = staff_membership_activity_links_1.staff_membership_id)
80. 0.000 0.000 ↓ 0.0 0

Function Scan on generate_series generate_series_2 (cost=0.01..18.76 rows=25 width=8) (never executed)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
81. 0.000 0.000 ↓ 0.0 0

Index Scan using non_working_intervals_by_dates_idx on non_working_intervals_by_dates non_working_intervals_by_dates_1 (cost=0.41..1.52 rows=2 width=16) (never executed)

  • Index Cond: ((staff_memberships_2.account_id = account_id) AND (user_id = staff_memberships_2.user_id))
82. 0.000 0.000 ↓ 0.0 0

Materialize (cost=889.19..1,038.88 rows=468 width=4) (never executed)

83. 0.000 0.000 ↓ 0.0 0

Subquery Scan on staff_activities_with_dates (cost=889.19..1,036.54 rows=468 width=4) (never executed)

  • Filter: ((('now'::cstring)::date >= staff_activities_with_dates.start_date) AND (('now'::cstring)::date <= staff_activities_with_dates.end_date))
84. 0.000 0.000 ↓ 0.0 0

Unique (cost=889.19..931.29 rows=4,210 width=28) (never executed)

85. 0.000 0.000 ↓ 0.0 0

Sort (cost=889.19..899.72 rows=4,210 width=28) (never executed)

  • Sort Key: staff_memberships_3.account_id, staff_memberships_3.user_id, (COALESCE(staff_membership_activity_links_2.start_date, staff_memberships_3.joined_at)), (COALESCE(lag((staff_membership_activity_links_2.start_date - 1), 1) OVER (?), staff_memberships_3.archived_at, '3000-01-01'::date)) DESC
86. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=541.03..635.76 rows=4,210 width=28) (never executed)

87. 0.000 0.000 ↓ 0.0 0

Sort (cost=541.03..551.56 rows=4,210 width=28) (never executed)

  • Sort Key: staff_membership_activity_links_2.staff_membership_id, staff_membership_activity_links_2.start_date DESC NULLS LAST
88. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=145.39..287.60 rows=4,210 width=28) (never executed)

  • Hash Cond: (staff_membership_activity_links_2.staff_membership_id = staff_memberships_3.id)
89. 0.000 0.000 ↓ 0.0 0

Seq Scan on staff_membership_activity_links staff_membership_activity_links_2 (cost=0.00..84.26 rows=4,226 width=12) (never executed)

90. 0.000 0.000 ↓ 0.0 0

Hash (cost=95.73..95.73 rows=3,973 width=20) (never executed)

91. 0.000 0.000 ↓ 0.0 0

Seq Scan on staff_memberships staff_memberships_3 (cost=0.00..95.73 rows=3,973 width=20) (never executed)

92. 0.012 0.012 ↓ 0.0 0 1

Index Scan using index_discounts_on_project_id on discounts (cost=0.27..9.95 rows=1 width=26) (actual time=0.012..0.012 rows=0 loops=1)

  • Index Cond: (project_id = items.project_id)
  • Filter: ((items.date >= start_date) AND (items.date <= end_date) AND (((discountable_type)::text = 'User'::text) OR ((discountable_type)::text = 'Epic'::text)) AND (((discountable_id = items.user_id) AND ((discountable_type)::text = 'User'::text)) OR ((discountable_id = items.epic_id) AND ((discountable_type)::text = 'Epic'::text))))
93. 269.127 1,706.974 ↑ 1.1 261,902 1

Append (cost=308.53..21,784.48 rows=285,077 width=44) (actual time=4.494..1,706.974 rows=261,902 loops=1)

94. 0.220 19.315 ↓ 58.2 291 1

Subquery Scan on *SELECT* 1 (cost=308.53..470.83 rows=5 width=44) (actual time=4.492..19.315 rows=291 loops=1)

95. 0.437 19.095 ↓ 58.2 291 1

Merge Join (cost=308.53..470.78 rows=5 width=44) (actual time=4.491..19.095 rows=291 loops=1)

  • Merge Cond: (clients_1.brand_id = brands.id)
96. 0.295 18.406 ↓ 5.6 291 1

Nested Loop (cost=303.16..1,704.32 rows=52 width=52) (actual time=4.361..18.406 rows=291 loops=1)

97. 0.010 3.207 ↓ 3.0 3 1

Merge Join (cost=302.74..303.02 rows=1 width=32) (actual time=3.192..3.207 rows=3 loops=1)

  • Merge Cond: (rate_cards.rateable_id = clients_1.brand_id)
98. 0.019 1.461 ↑ 37.7 3 1

WindowAgg (cost=113.60..116.15 rows=113 width=29) (actual time=1.447..1.461 rows=3 loops=1)

99. 0.095 1.442 ↑ 37.7 3 1

Sort (cost=113.60..113.89 rows=113 width=29) (actual time=1.440..1.442 rows=3 loops=1)

  • Sort Key: rate_cards.rateable_id, rate_cards.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 33kB
100. 1.347 1.347 ↓ 1.0 115 1

Seq Scan on rate_cards (cost=0.00..109.75 rows=113 width=29) (actual time=0.261..1.347 rows=115 loops=1)

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4731
101. 0.010 1.736 ↑ 1.3 3 1

Sort (cost=189.14..189.15 rows=4 width=8) (actual time=1.735..1.736 rows=3 loops=1)

  • Sort Key: clients_1.brand_id
  • Sort Method: quicksort Memory: 25kB
102. 0.008 1.726 ↑ 1.3 3 1

Nested Loop (cost=0.56..189.10 rows=4 width=8) (actual time=0.921..1.726 rows=3 loops=1)

103. 0.013 1.700 ↑ 1.3 3 1

Nested Loop (cost=0.28..187.78 rows=4 width=8) (actual time=0.910..1.700 rows=3 loops=1)

104. 1.669 1.669 ↑ 1.3 3 1

Seq Scan on pricing_models (cost=0.00..154.54 rows=4 width=4) (actual time=0.896..1.669 rows=3 loops=1)

  • Filter: ((rates_type)::text = 'brand'::text)
  • Rows Removed by Filter: 5799
105. 0.018 0.018 ↑ 1.0 1 3

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

  • Index Cond: (id = pricing_models.project_id)
106. 0.018 0.018 ↑ 1.0 1 3

Index Scan using clients_pkey on clients clients_1 (cost=0.28..0.32 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=3)

  • Index Cond: (id = projects_2.client_id)
107. 14.904 14.904 ↓ 1.9 97 3

Index Scan using index_rates_on_activity_id_and_rate_card_id on rates (cost=0.42..1,400.78 rows=52 width=28) (actual time=1.136..4.968 rows=97 loops=3)

  • Index Cond: (rate_card_id = rate_cards.id)
108. 0.201 0.252 ↓ 2.9 293 1

Sort (cost=5.37..5.62 rows=101 width=4) (actual time=0.125..0.252 rows=293 loops=1)

  • Sort Key: brands.id
  • Sort Method: quicksort Memory: 30kB
109. 0.051 0.051 ↓ 1.1 115 1

Seq Scan on brands (cost=0.00..2.01 rows=101 width=4) (actual time=0.008..0.051 rows=115 loops=1)

110. 117.123 482.191 ↓ 2.5 90,652 1

Subquery Scan on *SELECT* 2 (cost=725.76..5,732.67 rows=36,356 width=44) (actual time=44.898..482.191 rows=90,652 loops=1)

111. 246.535 365.068 ↓ 2.5 90,652 1

Hash Join (cost=725.76..5,369.11 rows=36,356 width=44) (actual time=44.897..365.068 rows=90,652 loops=1)

  • Hash Cond: (rates_1.rate_card_id = rate_cards_with_dates.id)
112. 100.999 100.999 ↑ 1.0 174,214 1

Seq Scan on rates rates_1 (cost=0.00..3,619.57 rows=176,057 width=28) (actual time=0.007..100.999 rows=174,214 loops=1)

113. 0.430 17.534 ↑ 1.1 651 1

Hash (cost=717.02..717.02 rows=699 width=24) (actual time=17.534..17.534 rows=651 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
114. 0.664 17.104 ↑ 1.1 651 1

Hash Join (cost=394.70..717.02 rows=699 width=24) (actual time=8.522..17.104 rows=651 loops=1)

  • Hash Cond: (clients_2.office_id = rate_cards_with_dates.rateable_id)
115. 0.556 12.929 ↓ 1.0 617 1

Hash Join (cost=241.88..554.95 rows=602 width=12) (actual time=5.002..12.929 rows=617 loops=1)

  • Hash Cond: (clients_2.office_id = offices.id)
116. 0.659 11.911 ↓ 1.0 617 1

Hash Join (cost=228.04..532.83 rows=602 width=8) (actual time=4.530..11.911 rows=617 loops=1)

  • Hash Cond: (projects_3.client_id = clients_2.id)
117. 2.729 9.123 ↓ 1.0 617 1

Hash Join (cost=162.08..458.59 rows=602 width=8) (actual time=2.390..9.123 rows=617 loops=1)

  • Hash Cond: (projects_3.id = pricing_models_1.project_id)
118. 4.023 4.023 ↓ 1.0 5,804 1

Seq Scan on projects projects_3 (cost=0.00..269.09 rows=5,709 width=8) (actual time=0.007..4.023 rows=5,804 loops=1)

119. 0.331 2.371 ↓ 1.0 617 1

Hash (cost=154.54..154.54 rows=603 width=4) (actual time=2.371..2.371 rows=617 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
120. 2.040 2.040 ↓ 1.0 617 1

Seq Scan on pricing_models pricing_models_1 (cost=0.00..154.54 rows=603 width=4) (actual time=0.029..2.040 rows=617 loops=1)

  • Filter: ((rates_type)::text = 'office'::text)
  • Rows Removed by Filter: 5185
121. 0.910 2.129 ↑ 1.0 1,849 1

Hash (cost=42.65..42.65 rows=1,865 width=8) (actual time=2.129..2.129 rows=1,849 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 89kB
122. 1.219 1.219 ↑ 1.0 1,849 1

Seq Scan on clients clients_2 (cost=0.00..42.65 rows=1,865 width=8) (actual time=0.010..1.219 rows=1,849 loops=1)

123. 0.179 0.462 ↓ 1.1 441 1

Hash (cost=8.93..8.93 rows=393 width=4) (actual time=0.462..0.462 rows=441 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
124. 0.283 0.283 ↓ 1.1 441 1

Seq Scan on offices (cost=0.00..8.93 rows=393 width=4) (actual time=0.008..0.283 rows=441 loops=1)

125. 0.284 3.511 ↓ 1.0 489 1

Hash (cost=146.81..146.81 rows=481 width=24) (actual time=3.511..3.511 rows=489 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
126. 0.398 3.227 ↓ 1.0 489 1

Subquery Scan on rate_cards_with_dates (cost=131.18..146.81 rows=481 width=24) (actual time=1.825..3.227 rows=489 loops=1)

127. 0.800 2.829 ↓ 1.0 489 1

WindowAgg (cost=131.18..142.00 rows=481 width=29) (actual time=1.824..2.829 rows=489 loops=1)

128. 0.569 2.029 ↓ 1.0 489 1

Sort (cost=131.18..132.38 rows=481 width=29) (actual time=1.818..2.029 rows=489 loops=1)

  • Sort Key: rate_cards_1.rateable_id, rate_cards_1.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 63kB
129. 1.460 1.460 ↓ 1.0 489 1

Seq Scan on rate_cards rate_cards_1 (cost=0.00..109.75 rows=481 width=29) (actual time=0.246..1.460 rows=489 loops=1)

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 4357
130. 119.009 553.372 ↑ 2.3 94,593 1

Subquery Scan on *SELECT* 3 (cost=1,003.83..9,811.22 rows=215,377 width=44) (actual time=32.823..553.372 rows=94,593 loops=1)

131. 286.366 434.363 ↑ 2.3 94,593 1

Hash Join (cost=1,003.83..7,657.45 rows=215,377 width=44) (actual time=32.821..434.363 rows=94,593 loops=1)

  • Hash Cond: (rates_2.rate_card_id = rate_cards_2.id)
132. 115.259 115.259 ↑ 1.0 174,214 1

Seq Scan on rates rates_2 (cost=0.00..3,619.57 rows=176,057 width=28) (actual time=0.006..115.259 rows=174,214 loops=1)

133. 2.526 32.738 ↑ 1.0 4,004 1

Hash (cost=952.07..952.07 rows=4,141 width=24) (actual time=32.738..32.738 rows=4,004 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 299kB
134. 3.883 30.212 ↑ 1.0 4,004 1

Hash Join (cost=578.12..952.07 rows=4,141 width=24) (actual time=17.442..30.212 rows=4,004 loops=1)

  • Hash Cond: (projects_4.client_id = clients_3.id)
135. 4.730 14.223 ↓ 1.0 3,620 1

Hash Join (cost=199.21..525.36 rows=3,565 width=8) (actual time=5.320..14.223 rows=3,620 loops=1)

  • Hash Cond: (projects_4.id = pricing_models_2.project_id)
136. 4.195 4.195 ↓ 1.0 5,804 1

Seq Scan on projects projects_4 (cost=0.00..269.09 rows=5,709 width=8) (actual time=0.007..4.195 rows=5,804 loops=1)

137. 1.950 5.298 ↓ 1.0 3,620 1

Hash (cost=154.54..154.54 rows=3,574 width=4) (actual time=5.298..5.298 rows=3,620 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 160kB
138. 3.348 3.348 ↓ 1.0 3,621 1

Seq Scan on pricing_models pricing_models_2 (cost=0.00..154.54 rows=3,574 width=4) (actual time=0.014..3.348 rows=3,621 loops=1)

  • Filter: ((rates_type)::text = 'client'::text)
  • Rows Removed by Filter: 2181
139. 1.106 12.106 ↓ 1.0 1,820 1

Hash (cost=356.39..356.39 rows=1,801 width=28) (actual time=12.106..12.106 rows=1,820 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 123kB
140. 1.892 11.000 ↓ 1.0 1,820 1

Hash Join (cost=273.10..356.39 rows=1,801 width=28) (actual time=5.334..11.000 rows=1,820 loops=1)

  • Hash Cond: (rate_cards_2.rateable_id = clients_3.id)
141. 3.005 7.207 ↓ 1.0 1,821 1

WindowAgg (cost=207.14..247.66 rows=1,801 width=29) (actual time=3.422..7.207 rows=1,821 loops=1)

142. 2.175 4.202 ↓ 1.0 1,821 1

Sort (cost=207.14..211.64 rows=1,801 width=29) (actual time=3.413..4.202 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
143. 2.027 2.027 ↓ 1.0 1,821 1

Seq Scan on rate_cards rate_cards_2 (cost=0.00..109.75 rows=1,801 width=29) (actual time=0.009..2.027 rows=1,821 loops=1)

  • Filter: ((rateable_type)::text = 'Client'::text)
  • Rows Removed by Filter: 3025
144. 0.961 1.901 ↑ 1.0 1,849 1

Hash (cost=42.65..42.65 rows=1,865 width=4) (actual time=1.901..1.901 rows=1,849 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 82kB
145. 0.940 0.940 ↑ 1.0 1,849 1

Seq Scan on clients clients_3 (cost=0.00..42.65 rows=1,865 width=4) (actual time=0.009..0.940 rows=1,849 loops=1)

146. 70.850 382.969 ↓ 2.3 76,366 1

Subquery Scan on *SELECT* 4 (cost=823.20..5,769.77 rows=33,339 width=44) (actual time=23.530..382.969 rows=76,366 loops=1)

147. 189.904 312.119 ↓ 2.3 76,366 1

Hash Join (cost=823.20..5,436.38 rows=33,339 width=44) (actual time=23.527..312.119 rows=76,366 loops=1)

  • Hash Cond: (rates_3.rate_card_id = rate_cards_3.id)
148. 98.723 98.723 ↑ 1.0 174,214 1

Seq Scan on rates rates_3 (cost=0.00..3,619.57 rows=176,057 width=28) (actual time=0.008..98.723 rows=174,214 loops=1)

149. 1.019 23.492 ↓ 2.5 1,591 1

Hash (cost=815.19..815.19 rows=641 width=24) (actual time=23.492..23.492 rows=1,591 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 110kB
150. 3.443 22.473 ↓ 2.5 1,591 1

Hash Join (cost=518.28..815.19 rows=641 width=24) (actual time=15.782..22.473 rows=1,591 loops=1)

  • Hash Cond: (projects_5.id = pricing_models_3.project_id)
151. 3.272 3.272 ↓ 1.0 5,804 1

Seq Scan on projects projects_5 (cost=0.00..269.09 rows=5,709 width=4) (actual time=0.008..3.272 rows=5,804 loops=1)

152. 0.991 15.758 ↓ 2.5 1,591 1

Hash (cost=510.24..510.24 rows=643 width=24) (actual time=15.758..15.758 rows=1,591 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 110kB
153. 2.060 14.767 ↓ 2.5 1,591 1

Hash Join (cost=417.36..510.24 rows=643 width=24) (actual time=7.741..14.767 rows=1,591 loops=1)

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
154. 3.899 9.146 ↓ 1.0 2,421 1

WindowAgg (cost=243.55..297.21 rows=2,385 width=29) (actual time=4.167..9.146 rows=2,421 loops=1)

155. 2.976 5.247 ↓ 1.0 2,421 1

Sort (cost=243.55..249.51 rows=2,385 width=29) (actual time=4.161..5.247 rows=2,421 loops=1)

  • Sort Key: rate_cards_3.rateable_id, rate_cards_3.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 286kB
156. 2.271 2.271 ↓ 1.0 2,421 1

Seq Scan on rate_cards rate_cards_3 (cost=0.00..109.75 rows=2,385 width=29) (actual time=0.008..2.271 rows=2,421 loops=1)

  • Filter: ((rateable_type)::text = 'PricingModel'::text)
  • Rows Removed by Filter: 2425
157. 0.844 3.561 ↓ 1.0 1,561 1

Hash (cost=154.54..154.54 rows=1,542 width=8) (actual time=3.561..3.561 rows=1,561 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 77kB
158. 2.717 2.717 ↓ 1.0 1,561 1

Seq Scan on pricing_models pricing_models_3 (cost=0.00..154.54 rows=1,542 width=8) (actual time=0.019..2.717 rows=1,561 loops=1)

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 4241
159. 0.010 0.037 ↑ 3.0 1 1

Bitmap Heap Scan on exchange_rates_by_months (cost=4.43..14.19 rows=3 width=24) (actual time=0.036..0.037 rows=1 loops=1)

  • Recheck Cond: ((account_id = items.account_id) AND ((currency)::text = ("*SELECT* 1".currency)::text) AND (start_date <= items.date) AND (end_date >= items.date))
  • Heap Blocks: exact=1
160. 0.027 0.027 ↑ 3.0 1 1

Bitmap Index Scan on exchange_rates_by_months_idx (cost=0.00..4.43 rows=3 width=0) (actual time=0.027..0.027 rows=1 loops=1)

  • Index Cond: ((account_id = items.account_id) AND ((currency)::text = ("*SELECT* 1".currency)::text) AND (start_date <= items.date) AND (end_date >= items.date))
161. 0.005 0.019 ↑ 3.0 1 1

Bitmap Heap Scan on exchange_rates_by_months original_exchange_rates (cost=4.43..14.19 rows=3 width=24) (actual time=0.018..0.019 rows=1 loops=1)

  • Recheck Cond: ((account_id = items.account_id) AND ((currency)::text = ("*SELECT* 1".currency)::text) AND (start_date <= items.date) AND (end_date >= items.date))
  • Heap Blocks: exact=1
162. 0.014 0.014 ↑ 3.0 1 1

Bitmap Index Scan on exchange_rates_by_months_idx (cost=0.00..4.43 rows=3 width=0) (actual time=0.014..0.014 rows=1 loops=1)

  • Index Cond: ((account_id = items.account_id) AND ((currency)::text = ("*SELECT* 1".currency)::text) AND (start_date <= items.date) AND (end_date >= items.date))
163. 0.003 0.017 ↑ 3.0 1 1

Bitmap Heap Scan on exchange_rates_by_months cost_exchange_rates (cost=4.43..14.19 rows=3 width=24) (actual time=0.017..0.017 rows=1 loops=1)

  • Recheck Cond: ((account_id = items.account_id) AND ((currency)::text = (items.cost_currency)::text) AND (start_date <= items.date) AND (end_date >= items.date))
  • Heap Blocks: exact=1
164. 0.014 0.014 ↑ 3.0 1 1

Bitmap Index Scan on exchange_rates_by_months_idx (cost=0.00..4.43 rows=3 width=0) (actual time=0.014..0.014 rows=1 loops=1)

  • Index Cond: ((account_id = items.account_id) AND ((currency)::text = (items.cost_currency)::text) AND (start_date <= items.date) AND (end_date >= items.date))
165. 0.022 0.022 ↑ 1.0 1 1

Index Scan using accounts_pkey on accounts (cost=0.27..8.30 rows=1 width=4) (actual time=0.021..0.022 rows=1 loops=1)

  • Index Cond: (id = items.account_id)
  • Filter: ((suspended_at)::date >= ('now'::cstring)::date)
Planning time : 32.919 ms
Execution time : 2,953.616 ms