explain.depesz.com

PostgreSQL's explain analyze made readable

Result: X6h1

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

Subquery Scan on report_items_live (cost=1,237,177.34..1,237,177.45 rows=3 width=140) (actual time=1,850.885..1,850.887 rows=1 loops=1)

2. 0.060 1,850.884 ↑ 3.0 1 1

HashAggregate (cost=1,237,177.34..1,237,177.42 rows=3 width=184) (actual time=1,850.883..1,850.884 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 1,850.824 ↑ 3.0 1 1

Nested Loop (cost=1,132,956.75..1,237,177.18 rows=3 width=184) (actual time=1,131.791..1,850.824 rows=1 loops=1)

4. 0.007 1,850.790 ↑ 3.0 1 1

Nested Loop Left Join (cost=1,132,956.48..1,237,152.25 rows=3 width=184) (actual time=1,131.760..1,850.790 rows=1 loops=1)

5. 0.005 1,850.766 ↑ 1.0 1 1

Nested Loop Left Join (cost=1,132,952.05..1,237,138.02 rows=1 width=692) (actual time=1,131.739..1,850.766 rows=1 loops=1)

6. 0.010 1,850.736 ↑ 1.0 1 1

Nested Loop Left Join (cost=1,132,947.62..1,237,123.80 rows=1 width=684) (actual time=1,131.710..1,850.736 rows=1 loops=1)

7. 136.216 1,850.684 ↑ 1.0 1 1

Nested Loop Left Join (cost=1,132,943.19..1,237,109.58 rows=1 width=676) (actual time=1,131.660..1,850.684 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.022 587.574 ↑ 1.0 1 1

Nested Loop Left Join (cost=1,132,634.65..1,209,623.56 rows=1 width=648) (actual time=564.547..587.574 rows=1 loops=1)

9. 0.006 587.539 ↑ 1.0 1 1

Subquery Scan on items (cost=1,132,634.38..1,209,613.59 rows=1 width=644) (actual time=564.514..587.539 rows=1 loops=1)

  • Filter: (items.reportable_type = 'TimeLog'::text)
10. 0.004 587.533 ↑ 2.0 1 1

Append (cost=1,132,634.38..1,209,613.57 rows=2 width=112) (actual time=564.510..587.533 rows=1 loops=1)

11. 0.078 587.506 ↑ 1.0 1 1

Hash Left Join (cost=1,132,634.38..1,208,529.92 rows=1 width=161) (actual time=564.509..587.506 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.152 62.737 ↑ 1.0 1 1

Merge Left Join (cost=1,122,415.41..1,198,309.50 rows=1 width=149) (actual time=39.765..62.737 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. 5.626 30.312 ↑ 2.0 1 1

Nested Loop (cost=1,121,526.22..1,197,314.25 rows=2 width=125) (actual time=7.498..30.312 rows=1 loops=1)

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

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

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

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

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

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

17. 2.625 2.883 ↓ 17.2 5,918 1

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

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

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

19. 5.733 5.804 ↑ 2.0 1 5,804

Materialize (cost=1,120,861.92..1,196,364.67 rows=2 width=116) (actual time=0.000..0.001 rows=1 loops=5,804)

20. 0.002 0.071 ↑ 2.0 1 1

Subquery Scan on items_1 (cost=1,120,861.92..1,196,364.66 rows=2 width=116) (actual time=0.063..0.071 rows=1 loops=1)

21. 0.002 0.069 ↑ 2.0 1 1

Append (cost=1,120,861.92..1,196,364.64 rows=2 width=73) (actual time=0.062..0.069 rows=1 loops=1)

22. 0.002 0.024 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1_1 (cost=1,120,861.92..1,196,347.54 rows=1 width=106) (actual time=0.024..0.024 rows=0 loops=1)

23. 0.000 0.022 ↓ 0.0 0 1

Nested Loop Left Join (cost=1,120,861.92..1,196,347.53 rows=1 width=106) (actual time=0.022..0.022 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.001 0.022 ↓ 0.0 0 1

Nested Loop Left Join (cost=1,120,861.51..1,194,399.02 rows=25 width=110) (actual time=0.022..0.022 rows=0 loops=1)

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

Hash Right Join (cost=1,120,861.09..1,194,368.58 rows=25 width=106) (actual time=0.021..0.021 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,120,815.76..1,184,358.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,120,815.76..1,131,816.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.000 0.014 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
35. 0.001 0.014 ↓ 0.0 0 1

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

36. 0.001 0.013 ↓ 0.0 0 1

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

37. 0.000 0.012 ↓ 0.0 0 1

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

38. 0.001 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.001 0.011 ↓ 0.0 0 1

Nested Loop (cost=0.58..16.63 rows=1 width=58) (actual time=0.011..0.011 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.009 0.043 ↑ 1.0 1 1

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

  • Join Filter: (epics.project_plan_id IS NULL)
49. 0.004 0.022 ↑ 1.0 1 1

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

50. 0.011 0.011 ↑ 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.010..0.011 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.006..0.007 rows=1 loops=1)

  • Index Cond: (id = time_logs.epic_id)
52. 0.012 0.012 ↑ 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.010..0.012 rows=1 loops=1)

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

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

54. 0.172 32.124 ↑ 21.5 196 1

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

55. 5.280 31.952 ↑ 21.4 197 1

Sort (cost=889.19..899.72 rows=4,210 width=56) (actual time=31.852..31.952 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.167 26.672 ↓ 1.0 4,249 1

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

57. 6.073 18.505 ↓ 1.0 4,249 1

Sort (cost=541.03..551.56 rows=4,210 width=56) (actual time=16.613..18.505 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. 5.318 12.432 ↓ 1.0 4,249 1

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

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships_1.id)
59. 1.845 1.845 ↓ 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.007..1.845 rows=4,256 loops=1)

60. 2.534 5.269 ↓ 1.0 4,002 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 276kB
61. 2.735 2.735 ↓ 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..2.735 rows=4,002 loops=1)

62. 33.231 524.691 ↓ 8.3 44,370 1

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

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

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

64. 160.960 457.262 ↓ 8.3 44,370 1

HashAggregate (cost=10,018.38..10,098.62 rows=5,349 width=48) (actual time=373.796..457.262 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. 77.186 296.302 ↑ 1.1 44,523 1

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

66. 79.078 219.116 ↑ 1.1 44,523 1

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

  • Sort Key: plan_roles.plan_row_uuid, plan_roles.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 7146kB
67. 61.336 140.038 ↑ 1.1 44,523 1

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

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

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

69. 29.604 59.185 ↓ 1.0 48,052 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3177kB
70. 29.581 29.581 ↓ 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..29.581 rows=48,052 loops=1)

71. 0.001 0.023 ↓ 0.0 0 1

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

72. 0.002 0.022 ↓ 0.0 0 1

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

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

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

74. 0.020 0.020 ↓ 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.020..0.020 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.013 0.013 ↓ 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.013..0.013 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. 172.167 1,126.894 ↑ 1.1 261,902 1

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

94. 0.208 19.292 ↓ 58.2 291 1

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

95. 0.439 19.084 ↓ 58.2 291 1

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

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

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

97. 0.009 3.126 ↓ 3.0 3 1

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

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

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

99. 0.098 1.382 ↑ 37.7 3 1

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

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

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

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

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

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

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

103. 0.007 1.676 ↑ 1.3 3 1

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

104. 1.648 1.648 ↑ 1.3 3 1

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

  • Filter: ((rates_type)::text = 'brand'::text)
  • Rows Removed by Filter: 5799
105. 0.021 0.021 ↑ 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.007..0.007 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.979 14.979 ↓ 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.127..4.993 rows=97 loops=3)

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

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

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

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

110. 68.718 315.134 ↓ 2.5 90,652 1

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

111. 154.286 246.416 ↓ 2.5 90,652 1

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

  • Hash Cond: (rates_1.rate_card_id = rate_cards_with_dates.id)
112. 74.259 74.259 ↑ 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.006..74.259 rows=174,214 loops=1)

113. 0.401 17.871 ↑ 1.1 651 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
114. 0.667 17.470 ↑ 1.1 651 1

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

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

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

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

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

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

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

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

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

119. 0.321 2.447 ↓ 1.0 617 1

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

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

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

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

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

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

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

123. 0.226 0.459 ↓ 1.1 441 1

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

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

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

125. 0.309 3.599 ↓ 1.0 489 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
126. 0.362 3.290 ↓ 1.0 489 1

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

127. 0.806 2.928 ↓ 1.0 489 1

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

128. 0.617 2.122 ↓ 1.0 489 1

Sort (cost=131.18..132.38 rows=481 width=29) (actual time=1.916..2.122 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.505 1.505 ↓ 1.0 489 1

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

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

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

131. 146.589 253.920 ↑ 2.3 94,593 1

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

  • Hash Cond: (rates_2.rate_card_id = rate_cards_2.id)
132. 69.725 69.725 ↑ 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..69.725 rows=174,214 loops=1)

133. 2.284 37.606 ↑ 1.0 4,004 1

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

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

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

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

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

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

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

137. 1.909 5.531 ↓ 1.0 3,620 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 160kB
138. 3.622 3.622 ↓ 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.016..3.622 rows=3,621 loops=1)

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

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

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

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

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

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

142. 8.689 10.730 ↓ 1.0 1,821 1

Sort (cost=207.14..211.64 rows=1,801 width=29) (actual time=10.025..10.730 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.041 2.041 ↓ 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.011..2.041 rows=1,821 loops=1)

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

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

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

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

146. 54.672 300.538 ↓ 2.3 76,366 1

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

147. 145.998 245.866 ↓ 2.3 76,366 1

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

  • Hash Cond: (rates_3.rate_card_id = rate_cards_3.id)
148. 77.908 77.908 ↑ 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.006..77.908 rows=174,214 loops=1)

149. 0.962 21.960 ↓ 2.5 1,591 1

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

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

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

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

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

152. 0.834 14.575 ↓ 2.5 1,591 1

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

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

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

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

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

155. 2.713 4.981 ↓ 1.0 2,421 1

Sort (cost=243.55..249.51 rows=2,385 width=29) (actual time=4.063..4.981 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.268 2.268 ↓ 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.009..2.268 rows=2,421 loops=1)

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

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

  • Buckets: 2048 Batches: 1 Memory Usage: 77kB
158. 2.624 2.624 ↓ 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.020..2.624 rows=1,561 loops=1)

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

Bitmap Heap Scan on exchange_rates_by_months (cost=4.43..14.19 rows=3 width=24) (actual time=0.041..0.042 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.029 0.029 ↑ 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.029..0.029 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.013 0.025 ↑ 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.024..0.025 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.012 0.012 ↑ 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.012..0.012 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.006 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.011 0.011 ↑ 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.011..0.011 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.027 0.027 ↑ 1.0 1 1

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

  • Index Cond: (id = items.account_id)
  • Filter: ((suspended_at)::date >= ('now'::cstring)::date)
Planning time : 16.086 ms
Execution time : 1,852.195 ms