explain.depesz.com

PostgreSQL's explain analyze made readable

Result: euAP : Optimization for: plan #4xF

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 274.061 28,225.826 ↓ 3.9 638,535 1

Hash Left Join (cost=1,087,947.35..25,066,953.01 rows=165,528 width=140) (actual time=2,336.924..28,225.826 rows=638,535 loops=1)

  • Hash 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: 599,801
2. 3,744.144 27,951.581 ↓ 3.9 638,460 1

Hash Left Join (cost=1,087,933.39..25,052,331.20 rows=165,528 width=148) (actual time=2,336.689..27,951.581 rows=638,460 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: 34,220,167
3. 4,186.183 24,206.159 ↓ 3.9 638,460 1

Hash Left Join (cost=1,087,819.19..24,893,534.74 rows=165,528 width=172) (actual time=2,334.606..24,206.159 rows=638,460 loops=1)

  • Hash 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: 38,539,729
4. 336.393 20,018.686 ↓ 3.9 638,460 1

Hash Join (cost=1,087,704.99..24,734,738.29 rows=165,528 width=164) (actual time=2,333.242..20,018.686 rows=638,460 loops=1)

  • Hash Cond: ((projects.id = ""*SELECT* 1"".project_id) AND ((COALESCE(""*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: 229,908
5. 259.525 19,290.627 ↓ 3.9 849,952 1

Hash Join (cost=1,060,179.63..9,933,922.34 rows=216,380 width=136) (actual time=1,939.436..19,290.627 rows=849,952 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
6. 143.824 19,030.934 ↓ 1.7 1,116,116 1

Append (cost=1,060,160.96..9,925,687.86 rows=649,141 width=136) (actual time=1,939.251..19,030.934 rows=1,116,116 loops=1)

7. 572.404 18,634.475 ↓ 2.7 1,087,210 1

Hash Join (cost=1,060,160.96..9,406,382.19 rows=403,983 width=108) (actual time=1,939.250..18,634.475 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(sa.capacity, accounts_1.default_capacity))::double precision))
  • Rows Removed by Join Filter: 13,596
8. 464.670 18,061.920 ↑ 1.1 1,100,806 1

Hash Left Join (cost=1,060,141.99..9,388,045.59 rows=1,199,950 width=144) (actual time=1,926.455..18,061.920 rows=1,100,806 loops=1)

  • Hash Cond: (("*SELECT* 1_1".user_id = sa.user_id) AND (projects.account_id = sa.account_id))
  • Join Filter: (("*SELECT* 1_1".date >= sa.start_date) AND ("*SELECT* 1_1".date <= sa.end_date))
  • Rows Removed by Join Filter: 258,899
9. 322.593 17,578.535 ↑ 1.1 1,100,806 1

Hash Join (cost=1,059,146.14..8,104,503.14 rows=1,199,950 width=120) (actual time=1,907.652..17,578.535 rows=1,100,806 loops=1)

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
10. 136.790 17,254.117 ↑ 1.1 1,100,806 1

Append (cost=1,058,917.37..8,089,122.71 rows=1,199,950 width=116) (actual time=1,905.780..17,254.117 rows=1,100,806 loops=1)

11. 132.532 4,239.365 ↑ 1.2 396,845 1

Result (cost=1,058,917.37..7,892,918.74 rows=495,989 width=116) (actual time=1,905.779..4,239.365 rows=396,845 loops=1)

12. 47.350 4,106.833 ↑ 1.2 396,845 1

Append (cost=1,058,917.37..7,886,718.88 rows=495,989 width=140) (actual time=1,905.775..4,106.833 rows=396,845 loops=1)

13. 63.755 3,749.748 ↑ 1.4 338,629 1

Subquery Scan on *SELECT* 1_1 (cost=1,058,917.37..7,821,758.58 rows=457,239 width=114) (actual time=1,905.775..3,749.748 rows=338,629 loops=1)

14. 1,761.130 3,685.993 ↑ 1.4 338,629 1

Hash Right Join (cost=1,058,917.37..7,814,899.99 rows=457,239 width=134) (actual time=1,905.773..3,685.993 rows=338,629 loops=1)

  • Hash Cond: ((staff_memberships.account_id = projects_1.account_id) AND (staff_memberships.user_id = plan_rows.user_id))
  • Join Filter: (generate_series.generate_series = ((generate_series_1.generate_series)::date))
  • Rows Removed by Join Filter: 14,110,732
15. 39.802 91.221 ↑ 17.8 32,266 1

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

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
16. 11.635 51.419 ↑ 134.2 32,422 1

Hash Join (cost=138.05..493,700.50 rows=4,351,382 width=21) (actual time=1.880..51.419 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: 1,908
17. 12.878 37.996 ↑ 634.8 34,330 1

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

18. 3.326 3.326 ↑ 1.0 21,792 1

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

19. 21.792 21.792 ↑ 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.001 rows=2 loops=21,792)

20. 0.848 1.788 ↑ 1.0 4,002 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 229kB
21. 0.940 0.940 ↑ 1.0 4,002 1

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

22. 119.486 1,833.642 ↑ 1.4 338,629 1

Hash (cost=514,844.46..514,844.46 rows=457,239 width=66) (actual time=1,833.642..1,833.642 rows=338,629 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 38,679kB
23. 928.948 1,714.156 ↑ 1.4 338,629 1

Hash Anti Join (cost=464,677.72..514,844.46 rows=457,239 width=66) (actual time=602.364..1,714.156 rows=338,629 loops=1)

  • Hash Cond: ((projects_1.account_id = non_working_intervals_by_dates.account_id) AND (plan_rows.user_id = non_working_intervals_by_dates.user_id))
  • Join Filter: (generate_series.generate_series = non_working_intervals_by_dates.date)
  • Rows Removed by Join Filter: 11,449,923
24. 147.133 763.093 ↑ 1.7 346,275 1

Hash Right Join (cost=462,500.92..466,446.67 rows=579,175 width=66) (actual time=580.200..763.093 rows=346,275 loops=1)

  • Hash Cond: ((plan_rows_1.project_plan_id = project_plans.id) AND (((min(((plan_rows_1.uuid)::character varying)::text))::uuid) = plan_items.plan_row_uuid) AND (plan_rows_1.user_id = plan_rows.user_id))
  • Join Filter: (((plan_rows_1.epic_id = plan_rows.epic_id) OR (plan_rows.epic_id IS NULL)) AND ((generate_series.generate_series)::date >= (COALESCE(plan_roles.start_date, '1980-01-01'::date))) AND ((generate_series.generate_series)::date <= (COALESCE(lag((plan_roles.start_date - 1), 1) OVER (?), '3000-01-01'::date))))
  • Rows Removed by Join Filter: 3,247
25. 69.853 161.256 ↓ 8.9 44,428 1

HashAggregate (cost=8,529.07..8,603.63 rows=4,971 width=48) (actual time=120.692..161.256 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)
26. 28.247 91.403 ↑ 1.0 44,581 1

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

27. 25.468 63.156 ↑ 1.0 44,581 1

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

  • Sort Key: plan_roles.plan_row_uuid, plan_roles.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 5,023kB
28. 15.300 37.688 ↑ 1.0 44,581 1

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

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

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

30. 9.491 17.155 ↑ 1.0 48,106 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,180kB
31. 7.664 7.664 ↑ 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.013..7.664 rows=48,106 loops=1)

32. 122.556 454.704 ↑ 1.7 346,275 1

Hash (cost=443,836.29..443,836.29 rows=579,175 width=70) (actual time=454.704..454.704 rows=346,275 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 45,544kB
33. 66.608 332.148 ↑ 1.7 346,275 1

Nested Loop (cost=1,991.21..443,836.29 rows=579,175 width=70) (actual time=31.355..332.148 rows=346,275 loops=1)

34. 8.995 74.706 ↓ 1.2 27,262 1

Hash Join (cost=1,991.21..3,663.28 rows=23,167 width=70) (actual time=31.304..74.706 rows=27,262 loops=1)

  • Hash Cond: (project_plans.project_id = projects_1.id)
35. 10.584 63.723 ↓ 1.2 27,262 1

Hash Join (cost=1,762.44..3,373.65 rows=23,167 width=66) (actual time=28.498..63.723 rows=27,262 loops=1)

  • Hash Cond: (plan_items.plan_row_uuid = plan_rows.uuid)
36. 18.003 32.533 ↓ 1.1 29,797 1

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

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

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

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
38. 0.452 1.884 ↑ 1.0 2,851 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 144kB
39. 1.432 1.432 ↑ 1.0 2,851 1

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

  • Filter: active
  • Rows Removed by Filter: 3,188
40. 10.345 20.606 ↑ 1.0 42,344 1

Hash (cost=1,030.06..1,030.06 rows=42,428 width=28) (actual time=20.606..20.606 rows=42,344 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,816kB
41. 10.261 10.261 ↑ 1.0 42,344 1

Seq Scan on plan_rows (cost=0.00..1,030.06 rows=42,428 width=28) (actual time=0.014..10.261 rows=42,344 loops=1)

  • Filter: (user_id IS NOT NULL)
  • Rows Removed by Filter: 5,762
42. 1.005 1.988 ↑ 1.0 5,812 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 292kB
43. 0.983 0.983 ↑ 1.0 5,812 1

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

44. 190.834 190.834 ↑ 1.9 13 27,262

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.003..0.007 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
45. 10.636 22.115 ↑ 1.0 58,975 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,047kB
46. 11.479 11.479 ↑ 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.018..11.479 rows=59,712 loops=1)

47. 49.616 309.735 ↓ 1.5 58,216 1

Hash Join (cost=1,215.85..62,092.86 rows=38,750 width=114) (actual time=9.696..309.735 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
48. 13.208 80.940 ↑ 1.3 58,986 1

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

49. 1.656 29.707 ↑ 1.2 2,535 1

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

  • Hash Cond: (plan_items_1.project_plan_id = project_plans_1.id)
50. 11.646 26.556 ↓ 1.2 7,876 1

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

  • Hash Cond: (plan_items_1.plan_row_uuid = plan_rows_2.uuid)
51. 9.833 9.833 ↓ 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.014..9.833 rows=55,664 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
52. 1.162 5.077 ↓ 1.0 5,762 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 366kB
53. 1.599 3.915 ↓ 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=2.363..3.915 rows=5,762 loops=1)

  • Recheck Cond: (user_id IS NULL)
  • Heap Blocks: exact=430
54. 2.316 2.316 ↓ 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=2.316..2.316 rows=5,762 loops=1)

  • Index Cond: (user_id IS NULL)
55. 0.423 1.495 ↑ 1.0 2,851 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 144kB
56. 1.072 1.072 ↑ 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.008..1.072 rows=2,851 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3,188
57. 38.025 38.025 ↑ 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.006..0.015 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
58. 0.998 2.221 ↑ 1.0 5,812 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 314kB
59. 1.223 1.223 ↑ 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.016..1.223 rows=5,812 loops=1)

60.          

SubPlan (for Hash Join)

61. 58.986 176.958 ↓ 0.0 0 58,986

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

62. 58.986 58.986 ↑ 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.001..0.001 rows=1 loops=58,986)

  • Index Cond: (projects_1_1.client_id = id)
63. 58.986 58.986 ↓ 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.001..0.001 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
64. 12,143.163 12,877.962 ↑ 1.0 703,961 1

Hash Right Join (cost=45,029.23..183,164.61 rows=703,961 width=116) (actual time=670.507..12,877.962 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: 63,215,381
65. 96.201 190.472 ↓ 8.9 44,428 1

HashAggregate (cost=8,529.07..8,603.63 rows=4,971 width=48) (actual time=122.583..190.472 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)
66. 28.123 94.271 ↑ 1.0 44,581 1

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

67. 25.709 66.148 ↑ 1.0 44,581 1

Sort (cost=6,077.00..6,188.46 rows=44,583 width=52) (actual time=60.853..66.148 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: 5,023kB
68. 16.373 40.439 ↑ 1.0 44,581 1

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

  • Hash Cond: (plan_roles_1.plan_row_uuid = plan_rows_3.uuid)
69. 4.475 4.475 ↑ 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.018..4.475 rows=44,581 loops=1)

70. 11.212 19.591 ↑ 1.0 48,106 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,180kB
71. 8.379 8.379 ↑ 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.008..8.379 rows=48,106 loops=1)

72. 189.299 544.327 ↑ 1.0 703,961 1

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

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 62,366kB
73. 240.484 355.028 ↑ 1.0 703,961 1

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

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

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

75. 5.473 19.064 ↑ 1.0 26,435 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,381kB
76. 7.726 13.591 ↑ 1.0 26,435 1

Hash Left Join (cost=202.03..1,496.14 rows=26,435 width=16) (actual time=2.063..13.591 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: 11,144
77. 3.855 3.855 ↑ 1.0 26,435 1

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

78. 0.602 2.010 ↑ 1.0 2,851 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 144kB
79. 1.408 1.408 ↑ 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.008..1.408 rows=2,851 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3,188
80. 0.962 1.825 ↑ 1.0 5,812 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 292kB
81. 0.863 0.863 ↑ 1.0 5,812 1

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

82. 1.221 18.715 ↑ 1.0 4,221 1

Hash (cost=932.12..932.12 rows=4,249 width=40) (actual time=18.715..18.715 rows=4,221 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 370kB
83. 0.835 17.494 ↑ 1.0 4,221 1

Subquery Scan on sa (cost=847.14..932.12 rows=4,249 width=40) (actual time=15.583..17.494 rows=4,221 loops=1)

84. 0.780 16.659 ↑ 1.0 4,221 1

Unique (cost=847.14..889.63 rows=4,249 width=65) (actual time=15.581..16.659 rows=4,221 loops=1)

85. 2.824 15.879 ↑ 1.0 4,249 1

Sort (cost=847.14..857.76 rows=4,249 width=65) (actual time=15.580..15.879 rows=4,249 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
86. 2.776 13.055 ↑ 1.0 4,249 1

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

87. 6.660 10.279 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=56) (actual time=9.998..10.279 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
88. 1.409 3.619 ↑ 1.0 4,249 1

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

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships_1.id)
89. 0.513 0.513 ↑ 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.014..0.513 rows=4,256 loops=1)

90. 0.910 1.697 ↑ 1.0 4,002 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 276kB
91. 0.787 0.787 ↑ 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.009..0.787 rows=4,002 loops=1)

92. 0.067 0.151 ↑ 1.0 354 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
93. 0.084 0.084 ↑ 1.0 354 1

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

94. 8.407 252.635 ↑ 8.5 28,906 1

Subquery Scan on *SELECT* 2_1 (cost=424,372.25..512,020.13 rows=245,158 width=136) (actual time=89.036..252.635 rows=28,906 loops=1)

95. 140.954 244.228 ↑ 8.5 28,906 1

Hash Anti Join (cost=424,372.25..508,955.66 rows=245,158 width=143) (actual time=89.034..244.228 rows=28,906 loops=1)

  • Hash Cond: ((staff_memberships_2.account_id = non_working_intervals_by_dates_1.account_id) AND (staff_memberships_2.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: 1,153,172
96. 16.884 81.742 ↑ 9.5 29,394 1

Hash Join (cost=422,195.45..490,256.83 rows=280,181 width=64) (actual time=67.424..81.742 rows=29,394 loops=1)

  • Hash Cond: (staff_memberships_2.id = vacations_1.staff_membership_id)
  • Join Filter: ((generate_series_3.generate_series >= (COALESCE(staff_membership_activity_links_1.start_date, staff_memberships_2.joined_at))) AND (generate_series_3.generate_series <= (COALESCE(lag((staff_membership_activity_links_1.start_date - 1), 1) OVER (?), staff_memberships_2.archived_at, '3000-01-01'::date))))
  • Rows Removed by Join Filter: 10,924
97. 0.910 13.700 ↑ 1.0 4,221 1

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

98. 4.710 12.790 ↑ 1.0 4,249 1

Sort (cost=836.52..847.14 rows=4,249 width=65) (actual time=12.413..12.790 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
99. 2.779 8.080 ↑ 1.0 4,249 1

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

100. 1.965 5.301 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=44) (actual time=4.971..5.301 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
101. 1.358 3.336 ↑ 1.0 4,249 1

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

  • Hash Cond: (staff_membership_activity_links_1.staff_membership_id = staff_memberships_2.id)
102. 0.405 0.405 ↑ 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.014..0.405 rows=4,256 loops=1)

103. 0.815 1.573 ↑ 1.0 4,002 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 245kB
104. 0.758 0.758 ↑ 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.013..0.758 rows=4,002 loops=1)

105. 8.826 51.158 ↑ 17.5 31,132 1

Hash (cost=414,548.93..414,548.93 rows=544,800 width=44) (actual time=51.158..51.158 rows=31,132 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 10,776kB
106. 18.355 42.332 ↑ 17.5 31,132 1

Nested Loop (cost=0.01..414,548.93 rows=544,800 width=44) (actual time=0.039..42.332 rows=31,132 loops=1)

107. 2.185 2.185 ↑ 1.0 21,792 1

Seq Scan on vacations vacations_1 (cost=0.00..500.92 rows=21,792 width=44) (actual time=0.018..2.185 rows=21,792 loops=1)

108. 21.792 21.792 ↑ 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.001..0.001 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
109. 11.417 21.532 ↑ 1.0 58,975 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,047kB
110. 10.115 10.115 ↑ 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.018..10.115 rows=59,712 loops=1)

111. 0.007 0.168 ↑ 9.8 12 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
112. 0.161 0.161 ↑ 9.8 12 1

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

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 342
113. 93.304 391.666 ↑ 1.0 263,033 1

Hash (cost=23,435.32..23,435.32 rows=272,669 width=44) (actual time=391.666..391.666 rows=263,033 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 24,593kB
114. 28.049 298.362 ↑ 1.0 263,033 1

Append (cost=109.73..23,435.32 rows=272,669 width=44) (actual time=5.189..298.362 rows=263,033 loops=1)

115. 0.099 18.641 ↓ 9.7 474 1

Subquery Scan on *SELECT* 1 (cost=109.73..1,692.67 rows=49 width=44) (actual time=5.188..18.641 rows=474 loops=1)

116. 0.123 18.542 ↓ 9.7 474 1

Nested Loop (cost=109.73..1,692.18 rows=49 width=612) (actual time=5.186..18.542 rows=474 loops=1)

117. 0.123 17.945 ↓ 9.7 474 1

Nested Loop (cost=109.59..1,684.02 rows=49 width=52) (actual time=5.146..17.945 rows=474 loops=1)

118. 0.127 4.342 ↓ 5.0 5 1

Nested Loop (cost=109.17..306.98 rows=1 width=32) (actual time=4.131..4.342 rows=5 loops=1)

  • Join Filter: (clients.brand_id = rate_cards.rateable_id)
  • Rows Removed by Join Filter: 570
119. 0.115 0.880 ↑ 1.0 115 1

WindowAgg (cost=108.61..111.49 rows=115 width=45) (actual time=0.767..0.880 rows=115 loops=1)

120. 0.059 0.765 ↑ 1.0 115 1

Sort (cost=108.61..108.90 rows=115 width=29) (actual time=0.756..0.765 rows=115 loops=1)

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

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

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4,739
122. 0.000 3.335 ↑ 1.0 5 115

Materialize (cost=0.56..185.73 rows=5 width=8) (actual time=0.024..0.029 rows=5 loops=115)

123. 0.034 3.336 ↑ 1.0 5 1

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

124. 0.317 3.177 ↑ 1.0 5 1

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

125. 0.965 0.965 ↑ 1.0 5 1

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

  • Filter: ((rates_type)::text = 'brand'::text)
  • Rows Removed by Filter: 5,805
126. 1.895 1.895 ↑ 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.379..0.379 rows=1 loops=5)

  • Index Cond: (id = pricing_models.project_id)
127. 0.125 0.125 ↑ 1.0 1 5

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

  • Index Cond: (id = projects_2.client_id)
128. 13.480 13.480 ↓ 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=0.642..2.696 rows=95 loops=5)

  • Index Cond: (rate_card_id = rate_cards.id)
129. 0.474 0.474 ↑ 1.0 1 474

Index Only Scan using brands_pkey on brands (cost=0.14..0.17 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=474)

  • Index Cond: (id = clients.brand_id)
  • Heap Fetches: 474
130. 12.485 72.615 ↓ 2.6 91,600 1

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

131. 34.630 60.130 ↓ 2.6 91,600 1

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

  • Hash Cond: (rates_1.rate_card_id = rate_cards_1.id)
132. 19.919 19.919 ↑ 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.016..19.919 rows=174,437 loops=1)

133. 0.169 5.581 ↑ 1.1 657 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 47kB
134. 0.184 5.412 ↑ 1.1 657 1

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

  • Hash Cond: (clients_1.office_id = offices.id)
135. 0.183 3.661 ↑ 1.0 623 1

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

  • Hash Cond: (projects_3.client_id = clients_1.id)
136. 0.878 2.735 ↑ 1.0 623 1

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

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

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

138. 0.109 0.872 ↑ 1.0 623 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
139. 0.763 0.763 ↑ 1.0 623 1

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

  • Filter: ((rates_type)::text = 'office'::text)
  • Rows Removed by Filter: 5,187
140. 0.325 0.743 ↑ 1.0 1,849 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 89kB
141. 0.418 0.418 ↑ 1.0 1,849 1

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

142. 0.119 1.567 ↑ 1.1 450 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 35kB
143. 0.136 1.448 ↑ 1.1 450 1

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

  • Hash Cond: (rate_cards_1.rateable_id = offices.id)
144. 0.353 1.145 ↑ 1.0 489 1

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

145. 0.201 0.792 ↑ 1.0 489 1

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

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

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

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 4,365
147. 0.080 0.167 ↑ 1.0 441 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
148. 0.087 0.087 ↑ 1.0 441 1

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

149. 19.496 91.712 ↑ 2.2 94,593 1

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

150. 45.167 72.216 ↑ 2.2 94,593 1

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

  • Hash Cond: (rates_2.rate_card_id = rate_cards_2.id)
151. 17.146 17.146 ↑ 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.009..17.146 rows=174,437 loops=1)

