explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FSj2 : merge joins

Settings
# exclusive inclusive rows x rows loops node
1. 67,630.204 105,898.383 ↓ 3.9 850,568 1

Merge Left Join (cost=2,161,294.41..2,413,451.51 rows=216,456 width=188) (actual time=37,875.106..105,898.383 rows=850,568 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: 238662
  • Buffers: shared hit=11910905 read=4837
2. 775.248 37,596.032 ↓ 3.9 850,568 1

Sort (cost=2,114,716.45..2,115,257.59 rows=216,456 width=136) (actual time=37,403.225..37,596.032 rows=850,568 loops=1)

  • Sort Key: projects.id, (COALESCE(""*SELECT* 1_1"".activity_id, staff_membership_activity_links.activity_id))
  • Sort Method: quicksort Memory: 144188kB
  • Buffers: shared hit=3137005 read=4837
3. 229.481 36,820.784 ↓ 3.9 850,568 1

Hash Join (cost=1,559,144.04..2,095,534.42 rows=216,456 width=136) (actual time=34,903.132..36,820.784 rows=850,568 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
  • Buffers: shared hit=3137005 read=4837
4. 138.536 36,591.182 ↓ 1.7 1,116,748 1

Append (cost=1,559,125.37..2,087,297.09 rows=649,367 width=136) (actual time=34,902.987..36,591.182 rows=1,116,748 loops=1)

  • Buffers: shared hit=3136994 read=4837
5. 531.409 36,199.349 ↓ 2.7 1,087,842 1

Hash Join (cost=1,559,125.37..1,588,944.20 rows=404,209 width=108) (actual time=34,902.986..36,199.349 rows=1,087,842 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
  • Buffers: shared hit=3125549 read=4837
6. 561.713 35,667.798 ↑ 1.1 1,101,438 1

Merge Left Join (cost=1,559,106.40..1,570,597.35 rows=1,200,621 width=144) (actual time=34,902.824..35,667.798 rows=1,101,438 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: 259092
  • Buffers: shared hit=3125538 read=4837
7. 1,088.238 35,004.633 ↑ 1.1 1,101,438 1

Sort (cost=1,558,259.26..1,561,260.81 rows=1,200,621 width=120) (actual time=34,890.440..35,004.633 rows=1,101,438 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1_1"".user_id
  • Sort Method: quicksort Memory: 184096kB
  • Buffers: shared hit=3125453 read=4837
8. 359.524 33,916.395 ↑ 1.1 1,101,438 1

Hash Join (cost=1,128,530.76..1,437,024.46 rows=1,200,621 width=120) (actual time=2,951.986..33,916.395 rows=1,101,438 loops=1)

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
  • Buffers: shared hit=3125453 read=4837
9. 170.668 33,554.948 ↑ 1.1 1,101,438 1

Append (cost=1,128,301.99..1,421,635.55 rows=1,200,621 width=116) (actual time=2,950.041..33,554.948 rows=1,101,438 loops=1)

  • Buffers: shared hit=3125355 read=4837
10. 195.607 20,813.827 ↑ 1.3 396,845 1

Result (cost=1,128,301.99..1,224,365.18 rows=496,660 width=116) (actual time=2,950.040..20,813.827 rows=396,845 loops=1)

  • Buffers: shared hit=3108173
11. 68.522 20,618.220 ↑ 1.3 396,845 1

Append (cost=1,128,301.99..1,218,156.93 rows=496,660 width=140) (actual time=2,950.032..20,618.220 rows=396,845 loops=1)

  • Buffers: shared hit=3108173
12. 92.100 3,414.676 ↑ 1.4 338,629 1

Subquery Scan on *SELECT* 1_1 (cost=1,128,301.99..1,152,096.36 rows=457,222 width=114) (actual time=2,950.031..3,414.676 rows=338,629 loops=1)

  • Buffers: shared hit=51111
13. 144.959 3,322.576 ↑ 1.4 338,629 1

Merge Left Join (cost=1,128,301.99..1,145,238.03 rows=457,222 width=134) (actual time=2,950.028..3,322.576 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))
  • Buffers: shared hit=51111
14. 164.890 2,972.384 ↑ 1.4 338,629 1

Merge Anti Join (cost=521,704.72..528,289.97 rows=457,222 width=66) (actual time=2,751.632..2,972.384 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))
  • Buffers: shared hit=50780
15. 1,552.446 2,738.724 ↑ 1.7 346,275 1

Sort (cost=515,686.72..517,131.10 rows=577,750 width=66) (actual time=2,689.624..2,738.724 rows=346,275 loops=1)

  • Sort Key: projects_1.account_id, generate_series.generate_series, plan_rows.user_id
  • Sort Method: quicksort Memory: 60983kB
  • Buffers: shared hit=50096
16. 260.362 1,186.278 ↑ 1.7 346,275 1

Merge Left Join (cost=12,927.40..460,395.80 rows=577,750 width=66) (actual time=351.019..1,186.278 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
  • Buffers: shared hit=50089
17. 100.043 586.565 ↑ 1.7 346,275 1

Nested Loop (cost=3,970.91..445,051.49 rows=577,750 width=70) (actual time=59.960..586.565 rows=346,275 loops=1)

  • Buffers: shared hit=49100
18. 25.267 132.116 ↓ 1.2 27,262 1

Merge Join (cost=3,970.91..5,961.49 rows=23,110 width=70) (actual time=59.936..132.116 rows=27,262 loops=1)

  • Merge Cond: (plan_rows.uuid = plan_items.plan_row_uuid)
  • Buffers: shared hit=49100
19. 39.982 39.982 ↓ 1.0 42,344 1

Index Scan using plan_rows_uuid_key on plan_rows (cost=0.29..1,531.44 rows=42,328 width=28) (actual time=0.006..39.982 rows=42,344 loops=1)

  • Filter: (user_id IS NOT NULL)
  • Rows Removed by Filter: 5762
  • Buffers: shared hit=48199
20. 26.280 66.867 ↓ 1.1 29,797 1

Sort (cost=3,970.00..4,035.66 rows=26,265 width=58) (actual time=59.902..66.867 rows=29,797 loops=1)

  • Sort Key: plan_items.plan_row_uuid
  • Sort Method: quicksort Memory: 4959kB
  • Buffers: shared hit=901
21. 9.156 40.587 ↓ 1.1 29,797 1

Hash Join (cost=430.80..2,042.04 rows=26,265 width=58) (actual time=3.710..40.587 rows=29,797 loops=1)

  • Hash Cond: (project_plans.project_id = projects_1.id)
  • Buffers: shared hit=901
22. 15.351 29.459 ↓ 1.1 29,797 1

Hash Join (cost=202.03..1,744.27 rows=26,265 width=54) (actual time=1.694..29.459 rows=29,797 loops=1)

  • Hash Cond: (plan_items.project_plan_id = project_plans.id)
  • Buffers: shared hit=803
23. 12.450 12.450 ↓ 1.0 55,664 1

Seq Scan on plan_items (cost=0.00..1,396.10 rows=55,634 width=46) (actual time=0.007..12.450 rows=55,664 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
  • Buffers: shared hit=697
24. 0.496 1.658 ↑ 1.0 2,851 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 144kB
  • Buffers: shared hit=106
25. 1.162 1.162 ↑ 1.0 2,851 1

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

  • Filter: active
  • Rows Removed by Filter: 3188
  • Buffers: shared hit=106
26. 1.043 1.972 ↑ 1.0 5,812 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 292kB
  • Buffers: shared hit=98
27. 0.929 0.929 ↑ 1.0 5,812 1

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

  • Buffers: shared hit=98
28. 354.406 354.406 ↑ 1.9 13 27,262

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.005..0.013 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
29. 74.445 339.351 ↓ 77.7 384,529 1

Sort (cost=8,956.49..8,968.86 rows=4,949 width=36) (actual time=291.045..339.351 rows=384,529 loops=1)

  • Sort Key: pa.plan_row_uuid
  • Sort Method: quicksort Memory: 5007kB
  • Buffers: shared hit=989
30. 12.056 264.906 ↓ 9.0 44,428 1

Subquery Scan on pa (cost=8,529.07..8,652.79 rows=4,949 width=36) (actual time=189.825..264.906 rows=44,428 loops=1)

  • Buffers: shared hit=989
31. 110.026 252.850 ↓ 9.0 44,428 1

HashAggregate (cost=8,529.07..8,603.30 rows=4,949 width=48) (actual time=189.823..252.850 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)
  • Buffers: shared hit=989
32. 49.566 142.824 ↑ 1.0 44,581 1

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

  • Buffers: shared hit=989
33. 35.884 93.258 ↑ 1.0 44,581 1

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

  • Sort Key: plan_roles.plan_row_uuid, plan_roles.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 5023kB
  • Buffers: shared hit=989
34. 23.915 57.374 ↑ 1.0 44,581 1

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

  • Hash Cond: (plan_roles.plan_row_uuid = plan_rows_1.uuid)
  • Buffers: shared hit=989
35. 6.866 6.866 ↑ 1.0 44,581 1

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

  • Buffers: shared hit=440
36. 14.853 26.593 ↑ 1.0 48,106 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3180kB
  • Buffers: shared hit=549
37. 11.740 11.740 ↑ 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.010..11.740 rows=48,106 loops=1)

  • Buffers: shared hit=549
38. 52.691 68.770 ↑ 1.0 59,679 1

Sort (cost=6,017.99..6,167.27 rows=59,712 width=12) (actual time=61.998..68.770 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
  • Buffers: shared hit=684
39. 16.079 16.079 ↑ 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.020..16.079 rows=59,712 loops=1)

  • Buffers: shared hit=684
40. 63.133 205.233 ↑ 15.5 37,180 1

Sort (cost=606,597.28..608,034.78 rows=575,000 width=44) (actual time=198.384..205.233 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
  • Buffers: shared hit=331
41. 8.032 142.100 ↑ 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=107.846..142.100 rows=32,266 loops=1)

  • Buffers: shared hit=331
42. 50.659 134.068 ↑ 17.8 32,266 1

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

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
  • Buffers: shared hit=331
43. 19.866 83.409 ↑ 134.2 32,422 1

Hash Join (cost=138.05..493,700.50 rows=4,351,382 width=21) (actual time=2.727..83.409 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
  • Buffers: shared hit=331
44. 13.507 60.866 ↑ 634.8 34,330 1

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

  • Buffers: shared hit=283
45. 3.775 3.775 ↑ 1.0 21,792 1

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

  • Buffers: shared hit=283
46. 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)

47. 1.411 2.677 ↑ 1.0 4,002 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 229kB
  • Buffers: shared hit=48
48. 1.266 1.266 ↑ 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.266 rows=4,002 loops=1)

  • Buffers: shared hit=48
49. 84.414 17,135.022 ↓ 1.5 58,216 1

Hash Join (cost=1,059.11..63,182.89 rows=39,438 width=114) (actual time=10.586..17,135.022 rows=58,216 loops=1)

  • Hash Cond: (project_plans_1.project_id = project.id)
  • Join Filter: (NOT (SubPlan 1))
  • Rows Removed by Join Filter: 770
  • Buffers: shared hit=3057062
50. 18.480 117.781 ↑ 1.3 58,986 1

Nested Loop (cost=830.34..62,335.05 rows=78,875 width=58) (actual time=6.630..117.781 rows=58,986 loops=1)

  • Buffers: shared hit=1251
51. 2.738 38.461 ↑ 1.2 2,535 1

Hash Join (cost=830.34..2,390.04 rows=3,155 width=58) (actual time=6.605..38.461 rows=2,535 loops=1)

  • Hash Cond: (plan_items_1.project_plan_id = project_plans_1.id)
  • Buffers: shared hit=1251
52. 16.838 33.339 ↓ 1.2 7,876 1

Hash Join (cost=628.31..2,170.46 rows=6,682 width=54) (actual time=4.159..33.339 rows=7,876 loops=1)

  • Hash Cond: (plan_items_1.plan_row_uuid = plan_rows_2.uuid)
  • Buffers: shared hit=1145
53. 12.419 12.419 ↓ 1.0 55,664 1

Seq Scan on plan_items plan_items_1 (cost=0.00..1,396.10 rows=55,634 width=46) (actual time=0.009..12.419 rows=55,664 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
  • Buffers: shared hit=697
54. 1.734 4.082 ↑ 1.0 5,762 1

Hash (cost=556.08..556.08 rows=5,778 width=24) (actual time=4.082..4.082 rows=5,762 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 366kB
  • Buffers: shared hit=448
55. 2.348 2.348 ↑ 1.0 5,762 1

Index Scan using index_plan_rows_on_user_id on plan_rows plan_rows_2 (cost=0.29..556.08 rows=5,778 width=24) (actual time=0.024..2.348 rows=5,762 loops=1)

  • Index Cond: (user_id IS NULL)
  • Buffers: shared hit=448
56. 0.732 2.384 ↑ 1.0 2,851 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 144kB
  • Buffers: shared hit=106
57. 1.652 1.652 ↑ 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.652 rows=2,851 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3188
  • Buffers: shared hit=106
58. 60.840 60.840 ↑ 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.008..0.024 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
59. 1.789 3.845 ↑ 1.0 5,812 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 314kB
  • Buffers: shared hit=98
60. 2.056 2.056 ↑ 1.0 5,812 1

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

  • Buffers: shared hit=98
61.          

SubPlan (for Hash Join)

62. 0.000 16,928.982 ↓ 0.0 0 58,986

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

  • Join Filter: (non_working_intervals_by_dates_2.office_id = clients_3.office_id)
  • Rows Removed by Join Filter: 3
  • Buffers: shared hit=3055713
63. 117.972 117.972 ↑ 1.0 1 58,986

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

  • Index Cond: (project.client_id = id)
  • Buffers: shared hit=177008
64. 16,811.010 16,811.010 ↑ 4.0 3 58,986

Index Scan using non_working_intervals_by_dates_idx on non_working_intervals_by_dates non_working_intervals_by_dates_2 (cost=0.41..42.12 rows=12 width=4) (actual time=0.276..0.285 rows=3 loops=58,986)

  • Index Cond: ((project.account_id = account_id) AND (generate_series_2.generate_series = date))
  • Buffers: shared hit=2878705
65. 11,830.677 12,570.453 ↓ 1.0 704,593 1

Hash Right Join (cost=44,810.95..184,227.65 rows=703,961 width=116) (actual time=681.325..12,570.453 rows=704,593 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 = active_plan.id))))
  • Rows Removed by Join Filter: 62978265
  • Buffers: shared hit=17182 read=4837
66. 90.213 179.487 ↓ 9.0 44,428 1

HashAggregate (cost=8,529.07..8,603.30 rows=4,949 width=48) (actual time=117.426..179.487 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)
  • Buffers: shared hit=989
67. 28.551 89.274 ↑ 1.0 44,581 1

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

  • Buffers: shared hit=989
68. 23.325 60.723 ↑ 1.0 44,581 1

Sort (cost=6,077.00..6,188.46 rows=44,583 width=52) (actual time=55.114..60.723 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
  • Buffers: shared hit=989
69. 15.025 37.398 ↑ 1.0 44,581 1

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

  • Hash Cond: (plan_roles_1.plan_row_uuid = plan_rows_3.uuid)
  • Buffers: shared hit=989
70. 4.155 4.155 ↑ 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.008..4.155 rows=44,581 loops=1)

  • Buffers: shared hit=440
71. 10.179 18.218 ↑ 1.0 48,106 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3180kB
  • Buffers: shared hit=549
72. 8.039 8.039 ↑ 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.006..8.039 rows=48,106 loops=1)

  • Buffers: shared hit=549
73. 189.928 560.289 ↑ 1.0 703,961 1

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

  • Buckets: 1048576 Batches: 1 Memory Usage: 61640kB
  • Buffers: shared hit=16193 read=4837
74. 242.129 370.361 ↑ 1.0 703,961 1

Hash Left Join (cost=1,608.29..27,482.37 rows=703,961 width=40) (actual time=20.557..370.361 rows=703,961 loops=1)

  • Hash Cond: (time_logs.epic_id = epics.id)
  • Buffers: shared hit=16193 read=4837
75. 107.759 107.759 ↑ 1.0 703,961 1

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

  • Buffers: shared hit=4318 read=4837
76. 5.294 20.473 ↑ 1.0 26,435 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1424kB
  • Buffers: shared hit=11875
77. 6.695 15.179 ↑ 1.0 26,435 1

Merge Right Join (cost=0.57..1,277.86 rows=26,435 width=16) (actual time=0.024..15.179 rows=26,435 loops=1)

  • Merge Cond: (active_plan.project_id = epics.project_id)
  • Buffers: shared hit=11875
78. 2.148 2.148 ↑ 1.0 2,851 1

Index Scan using index_project_plans_on_project_id on project_plans active_plan (cost=0.28..220.13 rows=2,851 width=8) (actual time=0.015..2.148 rows=2,851 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3188
  • Buffers: shared hit=3507
79. 6.336 6.336 ↑ 1.0 26,435 1

Index Scan using index_epics_on_project_id on epics (cost=0.29..838.46 rows=26,435 width=12) (actual time=0.005..6.336 rows=26,435 loops=1)

  • Buffers: shared hit=8368
80. 0.999 1.923 ↑ 1.0 5,812 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 292kB
  • Buffers: shared hit=98
81. 0.924 0.924 ↑ 1.0 5,812 1

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

  • Buffers: shared hit=98
82. 86.861 101.452 ↓ 295.6 1,255,839 1

Materialize (cost=847.14..942.74 rows=4,249 width=40) (actual time=12.378..101.452 rows=1,255,839 loops=1)

  • Buffers: shared hit=85
83. 1.553 14.591 ↑ 1.0 4,218 1

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

  • Buffers: shared hit=85
84. 3.740 13.038 ↑ 1.0 4,246 1

Sort (cost=847.14..857.76 rows=4,249 width=65) (actual time=12.374..13.038 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
  • Buffers: shared hit=85
85. 3.730 9.298 ↑ 1.0 4,249 1

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

  • Buffers: shared hit=85
86. 2.061 5.568 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=56) (actual time=5.143..5.568 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
  • Buffers: shared hit=85
87. 1.434 3.507 ↑ 1.0 4,249 1

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

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships_1.id)
  • Buffers: shared hit=85
88. 0.402 0.402 ↑ 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.015..0.402 rows=4,256 loops=1)

  • Buffers: shared hit=37
89. 0.895 1.671 ↑ 1.0 4,002 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 276kB
  • Buffers: shared hit=48
90. 0.776 0.776 ↑ 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.007..0.776 rows=4,002 loops=1)

  • Buffers: shared hit=48
