explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PDSw

Settings
# exclusive inclusive rows x rows loops node
1. 7,668.486 66,478.843 ↓ 2.3 838,440 1

Merge Left Join (cost=3,312,860.45..3,320,679.19 rows=360,070 width=140) (actual time=55,768.461..66,478.843 rows=838,440 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: 40293124
  • Buffers: shared hit=4190769 read=6480
2. 686.527 55,840.107 ↓ 2.3 838,440 1

Sort (cost=3,312,615.59..3,313,515.77 rows=360,070 width=136) (actual time=55,766.677..55,840.107 rows=838,440 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1"".currency
  • Sort Method: quicksort Memory: 168806kB
  • Buffers: shared hit=4190731 read=6480
3. 318.998 55,153.580 ↓ 2.3 838,440 1

Merge Left Join (cost=3,204,030.76..3,279,384.88 rows=360,070 width=136) (actual time=54,024.997..55,153.580 rows=838,440 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: 647094
  • Buffers: shared hit=4190731 read=6480
4. 564.429 54,787.369 ↓ 2.3 838,352 1

Merge Left Join (cost=3,204,008.00..3,261,934.73 rows=360,070 width=132) (actual time=54,024.602..54,787.369 rows=838,352 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: 243485
  • Buffers: shared hit=4190727 read=6480
5. 641.955 53,450.025 ↓ 2.3 838,352 1

Sort (cost=3,157,177.08..3,158,077.26 rows=360,070 width=104) (actual time=53,353.156..53,450.025 rows=838,352 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: 142470kB
  • Buffers: shared hit=4182047 read=6480
6. 255.390 52,808.070 ↓ 2.3 838,352 1

Hash Join (cost=2,268,999.86..3,123,946.37 rows=360,070 width=104) (actual time=41,280.717..52,808.070 rows=838,352 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
  • Buffers: shared hit=4182047 read=6480
7. 149.823 52,552.424 ↓ 1.0 1,104,421 1

Append (cost=2,268,981.19..3,110,256.09 rows=1,080,211 width=104) (actual time=41,279.009..52,552.424 rows=1,104,421 loops=1)

  • Buffers: shared hit=4182036 read=6480
8. 7,403.993 51,401.790 ↓ 1.8 1,087,210 1

Merge Left Join (cost=2,268,981.19..2,772,726.18 rows=590,704 width=104) (actual time=41,279.008..51,401.790 rows=1,087,210 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: 35834555
  • Buffers: shared hit=3063884 read=6480
9. 908.341 41,210.736 ↓ 1.8 1,087,210 1

Sort (cost=2,259,415.21..2,260,891.97 rows=590,704 width=153) (actual time=41,050.919..41,210.736 rows=1,087,210 loops=1)

  • Sort Key: "*SELECT* 1_1".project_plan_id
  • Sort Method: quicksort Memory: 227026kB
  • Buffers: shared hit=3062895 read=6480
10. 8,095.788 40,302.395 ↓ 1.8 1,087,210 1

Merge Left Join (cost=2,197,347.05..2,202,790.10 rows=590,704 width=153) (actual time=29,005.476..40,302.395 rows=1,087,210 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: 42394908
  • Buffers: shared hit=3062895 read=6480
11. 1,088.079 29,097.506 ↓ 1.8 1,087,210 1

Sort (cost=2,197,102.19..2,198,578.95 rows=590,704 width=153) (actual time=29,003.312..29,097.506 rows=1,087,210 loops=1)

  • Sort Key: staff_memberships_1.account_id, staff_memberships_1.currency
  • Sort Method: quicksort Memory: 227634kB
  • Buffers: shared hit=3062857 read=6480
12. 358.349 28,009.427 ↓ 1.8 1,087,210 1

Hash Left Join (cost=2,119,038.00..2,140,477.08 rows=590,704 width=153) (actual time=26,725.865..28,009.427 rows=1,087,210 loops=1)

  • Hash Cond: (projects.account_id = accounts_1.id)
  • 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 Filter: 13596
  • Buffers: shared hit=3062857 read=6480
13. 650.098 27,650.915 ↑ 1.6 1,100,806 1

Merge Left Join (cost=2,119,019.03..2,135,772.46 rows=1,754,565 width=148) (actual time=26,725.668..27,650.915 rows=1,100,806 loops=1)

  • Merge Cond: ((projects.account_id = staff_memberships_1.account_id) AND ("*SELECT* 1_1".user_id = staff_memberships_1.user_id))
  • Join Filter: ((""*SELECT* 1_1"".date >= (COALESCE(staff_membership_activity_links.start_date, staff_memberships_1.joined_at))) AND (""*SELECT* 1_1"".date <= (COALESCE(lag((staff_membership_activity_links.start_date - 1), 1) OVER (?), staff_memberships_1.archived_at, '3000-01-01'::date))))
  • Rows Removed by Join Filter: 258899
  • Buffers: shared hit=3062846 read=6480
14. 1,328.847 26,877.889 ↑ 1.6 1,100,806 1

Sort (cost=2,118,171.89..2,122,558.31 rows=1,754,565 width=120) (actual time=26,709.468..26,877.889 rows=1,100,806 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1_1"".user_id
  • Sort Method: quicksort Memory: 198687kB
  • Buffers: shared hit=3062761 read=6480
15. 390.726 25,549.042 ↑ 1.6 1,100,806 1

Hash Join (cost=1,673,625.69..1,936,199.97 rows=1,754,565 width=120) (actual time=2,075.017..25,549.042 rows=1,100,806 loops=1)

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
  • Buffers: shared hit=3062761 read=6480
16. 220.906 25,154.553 ↑ 1.1 1,597,377 1

Append (cost=1,673,396.92..1,913,816.46 rows=1,754,565 width=116) (actual time=1,897.731..25,154.553 rows=1,597,377 loops=1)

  • Buffers: shared hit=3062663 read=6480
17. 324.000 24,404.096 ↑ 1.2 893,416 1

Result (cost=1,673,396.92..1,866,783.57 rows=1,050,604 width=116) (actual time=1,897.730..24,404.096 rows=893,416 loops=1)

  • Buffers: shared hit=3059527
18. 116.804 24,080.096 ↑ 1.2 893,416 1

Append (cost=1,673,396.92..1,853,651.02 rows=1,050,604 width=140) (actual time=1,897.726..24,080.096 rows=893,416 loops=1)

  • Buffers: shared hit=3059527
19. 132.083 2,700.827 ↑ 1.5 661,732 1

Subquery Scan on *SELECT* 1_1 (cost=1,673,396.92..1,716,730.33 rows=968,516 width=114) (actual time=1,897.725..2,700.827 rows=661,732 loops=1)

  • Buffers: shared hit=2465
20. 188.087 2,568.744 ↑ 1.5 661,732 1

Merge Left Join (cost=1,673,396.92..1,702,202.59 rows=968,516 width=134) (actual time=1,897.723..2,568.744 rows=661,732 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. 215.022 2,246.761 ↑ 1.5 661,732 1

Merge Anti Join (cost=1,066,799.64..1,080,109.02 rows=968,516 width=66) (actual time=1,897.720..2,246.761 rows=661,732 loops=1)

  • Merge Cond: ((generate_series.generate_series = non_working_intervals_by_dates.date) AND (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
22. 1,286.765 1,980.477 ↑ 1.8 669,378 1

Sort (cost=1,060,781.65..1,063,848.65 rows=1,226,800 width=66) (actual time=1,897.715..1,980.477 rows=669,378 loops=1)

  • Sort Key: generate_series.generate_series, projects_1.account_id, plan_rows.user_id
  • Sort Method: quicksort Memory: 118708kB
  • Buffers: shared hit=1450
23. 144.999 693.712 ↑ 1.8 669,378 1

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

  • Buffers: shared hit=1450
24. 14.473 118.621 ↑ 1.0 47,788 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.004..0.009 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. 34.522 51.262 ↑ 1.0 59,712 1

Sort (cost=6,017.99..6,167.27 rows=59,712 width=12) (actual time=45.285..51.262 rows=59,712 loops=1)

  • Sort Key: non_working_intervals_by_dates.date, non_working_intervals_by_dates.account_id, non_working_intervals_by_dates.user_id
  • Sort Method: quicksort Memory: 4336kB
  • Buffers: shared hit=684
36. 16.740 16.740 ↑ 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.036..16.740 rows=59,712 loops=1)

  • Buffers: shared hit=684
37. 35.849 133.896 ↑ 15.5 37,179 1

Sort (cost=606,597.28..608,034.78 rows=575,000 width=44) (actual time=127.761..133.896 rows=37,179 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. 5.191 98.047 ↑ 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=73.527..98.047 rows=32,266 loops=1)

  • Buffers: shared hit=331
39. 36.018 92.856 ↑ 17.8 32,266 1

HashAggregate (cost=537,214.32..545,839.32 rows=575,000 width=52) (actual time=73.525..92.856 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. 12.301 56.838 ↑ 134.2 32,422 1

Hash Join (cost=138.05..493,700.50 rows=4,351,382 width=21) (actual time=2.083..56.838 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. 14.822 42.502 ↑ 634.8 34,330 1

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

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

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

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

44. 0.828 2.035 ↑ 1.0 4,002 1

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

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

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

  • Buffers: shared hit=48
46. 280.119 21,262.465 ↓ 2.8 231,684 1

Hash Left Join (cost=1,215.85..130,846.80 rows=82,088 width=114) (actual time=8.546..21,262.465 rows=231,684 loops=1)

  • Hash Cond: (project_plans_1.project_id = projects_1_1.id)
  • Filter: (NOT (SubPlan 1))
  • Rows Removed by Filter: 770
  • Buffers: shared hit=3057062
47. 59.533 291.191 ↓ 1.4 232,454 1

Nested Loop (cost=987.08..127,447.75 rows=164,175 width=58) (actual time=5.741..291.191 rows=232,454 loops=1)

  • Buffers: shared hit=1251
48. 3.918 42.634 ↓ 1.2 7,876 1

Hash Left Join (cost=987.08..2,674.74 rows=6,567 width=58) (actual time=5.717..42.634 rows=7,876 loops=1)

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

Hash Join (cost=785.05..2,455.47 rows=6,567 width=54) (actual time=4.182..37.207 rows=7,876 loops=1)

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

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

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

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

Bitmap Heap Scan on plan_rows plan_rows_1 (cost=108.29..714.07 rows=5,678 width=24) (actual time=0.513..2.472 rows=5,762 loops=1)

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

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

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

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

  • Filter: active
  • Rows Removed by Filter: 3188
  • Buffers: shared hit=106
56. 189.024 189.024 ↓ 1.2 30 7,876

Function Scan on generate_series generate_series_2 (cost=0.01..18.76 rows=25 width=8) (actual time=0.008..0.024 rows=30 loops=7,876)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 12
57. 1.285 2.749 ↑ 1.0 5,812 1

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

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

  • Buffers: shared hit=98
59.          

SubPlan (for Hash Left Join)

60. 0.000 20,688.406 ↓ 0.0 0 232,454

Nested Loop (cost=0.69..90.49 rows=1 width=0) (actual time=0.089..0.089 rows=0 loops=232,454)

  • Join Filter: (non_working_intervals_by_dates_2.office_id = clients_3.office_id)
  • Rows Removed by Join Filter: 1
  • Buffers: shared hit=3055713
61. 232.454 232.454 ↓ 0.0 0 232,454

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=0 loops=232,454)

  • Index Cond: (projects_1_1.client_id = id)
  • Buffers: shared hit=177008
62. 20,527.128 20,527.128 ↑ 3.7 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..82.06 rows=11 width=4) (actual time=0.340..0.348 rows=3 loops=58,986)

  • Index Cond: ((projects_1_1.account_id = account_id) AND (generate_series_2.generate_series = date))
  • Buffers: shared hit=2878705
63. 337.284 529.551 ↑ 1.0 703,961 1

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

  • Hash Cond: (time_logs.epic_id = epics.id)
  • Buffers: shared hit=3136 read=6480
64. 167.017 167.017 ↑ 1.0 703,961 1

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

  • Buffers: shared hit=2675 read=6480
65. 6.726 25.250 ↑ 1.0 26,435 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1381kB
  • Buffers: shared hit=461
66. 8.986 18.524 ↑ 1.0 26,435 1

Hash Left Join (cost=202.03..1,496.14 rows=26,435 width=16) (actual time=1.830..18.524 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
67. 7.748 7.748 ↑ 1.0 26,435 1

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

  • Buffers: shared hit=355
68. 0.540 1.790 ↑ 1.0 2,851 1

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

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

  • Filter: active
  • Rows Removed by Filter: 3188
  • Buffers: shared hit=106
70. 1.219 3.763 ↑ 1.0 5,812 1

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

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

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

  • Buffers: shared hit=98
72. 103.970 122.928 ↓ 295.4 1,255,026 1

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

  • Buffers: shared hit=85
73. 1.932 18.958 ↑ 1.0 4,218 1

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

  • Buffers: shared hit=85
74. 4.265 17.026 ↑ 1.0 4,246 1

Sort (cost=847.14..857.76 rows=4,249 width=65) (actual time=16.187..17.026 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
75. 3.956 12.761 ↑ 1.0 4,249 1

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

  • Buffers: shared hit=85
76. 2.774 8.805 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=56) (actual time=8.349..8.805 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
77. 1.922 6.031 ↑ 1.0 4,249 1

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

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

  • Buffers: shared hit=37
79. 1.474 2.923 ↑ 1.0 4,002 1

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

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

  • Buffers: shared hit=48
81. 0.059 0.163 ↑ 1.0 354 1

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

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

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

  • Buffers: shared hit=11
83. 3,108.301 3,109.101 ↓ 14,133.4 43,078,540 1

Sort (cost=244.86..252.48 rows=3,048 width=24) (actual time=2.010..3,109.101 rows=43,078,540 loops=1)

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

  • Buffers: shared hit=38
85. 2,577.217 2,787.061 ↓ 7,397.9 36,774,966 1

Sort (cost=9,565.98..9,578.40 rows=4,971 width=48) (actual time=228.075..2,787.061 rows=36,774,966 loops=1)

  • Sort Key: pa.project_plan_id
  • Sort Method: quicksort Memory: 5645kB
  • Buffers: shared hit=989
86. 7.247 209.844 ↓ 8.9 44,428 1

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

  • Buffers: shared hit=989
87. 79.562 202.597 ↓ 8.9 44,428 1

HashAggregate (cost=9,136.50..9,211.06 rows=4,971 width=48) (actual time=157.788..202.597 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
88. 34.645 123.035 ↑ 1.0 44,581 1

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

  • Buffers: shared hit=989
89. 31.374 88.390 ↑ 1.0 44,581 1

Sort (cost=6,572.98..6,684.43 rows=44,583 width=60) (actual time=81.522..88.390 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
90. 22.176 57.016 ↑ 1.0 44,581 1

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

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

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

  • Buffers: shared hit=440
92. 13.873 24.295 ↑ 1.0 48,106 1

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

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

  • Buffers: shared hit=549
94. 8.347 1,000.811 ↑ 28.4 17,211 1

Subquery Scan on *SELECT* 2_1 (cost=1,111.30..326,221.82 rows=489,507 width=104) (actual time=16.727..1,000.811 rows=17,211 loops=1)

  • Buffers: shared hit=1118152
95. 15.002 992.464 ↑ 28.4 17,211 1

Nested Loop (cost=1,111.30..320,102.98 rows=489,507 width=111) (actual time=16.724..992.464 rows=17,211 loops=1)

  • Buffers: shared hit=1118152
96. 9.457 29.742 ↑ 1.3 11,020 1

Hash Join (cost=1,110.31..1,860.29 rows=14,010 width=60) (actual time=16.629..29.742 rows=11,020 loops=1)

  • Hash Cond: (vacations_1.staff_membership_id = staff_membership_activity_links_1.staff_membership_id)
  • Buffers: shared hit=405
97. 3.713 3.713 ↑ 1.0 21,792 1

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

  • Buffers: shared hit=283
98. 0.361 16.572 ↑ 1.3 1,591 1

Hash (cost=1,083.71..1,083.71 rows=2,128 width=16) (actual time=16.571..16.572 rows=1,591 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 109kB
  • Buffers: shared hit=122
99. 0.790 16.211 ↑ 1.3 1,591 1

Hash Join (cost=969.31..1,083.71 rows=2,128 width=16) (actual time=15.030..16.211 rows=1,591 loops=1)

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

  • Buffers: shared hit=37
101. 0.457 15.007 ↓ 8.6 1,591 1

Hash (cost=967.01..967.01 rows=184 width=4) (actual time=15.007..15.007 rows=1,591 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 72kB
  • Buffers: shared hit=85
102. 0.798 14.550 ↓ 8.6 1,591 1

HashAggregate (cost=965.17..967.01 rows=184 width=4) (actual time=14.320..14.550 rows=1,591 loops=1)

  • Group Key: staff_activities_with_dates.link_id
  • Buffers: shared hit=85
103. 1.522 13.752 ↓ 3.4 1,591 1

Subquery Scan on staff_activities_with_dates (cost=836.52..963.99 rows=472 width=4) (actual time=10.797..13.752 rows=1,591 loops=1)

  • Filter: ((CURRENT_DATE >= staff_activities_with_dates.start_date) AND (CURRENT_DATE <= staff_activities_with_dates.end_date))
  • Rows Removed by Filter: 2630
  • Buffers: shared hit=85
104. 1.041 12.230 ↑ 1.0 4,221 1

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

  • Buffers: shared hit=85
105. 3.127 11.189 ↑ 1.0 4,249 1

Sort (cost=836.52..847.14 rows=4,249 width=613) (actual time=10.787..11.189 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
106. 2.865 8.062 ↑ 1.0 4,249 1

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

  • Buffers: shared hit=85
107. 2.059 5.197 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=28) (actual time=4.857..5.197 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
108. 1.169 3.138 ↑ 1.0 4,249 1

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

  • Hash Cond: (staff_membership_activity_links_2.staff_membership_id = staff_memberships_3.id)
  • Buffers: shared hit=85
109. 0.379 0.379 ↑ 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.379 rows=4,256 loops=1)

  • Buffers: shared hit=37
110. 0.851 1.590 ↑ 1.0 4,002 1

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

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

  • Buffers: shared hit=48
112. 226.715 947.720 ↑ 1.0 2 11,020

Nested Loop Left Join (cost=0.98..22.43 rows=2 width=28) (actual time=0.049..0.086 rows=2 loops=11,020)

  • 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: 68
  • Buffers: shared hit=1117747
113. 97.520 462.840 ↓ 2.0 2 11,020

Nested Loop Anti Join (cost=0.70..20.82 rows=1 width=24) (actual time=0.032..0.042 rows=2 loops=11,020)

  • Join Filter: (generate_series_3.generate_series = non_working_intervals_by_dates_1.date)
  • Rows Removed by Join Filter: 59
  • Buffers: shared hit=726244
114. 11.020 66.120 ↓ 2.0 2 11,020

Nested Loop (cost=0.29..19.42 rows=1 width=24) (actual time=0.005..0.006 rows=2 loops=11,020)

  • Join Filter: (vacations_1.staff_membership_id = staff_memberships_2.id)
  • Buffers: shared hit=33060
115. 22.040 22.040 ↑ 1.0 1 11,020

Index Scan using memberships_pkey on staff_memberships staff_memberships_2 (cost=0.28..0.35 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=11,020)

  • Index Cond: (id = staff_membership_activity_links_1.staff_membership_id)
  • Buffers: shared hit=33060
116. 33.060 33.060 ↑ 12.5 2 11,020

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=11,020)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 0
117. 299.200 299.200 ↓ 18.5 37 17,600

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.37 rows=2 width=12) (actual time=0.003..0.017 rows=37 loops=17,600)

  • Index Cond: ((account_id = staff_memberships_2.account_id) AND (user_id = staff_memberships_2.user_id))
  • Heap Fetches: 645522
  • Buffers: shared hit=693184
118. 258.165 258.165 ↓ 1.4 44 17,211

Index Scan using exchange_rates_by_months_idx on exchange_rates_by_months cost_exchange_rates_1 (cost=0.28..0.97 rows=32 width=24) (actual time=0.003..0.015 rows=44 loops=17,211)

  • Index Cond: ((account_id = staff_memberships_2.account_id) AND ((currency)::text = (staff_memberships_2.currency)::text))
  • Buffers: shared hit=391503
119. 0.005 0.256 ↑ 9.8 12 1

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

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

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

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 342
  • Buffers: shared hit=11
121. 425.562 772.915 ↓ 4.3 1,160,408 1

Sort (cost=46,830.92..47,512.59 rows=272,669 width=44) (actual time=671.422..772.915 rows=1,160,408 loops=1)

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

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

  • Buffers: shared hit=8680
123. 0.102 23.244 ↓ 9.7 474 1

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

  • Buffers: shared hit=2602
124. 0.168 23.142 ↓ 9.7 474 1

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

  • Merge Cond: (clients.brand_id = brands.id)
  • Buffers: shared hit=2602
125. 0.192 22.876 ↓ 9.7 474 1

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

  • Buffers: shared hit=2601
126. 0.012 2.839 ↓ 5.0 5 1

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

  • Merge Cond: (rate_cards.rateable_id = clients.brand_id)
  • Buffers: shared hit=144
127. 0.061 1.164 ↑ 28.8 4 1

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

  • Buffers: shared hit=44
128. 0.057 1.103 ↑ 23.0 5 1

Sort (cost=108.61..108.90 rows=115 width=29) (actual time=1.101..1.103 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
129. 1.046 1.046 ↑ 1.0 115 1

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

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4739
  • Buffers: shared hit=44
130. 0.015 1.663 ↑ 1.0 5 1

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

  • Sort Key: clients.brand_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=100
131. 0.006 1.648 ↑ 1.0 5 1

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

  • Buffers: shared hit=100
132. 0.020 1.622 ↑ 1.0 5 1

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

  • Buffers: shared hit=85
133. 1.562 1.562 ↑ 1.0 5 1

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

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

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

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

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

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

  • Index Cond: (rate_card_id = rate_cards.id)
  • Buffers: shared hit=2457
137. 0.041 0.098 ↑ 38.3 3 1

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

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

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

  • Buffers: shared hit=1
139. 14.870 100.334 ↓ 2.6 91,600 1

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

  • Buffers: shared hit=2036
140. 40.810 85.464 ↓ 2.6 91,600 1

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

  • Hash Cond: (rates_1.rate_card_id = rate_cards_1.id)
  • Buffers: shared hit=2036
141. 39.698 39.698 ↑ 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.021..39.698 rows=174,437 loops=1)

  • Buffers: shared hit=1798
142. 0.156 4.956 ↑ 1.1 657 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
  • Buffers: shared hit=238
143. 0.182 4.800 ↑ 1.1 657 1

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

  • Hash Cond: (clients_1.office_id = offices.id)
  • Buffers: shared hit=238
144. 0.164 2.978 ↑ 1.0 623 1

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

  • Hash Cond: (projects_3.client_id = clients_1.id)
  • Buffers: shared hit=190
145. 0.762 2.159 ↑ 1.0 623 1

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

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

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

  • Buffers: shared hit=98
147. 0.101 0.825 ↑ 1.0 623 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
  • Buffers: shared hit=70
148. 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
149. 0.313 0.655 ↑ 1.0 1,849 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 89kB
  • Buffers: shared hit=22
150. 0.342 0.342 ↑ 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.342 rows=1,849 loops=1)

  • Buffers: shared hit=22
151. 0.135 1.640 ↑ 1.1 450 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
  • Buffers: shared hit=48
152. 0.127 1.505 ↑ 1.1 450 1

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

  • Hash Cond: (rate_cards_1.rateable_id = offices.id)
  • Buffers: shared hit=48
153. 0.403 1.201 ↑ 1.0 489 1

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

  • Buffers: shared hit=44
154. 0.222 0.798 ↑ 1.0 489 1

Sort (cost=126.52..127.74 rows=489 width=29) (actual time=0.764..0.798 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
155. 0.576 0.576 ↑ 1.0 489 1

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

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

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

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

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

  • Buffers: shared hit=4
158. 16.817 99.720 ↑ 2.2 94,593 1

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

  • Buffers: shared hit=2032
159. 47.798 82.903 ↑ 2.2 94,593 1

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

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

  • Buffers: shared hit=1798
161. 1.445 13.273 ↑ 1.0 4,004 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 299kB
  • Buffers: shared hit=234
162. 1.556 11.828 ↑ 1.0 4,004 1

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

  • Hash Cond: (projects_4.client_id = clients_2.id)
  • Buffers: shared hit=234
163. 1.515 5.325 ↑ 1.0 3,620 1

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

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

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

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

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

  • Buffers: shared hit=98
167. 0.578 4.947 ↑ 1.0 1,820 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 123kB
  • Buffers: shared hit=66
168. 0.553 4.369 ↑ 1.0 1,820 1

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

  • Hash Cond: (rate_cards_2.rateable_id = clients_2.id)
  • Buffers: shared hit=66
169. 1.446 3.226 ↑ 1.0 1,821 1

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

  • Buffers: shared hit=44
170. 1.011 1.780 ↑ 1.0 1,821 1

Sort (cost=203.29..207.84 rows=1,821 width=29) (actual time=1.586..1.780 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
171. 0.769 0.769 ↑ 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.009..0.769 rows=1,821 loops=1)

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

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

  • Buckets: 2048 Batches: 1 Memory Usage: 82kB
  • Buffers: shared hit=22
173. 0.263 0.263 ↑ 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.263 rows=1,849 loops=1)

  • Buffers: shared hit=22
174. 13.859 89.691 ↓ 2.4 76,366 1

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

  • Buffers: shared hit=2010
175. 45.773 75.832 ↓ 2.4 76,366 1

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

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

  • Buffers: shared hit=1798
177. 0.521 8.619 ↓ 2.4 1,591 1

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

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

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

  • Hash Cond: (projects_5.id = pricing_models_3.project_id)
  • Buffers: shared hit=212
179. 0.707 0.707 ↑ 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.707 rows=5,812 loops=1)

  • Buffers: shared hit=98
180. 0.452 6.288 ↓ 2.4 1,591 1

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

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

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

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
  • Buffers: shared hit=114
182. 1.746 3.870 ↑ 1.0 2,429 1

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

  • Buffers: shared hit=44
183. 1.190 2.124 ↑ 1.0 2,429 1

Sort (cost=241.26..247.33 rows=2,429 width=29) (actual time=1.937..2.124 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
184. 0.934 0.934 ↑ 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.026..0.934 rows=2,429 loops=1)

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

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

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

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 4249
  • Buffers: shared hit=70
187. 47.017 47.213 ↓ 2,151.2 658,252 1

Sort (cost=22.77..23.53 rows=306 width=26) (actual time=0.391..47.213 rows=658,252 loops=1)

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

Seq Scan on discounts (cost=0.00..10.13 rows=306 width=26) (actual time=0.061..0.196 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
189. 2,969.661 2,970.250 ↓ 13,424.2 40,916,815 1

Sort (cost=244.86..252.48 rows=3,048 width=24) (actual time=1.687..2,970.250 rows=40,916,815 loops=1)

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

  • Buffers: shared hit=38
Planning time : 12.748 ms
Execution time : 66,722.924 ms