152. 0.846 9.903 ↑ 1.0 4,004 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 299kB
153. 0.938 9.057 ↑ 1.0 4,004 1

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

  • Hash Cond: (projects_4.client_id = clients_2.id)
154. 0.968 3.908 ↑ 1.0 3,620 1

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

  • Hash Cond: (pricing_models_2.project_id = projects_4.id)
155. 0.893 0.893 ↑ 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.013..0.893 rows=3,621 loops=1)

  • Filter: ((rates_type)::text = 'client'::text)
  • Rows Removed by Filter: 2,189
156. 0.888 2.047 ↑ 1.0 5,812 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 292kB
157. 1.159 1.159 ↑ 1.0 5,812 1

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

158. 0.486 4.211 ↑ 1.0 1,820 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 123kB
159. 0.472 3.725 ↑ 1.0 1,820 1

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

  • Hash Cond: (rate_cards_2.rateable_id = clients_2.id)
160. 1.371 2.777 ↑ 1.0 1,821 1

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

161. 0.730 1.406 ↑ 1.0 1,821 1

Sort (cost=203.29..207.84 rows=1,821 width=29) (actual time=1.273..1.406 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
162. 0.676 0.676 ↑ 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.006..0.676 rows=1,821 loops=1)

  • Filter: ((rateable_type)::text = 'Client'::text)
  • Rows Removed by Filter: 3,033