91. 0.059 0.142 ↑ 1.0 354 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
  • Buffers: shared hit=11
92. 0.083 0.083 ↑ 1.0 354 1

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

  • Buffers: shared hit=11
93. 9.366 253.297 ↑ 8.5 28,906 1

Subquery Scan on *SELECT* 2_1 (cost=3,354.66..491,063.97 rows=245,158 width=136) (actual time=39.396..253.297 rows=28,906 loops=1)

  • Buffers: shared hit=11445
94. 130.834 243.931 ↑ 8.5 28,906 1

Hash Anti Join (cost=3,354.66..487,999.49 rows=245,158 width=143) (actual time=39.394..243.931 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
  • Buffers: shared hit=11445
95. 16.794 89.675 ↑ 9.5 29,394 1

Merge Join (cost=1,177.86..467,724.62 rows=280,181 width=64) (actual time=15.869..89.675 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
  • Buffers: shared hit=10761
96. 22.418 54.091 ↑ 17.5 31,132 1

Nested Loop (cost=0.29..414,752.48 rows=544,800 width=44) (actual time=0.030..54.091 rows=31,132 loops=1)

  • Buffers: shared hit=10676
97. 9.881 9.881 ↑ 1.0 21,792 1

Index Scan using index_vacations_on_staff_membership_id on vacations vacations_1 (cost=0.29..704.47 rows=21,792 width=44) (actual time=0.014..9.881 rows=21,792 loops=1)

  • Buffers: shared hit=10676
98. 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
99. 4.624 18.790 ↓ 10.2 43,399 1

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

  • Sort Key: sa.staff_membership_id
  • Sort Method: quicksort Memory: 522kB
  • Buffers: shared hit=85
100. 0.866 14.166 ↑ 1.0 4,221 1

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

  • Buffers: shared hit=85
101. 0.844 13.300 ↑ 1.0 4,221 1

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

  • Buffers: shared hit=85
102. 2.883 12.456 ↑ 1.0 4,249 1

Sort (cost=836.52..847.14 rows=4,249 width=65) (actual time=12.174..12.456 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
  • Buffers: shared hit=85
103. 2.998 9.573 ↑ 1.0 4,249 1

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

  • Buffers: shared hit=85
104. 2.411 6.575 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=44) (actual time=6.235..6.575 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
  • Buffers: shared hit=85
105. 1.678 4.164 ↑ 1.0 4,249 1

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

  • Hash Cond: (staff_membership_activity_links_1.staff_membership_id = staff_memberships_2.id)
  • Buffers: shared hit=85
106. 0.491 0.491 ↑ 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.011..0.491 rows=4,256 loops=1)

  • Buffers: shared hit=37
107. 1.033 1.995 ↑ 1.0 4,002 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 245kB
  • Buffers: shared hit=48
108. 0.962 0.962 ↑ 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.007..0.962 rows=4,002 loops=1)

  • Buffers: shared hit=48
109. 12.270 23.422 ↑ 1.0 58,975 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3047kB
  • Buffers: shared hit=684
110. 11.152 11.152 ↑ 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.017..11.152 rows=59,712 loops=1)

  • Buffers: shared hit=684
