explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZiPB8 : Optimization for: Optimization for: Optimization for: Optimization for: plan #TeKD; plan #aMrH; plan #0vt9; plan #K3Qt

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 4,560.647 35,341.491 ↓ 1.6 505,163 1

Merge Left Join (cost=3,015,953.67..3,022,625.08 rows=307,233 width=140) (actual time=28,882.098..35,341.491 rows=505,163 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: 23919485
  • Buffers: shared hit=1880437 read=7248
2. 358.845 28,921.836 ↓ 1.6 505,163 1

Sort (cost=3,015,708.81..3,016,476.89 rows=307,233 width=136) (actual time=28,880.583..28,921.836 rows=505,163 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1"".currency
  • Sort Method: quicksort Memory: 83330kB
  • Buffers: shared hit=1880399 read=7248
3. 183.825 28,562.991 ↓ 1.6 505,163 1

Merge Left Join (cost=2,923,409.52..2,987,706.10 rows=307,233 width=136) (actual time=27,890.241..28,562.991 rows=505,163 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: 350171
  • Buffers: shared hit=1880399 read=7248
4. 340.360 28,352.819 ↓ 1.6 505,075 1

Merge Left Join (cost=2,923,386.75..2,972,813.27 rows=307,233 width=132) (actual time=27,890.018..28,352.819 rows=505,075 loops=1)

  • Merge Cond: ((projects.id = ""*SELECT* 1"".project_id) AND ((COALESCE(pa.activity_id, ""*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: 138500
  • Buffers: shared hit=1880395 read=7248
5. 407.108 27,338.237 ↓ 1.6 505,075 1

Sort (cost=2,876,555.83..2,877,323.92 rows=307,233 width=104) (actual time=27,284.822..27,338.237 rows=505,075 loops=1)

  • Sort Key: projects.id, (COALESCE(pa.activity_id, ""*SELECT* 1_1"".activity_id, staff_membership_activity_links.activity_id))
  • Sort Method: quicksort Memory: 83315kB
  • Buffers: shared hit=1871715 read=7248
6. 274.942 26,931.129 ↓ 1.6 505,075 1

Hash Join (cost=2,096,594.17..2,848,553.12 rows=307,233 width=104) (actual time=16,569.999..26,931.129 rows=505,075 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
  • Buffers: shared hit=1871715 read=7248
7. 113.061 26,656.059 ↑ 1.3 724,724 1

Append (cost=2,096,575.50..2,836,869.03 rows=921,699 width=104) (actual time=16,568.615..26,656.059 rows=724,724 loops=1)

  • Buffers: shared hit=1871704 read=7248
8. 7,291.032 26,462.720 ↓ 1.4 722,988 1

Merge Left Join (cost=2,096,575.50..2,521,911.80 rows=498,760 width=104) (actual time=16,568.614..26,462.720 rows=722,988 loops=1)

  • Merge Cond: ("*SELECT* 1_1".project_plan_id = pa.project_plan_id)
  • Join Filter: (("*SELECT* 1_1".date >= pa.start_date) AND ("*SELECT* 1_1".date <= pa.end_date) AND ((pa.plan_row_uuid = "*SELECT* 1_1".plan_row_uuid) OR ("*SELECT* 1_1".plan_row_uuid IS NULL)) AND ((pa.user_id = "*SELECT* 1_1".user_id) OR ("*SELECT* 1_1".user_id IS NULL)) AND ((pa.epic_id = "*SELECT* 1_1".epic_id) OR ((('PlanItem'::text) = 'PlanItem'::text) AND ("*SELECT* 1_1".epic_id IS NULL)) OR ((('PlanItem'::text) = 'TimeLog'::text) AND (pa.epic_id IS NULL))))
  • Rows Removed by Join Filter: 27584634
  • Buffers: shared hit=1865897 read=7248
9. 595.826 16,554.859 ↓ 1.4 722,988 1

Sort (cost=2,087,009.52..2,088,256.42 rows=498,760 width=153) (actual time=16,360.990..16,554.859 rows=722,988 loops=1)

  • Sort Key: "*SELECT* 1_1".project_plan_id
  • Sort Method: quicksort Memory: 127034kB
  • Buffers: shared hit=1864908 read=7248
10. 5,320.434 15,959.033 ↓ 1.4 722,988 1

Merge Left Join (cost=2,035,211.08..2,039,806.91 rows=498,760 width=153) (actual time=8,267.547..15,959.033 rows=722,988 loops=1)

  • Merge Cond: ((staff_memberships_1.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: 27566322
  • Buffers: shared hit=1864908 read=7248
11. 699.632 8,332.055 ↓ 1.4 722,988 1

Sort (cost=2,034,966.21..2,036,213.11 rows=498,760 width=153) (actual time=8,265.700..8,332.055 rows=722,988 loops=1)

  • Sort Key: staff_memberships_1.account_id, staff_memberships_1.currency
  • Sort Method: quicksort Memory: 127034kB
  • Buffers: shared hit=1864870 read=7248
12. 347.089 7,632.423 ↓ 1.4 722,988 1

Hash Left Join (cost=1,969,467.98..1,987,763.60 rows=498,760 width=153) (actual time=6,642.877..7,632.423 rows=722,988 loops=1)

  • Hash Cond: (projects.account_id = accounts_1.id)
  • Filter: (((""*SELECT* 1_1"".vacation_hours)::double precision) < (COALESCE(staff_membership_activity_links.capacity, accounts_1.default_capacity))::double precision)
  • Rows Removed by Filter: 60770
  • Buffers: shared hit=1864870 read=7248
13. 454.157 7,285.141 ↑ 1.9 783,758 1

Merge Left Join (cost=1,969,449.02..1,983,748.74 rows=1,496,281 width=148) (actual time=6,642.671..7,285.141 rows=783,758 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: 173156
  • Buffers: shared hit=1864859 read=7248
14. 922.076 6,741.655 ↑ 1.9 783,758 1

Sort (cost=1,968,601.88..1,972,342.58 rows=1,496,281 width=120) (actual time=6,630.035..6,741.655 rows=783,758 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1_1"".user_id
  • Sort Method: quicksort Memory: 134792kB
  • Buffers: shared hit=1864774 read=7248
15. 296.500 5,819.579 ↑ 1.9 783,758 1

Hash Join (cost=1,644,944.88..1,815,136.19 rows=1,496,281 width=120) (actual time=1,896.224..5,819.579 rows=783,758 loops=1)

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
  • Buffers: shared hit=1864774 read=7248
16. 188.991 5,520.866 ↑ 1.2 1,280,329 1

Append (cost=1,644,716.11..1,796,014.01 rows=1,496,281 width=116) (actual time=1,387.638..5,520.866 rows=1,280,329 loops=1)

  • Buffers: shared hit=1864676 read=7248
17. 283.113 4,864.398 ↑ 1.4 576,368 1

Result (cost=1,644,716.11..1,750,272.55 rows=792,320 width=116) (actual time=1,387.637..4,864.398 rows=576,368 loops=1)

  • Buffers: shared hit=1862308
18. 99.787 4,581.285 ↑ 1.4 576,368 1

Append (cost=1,644,716.11..1,740,368.55 rows=792,320 width=140) (actual time=1,387.632..4,581.285 rows=576,368 loops=1)

  • Buffers: shared hit=1862308
19. 130.996 2,435.111 ↑ 1.8 383,911 1

Subquery Scan on *SELECT* 1_1 (cost=1,644,716.11..1,670,042.32 rows=710,232 width=114) (actual time=1,387.632..2,435.111 rows=383,911 loops=1)

  • Buffers: shared hit=2465
20. 327.807 2,304.115 ↑ 1.8 383,911 1

Merge Left Join (cost=1,644,716.11..1,659,388.84 rows=710,232 width=134) (actual time=1,387.630..2,304.115 rows=383,911 loops=1)

  • Merge Cond: ((generate_series.generate_series = summary_vacations_by_dates.date) AND (projects_1.account_id = summary_vacations_by_dates.account_id) AND (plan_rows.user_id = summary_vacations_by_dates.user_id))
  • Buffers: shared hit=2465
21. 601.366 1,463.218 ↑ 1.8 383,911 1

Sort (cost=1,038,118.83..1,039,894.41 rows=710,232 width=66) (actual time=1,387.624..1,463.218 rows=383,911 loops=1)

  • Sort Key: generate_series.generate_series, projects_1.account_id, plan_rows.user_id
  • Sort Method: quicksort Memory: 66276kB
  • Buffers: shared hit=2134
22. 146.487 861.852 ↑ 1.8 383,911 1

Hash Anti Join (cost=4,168.02..969,091.63 rows=710,232 width=66) (actual time=49.035..861.852 rows=383,911 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))
  • Buffers: shared hit=2134
23. 115.088 690.579 ↑ 1.8 669,378 1

Nested Loop (cost=1,991.21..936,712.49 rows=1,226,800 width=66) (actual time=23.895..690.579 rows=669,378 loops=1)

  • Buffers: shared hit=1450
24. 14.800 97.611 ↑ 1.0 47,788 1

Hash Left Join (cost=1,991.21..4,344.48 rows=49,072 width=66) (actual time=23.864..97.611 rows=47,788 loops=1)

  • Hash Cond: (project_plans.project_id = projects_1.id)
  • Buffers: shared hit=1450
25. 15.102 81.009 ↑ 1.0 47,788 1

Hash Left Join (cost=1,762.44..3,986.81 rows=49,072 width=62) (actual time=22.027..81.009 rows=47,788 loops=1)

  • Hash Cond: (plan_items.project_plan_id = project_plans.id)
  • Buffers: shared hit=1352
26. 29.579 64.620 ↑ 1.0 47,788 1

Hash Join (cost=1,560.41..3,655.88 rows=49,072 width=58) (actual time=20.708..64.620 rows=47,788 loops=1)

  • Hash Cond: (plan_items.plan_row_uuid = plan_rows.uuid)
  • Buffers: shared hit=1246
27. 14.596 14.596 ↓ 1.0 55,664 1

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

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
  • Buffers: shared hit=697
28. 11.058 20.445 ↑ 1.0 42,344 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2816kB
  • Buffers: shared hit=549
29. 9.387 9.387 ↑ 1.0 42,344 1

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

  • Filter: (user_id IS NOT NULL)
  • Rows Removed by Filter: 5762
  • Buffers: shared hit=549
30. 0.384 1.287 ↑ 1.0 2,851 1

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

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

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

  • Filter: active
  • Rows Removed by Filter: 3188
  • Buffers: shared hit=106
32. 0.968 1.802 ↑ 1.0 5,812 1

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

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

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

  • Buffers: shared hit=98
34. 477.880 477.880 ↑ 1.8 14 47,788

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.004..0.010 rows=14 loops=47,788)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 5
35. 11.905 24.786 ↑ 1.0 58,975 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2816kB
  • Buffers: shared hit=684
36. 12.881 12.881 ↑ 1.0 59,712 1

Seq Scan on non_working_intervals_by_dates (cost=0.00..1,281.12 rows=59,712 width=8) (actual time=0.007..12.881 rows=59,712 loops=1)

  • Filter: (date IS NOT NULL)
  • Buffers: shared hit=684
37. 72.681 513.090 ↑ 17.7 32,474 1

Sort (cost=606,597.28..608,034.78 rows=575,000 width=44) (actual time=505.799..513.090 rows=32,474 loops=1)

  • Sort Key: summary_vacations_by_dates.date, summary_vacations_by_dates.account_id, summary_vacations_by_dates.user_id
  • Sort Method: quicksort Memory: 2844kB
  • Buffers: shared hit=331
38. 95.487 440.409 ↑ 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=225.843..440.409 rows=32,266 loops=1)

  • Buffers: shared hit=331
39. 137.056 344.922 ↑ 17.8 32,266 1

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

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
  • Buffers: shared hit=331
40. 13.725 207.866 ↑ 134.2 32,422 1

Hash Join (cost=138.05..493,700.50 rows=4,351,382 width=21) (actual time=1.782..207.866 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
41. 80.755 192.421 ↑ 634.8 34,330 1

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

  • Buffers: shared hit=283
42. 2.706 2.706 ↑ 1.0 21,792 1

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

  • Buffers: shared hit=283
43. 108.960 108.960 ↑ 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.005..0.005 rows=2 loops=21,792)

44. 0.904 1.720 ↑ 1.0 4,002 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 229kB
  • Buffers: shared hit=48
45. 0.816 0.816 ↑ 1.0 4,002 1

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

  • Buffers: shared hit=48
46. 58.716 2,046.387 ↓ 2.3 192,457 1

Nested Loop (cost=1,215.85..65,543.75 rows=82,088 width=114) (actual time=12.242..2,046.387 rows=192,457 loops=1)

  • Buffers: shared hit=1859843
47. 5.458 1,835.546 ↓ 1.9 6,085 1

Hash Left Join (cost=1,215.85..2,942.52 rows=3,284 width=62) (actual time=12.204..1,835.546 rows=6,085 loops=1)

  • Hash Cond: (project_plans_1.project_id = projects_1_1.id)
  • Filter: (NOT (SubPlan 1))
  • Rows Removed by Filter: 1791
  • Buffers: shared hit=1859843
48. 3.746 45.776 ↓ 1.2 7,876 1

Hash Left Join (cost=987.08..2,674.74 rows=6,567 width=62) (actual time=7.805..45.776 rows=7,876 loops=1)

  • Hash Cond: (plan_items_1.project_plan_id = project_plans_1.id)
  • Buffers: shared hit=1251
49. 19.216 39.346 ↓ 1.2 7,876 1

Hash Join (cost=785.05..2,455.47 rows=6,567 width=58) (actual time=5.061..39.346 rows=7,876 loops=1)

  • Hash Cond: (plan_items_1.plan_row_uuid = plan_rows_1.uuid)
  • Buffers: shared hit=1145
50. 15.185 15.185 ↓ 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.022..15.185 rows=55,664 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
  • Buffers: shared hit=697
51. 2.015 4.945 ↓ 1.0 5,762 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 366kB
  • Buffers: shared hit=448
52. 2.514 2.930 ↓ 1.0 5,762 1

Bitmap Heap Scan on plan_rows plan_rows_1 (cost=108.29..714.07 rows=5,678 width=28) (actual time=0.475..2.930 rows=5,762 loops=1)

  • Recheck Cond: (user_id IS NULL)
  • Heap Blocks: exact=430
  • Buffers: shared hit=448
53. 0.416 0.416 ↓ 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.416..0.416 rows=5,762 loops=1)

  • Index Cond: (user_id IS NULL)
  • Buffers: shared hit=18
54. 0.808 2.684 ↑ 1.0 2,851 1

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

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

  • Filter: active
  • Rows Removed by Filter: 3188
  • Buffers: shared hit=106
56. 2.073 4.336 ↑ 1.0 5,812 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 314kB
  • Buffers: shared hit=98
57. 2.263 2.263 ↑ 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.014..2.263 rows=5,812 loops=1)

  • Buffers: shared hit=98
58.          

SubPlan (for Hash Left Join)

59. 913.616 1,779.976 ↓ 0.0 0 7,876

Nested Loop (cost=0.69..1,412.30 rows=67 width=0) (actual time=0.226..0.226 rows=0 loops=7,876)

  • Join Filter: (non_working_intervals_by_dates_2.office_id = clients_3.office_id)
  • Rows Removed by Join Filter: 240
  • Buffers: shared hit=1858494
60. 866.360 866.360 ↑ 9.6 240 7,876

Index Scan using non_working_intervals_by_dates_idx on non_working_intervals_by_dates non_working_intervals_by_dates_2 (cost=0.41..1,369.58 rows=2,295 width=4) (actual time=0.002..0.110 rows=240 loops=7,876)

  • Index Cond: ((projects_1_1.account_id = account_id) AND (date IS NOT NULL))
  • Buffers: shared hit=1853070
61. 0.000 0.000 ↑ 1.0 1 1,890,203

Materialize (cost=0.28..8.30 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1,890,203)

  • Buffers: shared hit=5424
62. 3.616 3.616 ↑ 1.0 1 1,808

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=1,808)

  • Index Cond: (projects_1_1.client_id = id)
  • Buffers: shared hit=5424
63. 152.125 152.125 ↓ 1.3 32 6,085

Function Scan on generate_series generate_series_2 (cost=0.01..18.76 rows=25 width=8) (actual time=0.009..0.025 rows=32 loops=6,085)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 13
64. 315.793 467.477 ↑ 1.0 703,961 1

Hash Join (cost=1,826.58..31,220.45 rows=703,961 width=116) (actual time=19.375..467.477 rows=703,961 loops=1)

  • Hash Cond: (time_logs.epic_id = epics.id)
  • Buffers: shared hit=2368 read=7248
65. 132.367 132.367 ↑ 1.0 703,961 1

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

  • Buffers: shared hit=1907 read=7248
66. 6.190 19.317 ↑ 1.0 26,435 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1381kB
  • Buffers: shared hit=461
67. 8.386 13.127 ↑ 1.0 26,435 1

Hash Left Join (cost=202.03..1,496.14 rows=26,435 width=16) (actual time=1.663..13.127 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
  • Buffers: shared hit=461
68. 3.119 3.119 ↑ 1.0 26,435 1

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

  • Buffers: shared hit=355
69. 0.465 1.622 ↑ 1.0 2,851 1

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

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

  • Filter: active
  • Rows Removed by Filter: 3188
  • Buffers: shared hit=106
71. 1.120 2.213 ↑ 1.0 5,812 1

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

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

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

  • Buffers: shared hit=98
73. 74.530 89.329 ↓ 213.3 906,442 1

Materialize (cost=847.14..942.74 rows=4,249 width=40) (actual time=12.629..89.329 rows=906,442 loops=1)

  • Buffers: shared hit=85
74. 1.564 14.799 ↑ 1.0 4,218 1

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

  • Buffers: shared hit=85
75. 3.459 13.235 ↑ 1.0 4,246 1

Sort (cost=847.14..857.76 rows=4,249 width=65) (actual time=12.625..13.235 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
76. 3.139 9.776 ↑ 1.0 4,249 1

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

  • Buffers: shared hit=85
77. 2.323 6.637 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=56) (actual time=6.319..6.637 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
78. 1.676 4.314 ↑ 1.0 4,249 1

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

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

  • Buffers: shared hit=37
80. 1.190 2.175 ↑ 1.0 4,002 1

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

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

  • Buffers: shared hit=48
82. 0.073 0.193 ↑ 1.0 354 1

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

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

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

  • Buffers: shared hit=11
84. 2,305.961 2,306.544 ↓ 9,180.7 27,982,920 1

Sort (cost=244.86..252.48 rows=3,048 width=24) (actual time=1.628..2,306.544 rows=27,982,920 loops=1)

  • Sort Key: cost_exchange_rates.account_id, cost_exchange_rates.currency
  • Sort Method: quicksort Memory: 335kB
  • Buffers: shared hit=38
85. 0.583 0.583 ↑ 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.019..0.583 rows=3,048 loops=1)

  • Buffers: shared hit=38
86. 2,425.703 2,616.829 ↓ 5,671.2 28,191,725 1

Sort (cost=9,565.98..9,578.40 rows=4,971 width=48) (actual time=207.608..2,616.829 rows=28,191,725 loops=1)

  • Sort Key: pa.project_plan_id
  • Sort Method: quicksort Memory: 5645kB
  • Buffers: shared hit=989
87. 6.879 191.126 ↓ 8.9 44,428 1

Subquery Scan on pa (cost=9,136.50..9,260.77 rows=4,971 width=48) (actual time=142.696..191.126 rows=44,428 loops=1)

  • Buffers: shared hit=989
88. 75.994 184.247 ↓ 8.9 44,428 1

HashAggregate (cost=9,136.50..9,211.06 rows=4,971 width=48) (actual time=142.695..184.247 rows=44,428 loops=1)

  • Group Key: plan_rows_2.project_plan_id, plan_rows_2.epic_id, plan_rows_2.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
89. 33.949 108.253 ↑ 1.0 44,581 1

WindowAgg (cost=6,572.98..7,576.09 rows=44,583 width=68) (actual time=67.299..108.253 rows=44,581 loops=1)

  • Buffers: shared hit=989
90. 28.919 74.304 ↑ 1.0 44,581 1

Sort (cost=6,572.98..6,684.43 rows=44,583 width=60) (actual time=67.286..74.304 rows=44,581 loops=1)

  • Sort Key: plan_roles.plan_row_uuid, plan_roles.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 7154kB
  • Buffers: shared hit=989
91. 21.016 45.385 ↑ 1.0 44,581 1

Hash Join (cost=1,631.38..3,130.23 rows=44,583 width=60) (actual time=19.690..45.385 rows=44,581 loops=1)

  • Hash Cond: (plan_roles.plan_row_uuid = plan_rows_2.uuid)
  • Buffers: shared hit=989
92. 4.771 4.771 ↑ 1.0 44,583 1

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

  • Buffers: shared hit=440
93. 10.973 19.598 ↑ 1.0 48,106 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3180kB
  • Buffers: shared hit=549
94. 8.625 8.625 ↑ 1.0 48,106 1

Seq Scan on plan_rows plan_rows_2 (cost=0.00..1,030.06 rows=48,106 width=28) (actual time=0.007..8.625 rows=48,106 loops=1)

  • Buffers: shared hit=549
95. 0.615 80.278 ↑ 243.6 1,736 1

Subquery Scan on *SELECT* 2_1 (cost=190,575.05..305,361.14 rows=422,939 width=104) (actual time=34.437..80.278 rows=1,736 loops=1)

  • Buffers: shared hit=5807
96. 36.957 79.663 ↑ 243.6 1,736 1

Merge Left Join (cost=190,575.05..300,074.40 rows=422,939 width=111) (actual time=34.434..79.663 rows=1,736 loops=1)

  • Merge Cond: ((staff_memberships_2.account_id = cost_exchange_rates_1.account_id) AND ((staff_memberships_2.currency)::text = (cost_exchange_rates_1.currency)::text))
  • Join Filter: ((cost_exchange_rates_1.start_date <= (generate_series_3.generate_series)::date) AND (cost_exchange_rates_1.end_date >= (generate_series_3.generate_series)::date))
  • Rows Removed by Join Filter: 105620
  • Buffers: shared hit=5807
97. 1.266 32.578 ↑ 126.7 1,736 1

Sort (cost=190,330.19..190,880.06 rows=219,947 width=64) (actual time=32.426..32.578 rows=1,736 loops=1)

  • Sort Key: staff_memberships_2.account_id, staff_memberships_2.currency
  • Sort Method: quicksort Memory: 293kB
  • Buffers: shared hit=5769
98. 0.314 31.312 ↑ 126.7 1,736 1

Nested Loop (cost=2,980.84..170,813.42 rows=219,947 width=64) (actual time=23.213..31.312 rows=1,736 loops=1)

  • Buffers: shared hit=5769
99. 3.035 28.646 ↑ 11.2 784 1

Hash Join (cost=2,980.83..3,651.41 rows=8,798 width=64) (actual time=23.195..28.646 rows=784 loops=1)

  • Hash Cond: (vacations_1.staff_membership_id = staff_memberships_2.id)
  • Buffers: shared hit=5769
100. 2.477 2.477 ↑ 1.0 21,792 1

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

  • Buffers: shared hit=283
101. 0.357 23.134 ↑ 1.5 1,053 1

Hash (cost=2,960.64..2,960.64 rows=1,615 width=32) (actual time=23.134..23.134 rows=1,053 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 82kB
  • Buffers: shared hit=5486
102. 0.061 22.777 ↑ 1.5 1,053 1

Nested Loop Anti Join (cost=1,107.77..2,960.64 rows=1,615 width=32) (actual time=17.683..22.777 rows=1,053 loops=1)

  • Buffers: shared hit=5486
103. 0.596 19.532 ↑ 1.3 1,592 1

Hash Join (cost=1,107.35..1,227.36 rows=2,124 width=32) (actual time=17.653..19.532 rows=1,592 loops=1)

  • Hash Cond: (staff_membership_activity_links_1.staff_membership_id = staff_memberships_2.id)
  • Buffers: shared hit=170
104. 0.884 16.470 ↑ 1.3 1,592 1

Hash Join (cost=969.31..1,083.71 rows=2,128 width=16) (actual time=15.151..16.470 rows=1,592 loops=1)

  • Hash Cond: (staff_membership_activity_links_1.id = staff_activities_with_dates.link_id)
  • Buffers: shared hit=122
105. 0.489 0.489 ↑ 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.006..0.489 rows=4,256 loops=1)

  • Buffers: shared hit=37
106. 0.303 15.097 ↓ 8.7 1,592 1

Hash (cost=967.01..967.01 rows=184 width=4) (actual time=15.096..15.097 rows=1,592 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 72kB
  • Buffers: shared hit=85
107. 0.743 14.794 ↓ 8.7 1,592 1

HashAggregate (cost=965.17..967.01 rows=184 width=4) (actual time=14.530..14.794 rows=1,592 loops=1)

  • Group Key: staff_activities_with_dates.link_id
  • Buffers: shared hit=85
108. 1.485 14.051 ↓ 3.4 1,592 1

Subquery Scan on staff_activities_with_dates (cost=836.52..963.99 rows=472 width=4) (actual time=11.336..14.051 rows=1,592 loops=1)

  • Filter: ((CURRENT_DATE >= staff_activities_with_dates.start_date) AND (CURRENT_DATE <= staff_activities_with_dates.end_date))
  • Rows Removed by Filter: 2629
  • Buffers: shared hit=85
109. 0.911 12.566 ↑ 1.0 4,221 1

Unique (cost=836.52..879.01 rows=4,249 width=613) (actual time=11.329..12.566 rows=4,221 loops=1)

  • Buffers: shared hit=85
110. 3.144 11.655 ↑ 1.0 4,249 1

Sort (cost=836.52..847.14 rows=4,249 width=613) (actual time=11.328..11.655 rows=4,249 loops=1)

  • Sort Key: staff_memberships_3.account_id, staff_memberships_3.user_id, (COALESCE(staff_membership_activity_links_2.start_date, staff_memberships_3.joined_at)), (COALESCE(lag((staff_membership_activity_links_2.start_date - 1), 1) OVER (?), staff_memberships_3.archived_at, '3000-01-01'::date)) DESC
  • Sort Method: quicksort Memory: 524kB
  • Buffers: shared hit=85
111. 3.498 8.511 ↑ 1.0 4,249 1

WindowAgg (cost=484.85..580.45 rows=4,249 width=613) (actual time=4.623..8.511 rows=4,249 loops=1)

  • Buffers: shared hit=85
112. 1.981 5.013 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=28) (actual time=4.611..5.013 rows=4,249 loops=1)

  • Sort Key: staff_membership_activity_links_2.staff_membership_id, staff_membership_activity_links_2.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 524kB
  • Buffers: shared hit=85
113. 1.144 3.032 ↑ 1.0 4,249 1

Hash Join (cost=138.05..228.79 rows=4,249 width=28) (actual time=1.525..3.032 rows=4,249 loops=1)

  • Hash Cond: (staff_membership_activity_links_2.staff_membership_id = staff_memberships_3.id)
  • Buffers: shared hit=85
114. 0.397 0.397 ↑ 1.0 4,256 1

Seq Scan on staff_membership_activity_links staff_membership_activity_links_2 (cost=0.00..79.56 rows=4,256 width=12) (actual time=0.003..0.397 rows=4,256 loops=1)

  • Buffers: shared hit=37
115. 0.776 1.491 ↑ 1.0 4,002 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 229kB
  • Buffers: shared hit=48
116. 0.715 0.715 ↑ 1.0 4,002 1

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

  • Buffers: shared hit=48
117. 1.207 2.466 ↑ 1.0 4,002 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 220kB
  • Buffers: shared hit=48
118. 1.259 1.259 ↑ 1.0 4,002 1

Seq Scan on staff_memberships staff_memberships_2 (cost=0.00..88.02 rows=4,002 width=16) (actual time=0.011..1.259 rows=4,002 loops=1)

  • Buffers: shared hit=48
119. 3.184 3.184 ↓ 0.0 0 1,592

Index Only Scan using non_working_intervals_by_dates_idx on non_working_intervals_by_dates non_working_intervals_by_dates_1 (cost=0.41..1.38 rows=2 width=8) (actual time=0.002..0.002 rows=0 loops=1,592)

  • Index Cond: ((account_id = staff_memberships_2.account_id) AND (user_id = staff_memberships_2.user_id) AND (date IS NOT NULL))
  • Heap Fetches: 539
  • Buffers: shared hit=5316
120. 2.352 2.352 ↑ 12.5 2 784

Function Scan on generate_series generate_series_3 (cost=0.01..18.76 rows=25 width=8) (actual time=0.002..0.003 rows=2 loops=784)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 0
121. 9.559 10.128 ↓ 35.9 109,489 1

Sort (cost=244.86..252.48 rows=3,048 width=24) (actual time=1.896..10.128 rows=109,489 loops=1)

  • Sort Key: cost_exchange_rates_1.account_id, cost_exchange_rates_1.currency
  • Sort Method: quicksort Memory: 335kB
  • Buffers: shared hit=38
122. 0.569 0.569 ↑ 1.0 3,048 1

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

  • Buffers: shared hit=38
123. 0.004 0.128 ↑ 9.8 12 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=11
124. 0.124 0.124 ↑ 9.8 12 1

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

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 342
  • Buffers: shared hit=11
125. 364.002 674.222 ↓ 2.9 795,076 1

Sort (cost=46,830.92..47,512.59 rows=272,669 width=44) (actual time=605.172..674.222 rows=795,076 loops=1)

  • Sort Key: ""*SELECT* 1"".project_id, ""*SELECT* 1"".activity_id
  • Sort Method: quicksort Memory: 32838kB
  • Buffers: shared hit=8680
126. 33.891 310.220 ↑ 1.0 263,033 1

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

  • Buffers: shared hit=8680
127. 0.117 17.469 ↓ 9.7 474 1

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

  • Buffers: shared hit=2602
128. 0.141 17.352 ↓ 9.7 474 1

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

  • Merge Cond: (clients.brand_id = brands.id)
  • Buffers: shared hit=2602
129. 0.179 17.162 ↓ 9.7 474 1

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

  • Buffers: shared hit=2601
130. 0.009 1.463 ↓ 5.0 5 1

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

  • Merge Cond: (rate_cards.rateable_id = clients.brand_id)
  • Buffers: shared hit=144
131. 0.024 0.634 ↑ 28.8 4 1

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

  • Buffers: shared hit=44
132. 0.069 0.610 ↑ 23.0 5 1

Sort (cost=108.61..108.90 rows=115 width=29) (actual time=0.609..0.610 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
133. 0.541 0.541 ↑ 1.0 115 1

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

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4739
  • Buffers: shared hit=44
134. 0.029 0.820 ↑ 1.0 5 1

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

  • Sort Key: clients.brand_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=100
135. 0.005 0.791 ↑ 1.0 5 1

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

  • Buffers: shared hit=100
136. 0.008 0.751 ↑ 1.0 5 1

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

  • Buffers: shared hit=85
137. 0.713 0.713 ↑ 1.0 5 1

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

  • Filter: ((rates_type)::text = 'brand'::text)
  • Rows Removed by Filter: 5805
  • Buffers: shared hit=70
138. 0.030 0.030 ↑ 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.006..0.006 rows=1 loops=5)

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

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

  • Index Cond: (id = projects_2.client_id)
  • Buffers: shared hit=15
140. 15.520 15.520 ↓ 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.905..3.104 rows=95 loops=5)

  • Index Cond: (rate_card_id = rate_cards.id)
  • Buffers: shared hit=2457
141. 0.032 0.049 ↑ 38.3 3 1

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

  • Sort Key: brands.id
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=1
142. 0.017 0.017 ↑ 1.0 115 1

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

  • Buffers: shared hit=1
143. 15.277 84.717 ↓ 2.6 91,600 1

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

  • Buffers: shared hit=2036
144. 42.866 69.440 ↓ 2.6 91,600 1

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

  • Hash Cond: (rates_1.rate_card_id = rate_cards_1.id)
  • Buffers: shared hit=2036
145. 20.463 20.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.011..20.463 rows=174,437 loops=1)

  • Buffers: shared hit=1798
146. 0.187 6.111 ↑ 1.1 657 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
  • Buffers: shared hit=238
147. 0.186 5.924 ↑ 1.1 657 1

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

  • Hash Cond: (clients_1.office_id = offices.id)
  • Buffers: shared hit=238
148. 0.196 4.218 ↑ 1.0 623 1

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

  • Hash Cond: (projects_3.client_id = clients_1.id)
  • Buffers: shared hit=190
149. 1.067 2.912 ↑ 1.0 623 1

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

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

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

  • Buffers: shared hit=98
151. 0.120 1.069 ↑ 1.0 623 1

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

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

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

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

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

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

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

  • Buffers: shared hit=22
155. 0.134 1.520 ↑ 1.1 450 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
  • Buffers: shared hit=48
156. 0.132 1.386 ↑ 1.1 450 1

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

  • Hash Cond: (rate_cards_1.rateable_id = offices.id)
  • Buffers: shared hit=48
157. 0.387 1.089 ↑ 1.0 489 1

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

  • Buffers: shared hit=44
158. 0.155 0.702 ↑ 1.0 489 1

Sort (cost=126.52..127.74 rows=489 width=29) (actual time=0.664..0.702 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
159. 0.547 0.547 ↑ 1.0 489 1

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
  • Buffers: shared hit=4
161. 0.064 0.064 ↑ 1.0 441 1

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

  • Buffers: shared hit=4
162. 16.684 92.513 ↑ 2.2 94,593 1

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

  • Buffers: shared hit=2032
163. 44.522 75.829 ↑ 2.2 94,593 1

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

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

  • Buffers: shared hit=1798
165. 1.182 11.589 ↑ 1.0 4,004 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 299kB
  • Buffers: shared hit=234
166. 1.272 10.407 ↑ 1.0 4,004 1

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

  • Hash Cond: (projects_4.client_id = clients_2.id)
  • Buffers: shared hit=234
167. 1.343 5.044 ↑ 1.0 3,620 1

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

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

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

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

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

  • Buffers: shared hit=98
171. 0.460 4.091 ↑ 1.0 1,820 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 123kB
  • Buffers: shared hit=66
172. 0.465 3.631 ↑ 1.0 1,820 1

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

  • Hash Cond: (rate_cards_2.rateable_id = clients_2.id)
  • Buffers: shared hit=66
173. 1.171 2.661 ↑ 1.0 1,821 1

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

  • Buffers: shared hit=44
174. 0.839 1.490 ↑ 1.0 1,821 1

Sort (cost=203.29..207.84 rows=1,821 width=29) (actual time=1.368..1.490 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
175. 0.651 0.651 ↑ 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.007..0.651 rows=1,821 loops=1)

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

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

  • Buckets: 2048 Batches: 1 Memory Usage: 82kB
  • Buffers: shared hit=22
177. 0.231 0.231 ↑ 1.0 1,849 1

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

  • Buffers: shared hit=22
178. 12.535 81.630 ↓ 2.4 76,366 1

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

  • Buffers: shared hit=2010
179. 39.573 69.095 ↓ 2.4 76,366 1

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

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

  • Buffers: shared hit=1798
181. 0.531 9.183 ↓ 2.4 1,591 1

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

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

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

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

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

  • Buffers: shared hit=98
184. 0.387 6.239 ↓ 2.4 1,591 1

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

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

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

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
  • Buffers: shared hit=114
186. 1.709 3.839 ↑ 1.0 2,429 1

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

  • Buffers: shared hit=44
187. 1.261 2.130 ↑ 1.0 2,429 1

Sort (cost=241.26..247.33 rows=2,429 width=29) (actual time=1.936..2.130 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
188. 0.869 0.869 ↑ 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.869 rows=2,429 loops=1)

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

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

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

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 4249
  • Buffers: shared hit=70
191. 26.259 26.347 ↓ 1,169.2 357,766 1

Sort (cost=22.77..23.53 rows=306 width=26) (actual time=0.220..26.347 rows=357,766 loops=1)

  • Sort Key: discounts.project_id
  • Sort Method: quicksort Memory: 53kB
  • Buffers: shared hit=4
192. 0.088 0.088 ↓ 1.2 359 1

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

  • Filter: (((discountable_type)::text = 'User'::text) OR ((discountable_type)::text = 'Epic'::text))
  • Rows Removed by Filter: 50
  • Buffers: shared hit=4
193. 1,858.528 1,859.008 ↓ 7,966.8 24,282,940 1

Sort (cost=244.86..252.48 rows=3,048 width=24) (actual time=1.436..1,859.008 rows=24,282,940 loops=1)

  • Sort Key: exchange_rates_by_months.account_id, exchange_rates_by_months.currency
  • Sort Method: quicksort Memory: 335kB
  • Buffers: shared hit=38
194. 0.480 0.480 ↑ 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.480 rows=3,048 loops=1)

  • Buffers: shared hit=38
Planning time : 11.608 ms
Execution time : 35,516.184 ms