163. 0.259 0.476 ↑ 1.0 1,849 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 82kB
164. 0.217 0.217 ↑ 1.0 1,849 1

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

165. 11.694 87.345 ↓ 2.4 76,366 1

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

166. 51.291 75.651 ↓ 2.4 76,366 1

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

  • Hash Cond: (rates_3.rate_card_id = rate_cards_3.id)
167. 16.612 16.612 ↑ 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.010..16.612 rows=174,437 loops=1)

168. 0.554 7.748 ↓ 2.4 1,591 1

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

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 110kB
169. 0.978 7.194 ↓ 2.4 1,591 1

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

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

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

171. 0.417 5.611 ↓ 2.4 1,591 1

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

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 110kB
172. 0.541 5.194 ↓ 2.4 1,591 1

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

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
173. 1.671 3.489 ↑ 1.0 2,429 1

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

174. 1.056 1.818 ↑ 1.0 2,429 1

Sort (cost=241.26..247.33 rows=2,429 width=29) (actual time=1.639..1.818 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
175. 0.762 0.762 ↑ 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.008..0.762 rows=2,429 loops=1)

  • Filter: ((rateable_type)::text = 'PricingModel'::text)
  • Rows Removed by Filter: 2,425
176. 0.277 1.164 ↑ 1.0 1,561 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 77kB
177. 0.887 0.887 ↑ 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.017..0.887 rows=1,561 loops=1)

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 4,249
178. 0.640 1.290 ↑ 1.0 3,048 1

Hash (cost=68.48..68.48 rows=3,048 width=24) (actual time=1.290..1.290 rows=3,048 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 199kB
179. 0.650 0.650 ↑ 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.021..0.650 rows=3,048 loops=1)

180. 0.708 1.278 ↑ 1.0 3,048 1

Hash (cost=68.48..68.48 rows=3,048 width=24) (actual time=1.278..1.278 rows=3,048 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 199kB
181. 0.570 0.570 ↑ 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.570 rows=3,048 loops=1)

182. 0.083 0.184 ↓ 1.2 359 1

Hash (cost=10.13..10.13 rows=306 width=26) (actual time=0.184..0.184 rows=359 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 28kB
183. 0.101 0.101 ↓ 1.2 359 1

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

  • Filter: (((discountable_type)::text = 'User'::text) OR ((discountable_type)::text = 'Epic'::text))
  • Rows Removed by Filter: 50
Planning time : 57.774 ms
Execution time : 28,355.852 ms