111. 0.004 0.121 ↑ 11.8 10 1

Hash (cost=17.20..17.20 rows=118 width=4) (actual time=0.121..0.121 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=11
112. 0.117 0.117 ↑ 11.8 10 1

Seq Scan on accounts (cost=0.00..17.20 rows=118 width=4) (actual time=0.018..0.117 rows=10 loops=1)

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 344
  • Buffers: shared hit=11
113. 431.364 672.147 ↓ 4.2 1,139,297 1

Sort (cost=46,577.97..47,255.45 rows=270,995 width=44) (actual time=471.234..672.147 rows=1,139,297 loops=1)

  • Sort Key: ""*SELECT* 1"".project_id, ""*SELECT* 1"".activity_id
  • Sort Method: quicksort Memory: 32838kB
  • Buffers: shared hit=9940
114. 25.955 240.783 ↑ 1.0 263,033 1

Append (cost=265.86..22,123.50 rows=270,995 width=44) (actual time=2.035..240.783 rows=263,033 loops=1)

  • Buffers: shared hit=9940
115. 0.111 15.240 ↓ 9.7 474 1

Subquery Scan on *SELECT* 1 (cost=265.86..434.35 rows=49 width=44) (actual time=2.034..15.240 rows=474 loops=1)

  • Buffers: shared hit=2603
116. 0.183 15.129 ↓ 9.7 474 1

Merge Join (cost=265.86..433.86 rows=49 width=612) (actual time=2.033..15.129 rows=474 loops=1)

  • Merge Cond: (clients.brand_id = brands.id)
  • Buffers: shared hit=2603
117. 0.160 14.933 ↓ 9.7 474 1

Nested Loop (cost=265.72..1,593.75 rows=49 width=52) (actual time=2.019..14.933 rows=474 loops=1)

  • Buffers: shared hit=2601
118. 0.008 1.228 ↓ 5.0 5 1

Merge Join (cost=265.30..265.60 rows=1 width=32) (actual time=1.205..1.228 rows=5 loops=1)

  • Merge Cond: (rate_cards.rateable_id = clients.brand_id)
  • Buffers: shared hit=144
119. 0.028 0.567 ↑ 28.8 4 1

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

  • Buffers: shared hit=44
120. 0.049 0.539 ↑ 23.0 5 1

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

  • Sort Key: rate_cards.rateable_id, rate_cards.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 33kB
  • Buffers: shared hit=44
121. 0.490 0.490 ↑ 1.0 115 1

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

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4739
  • Buffers: shared hit=44
122. 0.010 0.653 ↑ 1.0 5 1

Sort (cost=156.69..156.70 rows=5 width=8) (actual time=0.650..0.653 rows=5 loops=1)

  • Sort Key: clients.brand_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=100
123. 0.008 0.643 ↑ 1.0 5 1

Nested Loop (cost=0.56..156.63 rows=5 width=8) (actual time=0.358..0.643 rows=5 loops=1)

  • Buffers: shared hit=100
124. 0.005 0.620 ↑ 1.0 5 1

Nested Loop (cost=0.28..155.13 rows=5 width=8) (actual time=0.347..0.620 rows=5 loops=1)

  • Buffers: shared hit=85
125. 0.595 0.595 ↑ 1.0 5 1

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

  • Filter: ((rates_type)::text = 'brand'::text)
  • Rows Removed by Filter: 5805
  • Buffers: shared hit=70
126. 0.020 0.020 ↑ 1.0 1 5

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

  • Index Cond: (id = pricing_models.project_id)
  • Buffers: shared hit=15
127. 0.015 0.015 ↑ 1.0 1 5

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

  • Index Cond: (id = projects_2.client_id)
  • Buffers: shared hit=15
128. 13.545 13.545 ↓ 1.9 95 5

Index Scan using index_rates_on_activity_id_and_rate_card_id on rates (cost=0.42..1,327.66 rows=49 width=28) (actual time=0.702..2.709 rows=95 loops=5)

  • Index Cond: (rate_card_id = rate_cards.id)
  • Buffers: shared hit=2457
129. 0.013 0.013 ↑ 38.3 3 1

Index Only Scan using brands_pkey on brands (cost=0.14..5.17 rows=115 width=4) (actual time=0.011..0.013 rows=3 loops=1)

  • Heap Fetches: 3
  • Buffers: shared hit=2
130. 11.632 64.294 ↓ 2.6 91,600 1

Subquery Scan on *SELECT* 2 (cost=577.01..5,473.99 rows=35,024 width=44) (actual time=11.546..64.294 rows=91,600 loops=1)

  • Buffers: shared hit=2047
131. 32.785 52.662 ↓ 2.6 91,600 1

Hash Join (cost=577.01..5,123.75 rows=35,024 width=612) (actual time=11.545..52.662 rows=91,600 loops=1)

  • Hash Cond: (rates_1.rate_card_id = rate_cards_1.id)
  • Buffers: shared hit=2047
132. 15.111 15.111 ↑ 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.010..15.111 rows=174,437 loops=1)

  • Buffers: shared hit=1798
