explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qMy9

Settings
# exclusive inclusive rows x rows loops node
1. 509.497 34,758.346 ↓ 7.6 595,625 1

GroupAggregate (cost=3,428,684.34..3,435,945.40 rows=78,498 width=208) (actual time=34,112.839..34,758.346 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(""*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, (GREATEST(staff_memberships_1.updated_at, staff_membership_activity_links.updated_at)))), ""*SELECT* 1"".updated_at, ""*SELECT* 1"".rate_card_updated_at))
2. 2,107.346 34,248.849 ↓ 7.6 595,625 1

Sort (cost=3,428,684.34..3,428,880.58 rows=78,498 width=204) (actual time=34,112.823..34,248.849 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(""*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, (GREATEST(staff_memberships_1.updated_at, staff_membership_activity_links.updated_at)))), ""*SELECT* 1"".updated_at, ""*SELECT* 1"".rate_card_updated_at))
  • Sort Method: external merge Disk: 88656kB
3. 4,492.237 32,141.503 ↓ 7.6 595,625 1

Merge Left Join (cost=3,413,229.91..3,414,518.30 rows=78,498 width=204) (actual time=19,399.393..32,141.503 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: 28568044
4. 4,464.493 25,912.654 ↓ 7.6 595,625 1

Merge Left Join (cost=3,413,136.70..3,413,780.90 rows=78,498 width=188) (actual time=19,398.706..25,912.654 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: 28568044
5. 907.751 19,712.040 ↓ 7.6 595,625 1

Sort (cost=3,413,043.49..3,413,239.73 rows=78,498 width=180) (actual time=19,398.089..19,712.040 rows=595,625 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1"".currency
  • Sort Method: external merge Disk: 75648kB
6. 171.845 18,804.289 ↓ 7.6 595,625 1

Merge Left Join (cost=3,383,257.89..3,399,682.46 rows=78,498 width=180) (actual time=18,028.708..18,804.289 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
7. 337.483 18,607.186 ↓ 7.6 595,625 1

Merge Left Join (cost=3,383,236.43..3,396,097.19 rows=78,498 width=176) (actual time=18,028.538..18,607.186 rows=595,625 loops=1)

  • Merge Cond: ((projects.id = ""*SELECT* 1"".project_id) AND ((COALESCE(""*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: 209515
8. 573.586 17,639.368 ↓ 7.6 595,625 1

Sort (cost=3,329,885.41..3,330,081.66 rows=78,498 width=144) (actual time=17,520.199..17,639.368 rows=595,625 loops=1)

  • Sort Key: projects.id, (COALESCE(""*SELECT* 1_1"".activity_id, staff_membership_activity_links.activity_id))
  • Sort Method: external merge Disk: 61048kB
9. 4,977.714 17,065.782 ↓ 7.6 595,625 1

Merge Left Join (cost=3,317,224.18..3,317,868.38 rows=78,498 width=144) (actual time=9,854.352..17,065.782 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
10. 760.890 10,133.465 ↓ 7.6 595,625 1

Sort (cost=3,317,130.97..3,317,327.22 rows=78,498 width=652) (actual time=9,853.720..10,133.465 rows=595,625 loops=1)

  • Sort Key: projects.account_id, staff_memberships_1.currency
  • Sort Method: external merge Disk: 59424kB
11. 181.322 9,372.575 ↓ 7.6 595,625 1

Hash Join (cost=3,013,634.98..3,287,942.94 rows=78,498 width=652) (actual time=7,386.749..9,372.575 rows=595,625 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
12. 113.224 9,191.154 ↓ 4.6 1,091,769 1

Append (cost=3,013,616.57..3,284,943.76 rows=235,493 width=652) (actual time=7,386.630..9,191.154 rows=1,091,769 loops=1)

13. 381.708 8,942.969 ↓ 4.6 1,075,067 1

Hash Left Join (cost=3,013,616.57..3,031,071.31 rows=235,492 width=111) (actual time=7,386.630..8,942.969 rows=1,075,067 loops=1)

  • Hash Cond: (projects.client_id = clients.id)
14. 293.876 8,560.687 ↓ 4.6 1,075,067 1

Hash Left Join (cost=3,013,554.57..3,022,147.49 rows=235,492 width=153) (actual time=7,386.047..8,560.687 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
15. 493.872 8,266.669 ↓ 1.5 1,080,426 1

Merge Left Join (cost=3,013,535.88..3,020,259.68 rows=699,480 width=148) (actual time=7,385.898..8,266.669 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
16. 1,320.650 7,685.507 ↓ 1.5 1,080,426 1

Sort (cost=3,012,714.99..3,014,463.69 rows=699,480 width=124) (actual time=7,376.069..7,685.507 rows=1,080,426 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1_1"".user_id
  • Sort Method: external merge Disk: 90888kB
17. 297.060 6,364.857 ↓ 1.5 1,080,426 1

Hash Join (cost=2,757,308.75..2,853,956.74 rows=699,480 width=124) (actual time=4,488.533..6,364.857 rows=1,080,426 loops=1)

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
18. 173.267 6,065.676 ↓ 2.2 1,549,746 1

Append (cost=2,757,083.82..2,844,899.50 rows=699,480 width=120) (actual time=4,486.404..6,065.676 rows=1,549,746 loops=1)

19. 253.245 5,548.341 ↓ 80.2 860,997 1

Subquery Scan on *SELECT* 1_1 (cost=2,757,083.82..2,803,952.12 rows=10,731 width=120) (actual time=4,486.403..5,548.341 rows=860,997 loops=1)

20. 415.724 5,295.096 ↓ 80.2 860,997 1

Merge Right Join (cost=2,757,083.82..2,803,764.32 rows=10,731 width=138) (actual time=4,486.399..5,295.096 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
21. 33.474 42.819 ↑ 1.0 58,766 1

Sort (cost=5,918.90..6,065.90 rows=58,799 width=16) (actual time=36.046..42.819 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: external merge Disk: 1504kB
22. 9.345 9.345 ↑ 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.017..9.345 rows=58,799 loops=1)

23. 150.881 4,836.553 ↓ 1.2 1,501,593 1

Materialize (cost=2,751,164.92..2,757,448.79 rows=1,256,775 width=110) (actual time=4,450.344..4,836.553 rows=1,501,593 loops=1)

24. 1,116.253 4,685.672 ↑ 1.4 870,252 1

Sort (cost=2,751,164.92..2,754,306.86 rows=1,256,775 width=110) (actual time=4,450.341..4,685.672 rows=870,252 loops=1)

  • Sort Key: projects_1.account_id, generate_series.generate_series, clients_1.office_id
  • Sort Method: external merge Disk: 69272kB
25. 171.678 3,569.419 ↑ 1.4 870,252 1

Merge Left Join (cost=2,447,606.68..2,477,790.47 rows=1,256,775 width=110) (actual time=2,705.205..3,569.419 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))
26. 219.109 3,341.716 ↑ 1.4 870,234 1

Merge Left Join (cost=2,441,687.78..2,461,409.89 rows=1,256,775 width=106) (actual time=2,655.490..3,341.716 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))
27. 2,288.011 3,012.023 ↑ 1.4 870,234 1

Sort (cost=1,190,017.64..1,193,159.58 rows=1,256,775 width=70) (actual time=2,551.819..3,012.023 rows=870,234 loops=1)

  • Sort Key: projects_1.account_id, generate_series.generate_series, plan_rows.user_id
  • Sort Method: external merge Disk: 68496kB
28. 155.741 724.012 ↑ 1.4 870,234 1

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

29. 12.373 96.500 ↓ 1.0 52,419 1

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

  • Hash Cond: (projects_1.client_id = clients_1.id)
30. 12.796 83.549 ↓ 1.0 52,419 1

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

  • Hash Cond: (project_plans.project_id = projects_1.id)
31. 13.091 68.446 ↓ 1.0 52,419 1

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

  • Hash Cond: (plan_items.project_plan_id = project_plans.id)
32. 24.271 53.804 ↓ 1.0 52,419 1

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

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

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

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 255
34. 9.496 17.971 ↓ 1.0 46,641 1

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

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

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

  • Filter: ((user_id IS NOT NULL) OR (activity_id IS NOT NULL))
  • Rows Removed by Filter: 4
36. 0.432 1.551 ↓ 1.0 2,819 1

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

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

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

  • Filter: active
  • Rows Removed by Filter: 3091
38. 1.029 2.307 ↑ 1.0 5,686 1

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

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

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

40. 0.272 0.578 ↑ 1.0 1,822 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
41. 0.306 0.306 ↑ 1.0 1,822 1

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

42. 471.771 471.771 ↑ 1.5 17 52,419

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.004..0.009 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
43. 4.527 110.584 ↑ 16.5 34,338 1

Materialize (cost=1,251,670.14..1,254,499.14 rows=565,800 width=44) (actual time=103.663..110.584 rows=34,338 loops=1)

44. 30.305 106.057 ↑ 19.1 29,556 1

Sort (cost=1,251,670.14..1,253,084.64 rows=565,800 width=44) (actual time=103.658..106.057 rows=29,556 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. 3.805 75.752 ↑ 19.1 29,556 1

Subquery Scan on summary_vacations_by_dates (cost=1,113,148.47..1,180,202.64 rows=565,800 width=44) (actual time=54.572..75.752 rows=29,556 loops=1)

46. 14.959 71.947 ↑ 19.1 29,556 1

GroupAggregate (cost=1,113,148.47..1,174,544.64 rows=565,800 width=52) (actual time=54.571..71.947 rows=29,556 loops=1)

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

Sort (cost=1,113,148.47..1,123,730.30 rows=4,232,734 width=21) (actual time=54.560..56.988 rows=29,683 loops=1)

  • Sort Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
  • Sort Method: quicksort Memory: 2714kB
48. 9.935 42.849 ↑ 142.6 29,683 1

Hash Join (cost=136.06..473,655.25 rows=4,232,734 width=21) (actual time=1.438..42.849 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
49. 8.570 31.508 ↑ 661.8 31,591 1

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

50. 2.031 2.031 ↑ 1.0 20,907 1

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

51. 20.907 20.907 ↑ 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.001 rows=2 loops=20,907)

52. 0.735 1.406 ↑ 1.0 3,958 1

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

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

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

54. 47.088 56.025 ↓ 1.0 60,807 1

Sort (cost=5,918.90..6,065.90 rows=58,799 width=16) (actual time=49.709..56.025 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: external sort Disk: 1728kB
55. 8.937 8.937 ↑ 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.013..8.937 rows=58,799 loops=1)

56. 256.168 344.068 ↑ 1.0 688,749 1

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

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

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

58. 4.861 15.632 ↑ 1.0 26,131 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1368kB
59. 6.926 10.771 ↑ 1.0 26,131 1

Hash Left Join (cost=198.32..1,479.32 rows=26,131 width=16) (actual time=1.455..10.771 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
60. 2.420 2.420 ↑ 1.0 26,131 1

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

61. 0.420 1.425 ↓ 1.0 2,819 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 143kB
62. 1.005 1.005 ↓ 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.006..1.005 rows=2,819 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3091
63. 1.012 2.121 ↑ 1.0 5,686 1

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

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

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

65. 75.528 87.290 ↓ 285.4 1,174,237 1

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

66. 1.382 11.762 ↑ 1.0 4,053 1

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

67. 2.733 10.380 ↑ 1.0 4,075 1

Sort (cost=820.88..831.17 rows=4,114 width=65) (actual time=9.820..10.380 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
68. 2.641 7.647 ↑ 1.0 4,114 1

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

69. 1.765 5.006 ↑ 1.0 4,114 1

Sort (cost=471.06..481.35 rows=4,114 width=56) (actual time=4.728..5.006 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
70. 1.246 3.241 ↑ 1.0 4,114 1

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

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships_1.id)
71. 0.366 0.366 ↑ 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.011..0.366 rows=4,121 loops=1)

72. 0.871 1.629 ↑ 1.0 3,958 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 273kB
73. 0.758 0.758 ↑ 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.006..0.758 rows=3,958 loops=1)

74. 0.058 0.142 ↑ 1.0 342 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
75. 0.084 0.084 ↑ 1.0 342 1

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

76. 0.271 0.574 ↑ 1.0 1,822 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
77. 0.303 0.303 ↑ 1.0 1,822 1

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

78. 5.409 134.961 ↓ 16,702.0 16,702 1

Subquery Scan on *SELECT* 2_1 (cost=243,049.32..250,340.07 rows=1 width=111) (actual time=84.341..134.961 rows=16,702 loops=1)

79. 18.299 129.552 ↓ 16,702.0 16,702 1

Merge Right Join (cost=243,049.32..250,340.06 rows=1 width=118) (actual time=84.336..129.552 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
80. 21.913 21.913 ↑ 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.013..21.913 rows=58,756 loops=1)

81. 3.105 89.340 ↑ 16.0 17,028 1

Materialize (cost=243,048.90..244,407.00 rows=271,620 width=64) (actual time=84.304..89.340 rows=17,028 loops=1)

82. 36.049 86.235 ↑ 16.0 17,028 1

Sort (cost=243,048.90..243,727.95 rows=271,620 width=64) (actual time=84.300..86.235 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: 2980kB
83. 2.413 50.186 ↑ 16.0 17,028 1

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

84. 5.627 25.317 ↓ 1.0 11,228 1

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

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

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

86. 0.511 17.388 ↑ 1.3 1,635 1

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

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

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

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

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

  • Hash Cond: (staff_membership_activity_links_1.id = staff_activities_with_dates.link_id)
89. 0.399 0.399 ↑ 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.005..0.399 rows=4,121 loops=1)

90. 0.294 13.597 ↓ 9.0 1,635 1

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

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

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

  • Group Key: staff_activities_with_dates.link_id
92. 1.414 12.608 ↓ 3.6 1,635 1

Subquery Scan on staff_activities_with_dates (cost=810.60..934.02 rows=457 width=4) (actual time=9.954..12.608 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. 0.876 11.194 ↑ 1.0 4,086 1

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

94. 2.665 10.318 ↑ 1.0 4,114 1

Sort (cost=810.60..820.88 rows=4,114 width=613) (actual time=9.946..10.318 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. 2.749 7.653 ↑ 1.0 4,114 1

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

96. 1.777 4.904 ↑ 1.0 4,114 1

Sort (cost=471.06..481.35 rows=4,114 width=28) (actual time=4.597..4.904 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.154 3.127 ↑ 1.0 4,114 1

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

  • Hash Cond: (staff_membership_activity_links_2.staff_membership_id = staff_memberships_3.id)
98. 0.384 0.384 ↑ 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.003..0.384 rows=4,121 loops=1)

99. 0.830 1.589 ↑ 1.0 3,958 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 227kB
100. 0.759 0.759 ↑ 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.004..0.759 rows=3,958 loops=1)

101. 0.763 1.556 ↑ 1.0 3,958 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 218kB
102. 0.793 0.793 ↑ 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.006..0.793 rows=3,958 loops=1)

103. 22.456 22.456 ↑ 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.001..0.002 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.002 0.099 ↑ 38.0 3 1

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

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

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

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 339
106. 1,954.392 1,954.603 ↓ 26,074.1 32,749,038 1

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

  • Sort Key: cost_exchange_rates.account_id, cost_exchange_rates.currency
  • Sort Method: quicksort Memory: 147kB
107. 0.211 0.211 ↑ 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.013..0.211 rows=1,256 loops=1)

108. 76.912 630.335 ↓ 3.4 904,455 1

Materialize (cost=53,351.02..54,666.21 rows=263,037 width=48) (actual time=495.944..630.335 rows=904,455 loops=1)

109. 318.522 553.423 ↑ 1.0 253,593 1

Sort (cost=53,351.02..54,008.62 rows=263,037 width=48) (actual time=495.940..553.423 rows=253,593 loops=1)

  • Sort Key: ""*SELECT* 1"".project_id, ""*SELECT* 1"".activity_id
  • Sort Method: external merge Disk: 14432kB
110. 25.712 234.901 ↑ 1.0 253,793 1

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

111. 0.061 10.116 ↓ 8.1 388 1

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

112. 0.083 10.055 ↓ 8.1 388 1

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

  • Merge Cond: (clients_2.brand_id = brands.id)
113. 0.087 9.938 ↓ 8.1 388 1

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

114. 0.005 1.091 ↓ 4.0 4 1

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

  • Merge Cond: (rate_cards.rateable_id = clients_2.brand_id)
115. 0.012 0.473 ↑ 37.7 3 1

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

116. 0.029 0.461 ↑ 28.2 4 1

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

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

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

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4642
118. 0.004 0.613 ↑ 1.0 4 1

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

  • Sort Key: clients_2.brand_id
  • Sort Method: quicksort Memory: 25kB
119. 0.004 0.609 ↑ 1.0 4 1

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

120. 0.006 0.597 ↑ 1.0 4 1

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

121. 0.579 0.579 ↑ 1.0 4 1

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

  • Filter: ((rates_type)::text = 'brand'::text)
  • Rows Removed by Filter: 5680
122. 0.012 0.012 ↑ 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.003..0.003 rows=1 loops=4)

  • Index Cond: (id = pricing_models.project_id)
123. 0.008 0.008 ↑ 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.002..0.002 rows=1 loops=4)

  • Index Cond: (id = projects_2.client_id)
124. 8.760 8.760 ↓ 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.520..2.190 rows=97 loops=4)

  • Index Cond: (rate_card_id = rate_cards.id)
125. 0.019 0.034 ↑ 58.0 2 1

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

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

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

127. 11.470 63.155 ↓ 2.6 87,611 1

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

128. 32.903 51.685 ↓ 2.6 87,611 1

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

  • Hash Cond: (rates_1.rate_card_id = rate_cards_1.id)
129. 14.535 14.535 ↑ 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.005..14.535 rows=170,280 loops=1)

130. 0.146 4.247 ↑ 1.1 631 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
131. 0.137 4.101 ↑ 1.1 631 1

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

  • Hash Cond: (clients_3.office_id = offices.id)
132. 0.139 2.716 ↑ 1.0 597 1

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

  • Hash Cond: (projects_3.client_id = clients_3.id)
133. 0.724 1.994 ↑ 1.0 597 1

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

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

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

135. 0.088 0.722 ↑ 1.0 597 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
136. 0.634 0.634 ↑ 1.0 597 1

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

  • Filter: ((rates_type)::text = 'office'::text)
  • Rows Removed by Filter: 5087
137. 0.272 0.583 ↑ 1.0 1,822 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
138. 0.311 0.311 ↑ 1.0 1,822 1

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

139. 0.105 1.248 ↑ 1.1 438 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
140. 0.102 1.143 ↑ 1.1 438 1

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

  • Hash Cond: (rate_cards_1.rateable_id = offices.id)
141. 0.295 0.936 ↑ 1.0 477 1

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

142. 0.142 0.641 ↑ 1.0 477 1

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

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

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

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 4278
144. 0.054 0.105 ↑ 1.0 428 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
145. 0.051 0.051 ↑ 1.0 428 1

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

146. 12.510 71.523 ↑ 2.2 91,407 1

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

147. 34.963 59.013 ↑ 2.2 91,407 1

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

  • Hash Cond: (rates_2.rate_card_id = rate_cards_2.id)
148. 15.014 15.014 ↑ 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.009..15.014 rows=170,280 loops=1)

149. 0.787 9.036 ↑ 1.1 3,909 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 294kB
150. 0.906 8.249 ↑ 1.1 3,909 1

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

  • Hash Cond: (projects_4.client_id = clients_4.id)
151. 0.913 3.752 ↑ 1.0 3,545 1

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

  • Hash Cond: (pricing_models_2.project_id = projects_4.id)
152. 0.850 0.850 ↑ 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.006..0.850 rows=3,546 loops=1)

  • Filter: ((rates_type)::text = 'client'::text)
  • Rows Removed by Filter: 2138
153. 0.858 1.989 ↑ 1.0 5,686 1

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

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

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

155. 0.396 3.591 ↑ 1.0 1,792 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 121kB
156. 0.396 3.195 ↑ 1.0 1,792 1

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

  • Hash Cond: (rate_cards_2.rateable_id = clients_4.id)
157. 1.107 2.334 ↑ 1.0 1,793 1

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

158. 0.619 1.227 ↑ 1.0 1,793 1

Sort (cost=199.39..203.88 rows=1,794 width=29) (actual time=1.111..1.227 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
159. 0.608 0.608 ↑ 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.006..0.608 rows=1,793 loops=1)

  • Filter: ((rateable_type)::text = 'Client'::text)
  • Rows Removed by Filter: 2962
160. 0.252 0.465 ↑ 1.0 1,822 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 81kB
161. 0.213 0.213 ↑ 1.0 1,822 1

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

162. 10.406 64.395 ↓ 2.4 74,387 1

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

163. 32.543 53.989 ↓ 2.4 74,387 1

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

  • Hash Cond: (rates_3.rate_card_id = rate_cards_3.id)
164. 15.049 15.049 ↑ 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.009..15.049 rows=170,280 loops=1)

165. 0.346 6.397 ↓ 2.4 1,567 1

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

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

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

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

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

168. 0.318 4.672 ↓ 2.4 1,567 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 108kB
169. 0.427 4.354 ↓ 2.4 1,567 1

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

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
170. 1.431 2.924 ↓ 1.0 2,372 1

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

171. 0.836 1.493 ↓ 1.0 2,372 1

Sort (cost=235.35..241.27 rows=2,371 width=29) (actual time=1.342..1.493 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
172. 0.657 0.657 ↓ 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.006..0.657 rows=2,372 loops=1)

  • Filter: ((rateable_type)::text = 'PricingModel'::text)
  • Rows Removed by Filter: 2383
173. 0.249 1.003 ↓ 1.0 1,537 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 77kB
174. 0.754 0.754 ↓ 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.008..0.754 rows=1,537 loops=1)

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 4147
175. 25.176 25.258 ↓ 1,450.2 414,749 1

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

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

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

  • Filter: (((discountable_type)::text = 'User'::text) OR ((discountable_type)::text = 'Epic'::text))
  • Rows Removed by Filter: 50
177. 1,735.908 1,736.121 ↓ 23,074.9 28,982,102 1

Sort (cost=93.21..96.35 rows=1,256 width=24) (actual time=0.551..1,736.121 rows=28,982,102 loops=1)

  • Sort Key: exchange_rates_by_months.account_id, exchange_rates_by_months.currency
  • Sort Method: quicksort Memory: 147kB
178. 0.213 0.213 ↑ 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.012..0.213 rows=1,256 loops=1)

179. 1,736.411 1,736.612 ↓ 23,074.9 28,982,102 1

Sort (cost=93.21..96.35 rows=1,256 width=24) (actual time=0.624..1,736.612 rows=28,982,102 loops=1)

  • Sort Key: original_exchange_rates.account_id, original_exchange_rates.currency
  • Sort Method: quicksort Memory: 147kB
180. 0.201 0.201 ↑ 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.201 rows=1,256 loops=1)

Planning time : 8.079 ms
Execution time : 34,913.791 ms