explain.depesz.com

PostgreSQL's explain analyze made readable

Result: awDA

Settings
# exclusive inclusive rows x rows loops node
1. 111.576 46,139.589 ↓ 7.7 257,364 1

Subquery Scan on report_items_live (cost=2,863,795.18..2,864,963.79 rows=33,389 width=140) (actual time=45,739.686..46,139.589 rows=257,364 loops=1)

2. 689.843 46,028.013 ↓ 7.7 257,364 1

HashAggregate (cost=2,863,795.18..2,864,629.90 rows=33,389 width=184) (actual time=45,739.684..46,028.013 rows=257,364 loops=1)

  • Group Key: projects.account_id, projects.id, ""*SELECT* 1_1"".project_plan_id, ""*SELECT* 1_1"".epic_id, (COALESCE(pa.activity_id, ""*SELECT* 1_1"".activity_id, sa.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(sa.capacity, '0'::numeric))::double precision - COALESCE(((""*SELECT* 1_1"".vacation_hours)::double precision), (sa.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(sa.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, sa.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, sa.updated_at)), ""*SELECT* 1"".updated_at, ""*SELECT* 1"".rate_card_updated_at)
3. 159.680 45,338.170 ↓ 7.7 257,364 1

Nested Loop (cost=2,856,017.52..2,861,958.78 rows=33,389 width=184) (actual time=23,444.259..45,338.170 rows=257,364 loops=1)

4. 0.020 0.020 ↑ 1.0 1 1

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

  • Index Cond: (id = 2)
  • Filter: ((suspended_at)::date >= ('now'::cstring)::date)
5. 6,466.181 45,178.470 ↓ 7.7 257,364 1

Merge Left Join (cost=2,856,017.25..2,861,616.59 rows=33,389 width=184) (actual time=23,444.239..45,178.470 rows=257,364 loops=1)

  • Merge Cond: (("*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) AND (original_exchange_rates.account_id = projects.account_id))
  • Rows Removed by Join Filter: 18958456
6. 6,503.334 34,362.356 ↓ 7.7 257,364 1

Merge Left Join (cost=2,855,894.02..2,858,735.42 rows=33,389 width=176) (actual time=23,443.274..34,362.356 rows=257,364 loops=1)

  • Merge Cond: (("*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) AND (exchange_rates_by_months.account_id = projects.account_id))
  • Rows Removed by Join Filter: 18958456
7. 441.192 23,564.464 ↓ 7.7 257,364 1

Sort (cost=2,855,770.78..2,855,854.26 rows=33,389 width=168) (actual time=23,442.200..23,564.464 rows=257,364 loops=1)

  • Sort Key: "*SELECT* 1".currency
  • Sort Method: external sort Disk: 32624kB
8. 261.559 23,123.272 ↓ 7.7 257,364 1

Merge Left Join (cost=2,846,213.34..2,853,262.09 rows=33,389 width=168) (actual time=22,018.176..23,123.272 rows=257,364 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: 411816
9. 394.242 22,766.208 ↓ 7.7 257,364 1

Merge Left Join (cost=2,846,191.88..2,851,724.77 rows=33,389 width=164) (actual time=22,017.844..22,766.208 rows=257,364 loops=1)

  • Merge Cond: ((projects.id = ""*SELECT* 1"".project_id) AND ((COALESCE(pa.activity_id, ""*SELECT* 1_1"".activity_id, sa.activity_id)) = ""*SELECT* 1"".activity_id))
  • Join Filter: (("*SELECT* 1_1".date >= "*SELECT* 1".start_date) AND (("*SELECT* 1_1".date <= "*SELECT* 1".end_date) OR ("*SELECT* 1".end_date IS NULL)))
  • Rows Removed by Join Filter: 215353
10. 649.973 21,083.035 ↓ 7.7 257,364 1

Sort (cost=2,801,838.64..2,801,922.12 rows=33,389 width=136) (actual time=20,935.838..21,083.035 rows=257,364 loops=1)

  • Sort Key: projects.id, (COALESCE(pa.activity_id, ""*SELECT* 1_1"".activity_id, sa.activity_id))
  • Sort Method: external merge Disk: 24832kB
11. 3,343.699 20,433.062 ↓ 7.7 257,364 1

Hash Left Join (cost=2,623,409.50..2,799,329.95 rows=33,389 width=136) (actual time=11,049.439..20,433.062 rows=257,364 loops=1)

  • Hash Cond: ((projects.account_id = cost_exchange_rates.account_id) AND ((sa.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: 18613848
12. 117.313 17,088.676 ↓ 7.7 257,364 1

Append (cost=2,623,309.32..2,776,495.19 rows=33,389 width=161) (actual time=11,048.722..17,088.676 rows=257,364 loops=1)

13. 1,518.568 16,925.845 ↓ 7.7 255,537 1

Hash Left Join (cost=2,623,309.32..2,762,728.59 rows=33,388 width=161) (actual time=11,048.722..16,925.845 rows=255,537 loops=1)

  • Hash 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: 5385454
14. 215.723 15,114.958 ↓ 7.7 255,537 1

Hash Left Join (cost=2,614,272.82..2,709,920.00 rows=33,388 width=149) (actual time=10,756.355..15,114.958 rows=255,537 loops=1)

  • Hash Cond: ((projects.account_id = sa.account_id) AND ("*SELECT* 1_1".user_id = sa.user_id))
  • Join Filter: (("*SELECT* 1_1".date >= sa.start_date) AND ("*SELECT* 1_1".date <= sa.end_date))
  • Rows Removed by Join Filter: 35044
  • Filter: ((((""*SELECT* 1_1"".vacation_hours)::double precision) IS NULL) OR (((""*SELECT* 1_1"".vacation_hours)::double precision) < (COALESCE(sa.capacity, accounts_1.default_capacity))::double precision))
  • Rows Removed by Filter: 749
15. 475.579 14,878.178 ↓ 2.6 256,286 1

Hash Join (cost=2,613,326.28..2,707,773.48 rows=99,172 width=125) (actual time=10,735.277..14,878.178 rows=256,286 loops=1)

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
16. 598.073 14,400.938 ↓ 2.2 1,549,352 1

Append (cost=2,613,060.22..2,696,899.34 rows=699,372 width=41) (actual time=10,733.607..14,400.938 rows=1,549,352 loops=1)

17. 605.369 13,020.058 ↓ 80.9 860,583 1

Subquery Scan on *SELECT* 1_1 (cost=2,613,060.22..2,659,449.96 rows=10,632 width=106) (actual time=10,733.605..13,020.058 rows=860,583 loops=1)

18. 878.651 12,414.689 ↓ 80.9 860,583 1

Merge Right Join (cost=2,613,060.22..2,659,343.64 rows=10,632 width=106) (actual time=10,733.596..12,414.689 rows=860,583 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.office_id))
  • Filter: (COALESCE(non_working_intervals_by_dates.id, nw_intervals_by_dates_roles.id) IS NULL)
  • Rows Removed by Filter: 635077
19. 38.203 56.960 ↑ 1.0 58,354 1

Sort (cost=5,948.18..6,094.15 rows=58,387 width=16) (actual time=42.847..56.960 rows=58,354 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: 4273kB
20. 18.757 18.757 ↑ 1.0 58,387 1

Seq Scan on non_working_intervals_by_dates nw_intervals_by_dates_roles (cost=0.00..1,325.87 rows=58,387 width=16) (actual time=0.003..18.757 rows=58,387 loops=1)

21. 483.500 11,479.078 ↓ 1.2 1,495,660 1

Materialize (cost=2,607,112.04..2,613,401.16 rows=1,257,825 width=110) (actual time=10,690.727..11,479.078 rows=1,495,660 loops=1)

22. 3,758.786 10,995.578 ↑ 1.4 869,816 1

Sort (cost=2,607,112.04..2,610,256.60 rows=1,257,825 width=110) (actual time=10,690.724..10,995.578 rows=869,816 loops=1)

  • Sort Key: projects_1.account_id, generate_series.generate_series, clients.office_id
  • Sort Method: external sort Disk: 69120kB
23. 452.636 7,236.792 ↑ 1.4 869,816 1

Merge Left Join (cost=2,376,426.74..2,406,591.64 rows=1,257,825 width=110) (actual time=5,282.155..7,236.792 rows=869,816 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))
24. 491.914 6,710.067 ↑ 1.4 869,800 1

Merge Left Join (cost=2,370,478.55..2,390,187.49 rows=1,257,825 width=106) (actual time=5,223.279..6,710.067 rows=869,800 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))
25. 4,614.228 6,009.037 ↑ 1.4 869,800 1

Sort (cost=1,139,630.07..1,142,774.63 rows=1,257,825 width=70) (actual time=5,039.223..6,009.037 rows=869,800 loops=1)

  • Sort Key: projects_1.account_id, generate_series.generate_series, plan_rows.user_id
  • Sort Method: external merge Disk: 68320kB
26. 449.522 1,394.809 ↑ 1.4 869,800 1

Nested Loop (cost=2,214.48..960,603.18 rows=1,257,825 width=70) (actual time=43.748..1,394.809 rows=869,800 loops=1)

27. 38.460 159.062 ↓ 1.0 52,415 1

Hash Left Join (cost=2,214.47..4,656.17 rows=50,313 width=70) (actual time=43.707..159.062 rows=52,415 loops=1)

  • Hash Cond: (plan_items.project_plan_id = project_plans.id)
28. 51.333 110.065 ↓ 1.0 52,415 1

Hash Join (cost=1,557.85..3,570.97 rows=50,313 width=58) (actual time=33.152..110.065 rows=52,415 loops=1)

  • Hash Cond: (plan_items.plan_row_uuid = plan_rows.uuid)
29. 25.687 25.687 ↑ 1.0 52,418 1

Seq Scan on plan_items (cost=0.00..1,313.36 rows=52,436 width=46) (actual time=0.011..25.687 rows=52,418 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 255
30. 16.235 33.045 ↓ 1.0 46,649 1

Hash (cost=998.42..998.42 rows=44,754 width=28) (actual time=33.045..33.045 rows=46,649 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3036kB
31. 16.810 16.810 ↓ 1.0 46,649 1

Seq Scan on plan_rows (cost=0.00..998.42 rows=44,754 width=28) (actual time=0.006..16.810 rows=46,649 loops=1)

  • Filter: ((user_id IS NOT NULL) OR (activity_id IS NOT NULL))
  • Rows Removed by Filter: 4
32. 0.976 10.537 ↓ 1.0 2,818 1

Hash (cost=621.41..621.41 rows=2,817 width=16) (actual time=10.537..10.537 rows=2,818 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 165kB
33. 1.538 9.561 ↓ 1.0 2,818 1

Hash Left Join (cost=380.86..621.41 rows=2,817 width=16) (actual time=4.898..9.561 rows=2,818 loops=1)

  • Hash Cond: (projects_1.client_id = clients.id)
34. 1.680 6.900 ↓ 1.0 2,818 1

Hash Left Join (cost=318.89..520.70 rows=2,817 width=16) (actual time=3.763..6.900 rows=2,818 loops=1)

  • Hash Cond: (project_plans.project_id = projects_1.id)
35. 1.478 1.478 ↓ 1.0 2,818 1

Seq Scan on project_plans (cost=0.00..163.08 rows=2,817 width=8) (actual time=0.004..1.478 rows=2,818 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3091
36. 1.618 3.742 ↓ 1.0 5,688 1

Hash (cost=247.84..247.84 rows=5,684 width=12) (actual time=3.742..3.742 rows=5,688 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 309kB
37. 2.124 2.124 ↓ 1.0 5,688 1

Seq Scan on projects projects_1 (cost=0.00..247.84 rows=5,684 width=12) (actual time=0.003..2.124 rows=5,688 loops=1)

38. 0.512 1.123 ↓ 1.0 1,822 1

Hash (cost=39.21..39.21 rows=1,821 width=8) (actual time=1.123..1.123 rows=1,822 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
39. 0.611 0.611 ↓ 1.0 1,822 1

Seq Scan on clients (cost=0.00..39.21 rows=1,821 width=8) (actual time=0.006..0.611 rows=1,822 loops=1)

40. 786.225 786.225 ↑ 1.5 17 52,415

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.004..0.015 rows=17 loops=52,415)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 6
41. 14.009 209.116 ↑ 16.3 34,667 1

Materialize (cost=1,230,848.48..1,233,668.48 rows=564,000 width=44) (actual time=184.047..209.116 rows=34,667 loops=1)

42. 45.159 195.107 ↑ 18.9 29,858 1

Sort (cost=1,230,848.48..1,232,258.48 rows=564,000 width=44) (actual time=184.040..195.107 rows=29,858 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: 2728kB
43. 12.058 149.948 ↑ 18.9 29,858 1

Subquery Scan on summary_vacations_by_dates (cost=1,091,865.24..1,159,618.44 rows=564,000 width=44) (actual time=103.416..149.948 rows=29,858 loops=1)

44. 25.457 137.890 ↑ 18.9 29,858 1

GroupAggregate (cost=1,091,865.24..1,153,978.44 rows=564,000 width=21) (actual time=103.414..137.890 rows=29,858 loops=1)

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
45. 27.937 112.433 ↑ 143.1 29,988 1

Sort (cost=1,091,865.24..1,102,595.88 rows=4,292,256 width=21) (actual time=103.392..112.433 rows=29,988 loops=1)

  • Sort Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
  • Sort Method: quicksort Memory: 2738kB
46. 19.140 84.496 ↑ 143.1 29,988 1

Nested Loop (cost=135.79..530,977.32 rows=4,292,256 width=21) (actual time=2.627..84.496 rows=29,988 loops=1)

  • 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
47. 14.900 22.928 ↓ 1.0 21,214 1

Hash Join (cost=135.79..952.31 rows=21,201 width=29) (actual time=2.602..22.928 rows=21,214 loops=1)

  • Hash Cond: (vacations.staff_membership_id = staff_memberships.id)
48. 5.454 5.454 ↓ 1.0 21,214 1

Seq Scan on vacations (cost=0.00..525.01 rows=21,201 width=17) (actual time=0.008..5.454 rows=21,214 loops=1)

49. 1.232 2.574 ↓ 1.0 3,948 1

Hash (cost=86.46..86.46 rows=3,946 width=20) (actual time=2.574..2.574 rows=3,948 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 227kB
50. 1.342 1.342 ↓ 1.0 3,948 1

Seq Scan on staff_memberships (cost=0.00..86.46 rows=3,946 width=20) (actual time=0.005..1.342 rows=3,948 loops=1)

51. 42.428 42.428 ↑ 500.0 2 21,214

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=21,214)

52. 55.082 74.089 ↓ 1.0 60,382 1

Sort (cost=5,948.18..6,094.15 rows=58,387 width=16) (actual time=58.871..74.089 rows=60,382 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: 4273kB
53. 19.007 19.007 ↑ 1.0 58,387 1

Seq Scan on non_working_intervals_by_dates (cost=0.00..1,325.87 rows=58,387 width=16) (actual time=0.011..19.007 rows=58,387 loops=1)

54. 571.233 782.807 ↓ 1.0 688,769 1

Hash Join (cost=1,805.70..30,561.98 rows=688,740 width=40) (actual time=33.437..782.807 rows=688,769 loops=1)

  • Hash Cond: (time_logs.epic_id = epics.id)
55. 178.211 178.211 ↓ 1.0 688,769 1

Seq Scan on time_logs (cost=0.00..15,842.40 rows=688,740 width=28) (actual time=0.012..178.211 rows=688,769 loops=1)

56. 9.059 33.363 ↓ 1.0 26,132 1

Hash (cost=1,479.11..1,479.11 rows=26,127 width=16) (actual time=33.363..33.363 rows=26,132 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1368kB
57. 15.334 24.304 ↓ 1.0 26,132 1

Hash Left Join (cost=198.29..1,479.11 rows=26,127 width=16) (actual time=2.266..24.304 rows=26,132 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: 11263
58. 6.724 6.724 ↓ 1.0 26,132 1

Seq Scan on epics (cost=0.00..612.27 rows=26,127 width=12) (actual time=0.004..6.724 rows=26,132 loops=1)

59. 0.759 2.246 ↓ 1.0 2,818 1

Hash (cost=163.08..163.08 rows=2,817 width=8) (actual time=2.246..2.246 rows=2,818 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 143kB
60. 1.487 1.487 ↓ 1.0 2,818 1

Seq Scan on project_plans general_epic_plans (cost=0.00..163.08 rows=2,817 width=8) (actual time=0.006..1.487 rows=2,818 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3091
61. 0.251 1.661 ↓ 1.0 810 1

Hash (cost=255.99..255.99 rows=806 width=13) (actual time=1.661..1.661 rows=810 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
62. 1.077 1.410 ↓ 1.0 810 1

Nested Loop Left Join (cost=34.80..255.99 rows=806 width=13) (actual time=0.074..1.410 rows=810 loops=1)

  • Join Filter: (accounts_1.id = projects.account_id)
63. 0.275 0.333 ↓ 1.0 810 1

Bitmap Heap Scan on projects (cost=34.53..235.60 rows=806 width=8) (actual time=0.066..0.333 rows=810 loops=1)

  • Recheck Cond: (account_id = 2)
  • Heap Blocks: exact=44
64. 0.058 0.058 ↓ 1.0 822 1

Bitmap Index Scan on index_projects_on_account_id (cost=0.00..34.33 rows=806 width=0) (actual time=0.058..0.058 rows=822 loops=1)

  • Index Cond: (account_id = 2)
65. 0.000 0.000 ↑ 1.0 1 810

Materialize (cost=0.27..8.30 rows=1 width=9) (actual time=0.000..0.000 rows=1 loops=810)

66. 0.002 0.002 ↑ 1.0 1 1

Index Scan using accounts_pkey on accounts accounts_1 (cost=0.27..8.29 rows=1 width=9) (actual time=0.002..0.002 rows=1 loops=1)

  • Index Cond: (id = 2)
67. 0.087 21.057 ↓ 9.0 190 1

Hash (cost=946.22..946.22 rows=21 width=40) (actual time=21.057..21.057 rows=190 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
68. 1.081 20.970 ↓ 9.0 190 1

Subquery Scan on sa (cost=853.93..946.22 rows=21 width=40) (actual time=16.885..20.970 rows=190 loops=1)

  • Filter: (sa.account_id = 2)
  • Rows Removed by Filter: 3886
69. 2.137 19.889 ↑ 1.0 4,076 1

Unique (cost=853.93..894.95 rows=4,102 width=56) (actual time=16.878..19.889 rows=4,076 loops=1)

70. 3.731 17.752 ↓ 1.0 4,104 1

Sort (cost=853.93..864.18 rows=4,102 width=56) (actual time=16.877..17.752 rows=4,104 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: 769kB
71. 4.195 14.021 ↓ 1.0 4,104 1

WindowAgg (cost=515.47..607.76 rows=4,102 width=56) (actual time=8.760..14.021 rows=4,104 loops=1)

72. 3.303 9.826 ↓ 1.0 4,104 1

Sort (cost=515.47..525.72 rows=4,102 width=56) (actual time=8.751..9.826 rows=4,104 loops=1)

  • Sort Key: staff_membership_activity_links.staff_membership_id, staff_membership_activity_links.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 770kB
73. 2.744 6.523 ↓ 1.0 4,104 1

Hash Join (cost=135.79..269.30 rows=4,102 width=56) (actual time=2.792..6.523 rows=4,104 loops=1)

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships_1.id)
74. 1.009 1.009 ↓ 1.0 4,111 1

Seq Scan on staff_membership_activity_links (cost=0.00..77.09 rows=4,109 width=28) (actual time=0.010..1.009 rows=4,111 loops=1)

75. 1.337 2.770 ↓ 1.0 3,948 1

Hash (cost=86.46..86.46 rows=3,946 width=32) (actual time=2.770..2.770 rows=3,948 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 273kB
76. 1.433 1.433 ↓ 1.0 3,948 1

Seq Scan on staff_memberships staff_memberships_1 (cost=0.00..86.46 rows=3,946 width=32) (actual time=0.006..1.433 rows=3,948 loops=1)

77. 17.840 292.319 ↓ 8.9 43,112 1

Hash (cost=8,975.61..8,975.61 rows=4,871 width=48) (actual time=292.319..292.319 rows=43,112 loops=1)

  • Buckets: 65536 (originally 8192) Batches: 1 (originally 1) Memory Usage: 3805kB
78. 18.239 274.479 ↓ 8.9 43,112 1

Subquery Scan on pa (cost=8,853.84..8,975.61 rows=4,871 width=48) (actual time=209.097..274.479 rows=43,112 loops=1)

79. 89.428 256.240 ↓ 8.9 43,112 1

HashAggregate (cost=8,853.84..8,926.90 rows=4,871 width=48) (actual time=209.095..256.240 rows=43,112 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)
80. 40.094 166.812 ↓ 1.0 43,265 1

WindowAgg (cost=6,366.62..7,339.88 rows=43,256 width=60) (actual time=110.228..166.812 rows=43,265 loops=1)

81. 50.803 126.718 ↓ 1.0 43,265 1

Sort (cost=6,366.62..6,474.76 rows=43,256 width=60) (actual time=110.215..126.718 rows=43,265 loops=1)

  • Sort Key: plan_roles.plan_row_uuid, plan_roles.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 6970kB
82. 33.243 75.915 ↓ 1.0 43,265 1

Hash Join (cost=1,581.45..3,035.78 rows=43,256 width=60) (actual time=32.274..75.915 rows=43,265 loops=1)

  • Hash Cond: (plan_roles.plan_row_uuid = plan_rows_1.uuid)
83. 10.680 10.680 ↓ 1.0 43,267 1

Seq Scan on plan_roles (cost=0.00..859.56 rows=43,256 width=32) (actual time=0.006..10.680 rows=43,267 loops=1)

84. 16.596 31.992 ↓ 1.0 46,653 1

Hash (cost=998.42..998.42 rows=46,642 width=28) (actual time=31.992..31.992 rows=46,653 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3097kB
85. 15.396 15.396 ↓ 1.0 46,653 1

Seq Scan on plan_rows plan_rows_1 (cost=0.00..998.42 rows=46,642 width=28) (actual time=0.006..15.396 rows=46,653 loops=1)

86. 1.375 45.518 ↓ 1,827.0 1,827 1

Subquery Scan on *SELECT* 2_1 (cost=13,285.09..13,432.72 rows=1 width=64) (actual time=38.500..45.518 rows=1,827 loops=1)

87. 3.210 44.143 ↓ 1,827.0 1,827 1

Merge Left Join (cost=13,285.09..13,432.71 rows=1 width=64) (actual time=38.491..44.143 rows=1,827 loops=1)

  • Merge Cond: ((staff_memberships_2.user_id = non_working_intervals_by_dates_1.user_id) AND (generate_series_2.generate_series = non_working_intervals_by_dates_1.date))
  • Join Filter: (staff_memberships_2.account_id = non_working_intervals_by_dates_1.account_id)
  • Filter: (non_working_intervals_by_dates_1.id IS NULL)
  • Rows Removed by Filter: 43
88. 2.537 32.787 ↑ 6.8 1,870 1

Sort (cost=11,864.40..11,896.33 rows=12,775 width=64) (actual time=32.187..32.787 rows=1,870 loops=1)

  • Sort Key: staff_memberships_2.user_id, generate_series_2.generate_series
  • Sort Method: quicksort Memory: 311kB
89. 1.141 30.250 ↑ 6.8 1,870 1

Nested Loop (cost=1,056.72..10,993.07 rows=12,775 width=64) (actual time=24.325..30.250 rows=1,870 loops=1)

90. 0.232 26.862 ↑ 1.6 321 1

Nested Loop (cost=1,056.71..1,284.07 rows=511 width=64) (actual time=24.299..26.862 rows=321 loops=1)

  • Join Filter: (staff_memberships_2.id = vacations_1.staff_membership_id)
91. 0.084 26.273 ↑ 1.9 51 1

Hash Join (cost=1,056.43..1,151.55 rows=95 width=32) (actual time=24.277..26.273 rows=51 loops=1)

  • Hash Cond: (staff_membership_activity_links_1.id = staff_activities_with_dates.link_id)
92. 1.127 2.312 ↓ 1.0 191 1

Hash Join (cost=57.27..151.67 rows=190 width=36) (actual time=0.391..2.312 rows=191 loops=1)

  • Hash Cond: (staff_membership_activity_links_1.staff_membership_id = staff_memberships_2.id)
93. 0.983 0.983 ↓ 1.0 4,111 1

Seq Scan on staff_membership_activity_links staff_membership_activity_links_1 (cost=0.00..77.09 rows=4,109 width=20) (actual time=0.005..0.983 rows=4,111 loops=1)

94. 0.061 0.202 ↓ 1.0 184 1

Hash (cost=54.99..54.99 rows=183 width=16) (actual time=0.202..0.202 rows=184 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
95. 0.112 0.141 ↓ 1.0 184 1

Bitmap Heap Scan on staff_memberships staff_memberships_2 (cost=5.70..54.99 rows=183 width=16) (actual time=0.038..0.141 rows=184 loops=1)

  • Recheck Cond: (account_id = 2)
  • Heap Blocks: exact=22
96. 0.029 0.029 ↓ 1.0 187 1

Bitmap Index Scan on index_staff_memberships_on_account_id (cost=0.00..5.65 rows=183 width=0) (actual time=0.029..0.029 rows=187 loops=1)

  • Index Cond: (account_id = 2)
97. 0.474 23.877 ↓ 70.7 1,625 1

Hash (cost=998.87..998.87 rows=23 width=4) (actual time=23.877..23.877 rows=1,625 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 74kB
98. 1.156 23.403 ↓ 70.7 1,625 1

HashAggregate (cost=998.64..998.87 rows=23 width=4) (actual time=22.955..23.403 rows=1,625 loops=1)

  • Group Key: staff_activities_with_dates.link_id
99. 3.632 22.247 ↓ 3.6 1,625 1

Subquery Scan on staff_activities_with_dates (cost=853.93..997.50 rows=456 width=4) (actual time=15.686..22.247 rows=1,625 loops=1)

  • Filter: ((('now'::cstring)::date >= staff_activities_with_dates.start_date) AND (('now'::cstring)::date <= staff_activities_with_dates.end_date))
  • Rows Removed by Filter: 2451
100. 1.994 18.615 ↑ 1.0 4,076 1

Unique (cost=853.93..894.95 rows=4,102 width=28) (actual time=15.669..18.615 rows=4,076 loops=1)

101. 3.702 16.621 ↓ 1.0 4,104 1

Sort (cost=853.93..864.18 rows=4,102 width=28) (actual time=15.667..16.621 rows=4,104 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: 513kB
102. 3.946 12.919 ↓ 1.0 4,104 1

WindowAgg (cost=515.47..607.76 rows=4,102 width=28) (actual time=7.956..12.919 rows=4,104 loops=1)

103. 2.905 8.973 ↓ 1.0 4,104 1

Sort (cost=515.47..525.72 rows=4,102 width=28) (actual time=7.948..8.973 rows=4,104 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: 513kB
104. 2.514 6.068 ↓ 1.0 4,104 1

Hash Join (cost=135.79..269.30 rows=4,102 width=28) (actual time=2.561..6.068 rows=4,104 loops=1)

  • Hash Cond: (staff_membership_activity_links_2.staff_membership_id = staff_memberships_3.id)
105. 1.006 1.006 ↓ 1.0 4,111 1

Seq Scan on staff_membership_activity_links staff_membership_activity_links_2 (cost=0.00..77.09 rows=4,109 width=12) (actual time=0.003..1.006 rows=4,111 loops=1)

106. 1.186 2.548 ↓ 1.0 3,948 1

Hash (cost=86.46..86.46 rows=3,946 width=20) (actual time=2.548..2.548 rows=3,948 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 227kB
107. 1.362 1.362 ↓ 1.0 3,948 1

Seq Scan on staff_memberships staff_memberships_3 (cost=0.00..86.46 rows=3,946 width=20) (actual time=0.005..1.362 rows=3,948 loops=1)

108. 0.357 0.357 ↑ 4.0 6 51

Index Scan using index_vacations_on_staff_membership_id on vacations vacations_1 (cost=0.29..1.09 rows=24 width=44) (actual time=0.002..0.007 rows=6 loops=51)

  • Index Cond: (staff_membership_id = staff_membership_activity_links_1.staff_membership_id)
109. 2.247 2.247 ↑ 4.2 6 321

Function Scan on generate_series generate_series_2 (cost=0.01..18.76 rows=25 width=8) (actual time=0.003..0.007 rows=6 loops=321)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 2
110. 5.442 8.146 ↑ 1.1 6,205 1

Sort (cost=1,420.69..1,437.46 rows=6,709 width=16) (actual time=6.285..8.146 rows=6,205 loops=1)

  • Sort Key: non_working_intervals_by_dates_1.user_id, non_working_intervals_by_dates_1.date
  • Sort Method: quicksort Memory: 505kB
111. 2.237 2.704 ↑ 1.0 6,667 1

Bitmap Heap Scan on non_working_intervals_by_dates non_working_intervals_by_dates_1 (cost=168.41..994.27 rows=6,709 width=16) (actual time=0.488..2.704 rows=6,667 loops=1)

  • Recheck Cond: (account_id = 2)
  • Heap Blocks: exact=103
112. 0.467 0.467 ↓ 1.1 7,105 1

Bitmap Index Scan on non_working_intervals_by_dates_idx (cost=0.00..166.73 rows=6,709 width=0) (actual time=0.467..0.467 rows=7,105 loops=1)

  • Index Cond: (account_id = 2)
113. 0.244 0.687 ↑ 1.0 712 1

Hash (cost=89.50..89.50 rows=712 width=24) (actual time=0.687..0.687 rows=712 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
114. 0.443 0.443 ↑ 1.0 712 1

Seq Scan on exchange_rates_by_months cost_exchange_rates (cost=0.00..89.50 rows=712 width=24) (actual time=0.013..0.443 rows=712 loops=1)

  • Filter: (account_id = 2)
  • Rows Removed by Filter: 1248
115. 691.335 1,288.931 ↓ 2.6 680,434 1

Sort (cost=44,353.24..45,012.86 rows=263,848 width=44) (actual time=1,064.979..1,288.931 rows=680,434 loops=1)

  • Sort Key: ""*SELECT* 1"".project_id, ""*SELECT* 1"".activity_id
  • Sort Method: external sort Disk: 15336kB
116. 94.601 597.596 ↑ 1.0 253,814 1

Append (cost=286.24..20,594.59 rows=263,848 width=44) (actual time=2.218..597.596 rows=253,814 loops=1)

117. 0.165 12.255 ↓ 77.6 388 1

Subquery Scan on *SELECT* 1 (cost=286.24..427.06 rows=5 width=44) (actual time=2.217..12.255 rows=388 loops=1)

118. 0.338 12.090 ↓ 77.6 388 1

Merge Join (cost=286.24..427.01 rows=5 width=44) (actual time=2.215..12.090 rows=388 loops=1)

  • Merge Cond: (clients_1.brand_id = brands.id)
119. 0.199 11.576 ↓ 8.1 388 1

Nested Loop (cost=280.25..1,625.62 rows=48 width=52) (actual time=2.136..11.576 rows=388 loops=1)

120. 0.006 1.529 ↓ 4.0 4 1

Merge Join (cost=279.83..280.11 rows=1 width=32) (actual time=1.516..1.529 rows=4 loops=1)

  • Merge Cond: (rate_cards.rateable_id = clients_1.brand_id)
121. 0.020 0.686 ↑ 37.7 3 1

WindowAgg (cost=106.23..108.77 rows=113 width=29) (actual time=0.672..0.686 rows=3 loops=1)

122. 0.061 0.666 ↑ 37.7 3 1

Sort (cost=106.23..106.51 rows=113 width=29) (actual time=0.664..0.666 rows=3 loops=1)

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

Seq Scan on rate_cards (cost=0.00..102.38 rows=113 width=29) (actual time=0.152..0.605 rows=113 loops=1)

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4640
124. 0.016 0.837 ↑ 1.0 4 1

Sort (cost=173.60..173.61 rows=4 width=8) (actual time=0.835..0.837 rows=4 loops=1)

  • Sort Key: clients_1.brand_id
  • Sort Method: quicksort Memory: 25kB
125. 0.006 0.821 ↑ 1.0 4 1

Nested Loop (cost=0.56..173.56 rows=4 width=8) (actual time=0.457..0.821 rows=4 loops=1)

126. 0.012 0.799 ↑ 1.0 4 1

Nested Loop (cost=0.28..172.27 rows=4 width=8) (actual time=0.448..0.799 rows=4 loops=1)

127. 0.771 0.771 ↑ 1.0 4 1

Seq Scan on pricing_models (cost=0.00..139.03 rows=4 width=4) (actual time=0.432..0.771 rows=4 loops=1)

  • Filter: ((rates_type)::text = 'brand'::text)
  • Rows Removed by Filter: 5682
128. 0.016 0.016 ↑ 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.004..0.004 rows=1 loops=4)

  • Index Cond: (id = pricing_models.project_id)
129. 0.016 0.016 ↑ 1.0 1 4

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

  • Index Cond: (id = projects_2.client_id)
130. 9.848 9.848 ↓ 2.0 97 4

Index Scan using index_rates_on_activity_id_and_rate_card_id on rates (cost=0.42..1,345.03 rows=48 width=28) (actual time=0.580..2.462 rows=97 loops=4)

  • Index Cond: (rate_card_id = rate_cards.id)
131. 0.142 0.176 ↓ 3.5 390 1

Sort (cost=5.98..6.27 rows=113 width=4) (actual time=0.075..0.176 rows=390 loops=1)

  • Sort Key: brands.id
  • Sort Method: quicksort Memory: 30kB
132. 0.034 0.034 ↑ 1.0 113 1

Seq Scan on brands (cost=0.00..2.13 rows=113 width=4) (actual time=0.006..0.034 rows=113 loops=1)

133. 35.750 158.603 ↓ 2.6 87,639 1

Subquery Scan on *SELECT* 2 (cost=676.50..5,447.39 rows=33,682 width=44) (actual time=23.150..158.603 rows=87,639 loops=1)

134. 75.767 122.853 ↓ 2.6 87,639 1

Hash Join (cost=676.50..5,110.57 rows=33,682 width=44) (actual time=23.149..122.853 rows=87,639 loops=1)

  • Hash Cond: (rates_1.rate_card_id = rate_cards_with_dates.id)
135. 38.308 38.308 ↑ 1.0 170,236 1

Seq Scan on rates rates_1 (cost=0.00..3,458.46 rows=170,346 width=28) (actual time=0.004..38.308 rows=170,236 loops=1)

136. 0.223 8.778 ↑ 1.1 632 1

Hash (cost=667.80..667.80 rows=696 width=24) (actual time=8.778..8.778 rows=632 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 46kB
137. 0.374 8.555 ↑ 1.1 632 1

Hash Join (cost=367.05..667.80 rows=696 width=24) (actual time=4.436..8.555 rows=632 loops=1)

  • Hash Cond: (clients_2.office_id = rate_cards_with_dates.rateable_id)
138. 0.320 6.339 ↓ 1.0 598 1

Hash Join (cost=222.09..513.63 rows=597 width=12) (actual time=2.572..6.339 rows=598 loops=1)

  • Hash Cond: (clients_2.office_id = offices.id)
139. 0.343 5.781 ↓ 1.0 598 1

Hash Join (cost=208.46..491.79 rows=597 width=8) (actual time=2.321..5.781 rows=598 loops=1)

  • Hash Cond: (projects_3.client_id = clients_2.id)
140. 1.776 4.307 ↓ 1.0 598 1

Hash Join (cost=146.49..421.61 rows=597 width=8) (actual time=1.176..4.307 rows=598 loops=1)

  • Hash Cond: (projects_3.id = pricing_models_1.project_id)
141. 1.374 1.374 ↓ 1.0 5,688 1

Seq Scan on projects projects_3 (cost=0.00..247.84 rows=5,684 width=8) (actual time=0.003..1.374 rows=5,688 loops=1)

142. 0.195 1.157 ↓ 1.0 598 1

Hash (cost=139.03..139.03 rows=597 width=4) (actual time=1.157..1.157 rows=598 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
143. 0.962 0.962 ↓ 1.0 598 1

Seq Scan on pricing_models pricing_models_1 (cost=0.00..139.03 rows=597 width=4) (actual time=0.018..0.962 rows=598 loops=1)

  • Filter: ((rates_type)::text = 'office'::text)
  • Rows Removed by Filter: 5088
144. 0.526 1.131 ↓ 1.0 1,822 1

Hash (cost=39.21..39.21 rows=1,821 width=8) (actual time=1.131..1.131 rows=1,822 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
145. 0.605 0.605 ↓ 1.0 1,822 1

Seq Scan on clients clients_2 (cost=0.00..39.21 rows=1,821 width=8) (actual time=0.006..0.605 rows=1,822 loops=1)

146. 0.179 0.238 ↓ 1.0 429 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
147. 0.059 0.059 ↓ 1.0 429 1

Seq Scan on offices (cost=0.00..8.28 rows=428 width=4) (actual time=0.004..0.059 rows=429 loops=1)

148. 0.161 1.842 ↓ 1.0 477 1

Hash (cost=139.01..139.01 rows=476 width=24) (actual time=1.842..1.842 rows=477 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
149. 0.210 1.681 ↓ 1.0 477 1

Subquery Scan on rate_cards_with_dates (cost=123.54..139.01 rows=476 width=24) (actual time=0.952..1.681 rows=477 loops=1)

150. 0.415 1.471 ↓ 1.0 477 1

WindowAgg (cost=123.54..134.25 rows=476 width=29) (actual time=0.950..1.471 rows=477 loops=1)

151. 0.327 1.056 ↓ 1.0 477 1

Sort (cost=123.54..124.73 rows=476 width=29) (actual time=0.945..1.056 rows=477 loops=1)

  • Sort Key: rate_cards_1.rateable_id, rate_cards_1.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 62kB
152. 0.729 0.729 ↓ 1.0 477 1

Seq Scan on rate_cards rate_cards_1 (cost=0.00..102.38 rows=476 width=29) (actual time=0.143..0.729 rows=477 loops=1)

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 4276
153. 37.526 173.234 ↑ 2.2 91,492 1

Subquery Scan on *SELECT* 3 (cost=954.05..9,248.02 rows=199,189 width=44) (actual time=17.221..173.234 rows=91,492 loops=1)

154. 79.147 135.708 ↑ 2.2 91,492 1

Hash Join (cost=954.05..7,256.13 rows=199,189 width=44) (actual time=17.219..135.708 rows=91,492 loops=1)

  • Hash Cond: (rates_2.rate_card_id = rate_cards_2.id)
155. 39.399 39.399 ↑ 1.0 170,236 1

Seq Scan on rates rates_2 (cost=0.00..3,458.46 rows=170,346 width=28) (actual time=0.004..39.399 rows=170,236 loops=1)

156. 1.384 17.162 ↑ 1.1 3,915 1

Hash (cost=902.60..902.60 rows=4,116 width=24) (actual time=17.162..17.162 rows=3,915 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 294kB
157. 2.153 15.778 ↑ 1.1 3,915 1

Hash Join (cost=549.81..902.60 rows=4,116 width=24) (actual time=9.094..15.778 rows=3,915 loops=1)

  • Hash Cond: (projects_4.client_id = clients_3.id)
158. 3.170 7.241 ↓ 1.0 3,549 1

Hash Join (cost=183.35..487.96 rows=3,545 width=8) (actual time=2.702..7.241 rows=3,549 loops=1)

  • Hash Cond: (projects_4.id = pricing_models_2.project_id)
159. 1.383 1.383 ↓ 1.0 5,688 1

Seq Scan on projects projects_4 (cost=0.00..247.84 rows=5,684 width=8) (actual time=0.002..1.383 rows=5,688 loops=1)

160. 0.964 2.688 ↓ 1.0 3,549 1

Hash (cost=139.03..139.03 rows=3,546 width=4) (actual time=2.688..2.688 rows=3,549 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 157kB
161. 1.724 1.724 ↓ 1.0 3,550 1

Seq Scan on pricing_models pricing_models_2 (cost=0.00..139.03 rows=3,546 width=4) (actual time=0.012..1.724 rows=3,550 loops=1)

  • Filter: ((rates_type)::text = 'client'::text)
  • Rows Removed by Filter: 2136
162. 0.584 6.384 ↑ 1.0 1,792 1

Hash (cost=344.06..344.06 rows=1,792 width=28) (actual time=6.384..6.384 rows=1,792 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 121kB
163. 0.974 5.800 ↑ 1.0 1,792 1

Hash Join (cost=261.18..344.06 rows=1,792 width=28) (actual time=2.794..5.800 rows=1,792 loops=1)

  • Hash Cond: (rate_cards_2.rateable_id = clients_3.id)
164. 1.616 3.838 ↓ 1.0 1,793 1

WindowAgg (cost=199.21..239.53 rows=1,792 width=29) (actual time=1.798..3.838 rows=1,793 loops=1)

165. 1.203 2.222 ↓ 1.0 1,793 1

Sort (cost=199.21..203.69 rows=1,792 width=29) (actual time=1.790..2.222 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
166. 1.019 1.019 ↓ 1.0 1,793 1

Seq Scan on rate_cards rate_cards_2 (cost=0.00..102.38 rows=1,792 width=29) (actual time=0.007..1.019 rows=1,793 loops=1)

  • Filter: ((rateable_type)::text = 'Client'::text)
  • Rows Removed by Filter: 2960
167. 0.423 0.988 ↓ 1.0 1,822 1

Hash (cost=39.21..39.21 rows=1,821 width=4) (actual time=0.988..0.988 rows=1,822 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 81kB
168. 0.565 0.565 ↓ 1.0 1,822 1

Seq Scan on clients clients_3 (cost=0.00..39.21 rows=1,821 width=4) (actual time=0.004..0.565 rows=1,822 loops=1)

169. 31.025 158.903 ↓ 2.4 74,295 1

Subquery Scan on *SELECT* 4 (cost=755.42..5,472.12 rows=30,972 width=44) (actual time=12.044..158.903 rows=74,295 loops=1)

170. 75.279 127.878 ↓ 2.4 74,295 1

Hash Join (cost=755.42..5,162.40 rows=30,972 width=44) (actual time=12.042..127.878 rows=74,295 loops=1)

  • Hash Cond: (rates_3.rate_card_id = rate_cards_3.id)
171. 40.577 40.577 ↑ 1.0 170,236 1

Seq Scan on rates rates_3 (cost=0.00..3,458.46 rows=170,346 width=28) (actual time=0.003..40.577 rows=170,236 loops=1)

172. 0.575 12.022 ↓ 2.4 1,563 1

Hash (cost=747.42..747.42 rows=640 width=24) (actual time=12.022..12.022 rows=1,563 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 108kB
173. 2.060 11.447 ↓ 2.4 1,563 1

Nested Loop (cost=393.65..747.42 rows=640 width=24) (actual time=3.876..11.447 rows=1,563 loops=1)

174. 1.173 7.824 ↓ 2.4 1,563 1

Hash Join (cost=393.37..485.65 rows=640 width=24) (actual time=3.859..7.824 rows=1,563 loops=1)

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
175. 2.181 5.009 ↓ 1.0 2,370 1

WindowAgg (cost=235.16..288.46 rows=2,369 width=29) (actual time=2.208..5.009 rows=2,370 loops=1)

176. 1.667 2.828 ↓ 1.0 2,370 1

Sort (cost=235.16..241.08 rows=2,369 width=29) (actual time=2.202..2.828 rows=2,370 loops=1)

  • Sort Key: rate_cards_3.rateable_id, rate_cards_3.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 282kB
177. 1.161 1.161 ↓ 1.0 2,370 1

Seq Scan on rate_cards rate_cards_3 (cost=0.00..102.38 rows=2,369 width=29) (actual time=0.008..1.161 rows=2,370 loops=1)

  • Filter: ((rateable_type)::text = 'PricingModel'::text)
  • Rows Removed by Filter: 2383
178. 0.430 1.642 ↑ 1.0 1,534 1

Hash (cost=139.03..139.03 rows=1,535 width=8) (actual time=1.642..1.642 rows=1,534 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 76kB
179. 1.212 1.212 ↑ 1.0 1,534 1

Seq Scan on pricing_models pricing_models_3 (cost=0.00..139.03 rows=1,535 width=8) (actual time=0.012..1.212 rows=1,534 loops=1)

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 4152
180. 1.563 1.563 ↑ 1.0 1 1,563

Index Only Scan using projects_pkey on projects projects_5 (cost=0.28..0.40 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1,563)

  • Index Cond: (id = pricing_models_3.project_id)
  • Heap Fetches: 238
181. 95.350 95.505 ↓ 1,450.3 414,794 1

Sort (cost=21.46..22.17 rows=286 width=26) (actual time=0.294..95.505 rows=414,794 loops=1)

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

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

  • Filter: (((discountable_type)::text = 'User'::text) OR ((discountable_type)::text = 'Epic'::text))
  • Rows Removed by Filter: 50
183. 4,294.121 4,294.558 ↓ 26,631.7 18,961,788 1

Sort (cost=123.23..125.01 rows=712 width=24) (actual time=0.823..4,294.558 rows=18,961,788 loops=1)

  • Sort Key: exchange_rates_by_months.currency
  • Sort Method: quicksort Memory: 80kB
184. 0.437 0.437 ↑ 1.0 712 1

Seq Scan on exchange_rates_by_months (cost=0.00..89.50 rows=712 width=24) (actual time=0.017..0.437 rows=712 loops=1)

  • Filter: (account_id = 2)
  • Rows Removed by Filter: 1248
185. 4,349.565 4,349.933 ↓ 26,631.7 18,961,788 1

Sort (cost=123.23..125.01 rows=712 width=24) (actual time=0.718..4,349.933 rows=18,961,788 loops=1)

  • Sort Key: original_exchange_rates.currency
  • Sort Method: quicksort Memory: 80kB
186. 0.368 0.368 ↑ 1.0 712 1

Seq Scan on exchange_rates_by_months original_exchange_rates (cost=0.00..89.50 rows=712 width=24) (actual time=0.005..0.368 rows=712 loops=1)

  • Filter: (account_id = 2)
  • Rows Removed by Filter: 1248
Planning time : 9.623 ms
Execution time : 46,218.614 ms