133. 0.175 4.766 ↑ 1.1 657 1

Hash (cost=568.01..568.01 rows=720 width=24) (actual time=4.766..4.766 rows=657 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
  • Buffers: shared hit=249
134. 0.169 4.591 ↑ 1.1 657 1

Hash Join (cost=372.98..568.01 rows=720 width=24) (actual time=2.833..4.591 rows=657 loops=1)

  • Hash Cond: (clients_1.office_id = offices.id)
  • Buffers: shared hit=249
135. 0.183 3.053 ↑ 1.0 623 1

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

  • Hash Cond: (projects_3.client_id = clients_1.id)
  • Buffers: shared hit=190
136. 0.823 2.253 ↑ 1.0 623 1

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

  • Hash Cond: (projects_3.id = pricing_models_1.project_id)
  • Buffers: shared hit=168
137. 0.604 0.604 ↑ 1.0 5,812 1

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

  • Buffers: shared hit=98
138. 0.102 0.826 ↑ 1.0 623 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
  • Buffers: shared hit=70
139. 0.724 0.724 ↑ 1.0 623 1

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

  • Filter: ((rates_type)::text = 'office'::text)
  • Rows Removed by Filter: 5187
  • Buffers: shared hit=70
140. 0.294 0.617 ↑ 1.0 1,849 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 89kB
  • Buffers: shared hit=22
141. 0.323 0.323 ↑ 1.0 1,849 1

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

  • Buffers: shared hit=22
142. 0.118 1.369 ↑ 1.1 450 1

Hash (cost=152.85..152.85 rows=489 width=28) (actual time=1.368..1.369 rows=450 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
  • Buffers: shared hit=59
143. 0.165 1.251 ↑ 1.1 450 1

Merge Join (cost=126.79..152.85 rows=489 width=28) (actual time=0.666..1.251 rows=450 loops=1)

  • Merge Cond: (rate_cards_1.rateable_id = offices.id)
  • Buffers: shared hit=59
144. 0.320 1.001 ↑ 1.0 489 1

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

  • Buffers: shared hit=44
145. 0.150 0.681 ↑ 1.0 489 1

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

  • Sort Key: rate_cards_1.rateable_id, rate_cards_1.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 63kB
  • Buffers: shared hit=44
146. 0.531 0.531 ↑ 1.0 489 1

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

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 4365
  • Buffers: shared hit=44
147. 0.085 0.085 ↑ 1.0 441 1

Index Only Scan using offices_pkey on offices (cost=0.27..15.39 rows=441 width=4) (actual time=0.009..0.085 rows=441 loops=1)

  • Heap Fetches: 441
  • Buffers: shared hit=15
148. 12.213 72.932 ↑ 2.2 94,593 1

Subquery Scan on *SELECT* 3 (cost=835.12..9,332.83 rows=204,158 width=44) (actual time=10.327..72.932 rows=94,593 loops=1)

  • Buffers: shared hit=3280
149. 34.814 60.719 ↑ 2.2 94,593 1

Hash Join (cost=835.12..7,291.25 rows=204,158 width=612) (actual time=10.326..60.719 rows=94,593 loops=1)

  • Hash Cond: (rates_2.rate_card_id = rate_cards_2.id)
  • Buffers: shared hit=3280
150. 15.614 15.614 ↑ 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..15.614 rows=174,437 loops=1)

  • Buffers: shared hit=1798
151. 1.076 10.291 ↑ 1.0 4,004 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 299kB
  • Buffers: shared hit=1482
152. 1.298 9.215 ↑ 1.0 4,004 1

Hash Join (cost=581.29..782.66 rows=4,197 width=24) (actual time=5.586..9.215 rows=4,004 loops=1)

  • Hash Cond: (projects_4.client_id = clients_2.id)
  • Buffers: shared hit=1482
153. 1.367 4.229 ↑ 1.0 3,620 1

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

  • Hash Cond: (pricing_models_2.project_id = projects_4.id)
  • Buffers: shared hit=168
154. 1.026 1.026 ↑ 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.007..1.026 rows=3,621 loops=1)

  • Filter: ((rates_type)::text = 'client'::text)
  • Rows Removed by Filter: 2189
  • Buffers: shared hit=70
