explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lgaI

Settings
# exclusive inclusive rows x rows loops node
1. 847.757 71,890.123 ↓ 7.6 595,625 1

GroupAggregate (cost=2,446,387.51..2,453,648.57 rows=78,498 width=208) (actual time=70,956.040..71,890.123 rows=595,625 loops=1)

  • Group Key: projects.account_id, projects.id, clients.id, clients.office_id, ""*SELECT* 1"".rate_card_id, ""*SELECT* 1_1"".project_plan_id, ""*SELECT* 1_1"".epic_id, (COALESCE(pa.activity_id, ""*SELECT* 1_1"".activity_id, staff_membership_activity_links.activity_id)), ""*SELECT* 1_1"".user_id, ""*SELECT* 1_1"".date, (CASE WHEN (('PlanItem'::text) = 'TimeLog'::text) THEN (NULL::double precision) WHEN (((""*SELECT* 1_1"".vacation_user_id)::double precision) IS NOT NULL) THEN GREATEST(((((COALESCE(staff_membership_activity_links.capacity, '0'::numeric))::double precision - COALESCE(((""*SELECT* 1_1"".vacation_hours)::double precision), (staff_membership_activity_links.capacity)::double precision, '0'::double precision)) * ((""*SELECT* 1_1"".utilization)::double precision)) / '100'::double precision), '0'::double precision) WHEN (""*SELECT* 1_1"".user_id IS NULL) THEN (((accounts_1.default_capacity)::double precision * ((""*SELECT* 1_1"".utilization)::double precision)) / '100'::double precision) ELSE (((COALESCE(staff_membership_activity_links.capacity, '0'::numeric))::double precision * ((""*SELECT* 1_1"".utilization)::double precision)) / '100'::double precision) END), ('PlanItem'::text), ""*SELECT* 1_1"".reportable_id, ""*SELECT* 1_1"".plan_row_uuid, ""*SELECT* 1"".currency, (COALESCE(exchange_rates_by_months.rate, '1'::double precision)), ""*SELECT* 1"".value, staff_membership_activity_links.cost, (COALESCE(cost_exchange_rates.rate, '1'::double precision)), (COALESCE(original_exchange_rates.rate, '1'::double precision)), (GREATEST((GREATEST(""*SELECT* 1_1"".updated_at, pa.updated_at, (GREATEST(staff_memberships_1.updated_at, staff_membership_activity_links.updated_at)))), ""*SELECT* 1"".updated_at, ""*SELECT* 1"".rate_card_updated_at))
2. 3,034.080 71,042.366 ↓ 7.6 595,625 1

Sort (cost=2,446,387.51..2,446,583.75 rows=78,498 width=204) (actual time=70,955.966..71,042.366 rows=595,625 loops=1)

  • Sort Key: projects.account_id, projects.id, clients.id, clients.office_id, ""*SELECT* 1"".rate_card_id, ""*SELECT* 1_1"".project_plan_id, ""*SELECT* 1_1"".epic_id, (COALESCE(pa.activity_id, ""*SELECT* 1_1"".activity_id, staff_membership_activity_links.activity_id)), ""*SELECT* 1_1"".user_id, ""*SELECT* 1_1"".date, (CASE WHEN (('PlanItem'::text) = 'TimeLog'::text) THEN (NULL::double precision) WHEN (((""*SELECT* 1_1"".vacation_user_id)::double precision) IS NOT NULL) THEN GREATEST(((((COALESCE(staff_membership_activity_links.capacity, '0'::numeric))::double precision - COALESCE(((""*SELECT* 1_1"".vacation_hours)::double precision), (staff_membership_activity_links.capacity)::double precision, '0'::double precision)) * ((""*SELECT* 1_1"".utilization)::double precision)) / '100'::double precision), '0'::double precision) WHEN (""*SELECT* 1_1"".user_id IS NULL) THEN (((accounts_1.default_capacity)::double precision * ((""*SELECT* 1_1"".utilization)::double precision)) / '100'::double precision) ELSE (((COALESCE(staff_membership_activity_links.capacity, '0'::numeric))::double precision * ((""*SELECT* 1_1"".utilization)::double precision)) / '100'::double precision) END), ('PlanItem'::text), ""*SELECT* 1_1"".reportable_id, ""*SELECT* 1_1"".plan_row_uuid, ""*SELECT* 1"".currency, (COALESCE(exchange_rates_by_months.rate, '1'::double precision)), ""*SELECT* 1"".value, staff_membership_activity_links.cost, (COALESCE(cost_exchange_rates.rate, '1'::double precision)), (COALESCE(original_exchange_rates.rate, '1'::double precision)), (GREATEST((GREATEST(""*SELECT* 1_1"".updated_at, pa.updated_at, (GREATEST(staff_memberships_1.updated_at, staff_membership_activity_links.updated_at)))), ""*SELECT* 1"".updated_at, ""*SELECT* 1"".rate_card_updated_at))
  • Sort Method: quicksort Memory: 168717kB
3. 7,927.167 68,008.286 ↓ 7.6 595,625 1

Merge Left Join (cost=2,438,717.08..2,440,005.48 rows=78,498 width=204) (actual time=45,772.328..68,008.286 rows=595,625 loops=1)

  • Merge Cond: ((projects.account_id = original_exchange_rates.account_id) AND (("*SELECT* 1".currency)::text = (original_exchange_rates.currency)::text))
  • Join Filter: ((original_exchange_rates.start_date <= "*SELECT* 1_1".date) AND (original_exchange_rates.end_date >= "*SELECT* 1_1".date))
  • Rows Removed by Join Filter: 31169112
4. 7,966.343 56,946.415 ↓ 7.6 595,625 1

Merge Left Join (cost=2,438,623.87..2,439,268.07 rows=78,498 width=188) (actual time=45,771.689..56,946.415 rows=595,625 loops=1)

  • Merge Cond: ((projects.account_id = exchange_rates_by_months.account_id) AND (("*SELECT* 1".currency)::text = (exchange_rates_by_months.currency)::text))
  • Join Filter: ((exchange_rates_by_months.start_date <= "*SELECT* 1_1".date) AND (exchange_rates_by_months.end_date >= "*SELECT* 1_1".date))
  • Rows Removed by Join Filter: 31169112
5. 815.716 45,848.597 ↓ 7.6 595,625 1

