explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zRmY

Settings
# exclusive inclusive rows x rows loops node
1. 8,592.897 55,782.823 ↓ 3.9 638,535 1

Merge Left Join (cost=2,235,577.49..2,239,585.67 rows=165,528 width=140) (actual time=43,778.220..55,782.823 rows=638,535 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: 34221967
2. 814.962 43,844.419 ↓ 3.9 638,535 1

Sort (cost=2,235,332.63..2,235,746.45 rows=165,528 width=176) (actual time=43,776.634..43,844.419 rows=638,535 loops=1)

  • Sort Key: projects.account_id, staff_memberships_1.currency
  • Sort Method: quicksort Memory: 184853kB
3. 9,773.034 43,029.457 ↓ 3.9 638,535 1

Merge Left Join (cost=2,219,458.82..2,220,984.07 rows=165,528 width=176) (actual time=29,362.779..43,029.457 rows=638,535 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: 38541529
4. 785.049 29,433.611 ↓ 3.9 638,535 1

Sort (cost=2,219,213.96..2,219,627.78 rows=165,528 width=168) (actual time=29,361.478..29,433.611 rows=638,535 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1"".currency
  • Sort Method: quicksort Memory: 159598kB
5. 350.218 28,648.562 ↓ 3.9 638,535 1

Merge Left Join (cost=2,162,043.42..2,204,865.40 rows=165,528 width=168) (actual time=27,314.048..28,648.562 rows=638,535 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: 599801
6. 666.640 28,235.153 ↓ 3.9 638,460 1

Merge Join (cost=2,162,020.65..2,196,831.07 rows=165,528 width=164) (actual time=27,313.775..28,235.153 rows=638,460 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: 229908
7. 918.739 26,763.210 ↓ 3.8 821,415 1

Sort (cost=2,115,189.73..2,115,730.68 rows=216,380 width=136) (actual time=26,635.025..26,763.210 rows=821,415 loops=1)

  • Sort Key: projects.id, (COALESCE(""*SELECT* 1_1"".activity_id, staff_membership_activity_links.activity_id))
  • Sort Method: quicksort Memory: 144101kB
8. 338.244 25,844.471 ↓ 3.9 849,952 1

Hash Join (cost=1,560,286.62..2,096,015.00 rows=216,380 width=136) (actual time=22,966.970..25,844.471 rows=849,952 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
9. 206.939 25,506.018 ↓ 1.7 1,116,116 1

Append (cost=1,560,267.95..2,087,780.52 rows=649,141 width=136) (actual time=22,966.742..25,506.018 rows=1,116,116 loops=1)

10. 791.369 24,885.853 ↓ 2.7 1,087,210 1

Hash Join (cost=1,560,267.95..1,590,070.15 rows=403,983 width=108) (actual time=22,966.742..24,885.853 rows=1,087,210 loops=1)

  • Hash Cond: (projects.account_id = accounts_1.id)
  • Join Filter: ((((""*SELECT* 1_1"".vacation_user_id)::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 Join Filter: 13596
11. 843.183 24,094.340 ↑ 1.1 1,100,806 1

Merge Left Join (cost=1,560,248.98..1,571,733.55 rows=1,199,950 width=144) (actual time=22,966.585..24,094.340 rows=1,100,806 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: 258899
12. 1,442.156 23,098.553 ↑ 1.1 1,100,806 1

Sort (cost=1,559,401.84..1,562,401.72 rows=1,199,950 width=120) (actual time=22,946.816..23,098.553 rows=1,100,806 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1_1"".user_id
  • Sort Method: quicksort Memory: 198687kB
13. 429.797 21,656.397 ↑ 1.1 1,100,806 1

Hash Join (cost=1,131,915.40..1,438,239.63 rows=1,199,950 width=120) (actual time=2,753.750..21,656.397 rows=1,100,806 loops=1)

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
14. 196.185 21,224.386 ↑ 1.1 1,100,806 1

Append (cost=1,131,686.63..1,422,859.20 rows=1,199,950 width=116) (actual time=2,751.511..21,224.386 rows=1,100,806 loops=1)

15. 203.051 3,992.292 ↑ 1.2 396,845 1

Result (cost=1,131,686.63..1,226,655.23 rows=495,989 width=116) (actual time=2,751.510..3,992.292 rows=396,845 loops=1)

16. 70.261 3,789.241 ↑ 1.2 396,845 1

Append (cost=1,131,686.63..1,220,455.37 rows=495,989 width=140) (actual time=2,751.489..3,789.241 rows=396,845 loops=1)

17. 93.648 3,232.507 ↑ 1.4 338,629 1

Subquery Scan on *SELECT* 1_1 (cost=1,131,686.63..1,155,495.07 rows=457,239 width=114) (actual time=2,751.488..3,232.507 rows=338,629 loops=1)

18. 147.360 3,138.859 ↑ 1.4 338,629 1

Merge Left Join (cost=1,131,686.63..1,148,636.49 rows=457,239 width=134) (actual time=2,751.486..3,138.859 rows=338,629 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))
19. 174.344 2,801.031 ↑ 1.4 338,629 1

Merge Anti Join (cost=525,089.35..531,687.96 rows=457,239 width=66) (actual time=2,568.384..2,801.031 rows=338,629 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))
20. 1,494.856 2,555.853 ↑ 1.7 346,275 1

Sort (cost=519,071.36..520,519.29 rows=579,175 width=66) (actual time=2,504.141..2,555.853 rows=346,275 loops=1)

  • Sort Key: projects_1.account_id, generate_series.generate_series, plan_rows.user_id
  • Sort Method: quicksort Memory: 60983kB
21. 212.770 1,060.997 ↑ 1.7 346,275 1

Merge Left Join (cost=12,929.64..463,633.77 rows=579,175 width=66) (actual time=347.656..1,060.997 rows=346,275 loops=1)

  • Merge Cond: (plan_items.plan_row_uuid = pa.plan_row_uuid)
  • Join Filter: (((pa.epic_id = plan_rows.epic_id) OR (plan_rows.epic_id IS NULL)) AND (pa.project_plan_id = project_plans.id) AND (pa.user_id = plan_rows.user_id) AND ((generate_series.generate_series)::date >= pa.start_date) AND ((generate_series.generate_series)::date <= pa.end_date))
  • Rows Removed by Join Filter: 3247
22. 87.024 523.412 ↑ 1.7 346,275 1

Nested Loop (cost=3,971.09..448,257.03 rows=579,175 width=70) (actual time=64.779..523.412 rows=346,275 loops=1)

23. 21.556 136.506 ↓ 1.2 27,262 1

Merge Join (cost=3,971.09..8,084.02 rows=23,167 width=70) (actual time=64.755..136.506 rows=27,262 loops=1)

  • Merge Cond: (plan_rows.uuid = plan_items.plan_row_uuid)
24. 43.790 43.790 ↑ 1.0 42,344 1

Index Scan using plan_rows_uuid_key on plan_rows (cost=0.29..3,663.60 rows=42,428 width=28) (actual time=0.023..43.790 rows=42,344 loops=1)

  • Filter: (user_id IS NOT NULL)
  • Rows Removed by Filter: 5762
25. 27.318 71.160 ↓ 1.1 29,797 1

Sort (cost=3,970.18..4,035.85 rows=26,267 width=58) (actual time=64.706..71.160 rows=29,797 loops=1)

  • Sort Key: plan_items.plan_row_uuid
  • Sort Method: quicksort Memory: 4959kB
26. 10.305 43.842 ↓ 1.1 29,797 1

Hash Join (cost=430.80..2,042.06 rows=26,267 width=58) (actual time=3.839..43.842 rows=29,797 loops=1)

  • Hash Cond: (project_plans.project_id = projects_1.id)
27. 16.637 31.510 ↓ 1.1 29,797 1

Hash Join (cost=202.03..1,744.28 rows=26,267 width=54) (actual time=1.697..31.510 rows=29,797 loops=1)

  • Hash Cond: (plan_items.project_plan_id = project_plans.id)
28. 13.217 13.217 ↓ 1.0 55,664 1

Seq Scan on plan_items (cost=0.00..1,396.10 rows=55,639 width=46) (actual time=0.012..13.217 rows=55,664 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
29. 0.485 1.656 ↑ 1.0 2,851 1

Hash (cost=166.39..166.39 rows=2,851 width=8) (actual time=1.656..1.656 rows=2,851 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 144kB
30. 1.171 1.171 ↑ 1.0 2,851 1

Seq Scan on project_plans (cost=0.00..166.39 rows=2,851 width=8) (actual time=0.006..1.171 rows=2,851 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3188
31. 1.077 2.027 ↑ 1.0 5,812 1

Hash (cost=156.12..156.12 rows=5,812 width=8) (actual time=2.027..2.027 rows=5,812 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 292kB
32. 0.950 0.950 ↑ 1.0 5,812 1

Seq Scan on projects projects_1 (cost=0.00..156.12 rows=5,812 width=8) (actual time=0.011..0.950 rows=5,812 loops=1)

33. 299.882 299.882 ↑ 1.9 13 27,262

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.005..0.011 rows=13 loops=27,262)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 5
34. 61.590 324.815 ↓ 77.4 384,529 1

Sort (cost=8,958.54..8,970.97 rows=4,971 width=36) (actual time=282.866..324.815 rows=384,529 loops=1)

  • Sort Key: pa.plan_row_uuid
  • Sort Method: quicksort Memory: 5007kB
35. 6.668 263.225 ↓ 8.9 44,428 1

Subquery Scan on pa (cost=8,529.07..8,653.34 rows=4,971 width=36) (actual time=190.352..263.225 rows=44,428 loops=1)

36. 112.366 256.557 ↓ 8.9 44,428 1

HashAggregate (cost=8,529.07..8,603.63 rows=4,971 width=48) (actual time=190.350..256.557 rows=44,428 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)
37. 49.680 144.191 ↑ 1.0 44,581 1

WindowAgg (cost=6,077.00..7,080.12 rows=44,583 width=68) (actual time=86.595..144.191 rows=44,581 loops=1)

38. 38.155 94.511 ↑ 1.0 44,581 1

Sort (cost=6,077.00..6,188.46 rows=44,583 width=52) (actual time=86.580..94.511 rows=44,581 loops=1)

  • Sort Key: plan_roles.plan_row_uuid, plan_roles.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 5023kB
39. 22.786 56.356 ↑ 1.0 44,581 1

Hash Join (cost=1,631.38..2,634.26 rows=44,583 width=52) (actual time=27.658..56.356 rows=44,581 loops=1)

  • Hash Cond: (plan_roles.plan_row_uuid = plan_rows_1.uuid)
40. 6.358 6.358 ↑ 1.0 44,581 1

Seq Scan on plan_roles (cost=0.00..885.83 rows=44,583 width=24) (actual time=0.013..6.358 rows=44,581 loops=1)

41. 15.226 27.212 ↑ 1.0 48,106 1

Hash (cost=1,030.06..1,030.06 rows=48,106 width=28) (actual time=27.212..27.212 rows=48,106 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3180kB
42. 11.986 11.986 ↑ 1.0 48,106 1

Seq Scan on plan_rows plan_rows_1 (cost=0.00..1,030.06 rows=48,106 width=28) (actual time=0.008..11.986 rows=48,106 loops=1)

43. 54.698 70.834 ↑ 1.0 59,679 1

Sort (cost=6,017.99..6,167.27 rows=59,712 width=12) (actual time=64.232..70.834 rows=59,679 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: 4336kB
44. 16.136 16.136 ↑ 1.0 59,712 1

Seq Scan on non_working_intervals_by_dates (cost=0.00..1,281.12 rows=59,712 width=12) (actual time=0.022..16.136 rows=59,712 loops=1)

45. 57.565 190.468 ↑ 15.5 37,180 1

Sort (cost=606,597.28..608,034.78 rows=575,000 width=44) (actual time=183.091..190.468 rows=37,180 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: 2844kB
46. 6.907 132.903 ↑ 17.8 32,266 1

Subquery Scan on summary_vacations_by_dates (cost=537,214.32..551,589.32 rows=575,000 width=44) (actual time=100.598..132.903 rows=32,266 loops=1)

47. 49.624 125.996 ↑ 17.8 32,266 1

HashAggregate (cost=537,214.32..545,839.32 rows=575,000 width=52) (actual time=100.596..125.996 rows=32,266 loops=1)

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
48. 17.586 76.372 ↑ 134.2 32,422 1

Hash Join (cost=138.05..493,700.50 rows=4,351,382 width=21) (actual time=2.776..76.372 rows=32,422 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.992 56.087 ↑ 634.8 34,330 1

Nested Loop (cost=0.01..436,340.93 rows=21,792,000 width=17) (actual time=0.034..56.087 rows=34,330 loops=1)

50. 3.511 3.511 ↑ 1.0 21,792 1

Seq Scan on vacations (cost=0.00..500.92 rows=21,792 width=17) (actual time=0.019..3.511 rows=21,792 loops=1)

51. 43.584 43.584 ↑ 500.0 2 21,792

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,792)

52. 1.418 2.699 ↑ 1.0 4,002 1

Hash (cost=88.02..88.02 rows=4,002 width=20) (actual time=2.699..2.699 rows=4,002 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 229kB
53. 1.281 1.281 ↑ 1.0 4,002 1

Seq Scan on staff_memberships (cost=0.00..88.02 rows=4,002 width=20) (actual time=0.009..1.281 rows=4,002 loops=1)

54. 60.082 486.473 ↓ 1.5 58,216 1

Hash Join (cost=1,215.85..62,092.86 rows=38,750 width=114) (actual time=10.614..486.473 rows=58,216 loops=1)

  • Hash Cond: (project_plans_1.project_id = projects_1_1.id)
  • Join Filter: (NOT (SubPlan 1))
  • Rows Removed by Join Filter: 770
55. 20.492 127.709 ↑ 1.3 58,986 1

Nested Loop (cost=987.08..61,446.50 rows=77,500 width=58) (actual time=6.791..127.709 rows=58,986 loops=1)

56. 2.546 43.842 ↑ 1.2 2,535 1

Hash Join (cost=987.08..2,546.49 rows=3,100 width=58) (actual time=6.764..43.842 rows=2,535 loops=1)

  • Hash Cond: (plan_items_1.project_plan_id = project_plans_1.id)
57. 18.998 38.749 ↓ 1.2 7,876 1

Hash Join (cost=785.05..2,327.21 rows=6,567 width=54) (actual time=4.146..38.749 rows=7,876 loops=1)

  • Hash Cond: (plan_items_1.plan_row_uuid = plan_rows_2.uuid)
58. 15.693 15.693 ↓ 1.0 55,664 1

Seq Scan on plan_items plan_items_1 (cost=0.00..1,396.10 rows=55,639 width=46) (actual time=0.010..15.693 rows=55,664 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
59. 1.647 4.058 ↓ 1.0 5,762 1

Hash (cost=714.07..714.07 rows=5,678 width=24) (actual time=4.058..4.058 rows=5,762 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 366kB
60. 2.062 2.411 ↓ 1.0 5,762 1

Bitmap Heap Scan on plan_rows plan_rows_2 (cost=108.29..714.07 rows=5,678 width=24) (actual time=0.423..2.411 rows=5,762 loops=1)

  • Recheck Cond: (user_id IS NULL)
  • Heap Blocks: exact=430
61. 0.349 0.349 ↓ 1.0 5,762 1

Bitmap Index Scan on index_plan_rows_on_user_id (cost=0.00..106.88 rows=5,678 width=0) (actual time=0.349..0.349 rows=5,762 loops=1)

  • Index Cond: (user_id IS NULL)
62. 0.755 2.547 ↑ 1.0 2,851 1

Hash (cost=166.39..166.39 rows=2,851 width=8) (actual time=2.547..2.547 rows=2,851 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 144kB
63. 1.792 1.792 ↑ 1.0 2,851 1

Seq Scan on project_plans project_plans_1 (cost=0.00..166.39 rows=2,851 width=8) (actual time=0.011..1.792 rows=2,851 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3188
64. 63.375 63.375 ↑ 1.1 23 2,535

Function Scan on generate_series generate_series_2 (cost=0.01..18.76 rows=25 width=8) (actual time=0.009..0.025 rows=23 loops=2,535)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 9
65. 1.730 3.752 ↑ 1.0 5,812 1

Hash (cost=156.12..156.12 rows=5,812 width=12) (actual time=3.752..3.752 rows=5,812 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 314kB
66. 2.022 2.022 ↑ 1.0 5,812 1

Seq Scan on projects projects_1_1 (cost=0.00..156.12 rows=5,812 width=12) (actual time=0.020..2.022 rows=5,812 loops=1)

67.          

SubPlan (for Hash Join)

68. 58.986 294.930 ↓ 0.0 0 58,986

Nested Loop (cost=0.69..16.74 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=58,986)

69. 117.972 117.972 ↑ 1.0 1 58,986

Index Scan using clients_pkey on clients clients_3 (cost=0.28..8.29 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=58,986)

  • Index Cond: (projects_1_1.client_id = id)
70. 117.972 117.972 ↓ 0.0 0 58,986

Index Only Scan using non_working_intervals_by_office_id_idx on non_working_intervals_by_dates non_working_intervals_by_dates_2 (cost=0.41..8.44 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=58,986)

  • Index Cond: ((account_id = projects_1_1.account_id) AND (office_id = clients_3.office_id) AND (date = generate_series_2.generate_series))
  • Heap Fetches: 770
71. 15,998.776 17,035.909 ↑ 1.0 703,961 1

Hash Right Join (cost=45,029.23..183,164.61 rows=703,961 width=116) (actual time=953.059..17,035.909 rows=703,961 loops=1)

  • Hash Cond: (plan_rows_3.user_id = time_logs.user_id)
  • Join Filter: ((time_logs.date >= (COALESCE(plan_roles_1.start_date, '1980-01-01'::date))) AND (time_logs.date <= (COALESCE(lag((plan_roles_1.start_date - 1), 1) OVER (?), '3000-01-01'::date))) AND (((plan_rows_3.epic_id = time_logs.epic_id) AND (plan_rows_3.project_plan_id = epics.project_plan_id)) OR ((plan_rows_3.epic_id IS NULL) AND (plan_rows_3.project_plan_id = general_epic_plans.id))))
  • Rows Removed by Join Filter: 63215381
72. 128.925 253.064 ↓ 8.9 44,428 1

HashAggregate (cost=8,529.07..8,603.63 rows=4,971 width=48) (actual time=162.800..253.064 rows=44,428 loops=1)

  • Group Key: plan_rows_3.project_plan_id, plan_rows_3.epic_id, plan_rows_3.user_id, plan_roles_1.activity_id, COALESCE(plan_roles_1.start_date, '1980-01-01'::date), COALESCE(lag((plan_roles_1.start_date - 1), 1) OVER (?), '3000-01-01'::date)
73. 39.040 124.139 ↑ 1.0 44,581 1

WindowAgg (cost=6,077.00..7,080.12 rows=44,583 width=68) (actual time=77.996..124.139 rows=44,581 loops=1)

74. 30.686 85.099 ↑ 1.0 44,581 1

Sort (cost=6,077.00..6,188.46 rows=44,583 width=52) (actual time=77.983..85.099 rows=44,581 loops=1)

  • Sort Key: plan_roles_1.plan_row_uuid, plan_roles_1.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 5023kB
75. 22.129 54.413 ↑ 1.0 44,581 1

Hash Join (cost=1,631.38..2,634.26 rows=44,583 width=52) (actual time=26.758..54.413 rows=44,581 loops=1)

  • Hash Cond: (plan_roles_1.plan_row_uuid = plan_rows_3.uuid)
76. 5.645 5.645 ↑ 1.0 44,581 1

Seq Scan on plan_roles plan_roles_1 (cost=0.00..885.83 rows=44,583 width=24) (actual time=0.013..5.645 rows=44,581 loops=1)

77. 15.357 26.639 ↑ 1.0 48,106 1

Hash (cost=1,030.06..1,030.06 rows=48,106 width=28) (actual time=26.639..26.639 rows=48,106 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3180kB
78. 11.282 11.282 ↑ 1.0 48,106 1

Seq Scan on plan_rows plan_rows_3 (cost=0.00..1,030.06 rows=48,106 width=28) (actual time=0.009..11.282 rows=48,106 loops=1)

79. 286.478 784.069 ↑ 1.0 703,961 1

Hash (cost=27,700.65..27,700.65 rows=703,961 width=40) (actual time=784.069..784.069 rows=703,961 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 62366kB
80. 365.609 497.591 ↑ 1.0 703,961 1

Hash Join (cost=1,826.58..27,700.65 rows=703,961 width=40) (actual time=27.626..497.591 rows=703,961 loops=1)

  • Hash Cond: (time_logs.epic_id = epics.id)
81. 104.436 104.436 ↑ 1.0 703,961 1

Seq Scan on time_logs (cost=0.00..16,194.61 rows=703,961 width=28) (actual time=0.018..104.436 rows=703,961 loops=1)

82. 8.642 27.546 ↑ 1.0 26,435 1

Hash (cost=1,496.14..1,496.14 rows=26,435 width=16) (actual time=27.546..27.546 rows=26,435 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1381kB
83. 12.147 18.904 ↑ 1.0 26,435 1

Hash Left Join (cost=202.03..1,496.14 rows=26,435 width=16) (actual time=2.683..18.904 rows=26,435 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: 11144
84. 4.149 4.149 ↑ 1.0 26,435 1

Seq Scan on epics (cost=0.00..619.35 rows=26,435 width=12) (actual time=0.007..4.149 rows=26,435 loops=1)

85. 0.804 2.608 ↑ 1.0 2,851 1

Hash (cost=166.39..166.39 rows=2,851 width=8) (actual time=2.607..2.608 rows=2,851 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 144kB
86. 1.804 1.804 ↑ 1.0 2,851 1

Seq Scan on project_plans general_epic_plans (cost=0.00..166.39 rows=2,851 width=8) (actual time=0.010..1.804 rows=2,851 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3188
87. 1.192 2.214 ↑ 1.0 5,812 1

Hash (cost=156.12..156.12 rows=5,812 width=8) (actual time=2.213..2.214 rows=5,812 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 292kB
88. 1.022 1.022 ↑ 1.0 5,812 1

Seq Scan on projects (cost=0.00..156.12 rows=5,812 width=8) (actual time=0.005..1.022 rows=5,812 loops=1)

89. 130.067 152.604 ↓ 295.4 1,255,026 1

Materialize (cost=847.14..942.74 rows=4,249 width=40) (actual time=19.760..152.604 rows=1,255,026 loops=1)

90. 1.966 22.537 ↑ 1.0 4,218 1

Unique (cost=847.14..889.63 rows=4,249 width=65) (actual time=19.755..22.537 rows=4,218 loops=1)

91. 5.088 20.571 ↑ 1.0 4,246 1

Sort (cost=847.14..857.76 rows=4,249 width=65) (actual time=19.753..20.571 rows=4,246 loops=1)

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

WindowAgg (cost=484.85..591.08 rows=4,249 width=65) (actual time=10.505..15.483 rows=4,249 loops=1)

93. 4.715 11.025 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=56) (actual time=10.489..11.025 rows=4,249 loops=1)

  • Sort Key: staff_membership_activity_links.staff_membership_id, staff_membership_activity_links.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 790kB
94. 2.552 6.310 ↑ 1.0 4,249 1

Hash Join (cost=138.05..228.79 rows=4,249 width=56) (actual time=3.167..6.310 rows=4,249 loops=1)

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships_1.id)
95. 0.670 0.670 ↑ 1.0 4,256 1

Seq Scan on staff_membership_activity_links (cost=0.00..79.56 rows=4,256 width=28) (actual time=0.023..0.670 rows=4,256 loops=1)

96. 1.744 3.088 ↑ 1.0 4,002 1

Hash (cost=88.02..88.02 rows=4,002 width=32) (actual time=3.088..3.088 rows=4,002 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 276kB
97. 1.344 1.344 ↑ 1.0 4,002 1

Seq Scan on staff_memberships staff_memberships_1 (cost=0.00..88.02 rows=4,002 width=32) (actual time=0.011..1.344 rows=4,002 loops=1)

98. 0.059 0.144 ↑ 1.0 354 1

Hash (cost=14.54..14.54 rows=354 width=9) (actual time=0.144..0.144 rows=354 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
99. 0.085 0.085 ↑ 1.0 354 1

Seq Scan on accounts accounts_1 (cost=0.00..14.54 rows=354 width=9) (actual time=0.006..0.085 rows=354 loops=1)

100. 14.930 413.226 ↑ 8.5 28,906 1

Subquery Scan on *SELECT* 2_1 (cost=3,354.66..490,424.84 rows=245,158 width=136) (actual time=48.602..413.226 rows=28,906 loops=1)

101. 228.637 398.296 ↑ 8.5 28,906 1

Hash Anti Join (cost=3,354.66..487,360.36 rows=245,158 width=143) (actual time=48.598..398.296 rows=28,906 loops=1)

  • Hash Cond: ((sa.account_id = non_working_intervals_by_dates_1.account_id) AND (sa.user_id = non_working_intervals_by_dates_1.user_id))
  • Join Filter: (generate_series_3.generate_series = non_working_intervals_by_dates_1.date)
  • Rows Removed by Join Filter: 1153172
102. 25.670 138.077 ↑ 9.5 29,394 1

Merge Join (cost=1,177.86..468,661.53 rows=280,181 width=64) (actual time=16.935..138.077 rows=29,394 loops=1)

  • Merge Cond: (vacations_1.staff_membership_id = sa.staff_membership_id)
  • Join Filter: ((generate_series_3.generate_series >= sa.start_date) AND (generate_series_3.generate_series <= sa.end_date))
  • Rows Removed by Join Filter: 10924
103. 30.655 90.548 ↑ 17.5 31,132 1

Nested Loop (cost=0.29..415,689.39 rows=544,800 width=44) (actual time=0.032..90.548 rows=31,132 loops=1)

104. 16.309 16.309 ↑ 1.0 21,792 1

Index Scan using index_vacations_on_staff_membership_id on vacations vacations_1 (cost=0.29..1,641.38 rows=21,792 width=44) (actual time=0.016..16.309 rows=21,792 loops=1)

105. 43.584 43.584 ↑ 25.0 1 21,792

Function Scan on generate_series generate_series_3 (cost=0.01..18.76 rows=25 width=8) (actual time=0.002..0.002 rows=1 loops=21,792)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 0
106. 6.809 21.859 ↓ 10.2 43,399 1

Sort (cost=1,177.56..1,188.19 rows=4,249 width=36) (actual time=16.891..21.859 rows=43,399 loops=1)

  • Sort Key: sa.staff_membership_id
  • Sort Method: quicksort Memory: 522kB
107. 0.946 15.050 ↑ 1.0 4,221 1

Subquery Scan on sa (cost=836.52..921.50 rows=4,249 width=36) (actual time=12.849..15.050 rows=4,221 loops=1)

108. 0.934 14.104 ↑ 1.0 4,221 1

Unique (cost=836.52..879.01 rows=4,249 width=65) (actual time=12.847..14.104 rows=4,221 loops=1)

109. 3.327 13.170 ↑ 1.0 4,249 1

Sort (cost=836.52..847.14 rows=4,249 width=65) (actual time=12.846..13.170 rows=4,249 loops=1)

  • Sort Key: staff_memberships_2.account_id, staff_memberships_2.user_id, (COALESCE(staff_membership_activity_links_1.start_date, staff_memberships_2.joined_at)), (COALESCE(lag((staff_membership_activity_links_1.start_date - 1), 1) OVER (?), staff_memberships_2.archived_at, '3000-01-01'::date)) DESC
  • Sort Method: quicksort Memory: 592kB
110. 3.439 9.843 ↑ 1.0 4,249 1

WindowAgg (cost=484.85..580.45 rows=4,249 width=65) (actual time=6.036..9.843 rows=4,249 loops=1)

111. 2.360 6.404 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=44) (actual time=6.026..6.404 rows=4,249 loops=1)

  • Sort Key: staff_membership_activity_links_1.staff_membership_id, staff_membership_activity_links_1.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 561kB
112. 1.578 4.044 ↑ 1.0 4,249 1

Hash Join (cost=138.05..228.79 rows=4,249 width=44) (actual time=2.014..4.044 rows=4,249 loops=1)

  • Hash Cond: (staff_membership_activity_links_1.staff_membership_id = staff_memberships_2.id)
113. 0.473 0.473 ↑ 1.0 4,256 1

Seq Scan on staff_membership_activity_links staff_membership_activity_links_1 (cost=0.00..79.56 rows=4,256 width=20) (actual time=0.010..0.473 rows=4,256 loops=1)

114. 1.046 1.993 ↑ 1.0 4,002 1

Hash (cost=88.02..88.02 rows=4,002 width=24) (actual time=1.992..1.993 rows=4,002 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 245kB
115. 0.947 0.947 ↑ 1.0 4,002 1

Seq Scan on staff_memberships staff_memberships_2 (cost=0.00..88.02 rows=4,002 width=24) (actual time=0.006..0.947 rows=4,002 loops=1)

116. 16.516 31.582 ↑ 1.0 58,975 1

Hash (cost=1,281.12..1,281.12 rows=59,712 width=12) (actual time=31.582..31.582 rows=58,975 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3047kB
117. 15.066 15.066 ↑ 1.0 59,712 1

Seq Scan on non_working_intervals_by_dates non_working_intervals_by_dates_1 (cost=0.00..1,281.12 rows=59,712 width=12) (actual time=0.015..15.066 rows=59,712 loops=1)

118. 0.005 0.209 ↑ 9.8 12 1

Hash (cost=17.20..17.20 rows=118 width=4) (actual time=0.209..0.209 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
119. 0.204 0.204 ↑ 9.8 12 1

Seq Scan on accounts (cost=0.00..17.20 rows=118 width=4) (actual time=0.016..0.204 rows=12 loops=1)

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 342
120. 457.637 805.303 ↓ 4.1 1,115,238 1

Sort (cost=46,830.92..47,512.59 rows=272,669 width=44) (actual time=678.446..805.303 rows=1,115,238 loops=1)

  • Sort Key: ""*SELECT* 1"".project_id, ""*SELECT* 1"".activity_id
  • Sort Method: quicksort Memory: 32838kB
121. 32.941 347.666 ↑ 1.0 263,033 1

Append (cost=300.88..22,213.28 rows=272,669 width=44) (actual time=6.506..347.666 rows=263,033 loops=1)

122. 0.134 33.636 ↓ 9.7 474 1

Subquery Scan on *SELECT* 1 (cost=300.88..470.62 rows=49 width=44) (actual time=6.505..33.636 rows=474 loops=1)

123. 0.196 33.502 ↓ 9.7 474 1

Merge Join (cost=300.88..470.13 rows=49 width=612) (actual time=6.503..33.502 rows=474 loops=1)

  • Merge Cond: (clients.brand_id = brands.id)
124. 0.215 33.210 ↓ 9.7 474 1

Nested Loop (cost=294.80..1,671.71 rows=49 width=52) (actual time=6.402..33.210 rows=474 loops=1)

125. 0.015 3.865 ↓ 5.0 5 1

Merge Join (cost=294.38..294.67 rows=1 width=32) (actual time=3.835..3.865 rows=5 loops=1)

  • Merge Cond: (rate_cards.rateable_id = clients.brand_id)
126. 0.035 1.534 ↑ 28.8 4 1

WindowAgg (cost=108.61..111.49 rows=115 width=45) (actual time=1.510..1.534 rows=4 loops=1)

127. 0.092 1.499 ↑ 23.0 5 1

Sort (cost=108.61..108.90 rows=115 width=29) (actual time=1.497..1.499 rows=5 loops=1)

  • Sort Key: rate_cards.rateable_id, rate_cards.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 33kB
128. 1.407 1.407 ↑ 1.0 115 1

Seq Scan on rate_cards (cost=0.00..104.68 rows=115 width=29) (actual time=0.333..1.407 rows=115 loops=1)

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4739
129. 0.016 2.316 ↑ 1.0 5 1

Sort (cost=185.76..185.78 rows=5 width=8) (actual time=2.314..2.316 rows=5 loops=1)

  • Sort Key: clients.brand_id
  • Sort Method: quicksort Memory: 25kB
130. 0.007 2.300 ↑ 1.0 5 1

Nested Loop (cost=0.56..185.71 rows=5 width=8) (actual time=1.263..2.300 rows=5 loops=1)

131. 0.013 2.273 ↑ 1.0 5 1

Nested Loop (cost=0.28..184.13 rows=5 width=8) (actual time=1.253..2.273 rows=5 loops=1)

132. 2.195 2.195 ↑ 1.0 5 1

Seq Scan on pricing_models (cost=0.00..142.62 rows=5 width=4) (actual time=1.194..2.195 rows=5 loops=1)

  • Filter: ((rates_type)::text = 'brand'::text)
  • Rows Removed by Filter: 5805
133. 0.065 0.065 ↑ 1.0 1 5

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

  • Index Cond: (id = pricing_models.project_id)
134. 0.020 0.020 ↑ 1.0 1 5

Index Scan using clients_pkey on clients (cost=0.28..0.32 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=5)

  • Index Cond: (id = projects_2.client_id)
135. 29.130 29.130 ↓ 1.9 95 5

Index Scan using index_rates_on_activity_id_and_rate_card_id on rates (cost=0.42..1,376.55 rows=49 width=28) (actual time=1.248..5.826 rows=95 loops=5)

  • Index Cond: (rate_card_id = rate_cards.id)
136. 0.054 0.096 ↑ 38.3 3 1

Sort (cost=6.09..6.37 rows=115 width=4) (actual time=0.095..0.096 rows=3 loops=1)

  • Sort Key: brands.id
  • Sort Method: quicksort Memory: 30kB
137. 0.042 0.042 ↑ 1.0 115 1

Seq Scan on brands (cost=0.00..2.15 rows=115 width=4) (actual time=0.024..0.042 rows=115 loops=1)

138. 14.358 114.575 ↓ 2.6 91,600 1

Subquery Scan on *SELECT* 2 (cost=583.00..5,484.31 rows=35,240 width=44) (actual time=23.553..114.575 rows=91,600 loops=1)

139. 42.448 100.217 ↓ 2.6 91,600 1

Hash Join (cost=583.00..5,131.91 rows=35,240 width=612) (actual time=23.552..100.217 rows=91,600 loops=1)

  • Hash Cond: (rates_1.rate_card_id = rate_cards_1.id)
140. 50.463 50.463 ↑ 1.0 174,437 1

Seq Scan on rates rates_1 (cost=0.00..3,542.37 rows=174,437 width=28) (actual time=0.037..50.463 rows=174,437 loops=1)

141. 0.232 7.306 ↑ 1.1 657 1

Hash (cost=574.00..574.00 rows=720 width=24) (actual time=7.306..7.306 rows=657 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
142. 0.234 7.074 ↑ 1.1 657 1

Hash Join (cost=378.98..574.00 rows=720 width=24) (actual time=4.663..7.074 rows=657 loops=1)

  • Hash Cond: (clients_1.office_id = offices.id)
143. 0.291 4.683 ↑ 1.0 623 1

Hash Join (cost=214.02..399.80 rows=623 width=8) (actual time=2.501..4.683 rows=623 loops=1)

  • Hash Cond: (projects_3.client_id = clients_1.id)
144. 1.191 3.307 ↑ 1.0 623 1

Hash Join (cost=150.41..334.56 rows=623 width=8) (actual time=1.355..3.307 rows=623 loops=1)

  • Hash Cond: (projects_3.id = pricing_models_1.project_id)
145. 0.813 0.813 ↑ 1.0 5,812 1

Seq Scan on projects projects_3 (cost=0.00..156.12 rows=5,812 width=8) (actual time=0.009..0.813 rows=5,812 loops=1)

146. 0.163 1.303 ↑ 1.0 623 1

Hash (cost=142.62..142.62 rows=623 width=4) (actual time=1.303..1.303 rows=623 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
147. 1.140 1.140 ↑ 1.0 623 1

Seq Scan on pricing_models pricing_models_1 (cost=0.00..142.62 rows=623 width=4) (actual time=0.022..1.140 rows=623 loops=1)

  • Filter: ((rates_type)::text = 'office'::text)
  • Rows Removed by Filter: 5187
148. 0.508 1.085 ↑ 1.0 1,849 1

Hash (cost=40.49..40.49 rows=1,849 width=8) (actual time=1.085..1.085 rows=1,849 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 89kB
149. 0.577 0.577 ↑ 1.0 1,849 1

Seq Scan on clients clients_1 (cost=0.00..40.49 rows=1,849 width=8) (actual time=0.010..0.577 rows=1,849 loops=1)

150. 0.163 2.157 ↑ 1.1 450 1

Hash (cost=158.85..158.85 rows=489 width=28) (actual time=2.157..2.157 rows=450 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
151. 0.212 1.994 ↑ 1.1 450 1

Hash Join (cost=140.44..158.85 rows=489 width=28) (actual time=1.283..1.994 rows=450 loops=1)

  • Hash Cond: (rate_cards_1.rateable_id = offices.id)
152. 0.503 1.503 ↑ 1.0 489 1

WindowAgg (cost=126.52..138.74 rows=489 width=45) (actual time=0.949..1.503 rows=489 loops=1)

153. 0.255 1.000 ↑ 1.0 489 1

Sort (cost=126.52..127.74 rows=489 width=29) (actual time=0.934..1.000 rows=489 loops=1)

  • Sort Key: rate_cards_1.rateable_id, rate_cards_1.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 63kB
154. 0.745 0.745 ↑ 1.0 489 1

Seq Scan on rate_cards rate_cards_1 (cost=0.00..104.68 rows=489 width=29) (actual time=0.166..0.745 rows=489 loops=1)

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 4365
155. 0.123 0.279 ↑ 1.0 441 1

Hash (cost=8.41..8.41 rows=441 width=4) (actual time=0.279..0.279 rows=441 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
156. 0.156 0.156 ↑ 1.0 441 1

Seq Scan on offices (cost=0.00..8.41 rows=441 width=4) (actual time=0.019..0.156 rows=441 loops=1)

157. 14.646 87.211 ↑ 2.2 94,593 1

Subquery Scan on *SELECT* 3 (cost=840.78..9,363.71 rows=205,419 width=44) (actual time=12.124..87.211 rows=94,593 loops=1)

158. 42.130 72.565 ↑ 2.2 94,593 1

Hash Join (cost=840.78..7,309.52 rows=205,419 width=612) (actual time=12.123..72.565 rows=94,593 loops=1)

  • Hash Cond: (rates_2.rate_card_id = rate_cards_2.id)
159. 18.372 18.372 ↑ 1.0 174,437 1

Seq Scan on rates rates_2 (cost=0.00..3,542.37 rows=174,437 width=28) (actual time=0.011..18.372 rows=174,437 loops=1)

160. 1.042 12.063 ↑ 1.0 4,004 1

Hash (cost=788.31..788.31 rows=4,197 width=24) (actual time=12.063..12.063 rows=4,004 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 299kB
161. 1.157 11.021 ↑ 1.0 4,004 1

Hash Join (cost=586.95..788.31 rows=4,197 width=24) (actual time=7.706..11.021 rows=4,004 loops=1)

  • Hash Cond: (projects_4.client_id = clients_2.id)
162. 1.173 4.959 ↑ 1.0 3,620 1

Hash Join (cost=228.77..380.91 rows=3,620 width=8) (actual time=2.743..4.959 rows=3,620 loops=1)

  • Hash Cond: (pricing_models_2.project_id = projects_4.id)
163. 1.111 1.111 ↑ 1.0 3,621 1

Seq Scan on pricing_models pricing_models_2 (cost=0.00..142.62 rows=3,621 width=4) (actual time=0.008..1.111 rows=3,621 loops=1)

  • Filter: ((rates_type)::text = 'client'::text)
  • Rows Removed by Filter: 2189
164. 1.179 2.675 ↑ 1.0 5,812 1

Hash (cost=156.12..156.12 rows=5,812 width=8) (actual time=2.675..2.675 rows=5,812 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 292kB
165. 1.496 1.496 ↑ 1.0 5,812 1

Seq Scan on projects projects_4 (cost=0.00..156.12 rows=5,812 width=8) (actual time=0.006..1.496 rows=5,812 loops=1)

166. 0.540 4.905 ↑ 1.0 1,820 1

Hash (cost=335.42..335.42 rows=1,821 width=28) (actual time=4.904..4.905 rows=1,820 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 123kB
167. 0.585 4.365 ↑ 1.0 1,820 1

Hash Join (cost=266.89..335.42 rows=1,821 width=28) (actual time=2.236..4.365 rows=1,820 loops=1)

  • Hash Cond: (rate_cards_2.rateable_id = clients_2.id)
168. 1.469 3.224 ↑ 1.0 1,821 1

WindowAgg (cost=203.29..248.81 rows=1,821 width=45) (actual time=1.624..3.224 rows=1,821 loops=1)

169. 0.948 1.755 ↑ 1.0 1,821 1

Sort (cost=203.29..207.84 rows=1,821 width=29) (actual time=1.608..1.755 rows=1,821 loops=1)

  • Sort Key: rate_cards_2.rateable_id, rate_cards_2.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 191kB
170. 0.807 0.807 ↑ 1.0 1,821 1

Seq Scan on rate_cards rate_cards_2 (cost=0.00..104.68 rows=1,821 width=29) (actual time=0.008..0.807 rows=1,821 loops=1)

  • Filter: ((rateable_type)::text = 'Client'::text)
  • Rows Removed by Filter: 3033
171. 0.301 0.556 ↑ 1.0 1,849 1

Hash (cost=40.49..40.49 rows=1,849 width=4) (actual time=0.556..0.556 rows=1,849 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 82kB
172. 0.255 0.255 ↑ 1.0 1,849 1

Seq Scan on clients clients_2 (cost=0.00..40.49 rows=1,849 width=4) (actual time=0.011..0.255 rows=1,849 loops=1)

173. 12.598 79.303 ↓ 2.4 76,366 1

Subquery Scan on *SELECT* 4 (cost=695.56..5,531.29 rows=31,961 width=44) (actual time=8.635..79.303 rows=76,366 loops=1)

174. 39.766 66.705 ↓ 2.4 76,366 1

Hash Join (cost=695.56..5,211.68 rows=31,961 width=612) (actual time=8.634..66.705 rows=76,366 loops=1)

  • Hash Cond: (rates_3.rate_card_id = rate_cards_3.id)
175. 18.343 18.343 ↑ 1.0 174,437 1

Seq Scan on rates rates_3 (cost=0.00..3,542.37 rows=174,437 width=28) (actual time=0.011..18.343 rows=174,437 loops=1)

176. 0.532 8.596 ↓ 2.4 1,591 1

Hash (cost=687.40..687.40 rows=653 width=24) (actual time=8.596..8.596 rows=1,591 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 110kB
177. 1.247 8.064 ↓ 2.4 1,591 1

Hash Join (cost=502.95..687.40 rows=653 width=24) (actual time=6.070..8.064 rows=1,591 loops=1)

  • Hash Cond: (projects_5.id = pricing_models_3.project_id)
178. 0.772 0.772 ↑ 1.0 5,812 1

Seq Scan on projects projects_5 (cost=0.00..156.12 rows=5,812 width=4) (actual time=0.005..0.772 rows=5,812 loops=1)

179. 0.453 6.045 ↓ 2.4 1,591 1

Hash (cost=494.79..494.79 rows=653 width=24) (actual time=6.044..6.045 rows=1,591 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 110kB
180. 0.572 5.592 ↓ 2.4 1,591 1

Hash Join (cost=403.40..494.79 rows=653 width=24) (actual time=3.016..5.592 rows=1,591 loops=1)

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
181. 1.801 3.743 ↑ 1.0 2,429 1

WindowAgg (cost=241.26..301.98 rows=2,429 width=45) (actual time=1.727..3.743 rows=2,429 loops=1)

182. 1.150 1.942 ↑ 1.0 2,429 1

Sort (cost=241.26..247.33 rows=2,429 width=29) (actual time=1.712..1.942 rows=2,429 loops=1)

  • Sort Key: rate_cards_3.rateable_id, rate_cards_3.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 286kB
183. 0.792 0.792 ↑ 1.0 2,429 1

Seq Scan on rate_cards rate_cards_3 (cost=0.00..104.68 rows=2,429 width=29) (actual time=0.011..0.792 rows=2,429 loops=1)

  • Filter: ((rateable_type)::text = 'PricingModel'::text)
  • Rows Removed by Filter: 2425
184. 0.354 1.277 ↑ 1.0 1,561 1

Hash (cost=142.62..142.62 rows=1,561 width=8) (actual time=1.277..1.277 rows=1,561 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 77kB
185. 0.923 0.923 ↑ 1.0 1,561 1

Seq Scan on pricing_models pricing_models_3 (cost=0.00..142.62 rows=1,561 width=8) (actual time=0.010..0.923 rows=1,561 loops=1)

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 4249
186. 63.091 63.191 ↓ 1,993.3 609,953 1

Sort (cost=22.77..23.53 rows=306 width=26) (actual time=0.269..63.191 rows=609,953 loops=1)

  • Sort Key: discounts.project_id
  • Sort Method: quicksort Memory: 53kB
187. 0.100 0.100 ↓ 1.2 359 1

Seq Scan on discounts (cost=0.00..10.13 rows=306 width=26) (actual time=0.016..0.100 rows=359 loops=1)

  • Filter: (((discountable_type)::text = 'User'::text) OR ((discountable_type)::text = 'Epic'::text))
  • Rows Removed by Filter: 50
188. 3,822.227 3,822.812 ↓ 12,841.5 39,140,740 1

Sort (cost=244.86..252.48 rows=3,048 width=24) (actual time=1.218..3,822.812 rows=39,140,740 loops=1)

  • Sort Key: exchange_rates_by_months.account_id, exchange_rates_by_months.currency
  • Sort Method: quicksort Memory: 335kB
189. 0.585 0.585 ↑ 1.0 3,048 1

Seq Scan on exchange_rates_by_months (cost=0.00..68.48 rows=3,048 width=24) (actual time=0.017..0.585 rows=3,048 loops=1)

190. 3,344.908 3,345.507 ↓ 11,405.1 34,762,629 1

Sort (cost=244.86..252.48 rows=3,048 width=24) (actual time=1.432..3,345.507 rows=34,762,629 loops=1)

  • Sort Key: cost_exchange_rates.account_id, cost_exchange_rates.currency
  • Sort Method: quicksort Memory: 335kB
191. 0.599 0.599 ↑ 1.0 3,048 1

Seq Scan on exchange_rates_by_months cost_exchange_rates (cost=0.00..68.48 rows=3,048 width=24) (actual time=0.016..0.599 rows=3,048 loops=1)

Planning time : 19.710 ms
Execution time : 56,041.016 ms