155. 0.804 1.836 ↑ 1.0 5,812 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 292kB
  • Buffers: shared hit=98
156. 1.032 1.032 ↑ 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.032 rows=5,812 loops=1)

  • Buffers: shared hit=98
157. 0.374 3.688 ↑ 1.0 1,820 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 123kB
  • Buffers: shared hit=1314
158. 0.552 3.314 ↑ 1.0 1,820 1

Merge Join (cost=203.56..329.76 rows=1,821 width=28) (actual time=1.090..3.314 rows=1,820 loops=1)

  • Merge Cond: (rate_cards_2.rateable_id = clients_2.id)
  • Buffers: shared hit=1314
159. 1.084 2.265 ↑ 1.0 1,821 1

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

  • Buffers: shared hit=44
160. 0.600 1.181 ↑ 1.0 1,821 1

Sort (cost=203.29..207.84 rows=1,821 width=29) (actual time=1.070..1.181 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
  • Buffers: shared hit=44
161. 0.581 0.581 ↑ 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.581 rows=1,821 loops=1)

  • Filter: ((rateable_type)::text = 'Client'::text)
  • Rows Removed by Filter: 3033
  • Buffers: shared hit=44
162. 0.497 0.497 ↑ 1.0 1,849 1

Index Only Scan using clients_pkey on clients clients_2 (cost=0.28..61.00 rows=1,849 width=4) (actual time=0.009..0.497 rows=1,849 loops=1)

  • Heap Fetches: 1849
  • Buffers: shared hit=1270