Sort (cost=2,438,530.66..2,438,726.91 rows=78,498 width=180) (actual time=45,771.045..45,848.597 rows=595,625 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1"".currency
  • Sort Method: quicksort Memory: 159966kB
6. 8,529.037 45,032.881 ↓ 7.6 595,625 1

Merge Left Join (cost=2,431,504.43..2,432,148.63 rows=78,498 width=180) (actual time=33,116.535..45,032.881 rows=595,625 loops=1)

  • Merge Cond: ((projects.account_id = cost_exchange_rates.account_id) AND ((staff_memberships_1.currency)::text = (cost_exchange_rates.currency)::text))
  • Join Filter: ((cost_exchange_rates.start_date <= "*SELECT* 1_1".date) AND (cost_exchange_rates.end_date >= "*SELECT* 1_1".date))
  • Rows Removed by Join Filter: 32276381
7. 765.392 33,184.837 ↓ 7.6 595,625 1

Sort (cost=2,431,411.22..2,431,607.47 rows=78,498 width=688) (actual time=33,115.343..33,184.837 rows=595,625 loops=1)

  • Sort Key: projects.account_id, staff_memberships_1.currency
  • Sort Method: quicksort Memory: 163035kB
8. 288.583 32,419.445 ↓ 7.6 595,625 1

Merge Left Join (cost=2,409,262.22..2,425,029.19 rows=78,498 width=688) (actual time=31,330.376..32,419.445 rows=595,625 loops=1)

  • Merge Cond: (projects.id = discounts.project_id)
  • Join Filter: (("*SELECT* 1_1".date >= discounts.start_date) AND ("*SELECT* 1_1".date <= discounts.end_date) AND (((discounts.discountable_id = "*SELECT* 1_1".user_id) AND ((discounts.discountable_type)::text = 'User'::text)) OR ((discounts.discountable_id = "*SELECT* 1_1".epic_id) AND ((discounts.discountable_type)::text = 'Epic'::text))))
  • Rows Removed by Join Filter: 411771
9. 577.991 32,089.031 ↓ 7.6 595,625 1

Merge Left Join (cost=2,409,240.76..2,421,443.93 rows=78,498 width=684) (actual time=31,330.078..32,089.031 rows=595,625 loops=1)

  • Merge Cond: ((projects.id = ""*SELECT* 1"".project_id) AND ((COALESCE(pa.activity_id, ""*SELECT* 1_1"".activity_id, staff_membership_activity_links.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: 228687
10. 636.671 30,557.532 ↓ 7.6 595,625 1

Sort (cost=2,363,981.73..2,364,177.98 rows=78,498 width=652) (actual time=30,477.912..30,557.532 rows=595,625 loops=1)

  • Sort Key: projects.id, (COALESCE(pa.activity_id, ""*SELECT* 1_1"".activity_id, staff_membership_activity_links.activity_id))
  • Sort Method: quicksort Memory: 108336kB
11. 326.255 29,920.861 ↓ 7.6 595,625 1

Hash Join (cost=2,035,340.23..2,357,599.70 rows=78,498 width=652) (actual time=15,872.024..29,920.861 rows=595,625 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
12. 211.779 29,594.496 ↓ 4.6 1,091,769 1

Append (cost=2,035,321.82..2,354,600.53 rows=235,493 width=652) (actual time=14,328.387..29,594.496 rows=1,091,769 loops=1)

13. 691.772 29,183.048 ↓ 4.6 1,075,067 1

Hash Left Join (cost=2,035,321.82..2,111,620.12 rows=235,492 width=111) (actual time=14,328.386..29,183.048 rows=1,075,067 loops=1)

  • Hash Cond: (projects.client_id = clients.id)
14. 10,046.311 28,490.278 ↓ 4.6 1,075,067 1

Merge Left Join (cost=2,035,259.82..2,102,696.30 rows=235,492 width=165) (actual time=14,327.371..28,490.278 rows=1,075,067 loops=1)

  • Merge Cond: ("*SELECT* 1_1".project_plan_id = pa.project_plan_id)
  • Join Filter: (("*SELECT* 1_1".date >= pa.start_date) AND ("*SELECT* 1_1".date <= pa.end_date) AND ((pa.plan_row_uuid = "*SELECT* 1_1".plan_row_uuid) OR ("*SELECT* 1_1".plan_row_uuid IS NULL)) AND ((pa.user_id = "*SELECT* 1_1".user_id) OR ("*SELECT* 1_1".user_id IS NULL)) AND ((pa.epic_id = "*SELECT* 1_1".epic_id) OR ((('PlanItem'::text) = 'PlanItem'::text) AND ("*SELECT* 1_1".epic_id IS NULL)) OR ((('PlanItem'::text) = 'TimeLog'::text) AND (pa.epic_id IS NULL))))
  • Rows Removed by Join Filter: 34327938
15. 1,803.014 14,587.266 ↓ 4.6 1,075,067 1

Sort (cost=2,031,118.65..2,031,707.38 rows=235,492 width=153) (actual time=14,295.965..14,587.266 rows=1,075,067 loops=1)

  • Sort Key: "*SELECT* 1_1".project_plan_id
  • Sort Method: external merge Disk: 124872kB
16. 462.303 12,784.252 ↓ 4.6 1,075,067 1

Hash Left Join (cost=2,001,513.58..2,010,106.50 rows=235,492 width=153) (actual time=11,160.445..12,784.252 rows=1,075,067 loops=1)

  • Hash Cond: (projects.account_id = accounts_1.id)
  • Filter: ((((""*SELECT* 1_1"".vacation_hours)::double precision) IS NULL) OR (((""*SELECT* 1_1"".vacation_hours)::double precision) < (COALESCE(staff_membership_activity_links.capacity, accounts_1.default_capacity))::double precision))
  • Rows Removed by Filter: 5359
17. 826.876 12,321.707 ↓ 1.5 1,080,426 1

Merge Left Join (cost=2,001,494.88..2,008,218.69 rows=699,480 width=148) (actual time=11,160.187..12,321.707 rows=1,080,426 loops=1)

  • Merge Cond: ((projects.account_id = staff_memberships_1.account_id) AND ("*SELECT* 1_1".user_id = staff_memberships_1.user_id))
  • Join Filter: ((""*SELECT* 1_1"".date >= (COALESCE(staff_membership_activity_links.start_date, staff_memberships_1.joined_at))) AND (""*SELECT* 1_1"".date <= (COALESCE(lag((staff_membership_activity_links.start_date - 1), 1) OVER (?), staff_memberships_1.archived_at, '3000-01-01'::date))))
  • Rows Removed by Join Filter: 198922
18. 1,687.792 11,348.953 ↓ 1.5 1,080,426 1

Sort (cost=2,000,674.00..2,002,422.70 rows=699,480 width=124) (actual time=11,146.454..11,348.953 rows=1,080,426 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1_1"".user_id
  • Sort Method: quicksort Memory: 181193kB
19. 521.996 9,661.161 ↓ 1.5 1,080,426 1

Hash Join (cost=1,839,262.70..1,932,768.75 rows=699,480 width=124) (actual time=6,748.648..9,661.161 rows=1,080,426 loops=1)

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
20. 291.659 9,137.011 ↓ 2.2 1,549,746 1

Append (cost=1,839,037.76..1,923,711.51 rows=699,480 width=120) (actual time=6,746.449..9,137.011 rows=1,549,746 loops=1)

21. 437.759 8,035.420 ↓ 80.2 860,997 1

Subquery Scan on *SELECT* 1_1 (cost=1,839,037.76..1,882,764.12 rows=10,731 width=120) (actual time=6,746.448..8,035.420 rows=860,997 loops=1)

22. 679.893 7,597.661 ↓ 80.2 860,997 1

Merge Right Join (cost=1,839,037.76..1,882,576.33 rows=10,731 width=138) (actual time=6,746.441..7,597.661 rows=860,997 loops=1)

  • Merge Cond: ((nw_intervals_by_dates_roles.account_id = projects_1.account_id) AND (nw_intervals_by_dates_roles.date = generate_series.generate_series) AND (nw_intervals_by_dates_roles.office_id = clients_1.office_id))
  • Filter: (COALESCE(non_working_intervals_by_dates.id, nw_intervals_by_dates_roles.id) IS NULL)
  • Rows Removed by Filter: 640596
23. 22.654 31.643 ↑ 1.0 58,766 1

Sort (cost=5,918.90..6,065.90 rows=58,799 width=16) (actual time=25.138..31.643 rows=58,766 loops=1)

  • Sort Key: nw_intervals_by_dates_roles.account_id, nw_intervals_by_dates_roles.date, nw_intervals_by_dates_roles.office_id
  • Sort Method: quicksort Memory: 4293kB
24. 8.989 8.989 ↑ 1.0 58,799 1

Seq Scan on non_working_intervals_by_dates nw_intervals_by_dates_roles (cost=0.00..1,260.99 rows=58,799 width=16) (actual time=0.006..8.989 rows=58,799 loops=1)

25. 1,552.357 6,886.125 ↓ 1.2 1,501,593 1

Sort (cost=1,833,118.86..1,836,260.80 rows=1,256,775 width=110) (actual time=6,721.289..6,886.125 rows=1,501,593 loops=1)

  • Sort Key: projects_1.account_id, generate_series.generate_series, clients_1.office_id
  • Sort Method: quicksort Memory: 146956kB
26. 295.505 5,333.768 ↑ 1.4 870,252 1

Merge Left Join (cost=1,677,030.13..1,705,799.42 rows=1,256,775 width=110) (actual time=4,515.106..5,333.768 rows=870,252 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))
27. 388.197 4,968.439 ↑ 1.4 870,234 1

Merge Left Join (cost=1,671,111.23..1,689,418.84 rows=1,256,775 width=106) (actual time=4,452.425..4,968.439 rows=870,234 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))
28. 3,283.458 4,392.549 ↑ 1.4 870,234 1

Sort (cost=1,086,921.64..1,090,063.58 rows=1,256,775 width=70) (actual time=4,271.157..4,392.549 rows=870,234 loops=1)

  • Sort Key: projects_1.account_id, generate_series.generate_series, plan_rows.user_id
  • Sort Method: quicksort Memory: 146953kB
29. 218.880 1,109.091 ↑ 1.4 870,234 1

Nested Loop (cost=2,043.15..959,602.20 rows=1,256,775 width=70) (actual time=25.669..1,109.091 rows=870,234 loops=1)

30. 35.367 156.345 ↓ 1.0 52,419 1

Hash Left Join (cost=2,043.14..4,453.19 rows=50,271 width=70) (actual time=25.643..156.345 rows=52,419 loops=1)

  • Hash Cond: (projects_1.client_id = clients_1.id)
31. 20.328 120.297 ↓ 1.0 52,419 1

Hash Left Join (cost=1,981.15..4,258.92 rows=50,271 width=70) (actual time=24.956..120.297 rows=52,419 loops=1)

  • Hash Cond: (project_plans.project_id = projects_1.id)
32. 20.482 97.470 ↓ 1.0 52,419 1

Hash Left Join (cost=1,756.21..3,901.92 rows=50,271 width=62) (actual time=22.446..97.470 rows=52,419 loops=1)

  • Hash Cond: (plan_items.project_plan_id = project_plans.id)
33. 36.952 75.287 ↓ 1.0 52,419 1

Hash Join (cost=1,557.89..3,571.54 rows=50,271 width=58) (actual time=20.719..75.287 rows=52,419 loops=1)

  • Hash Cond: (plan_items.plan_row_uuid = plan_rows.uuid)
34. 17.871 17.871 ↓ 1.0 52,422 1

Seq Scan on plan_items (cost=0.00..1,314.46 rows=52,394 width=46) (actual time=0.015..17.871 rows=52,422 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 255
35. 11.226 20.464 ↓ 1.0 46,641 1

Hash (cost=998.45..998.45 rows=44,755 width=28) (actual time=20.463..20.464 rows=46,641 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3035kB
36. 9.238 9.238 ↓ 1.0 46,641 1

Seq Scan on plan_rows (cost=0.00..998.45 rows=44,755 width=28) (actual time=0.006..9.238 rows=46,641 loops=1)

  • Filter: ((user_id IS NOT NULL) OR (activity_id IS NOT NULL))
  • Rows Removed by Filter: 4
37. 0.528 1.701 ↓ 1.0 2,819 1

Hash (cost=163.10..163.10 rows=2,818 width=8) (actual time=1.701..1.701 rows=2,819 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 143kB
38. 1.173 1.173 ↓ 1.0 2,819 1

Seq Scan on project_plans (cost=0.00..163.10 rows=2,818 width=8) (actual time=0.009..1.173 rows=2,819 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3091
39. 1.165 2.499 ↑ 1.0 5,686 1

Hash (cost=153.86..153.86 rows=5,686 width=12) (actual time=2.499..2.499 rows=5,686 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 309kB
40. 1.334 1.334 ↑ 1.0 5,686 1

Seq Scan on projects projects_1 (cost=0.00..153.86 rows=5,686 width=12) (actual time=0.029..1.334 rows=5,686 loops=1)

41. 0.315 0.681 ↑ 1.0 1,822 1

Hash (cost=39.22..39.22 rows=1,822 width=8) (actual time=0.680..0.681 rows=1,822 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
42. 0.366 0.366 ↑ 1.0 1,822 1

Seq Scan on clients clients_1 (cost=0.00..39.22 rows=1,822 width=8) (actual time=0.007..0.366 rows=1,822 loops=1)

43. 733.866 733.866 ↑ 1.5 17 52,419

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.006..0.014 rows=17 loops=52,419)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 6
44. 57.077 187.693 ↑ 16.5 34,338 1

Sort (cost=584,189.58..585,604.08 rows=565,800 width=44) (actual time=181.258..187.693 rows=34,338 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: 2705kB
45. 6.534 130.616 ↑ 19.1 29,556 1

Subquery Scan on summary_vacations_by_dates (cost=515,982.59..530,127.59 rows=565,800 width=44) (actual time=100.284..130.616 rows=29,556 loops=1)

46. 46.457 124.082 ↑ 19.1 29,556 1

HashAggregate (cost=515,982.59..524,469.59 rows=565,800 width=52) (actual time=100.283..124.082 rows=29,556 loops=1)

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
47. 17.446 77.625 ↑ 142.6 29,683 1

Hash Join (cost=136.06..473,655.25 rows=4,232,734 width=21) (actual time=2.687..77.625 rows=29,683 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
48. 12.110 57.544 ↑ 661.8 31,591 1

Nested Loop (cost=0.01..418,621.08 rows=20,907,000 width=17) (actual time=0.034..57.544 rows=31,591 loops=1)

49. 3.620 3.620 ↑ 1.0 20,907 1

Seq Scan on vacations (cost=0.00..481.07 rows=20,907 width=17) (actual time=0.018..3.620 rows=20,907 loops=1)

50. 41.814 41.814 ↑ 500.0 2 20,907

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

51. 1.397 2.635 ↑ 1.0 3,958 1

Hash (cost=86.58..86.58 rows=3,958 width=20) (actual time=2.635..2.635 rows=3,958 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 227kB
52. 1.238 1.238 ↑ 1.0 3,958 1

Seq Scan on staff_memberships (cost=0.00..86.58 rows=3,958 width=20) (actual time=0.008..1.238 rows=3,958 loops=1)

53. 54.236 69.824 ↓ 1.0 60,807 1

Sort (cost=5,918.90..6,065.90 rows=58,799 width=16) (actual time=62.674..69.824 rows=60,807 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: 4293kB
54. 15.588 15.588 ↑ 1.0 58,799 1

Seq Scan on non_working_intervals_by_dates (cost=0.00..1,260.99 rows=58,799 width=16) (actual time=0.021..15.588 rows=58,799 loops=1)

55. 454.219 809.932 ↑ 1.0 688,749 1

Hash Join (cost=1,805.96..30,562.50 rows=688,749 width=120) (actual time=27.502..809.932 rows=688,749 loops=1)

  • Hash Cond: (time_logs.epic_id = epics.id)
56. 328.466 328.466 ↑ 1.0 688,749 1

Seq Scan on time_logs (cost=0.00..15,842.49 rows=688,749 width=28) (actual time=0.021..328.466 rows=688,749 loops=1)

57. 8.421 27.247 ↑ 1.0 26,131 1

Hash (cost=1,479.32..1,479.32 rows=26,131 width=16) (actual time=27.247..27.247 rows=26,131 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1368kB
58. 12.196 18.826 ↑ 1.0 26,131 1

Hash Left Join (cost=198.32..1,479.32 rows=26,131 width=16) (actual time=2.501..18.826 rows=26,131 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: 11264
59. 4.147 4.147 ↑ 1.0 26,131 1

Seq Scan on epics (cost=0.00..612.31 rows=26,131 width=12) (actual time=0.007..4.147 rows=26,131 loops=1)

60. 0.752 2.483 ↓ 1.0 2,819 1

Hash (cost=163.10..163.10 rows=2,818 width=8) (actual time=2.483..2.483 rows=2,819 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 143kB
61. 1.731 1.731 ↓ 1.0 2,819 1

Seq Scan on project_plans general_epic_plans (cost=0.00..163.10 rows=2,818 width=8) (actual time=0.009..1.731 rows=2,819 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3091
62. 0.995 2.154 ↑ 1.0 5,686 1

Hash (cost=153.86..153.86 rows=5,686 width=12) (actual time=2.154..2.154 rows=5,686 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 309kB
63. 1.159 1.159 ↑ 1.0 5,686 1

Seq Scan on projects (cost=0.00..153.86 rows=5,686 width=12) (actual time=0.006..1.159 rows=5,686 loops=1)

64. 129.454 145.878 ↓ 285.4 1,174,237 1

Materialize (cost=820.88..913.45 rows=4,114 width=40) (actual time=13.725..145.878 rows=1,174,237 loops=1)

65. 1.916 16.424 ↑ 1.0 4,053 1

Unique (cost=820.88..862.02 rows=4,114 width=65) (actual time=13.721..16.424 rows=4,053 loops=1)

66. 4.557 14.508 ↑ 1.0 4,075 1

Sort (cost=820.88..831.17 rows=4,114 width=65) (actual time=13.719..14.508 rows=4,075 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: 771kB
67. 3.686 9.951 ↑ 1.0 4,114 1

WindowAgg (cost=471.06..573.91 rows=4,114 width=65) (actual time=5.893..9.951 rows=4,114 loops=1)

68. 2.405 6.265 ↑ 1.0 4,114 1

Sort (cost=471.06..481.35 rows=4,114 width=56) (actual time=5.884..6.265 rows=4,114 loops=1)

  • Sort Key: staff_membership_activity_links.staff_membership_id, staff_membership_activity_links.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 771kB
69. 1.540 3.860 ↑ 1.0 4,114 1

Hash Join (cost=136.06..224.09 rows=4,114 width=56) (actual time=1.928..3.860 rows=4,114 loops=1)

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships_1.id)
70. 0.446 0.446 ↑ 1.0 4,121 1

Seq Scan on staff_membership_activity_links (cost=0.00..77.21 rows=4,121 width=28) (actual time=0.014..0.446 rows=4,121 loops=1)

71. 1.008 1.874 ↑ 1.0 3,958 1

Hash (cost=86.58..86.58 rows=3,958 width=32) (actual time=1.874..1.874 rows=3,958 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 273kB
72. 0.866 0.866 ↑ 1.0 3,958 1

Seq Scan on staff_memberships staff_memberships_1 (cost=0.00..86.58 rows=3,958 width=32) (actual time=0.007..0.866 rows=3,958 loops=1)

73. 0.091 0.242 ↑ 1.0 342 1

Hash (cost=14.42..14.42 rows=342 width=9) (actual time=0.242..0.242 rows=342 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
74. 0.151 0.151 ↑ 1.0 342 1

Seq Scan on accounts accounts_1 (cost=0.00..14.42 rows=342 width=9) (actual time=0.019..0.151 rows=342 loops=1)

75. 3,845.582 3,856.701 ↓ 817.9 35,256,840 1

Sort (cost=4,141.18..4,248.94 rows=43,105 width=48) (actual time=31.390..3,856.701 rows=35,256,840 loops=1)

  • Sort Key: pa.project_plan_id
  • Sort Method: quicksort Memory: 5488kB
76. 11.119 11.119 ↑ 1.0 43,105 1

Seq Scan on planned_activities_with_dates pa (cost=0.00..823.05 rows=43,105 width=48) (actual time=0.015..11.119 rows=43,105 loops=1)

77. 0.460 0.998 ↑ 1.0 1,822 1

Hash (cost=39.22..39.22 rows=1,822 width=8) (actual time=0.998..0.998 rows=1,822 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
78. 0.538 0.538 ↑ 1.0 1,822 1

Seq Scan on clients (cost=0.00..39.22 rows=1,822 width=8) (actual time=0.011..0.538 rows=1,822 loops=1)

79. 8.437 199.669 ↓ 16,702.0 16,702 1

Subquery Scan on *SELECT* 2_1 (cost=232,836.32..239,448.02 rows=1 width=111) (actual time=129.124..199.669 rows=16,702 loops=1)

80. 27.383 191.232 ↓ 16,702.0 16,702 1

Merge Right Join (cost=232,836.32..239,448.01 rows=1 width=118) (actual time=129.117..191.232 rows=16,702 loops=1)

  • Merge Cond: ((non_working_intervals_by_dates_1.account_id = staff_memberships_2.account_id) AND (non_working_intervals_by_dates_1.user_id = staff_memberships_2.user_id) AND (non_working_intervals_by_dates_1.date = generate_series_2.generate_series))
  • Filter: (non_working_intervals_by_dates_1.id IS NULL)
  • Rows Removed by Filter: 326
81. 32.142 32.142 ↑ 1.0 58,756 1

Index Scan using non_working_intervals_by_dates_idx on non_working_intervals_by_dates non_working_intervals_by_dates_1 (cost=0.41..3,416.17 rows=58,799 width=16) (actual time=0.020..32.142 rows=58,756 loops=1)

82. 53.529 131.707 ↑ 16.0 17,028 1

Sort (cost=232,835.90..233,514.95 rows=271,620 width=64) (actual time=129.072..131.707 rows=17,028 loops=1)

  • Sort Key: staff_memberships_2.account_id, staff_memberships_2.user_id, generate_series_2.generate_series
  • Sort Method: quicksort Memory: 3163kB
83. 4.930 78.178 ↑ 16.0 17,028 1

Nested Loop (cost=1,217.40..208,320.53 rows=271,620 width=64) (actual time=27.143..78.178 rows=17,028 loops=1)

84. 8.923 39.564 ↓ 1.0 11,228 1

Hash Join (cost=1,217.39..1,885.52 rows=10,865 width=64) (actual time=27.123..39.564 rows=11,228 loops=1)

  • Hash Cond: (vacations_1.staff_membership_id = staff_memberships_2.id)
85. 3.551 3.551 ↑ 1.0 20,907 1

Seq Scan on vacations vacations_1 (cost=0.00..481.07 rows=20,907 width=44) (actual time=0.013..3.551 rows=20,907 loops=1)

86. 0.747 27.090 ↑ 1.3 1,635 1

Hash (cost=1,191.68..1,191.68 rows=2,057 width=32) (actual time=27.090..27.090 rows=1,635 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 137kB
87. 0.770 26.343 ↑ 1.3 1,635 1

Hash Join (cost=1,075.31..1,191.68 rows=2,057 width=32) (actual time=23.791..26.343 rows=1,635 loops=1)

  • Hash Cond: (staff_membership_activity_links_1.staff_membership_id = staff_memberships_2.id)
88. 1.268 22.981 ↑ 1.3 1,635 1

Hash Join (cost=939.26..1,050.20 rows=2,060 width=16) (actual time=21.131..22.981 rows=1,635 loops=1)

  • Hash Cond: (staff_membership_activity_links_1.id = staff_activities_with_dates.link_id)
89. 0.606 0.606 ↑ 1.0 4,121 1

Seq Scan on staff_membership_activity_links staff_membership_activity_links_1 (cost=0.00..77.21 rows=4,121 width=20) (actual time=0.011..0.606 rows=4,121 loops=1)

90. 0.414 21.107 ↓ 9.0 1,635 1

Hash (cost=936.98..936.98 rows=182 width=4) (actual time=21.107..21.107 rows=1,635 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 74kB
91. 0.887 20.693 ↓ 9.0 1,635 1

HashAggregate (cost=935.16..936.98 rows=182 width=4) (actual time=20.353..20.693 rows=1,635 loops=1)

  • Group Key: staff_activities_with_dates.link_id
92. 2.168 19.806 ↓ 3.6 1,635 1

Subquery Scan on staff_activities_with_dates (cost=810.60..934.02 rows=457 width=4) (actual time=15.906..19.806 rows=1,635 loops=1)

  • Filter: ((CURRENT_DATE >= staff_activities_with_dates.start_date) AND (CURRENT_DATE <= staff_activities_with_dates.end_date))
  • Rows Removed by Filter: 2451
93. 1.280 17.638 ↑ 1.0 4,086 1

Unique (cost=810.60..851.74 rows=4,114 width=613) (actual time=15.898..17.638 rows=4,086 loops=1)

94. 4.165 16.358 ↑ 1.0 4,114 1

Sort (cost=810.60..820.88 rows=4,114 width=613) (actual time=15.897..16.358 rows=4,114 loops=1)

  • 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
  • Sort Method: quicksort Memory: 514kB
95. 4.456 12.193 ↑ 1.0 4,114 1

WindowAgg (cost=471.06..563.63 rows=4,114 width=613) (actual time=7.278..12.193 rows=4,114 loops=1)

96. 2.785 7.737 ↑ 1.0 4,114 1

Sort (cost=471.06..481.35 rows=4,114 width=28) (actual time=7.266..7.737 rows=4,114 loops=1)

  • Sort Key: staff_membership_activity_links_2.staff_membership_id, staff_membership_activity_links_2.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 514kB
97. 1.907 4.952 ↑ 1.0 4,114 1

Hash Join (cost=136.06..224.09 rows=4,114 width=28) (actual time=2.478..4.952 rows=4,114 loops=1)

  • Hash Cond: (staff_membership_activity_links_2.staff_membership_id = staff_memberships_3.id)
98. 0.609 0.609 ↑ 1.0 4,121 1

Seq Scan on staff_membership_activity_links staff_membership_activity_links_2 (cost=0.00..77.21 rows=4,121 width=12) (actual time=0.004..0.609 rows=4,121 loops=1)

99. 1.260 2.436 ↑ 1.0 3,958 1

Hash (cost=86.58..86.58 rows=3,958 width=20) (actual time=2.436..2.436 rows=3,958 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 227kB
100. 1.176 1.176 ↑ 1.0 3,958 1

Seq Scan on staff_memberships staff_memberships_3 (cost=0.00..86.58 rows=3,958 width=20) (actual time=0.009..1.176 rows=3,958 loops=1)

101. 1.282 2.592 ↑ 1.0 3,958 1

Hash (cost=86.58..86.58 rows=3,958 width=16) (actual time=2.592..2.592 rows=3,958 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 218kB
102. 1.310 1.310 ↑ 1.0 3,958 1

Seq Scan on staff_memberships staff_memberships_2 (cost=0.00..86.58 rows=3,958 width=16) (actual time=0.017..1.310 rows=3,958 loops=1)

103. 33.684 33.684 ↑ 12.5 2 11,228

Function Scan on generate_series generate_series_2 (cost=0.01..18.76 rows=25 width=8) (actual time=0.002..0.003 rows=2 loops=11,228)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 0
104. 0.003 0.110 ↑ 38.0 3 1

Hash (cost=16.98..16.98 rows=114 width=4) (actual time=0.110..0.110 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
105. 0.107 0.107 ↑ 38.0 3 1

Seq Scan on accounts (cost=0.00..16.98 rows=114 width=4) (actual time=0.017..0.107 rows=3 loops=1)

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 339
106. 554.102 953.508 ↓ 3.7 973,811 1

Sort (cost=45,259.02..45,916.62 rows=263,037 width=48) (actual time=842.720..953.508 rows=973,811 loops=1)

  • Sort Key: ""*SELECT* 1"".project_id, ""*SELECT* 1"".activity_id
  • Sort Method: quicksort Memory: 25972kB
107. 44.378 399.406 ↑ 1.0 253,793 1

Append (cost=286.40..21,579.24 rows=263,037 width=48) (actual time=2.952..399.406 rows=253,793 loops=1)

108. 0.114 17.983 ↓ 8.1 388 1

Subquery Scan on *SELECT* 1 (cost=286.40..427.60 rows=48 width=48) (actual time=2.950..17.983 rows=388 loops=1)

109. 0.149 17.869 ↓ 8.1 388 1

Merge Join (cost=286.40..427.12 rows=48 width=612) (actual time=2.948..17.869 rows=388 loops=1)

  • Merge Cond: (clients_2.brand_id = brands.id)
110. 0.155 17.643 ↓ 8.1 388 1

Nested Loop (cost=280.26..1,625.03 rows=48 width=56) (actual time=2.867..17.643 rows=388 loops=1)

111. 0.008 1.892 ↓ 4.0 4 1

Merge Join (cost=279.84..280.14 rows=1 width=32) (actual time=1.878..1.892 rows=4 loops=1)

  • Merge Cond: (rate_cards.rateable_id = clients_2.brand_id)
112. 0.024 0.845 ↑ 37.7 3 1

WindowAgg (cost=106.29..109.12 rows=113 width=45) (actual time=0.831..0.845 rows=3 loops=1)

113. 0.051 0.821 ↑ 28.2 4 1

Sort (cost=106.29..106.57 rows=113 width=29) (actual time=0.820..0.821 rows=4 loops=1)

  • Sort Key: rate_cards.rateable_id, rate_cards.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 33kB
114. 0.770 0.770 ↑ 1.0 113 1

Seq Scan on rate_cards (cost=0.00..102.44 rows=113 width=29) (actual time=0.188..0.770 rows=113 loops=1)

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4642
115. 0.006 1.039 ↑ 1.0 4 1

Sort (cost=173.55..173.56 rows=4 width=8) (actual time=1.037..1.039 rows=4 loops=1)

  • Sort Key: clients_2.brand_id
  • Sort Method: quicksort Memory: 25kB
116. 0.007 1.033 ↑ 1.0 4 1

Nested Loop (cost=0.56..173.51 rows=4 width=8) (actual time=0.584..1.033 rows=4 loops=1)

117. 0.009 1.002 ↑ 1.0 4 1

Nested Loop (cost=0.28..172.25 rows=4 width=8) (actual time=0.565..1.002 rows=4 loops=1)

118. 0.973 0.973 ↑ 1.0 4 1

Seq Scan on pricing_models (cost=0.00..139.05 rows=4 width=4) (actual time=0.549..0.973 rows=4 loops=1)

  • Filter: ((rates_type)::text = 'brand'::text)
  • Rows Removed by Filter: 5680
119. 0.020 0.020 ↑ 1.0 1 4

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

  • Index Cond: (id = pricing_models.project_id)
120. 0.024 0.024 ↑ 1.0 1 4

Index Scan using clients_pkey on clients clients_2 (cost=0.28..0.31 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=4)

  • Index Cond: (id = projects_2.client_id)
121. 15.596 15.596 ↓ 2.0 97 4

Index Scan using index_rates_on_activity_id_and_rate_card_id on rates (cost=0.42..1,344.42 rows=48 width=28) (actual time=0.918..3.899 rows=97 loops=4)

  • Index Cond: (rate_card_id = rate_cards.id)
122. 0.049 0.077 ↑ 58.0 2 1

Sort (cost=6.14..6.43 rows=116 width=4) (actual time=0.077..0.077 rows=2 loops=1)

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

Seq Scan on brands (cost=0.00..2.16 rows=116 width=4) (actual time=0.009..0.028 rows=116 loops=1)

124. 19.792 108.957 ↓ 2.6 87,611 1

Subquery Scan on *SELECT* 2 (cost=570.01..5,336.48 rows=33,506 width=48) (actual time=17.578..108.957 rows=87,611 loops=1)

125. 56.537 89.165 ↓ 2.6 87,611 1

Hash Join (cost=570.01..5,001.42 rows=33,506 width=612) (actual time=17.577..89.165 rows=87,611 loops=1)

  • Hash Cond: (rates_1.rate_card_id = rate_cards_1.id)
126. 25.263 25.263 ↑ 1.0 170,280 1

Seq Scan on rates rates_1 (cost=0.00..3,457.80 rows=170,280 width=28) (actual time=0.012..25.263 rows=170,280 loops=1)

127. 0.240 7.365 ↑ 1.1 631 1

Hash (cost=561.33..561.33 rows=695 width=24) (actual time=7.365..7.365 rows=631 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
128. 0.243 7.125 ↑ 1.1 631 1

Hash Join (cost=369.72..561.33 rows=695 width=24) (actual time=4.516..7.125 rows=631 loops=1)

  • Hash Cond: (clients_3.office_id = offices.id)
129. 0.249 4.711 ↑ 1.0 597 1

Hash Join (cost=208.51..391.23 rows=597 width=8) (actual time=2.340..4.711 rows=597 loops=1)

  • Hash Cond: (projects_3.client_id = clients_3.id)
130. 1.250 3.402 ↑ 1.0 597 1

Hash Join (cost=146.51..327.67 rows=597 width=8) (actual time=1.273..3.402 rows=597 loops=1)

  • Hash Cond: (projects_3.id = pricing_models_1.project_id)
131. 0.892 0.892 ↑ 1.0 5,686 1

Seq Scan on projects projects_3 (cost=0.00..153.86 rows=5,686 width=8) (actual time=0.006..0.892 rows=5,686 loops=1)

132. 0.151 1.260 ↑ 1.0 597 1

Hash (cost=139.05..139.05 rows=597 width=4) (actual time=1.260..1.260 rows=597 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
133. 1.109 1.109 ↑ 1.0 597 1

Seq Scan on pricing_models pricing_models_1 (cost=0.00..139.05 rows=597 width=4) (actual time=0.019..1.109 rows=597 loops=1)

  • Filter: ((rates_type)::text = 'office'::text)
  • Rows Removed by Filter: 5087
134. 0.525 1.060 ↑ 1.0 1,822 1

Hash (cost=39.22..39.22 rows=1,822 width=8) (actual time=1.060..1.060 rows=1,822 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
135. 0.535 0.535 ↑ 1.0 1,822 1

Seq Scan on clients clients_3 (cost=0.00..39.22 rows=1,822 width=8) (actual time=0.009..0.535 rows=1,822 loops=1)

136. 0.179 2.171 ↑ 1.1 438 1

Hash (cost=155.25..155.25 rows=477 width=28) (actual time=2.171..2.171 rows=438 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
137. 0.177 1.992 ↑ 1.1 438 1

Hash Join (cost=137.29..155.25 rows=477 width=28) (actual time=1.256..1.992 rows=438 loops=1)

  • Hash Cond: (rate_cards_1.rateable_id = offices.id)
138. 0.521 1.629 ↑ 1.0 477 1

WindowAgg (cost=123.66..135.58 rows=477 width=45) (actual time=1.064..1.629 rows=477 loops=1)

139. 0.248 1.108 ↑ 1.0 477 1

Sort (cost=123.66..124.85 rows=477 width=29) (actual time=1.056..1.108 rows=477 loops=1)

  • Sort Key: rate_cards_1.rateable_id, rate_cards_1.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 62kB
140. 0.860 0.860 ↑ 1.0 477 1

Seq Scan on rate_cards rate_cards_1 (cost=0.00..102.44 rows=477 width=29) (actual time=0.167..0.860 rows=477 loops=1)

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 4278
141. 0.093 0.186 ↑ 1.0 428 1

Hash (cost=8.28..8.28 rows=428 width=4) (actual time=0.186..0.186 rows=428 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
142. 0.093 0.093 ↑ 1.0 428 1

Seq Scan on offices (cost=0.00..8.28 rows=428 width=4) (actual time=0.008..0.093 rows=428 loops=1)

143. 21.444 117.761 ↑ 2.2 91,407 1

Subquery Scan on *SELECT* 3 (cost=824.34..9,105.14 rows=198,580 width=48) (actual time=16.069..117.761 rows=91,407 loops=1)

144. 57.220 96.317 ↑ 2.2 91,407 1

Hash Join (cost=824.34..7,119.34 rows=198,580 width=612) (actual time=16.067..96.317 rows=91,407 loops=1)

  • Hash Cond: (rates_2.rate_card_id = rate_cards_2.id)
145. 23.137 23.137 ↑ 1.0 170,280 1

Seq Scan on rates rates_2 (cost=0.00..3,457.80 rows=170,280 width=28) (actual time=0.013..23.137 rows=170,280 loops=1)

146. 1.437 15.960 ↑ 1.1 3,909 1

Hash (cost=772.85..772.85 rows=4,119 width=24) (actual time=15.960..15.960 rows=3,909 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 294kB
147. 1.619 14.523 ↑ 1.1 3,909 1

Hash Join (cost=576.26..772.85 rows=4,119 width=24) (actual time=9.860..14.523 rows=3,909 loops=1)

  • Hash Cond: (projects_4.client_id = clients_4.id)
148. 1.615 6.568 ↑ 1.0 3,545 1

Hash Join (cost=224.94..373.31 rows=3,546 width=8) (actual time=3.515..6.568 rows=3,545 loops=1)

  • Hash Cond: (pricing_models_2.project_id = projects_4.id)
149. 1.515 1.515 ↑ 1.0 3,546 1

Seq Scan on pricing_models pricing_models_2 (cost=0.00..139.05 rows=3,547 width=4) (actual time=0.010..1.515 rows=3,546 loops=1)

  • Filter: ((rates_type)::text = 'client'::text)
  • Rows Removed by Filter: 2138
150. 1.504 3.438 ↑ 1.0 5,686 1

Hash (cost=153.86..153.86 rows=5,686 width=8) (actual time=3.438..3.438 rows=5,686 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 287kB
151. 1.934 1.934 ↑ 1.0 5,686 1

Seq Scan on projects projects_4 (cost=0.00..153.86 rows=5,686 width=8) (actual time=0.008..1.934 rows=5,686 loops=1)

152. 0.702 6.336 ↑ 1.0 1,792 1

Hash (cost=328.90..328.90 rows=1,794 width=28) (actual time=6.336..6.336 rows=1,792 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 121kB
153. 0.710 5.634 ↑ 1.0 1,792 1

Hash Join (cost=261.39..328.90 rows=1,794 width=28) (actual time=2.816..5.634 rows=1,792 loops=1)

  • Hash Cond: (rate_cards_2.rateable_id = clients_4.id)
154. 1.924 4.074 ↑ 1.0 1,793 1

WindowAgg (cost=199.39..244.24 rows=1,794 width=45) (actual time=1.959..4.074 rows=1,793 loops=1)

155. 1.077 2.150 ↑ 1.0 1,793 1

Sort (cost=199.39..203.88 rows=1,794 width=29) (actual time=1.948..2.150 rows=1,793 loops=1)

  • Sort Key: rate_cards_2.rateable_id, rate_cards_2.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 189kB
156. 1.073 1.073 ↑ 1.0 1,793 1

Seq Scan on rate_cards rate_cards_2 (cost=0.00..102.44 rows=1,794 width=29) (actual time=0.010..1.073 rows=1,793 loops=1)

  • Filter: ((rateable_type)::text = 'Client'::text)
  • Rows Removed by Filter: 2962
157. 0.467 0.850 ↑ 1.0 1,822 1

Hash (cost=39.22..39.22 rows=1,822 width=4) (actual time=0.850..0.850 rows=1,822 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 81kB
158. 0.383 0.383 ↑ 1.0 1,822 1

Seq Scan on clients clients_4 (cost=0.00..39.22 rows=1,822 width=4) (actual time=0.007..0.383 rows=1,822 loops=1)

159. 18.199 110.327 ↓ 2.4 74,387 1

Subquery Scan on *SELECT* 4 (cost=680.42..5,394.83 rows=30,903 width=48) (actual time=10.793..110.327 rows=74,387 loops=1)

160. 56.125 92.128 ↓ 2.4 74,387 1

Hash Join (cost=680.42..5,085.80 rows=30,903 width=612) (actual time=10.791..92.128 rows=74,387 loops=1)

  • Hash Cond: (rates_3.rate_card_id = rate_cards_3.id)
161. 25.245 25.245 ↑ 1.0 170,280 1

Seq Scan on rates rates_3 (cost=0.00..3,457.80 rows=170,280 width=28) (actual time=0.017..25.245 rows=170,280 loops=1)

162. 0.588 10.758 ↓ 2.4 1,567 1

Hash (cost=672.41..672.41 rows=641 width=24) (actual time=10.758..10.758 rows=1,567 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 108kB
163. 1.505 10.170 ↓ 2.4 1,567 1

Hash Join (cost=490.82..672.41 rows=641 width=24) (actual time=7.780..10.170 rows=1,567 loops=1)

  • Hash Cond: (projects_5.id = pricing_models_3.project_id)
164. 0.901 0.901 ↑ 1.0 5,686 1

Seq Scan on projects projects_5 (cost=0.00..153.86 rows=5,686 width=4) (actual time=0.006..0.901 rows=5,686 loops=1)

165. 0.493 7.764 ↓ 2.4 1,567 1

Hash (cost=482.81..482.81 rows=641 width=24) (actual time=7.764..7.764 rows=1,567 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 108kB
166. 0.672 7.271 ↓ 2.4 1,567 1

Hash Join (cost=393.60..482.81 rows=641 width=24) (actual time=4.153..7.271 rows=1,567 loops=1)

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
167. 2.228 4.882 ↓ 1.0 2,372 1

WindowAgg (cost=235.35..294.62 rows=2,371 width=45) (actual time=2.430..4.882 rows=2,372 loops=1)

168. 1.429 2.654 ↓ 1.0 2,372 1

Sort (cost=235.35..241.27 rows=2,371 width=29) (actual time=2.418..2.654 rows=2,372 loops=1)

  • Sort Key: rate_cards_3.rateable_id, rate_cards_3.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 282kB
169. 1.225 1.225 ↓ 1.0 2,372 1

Seq Scan on rate_cards rate_cards_3 (cost=0.00..102.44 rows=2,371 width=29) (actual time=0.010..1.225 rows=2,372 loops=1)

  • Filter: ((rateable_type)::text = 'PricingModel'::text)
  • Rows Removed by Filter: 2383
170. 0.417 1.717 ↓ 1.0 1,537 1

Hash (cost=139.05..139.05 rows=1,536 width=8) (actual time=1.717..1.717 rows=1,537 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 77kB
171. 1.300 1.300 ↓ 1.0 1,537 1

Seq Scan on pricing_models pricing_models_3 (cost=0.00..139.05 rows=1,536 width=8) (actual time=0.011..1.300 rows=1,537 loops=1)

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 4147
172. 41.690 41.831 ↓ 1,450.2 414,749 1

Sort (cost=21.46..22.17 rows=286 width=26) (actual time=0.273..41.831 rows=414,749 loops=1)

  • Sort Key: discounts.project_id
  • Sort Method: quicksort Memory: 51kB
173. 0.141 0.141 ↓ 1.2 336 1

Seq Scan on discounts (cost=0.00..9.79 rows=286 width=26) (actual time=0.023..0.141 rows=336 loops=1)

  • Filter: (((discountable_type)::text = 'User'::text) OR ((discountable_type)::text = 'Epic'::text))
  • Rows Removed by Filter: 50
174. 3,318.628 3,319.007 ↓ 26,074.1 32,749,038 1

Sort (cost=93.21..96.35 rows=1,256 width=24) (actual time=0.996..3,319.007 rows=32,749,038 loops=1)

  • Sort Key: cost_exchange_rates.account_id, cost_exchange_rates.currency
  • Sort Method: quicksort Memory: 147kB
175. 0.379 0.379 ↑ 1.0 1,256 1

Seq Scan on exchange_rates_by_months cost_exchange_rates (cost=0.00..28.56 rows=1,256 width=24) (actual time=0.022..0.379 rows=1,256 loops=1)

176. 3,131.259 3,131.475 ↓ 25,180.3 31,626,403 1

Sort (cost=93.21..96.35 rows=1,256 width=24) (actual time=0.565..3,131.475 rows=31,626,403 loops=1)

  • Sort Key: exchange_rates_by_months.account_id, exchange_rates_by_months.currency
  • Sort Method: quicksort Memory: 147kB
177. 0.216 0.216 ↑ 1.0 1,256 1

Seq Scan on exchange_rates_by_months (cost=0.00..28.56 rows=1,256 width=24) (actual time=0.016..0.216 rows=1,256 loops=1)

178. 3,134.489 3,134.704 ↓ 25,180.3 31,626,403 1

Sort (cost=93.21..96.35 rows=1,256 width=24) (actual time=0.560..3,134.704 rows=31,626,403 loops=1)

  • Sort Key: original_exchange_rates.account_id, original_exchange_rates.currency
  • Sort Method: quicksort Memory: 147kB
179. 0.215 0.215 ↑ 1.0 1,256 1

Seq Scan on exchange_rates_by_months original_exchange_rates (cost=0.00..28.56 rows=1,256 width=24) (actual time=0.004..0.215 rows=1,256 loops=1)

Planning time : 10.159 ms
Execution time : 72,232.850 ms