163. 10.058 62.362 ↓ 2.4 76,366 1

Subquery Scan on *SELECT* 4 (cost=695.56..5,527.35 rows=31,764 width=44) (actual time=6.021..62.362 rows=76,366 loops=1)

  • Buffers: shared hit=2010
164. 31.440 52.304 ↓ 2.4 76,366 1

Hash Join (cost=695.56..5,209.71 rows=31,764 width=612) (actual time=6.020..52.304 rows=76,366 loops=1)

  • Hash Cond: (rates_3.rate_card_id = rate_cards_3.id)
  • Buffers: shared hit=2010
165. 14.889 14.889 ↑ 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.009..14.889 rows=174,437 loops=1)

  • Buffers: shared hit=1798
166. 0.319 5.975 ↓ 2.4 1,591 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 110kB
  • Buffers: shared hit=212
167. 0.800 5.656 ↓ 2.4 1,591 1

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

  • Hash Cond: (projects_5.id = pricing_models_3.project_id)
  • Buffers: shared hit=212
168. 0.508 0.508 ↑ 1.0 5,812 1

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

  • Buffers: shared hit=98
169. 0.308 4.348 ↓ 2.4 1,591 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 110kB
  • Buffers: shared hit=114
170. 0.407 4.040 ↓ 2.4 1,591 1

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

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
  • Buffers: shared hit=114
171. 1.366 2.739 ↑ 1.0 2,429 1

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

  • Buffers: shared hit=44
172. 0.779 1.373 ↑ 1.0 2,429 1

Sort (cost=241.26..247.33 rows=2,429 width=29) (actual time=1.238..1.373 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
  • Buffers: shared hit=44
173. 0.594 0.594 ↑ 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.006..0.594 rows=2,429 loops=1)

  • Filter: ((rateable_type)::text = 'PricingModel'::text)
  • Rows Removed by Filter: 2425
  • Buffers: shared hit=44
174. 0.218 0.894 ↑ 1.0 1,561 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 77kB
  • Buffers: shared hit=70
175. 0.676 0.676 ↑ 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.007..0.676 rows=1,561 loops=1)

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 4249
  • Buffers: shared hit=70
Planning time : 14.123 ms
Execution time : 106,122.434 ms