explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EPBK

Settings
# exclusive inclusive rows x rows loops node
1. 7,343.108 69,595.853 ↓ 2.3 838,440 1

Merge Left Join (cost=3,314,197.51..3,322,016.24 rows=360,070 width=140) (actual time=59,354.249..69,595.853 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=4190998 read=6352, temp read=41828 written=41830
2. 638.959 59,425.665 ↓ 2.3 838,440 1

Sort (cost=3,313,952.65..3,314,852.82 rows=360,070 width=136) (actual time=59,352.440..59,425.665 rows=838,440 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1"".currency
  • Sort Method: quicksort Memory: 168806kB
  • Buffers: shared hit=4190960 read=6352, temp read=41828 written=41830
3. 310.888 58,786.706 ↓ 2.3 838,440 1

Merge Left Join (cost=3,205,367.82..3,280,721.94 rows=360,070 width=136) (actual time=57,701.984..58,786.706 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=4190960 read=6352, temp read=41828 written=41830
4. 541.372 58,428.865 ↓ 2.3 838,352 1

Merge Left Join (cost=3,205,345.05..3,263,271.78 rows=360,070 width=132) (actual time=57,701.740..58,428.865 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=4190956 read=6352, temp read=41828 written=41830
5. 595.475 57,200.384 ↓ 2.3 838,352 1

Sort (cost=3,158,514.14..3,159,414.31 rows=360,070 width=104) (actual time=57,111.898..57,200.384 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=4182276 read=6352, temp read=41828 written=41830
6. 291.617 56,604.909 ↓ 2.3 838,352 1

Hash Join (cost=2,270,336.91..3,125,283.42 rows=360,070 width=104) (actual time=42,410.569..56,604.909 rows=838,352 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
  • Buffers: shared hit=4182276 read=6352, temp read=41828 written=41830
7. 220.707 56,313.167 ↓ 1.5 1,600,992 1

Append (cost=2,270,318.24..3,111,593.15 rows=1,080,211 width=104) (actual time=42,407.792..56,313.167 rows=1,600,992 loops=1)

  • Buffers: shared hit=4182265 read=6352, temp read=41828 written=41830
8. 9,274.037 55,124.231 ↓ 2.7 1,583,781 1

Merge Left Join (cost=2,270,318.24..2,774,063.23 rows=590,704 width=104) (actual time=42,407.791..55,124.231 rows=1,583,781 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: 43669087
  • Buffers: shared hit=3064113 read=6352, temp read=41828 written=41830
9. 1,802.669 42,496.811 ↓ 2.7 1,583,781 1

Sort (cost=2,260,752.27..2,262,229.03 rows=590,704 width=153) (actual time=42,201.378..42,496.811 rows=1,583,781 loops=1)

  • Sort Key: "*SELECT* 1_1".project_plan_id
  • Sort Method: external merge Disk: 166144kB
  • Buffers: shared hit=3063124 read=6352, temp read=41828 written=41830
10. 7,951.846 40,694.142 ↓ 2.7 1,583,781 1

Merge Left Join (cost=2,198,684.11..2,204,127.16 rows=590,704 width=153) (actual time=29,351.676..40,694.142 rows=1,583,781 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=3063124 read=6352, temp read=21060 written=21061
11. 1,996.598 29,705.194 ↓ 2.7 1,583,781 1

Sort (cost=2,198,439.24..2,199,916.00 rows=590,704 width=153) (actual time=29,349.897..29,705.194 rows=1,583,781 loops=1)

  • Sort Key: staff_memberships_1.account_id, staff_memberships_1.currency
  • Sort Method: external merge Disk: 168480kB
  • Buffers: shared hit=3063086 read=6352, temp read=21060 written=21061
12. 453.974 27,708.596 ↓ 2.7 1,583,781 1

Hash Left Join (cost=2,120,375.06..2,141,814.13 rows=590,704 width=153) (actual time=26,181.356..27,708.596 rows=1,583,781 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=3063086 read=6352
13. 771.512 27,254.456 ↑ 1.1 1,597,377 1

Merge Left Join (cost=2,120,356.09..2,137,109.52 rows=1,754,565 width=148) (actual time=26,181.179..27,254.456 rows=1,597,377 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=3063075 read=6352
14. 1,726.816 26,372.925 ↑ 1.1 1,597,377 1

Sort (cost=2,119,508.95..2,123,895.36 rows=1,754,565 width=120) (actual time=26,168.238..26,372.925 rows=1,597,377 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1_1"".user_id
  • Sort Method: quicksort Memory: 268517kB
  • Buffers: shared hit=3062990 read=6352
15. 445.391 24,646.109 ↑ 1.1 1,597,377 1

Hash Left Join (cost=1,673,625.69..1,937,537.03 rows=1,754,565 width=120) (actual time=1,836.915..24,646.109 rows=1,597,377 loops=1)

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
  • Buffers: shared hit=3062990 read=6352
16. 225.005 24,198.503 ↑ 1.1 1,597,377 1

Append (cost=1,673,396.92..1,915,153.52 rows=1,754,565 width=116) (actual time=1,834.674..24,198.503 rows=1,597,377 loops=1)

  • Buffers: shared hit=3062892 read=6352
17. 331.044 23,545.345 ↑ 1.2 893,416 1

Result (cost=1,673,396.92..1,868,120.63 rows=1,050,604 width=116) (actual time=1,834.673..23,545.345 rows=893,416 loops=1)

  • Buffers: shared hit=3059628
18. 116.558 23,214.301 ↑ 1.2 893,416 1

Append (cost=1,673,396.92..1,854,988.08 rows=1,050,604 width=140) (actual time=1,834.667..23,214.301 rows=893,416 loops=1)

  • Buffers: shared hit=3059628
19. 135.137 2,671.766 ↑ 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,834.666..2,671.766 rows=661,732 loops=1)

  • Buffers: shared hit=2465
20. 197.635 2,536.629 ↑ 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,834.663..2,536.629 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. 227.671 2,205.731 ↑ 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,834.658..2,205.731 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,310.596 1,928.231 ↑ 1.8 669,378 1

Sort (cost=1,060,781.65..1,063,848.65 rows=1,226,800 width=66) (actual time=1,834.652..1,928.231 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.176 617.635 ↑ 1.8 669,378 1

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

  • Buffers: shared hit=1450
24. 13.397 91.155 ↑ 1.0 47,788 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.003..0.008 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. 37.928 49.829 ↑ 1.0 59,712 1

Sort (cost=6,017.99..6,167.27 rows=59,712 width=12) (actual time=44.736..49.829 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. 11.901 11.901 ↑ 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.007..11.901 rows=59,712 loops=1)

  • Buffers: shared hit=684
37. 33.132 133.263 ↑ 15.5 37,179 1

Sort (cost=606,597.28..608,034.78 rows=575,000 width=44) (actual time=126.882..133.263 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.451 100.131 ↑ 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=75.876..100.131 rows=32,266 loops=1)

  • Buffers: shared hit=331
39. 37.414 94.680 ↑ 17.8 32,266 1

HashAggregate (cost=537,214.32..545,839.32 rows=575,000 width=52) (actual time=75.875..94.680 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.047 57.266 ↑ 134.2 32,422 1

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

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

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

Seq Scan on vacations (cost=0.00..500.92 rows=21,792 width=17) (actual time=0.019..2.690 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. 1.380 2.650 ↑ 1.0 4,002 1

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

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

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

  • Buffers: shared hit=48
46. 121.094 20,425.977 ↓ 2.8 231,684 1

Hash Left Join (cost=2,062.19..132,183.85 rows=82,088 width=114) (actual time=25.954..20,425.977 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=3057163
47. 60.242 311.526 ↓ 1.4 232,454 1

Nested Loop (cost=1,833.42..128,784.81 rows=164,175 width=58) (actual time=23.608..311.526 rows=232,454 loops=1)

  • Buffers: shared hit=1352
48. 4.172 70.136 ↓ 1.2 7,876 1

Hash Left Join (cost=1,833.41..4,011.80 rows=6,567 width=58) (actual time=23.583..70.136 rows=7,876 loops=1)

  • Hash Cond: (plan_items_1.project_plan_id = project_plans_1.id)
  • Buffers: shared hit=1352
49. 28.317 64.386 ↓ 1.2 7,876 1

Hash Left Join (cost=1,631.38..3,792.52 rows=6,567 width=54) (actual time=21.952..64.386 rows=7,876 loops=1)

  • Hash Cond: (plan_items_1.plan_row_uuid = plan_rows_1.uuid)
  • Filter: (plan_rows_1.user_id IS NULL)
  • Rows Removed by Filter: 47788
  • Buffers: shared hit=1246
50. 14.231 14.231 ↓ 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.015..14.231 rows=55,664 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
  • Buffers: shared hit=697
51. 12.167 21.838 ↑ 1.0 48,106 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3117kB
  • Buffers: shared hit=549
52. 9.671 9.671 ↑ 1.0 48,106 1

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

  • Buffers: shared hit=549
53. 0.464 1.578 ↑ 1.0 2,851 1

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

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

  • Filter: active
  • Rows Removed by Filter: 3188
  • Buffers: shared hit=106
55. 181.148 181.148 ↓ 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.023 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
56. 1.092 2.313 ↑ 1.0 5,812 1

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

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

  • Buffers: shared hit=98
58.          

SubPlan (for Hash Left Join)

59. 57.266 19,991.044 ↓ 0.0 0 232,454

Nested Loop (cost=0.69..90.49 rows=1 width=0) (actual time=0.086..0.086 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
60. 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
61. 19,701.324 19,701.324 ↑ 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.326..0.334 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
62. 308.593 428.153 ↑ 1.0 703,961 1

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

  • Hash Cond: (time_logs.epic_id = epics.id)
  • Buffers: shared hit=3264 read=6352
63. 102.562 102.562 ↑ 1.0 703,961 1

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

  • Buffers: shared hit=2803 read=6352
64. 5.469 16.998 ↑ 1.0 26,435 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1381kB
  • Buffers: shared hit=461
65. 7.594 11.529 ↑ 1.0 26,435 1

Hash Left Join (cost=202.03..1,496.14 rows=26,435 width=16) (actual time=1.270..11.529 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
66. 2.700 2.700 ↑ 1.0 26,435 1

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

  • Buffers: shared hit=355
67. 0.370 1.235 ↑ 1.0 2,851 1

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

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

  • Filter: active
  • Rows Removed by Filter: 3188
  • Buffers: shared hit=106
69. 1.146 2.215 ↑ 1.0 5,812 1

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

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

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

  • Buffers: shared hit=98
71. 94.769 110.019 ↓ 295.4 1,255,026 1

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

  • Buffers: shared hit=85
72. 1.613 15.250 ↑ 1.0 4,218 1

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

  • Buffers: shared hit=85
73. 3.789 13.637 ↑ 1.0 4,246 1

Sort (cost=847.14..857.76 rows=4,249 width=65) (actual time=12.930..13.637 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
74. 3.343 9.848 ↑ 1.0 4,249 1

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

  • Buffers: shared hit=85
75. 2.451 6.505 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=56) (actual time=6.126..6.505 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
76. 1.602 4.054 ↑ 1.0 4,249 1

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

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships_1.id)
  • Buffers: shared hit=85
77. 0.471 0.471 ↑ 1.0 4,256 1

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

  • Buffers: shared hit=37
78. 1.070 1.981 ↑ 1.0 4,002 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 276kB
  • Buffers: shared hit=48
79. 0.911 0.911 ↑ 1.0 4,002 1

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

  • Buffers: shared hit=48
80. 0.061 0.166 ↑ 1.0 354 1

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

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

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

  • Buffers: shared hit=11
82. 3,036.443 3,037.102 ↓ 14,133.4 43,078,540 1

Sort (cost=244.86..252.48 rows=3,048 width=24) (actual time=1.640..3,037.102 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
83. 0.659 0.659 ↑ 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.013..0.659 rows=3,048 loops=1)

  • Buffers: shared hit=38
84. 3,163.906 3,353.383 ↓ 9,048.8 44,981,371 1

Sort (cost=9,565.98..9,578.40 rows=4,971 width=48) (actual time=206.394..3,353.383 rows=44,981,371 loops=1)

  • Sort Key: pa.project_plan_id
  • Sort Method: quicksort Memory: 5645kB
  • Buffers: shared hit=989
85. 6.967 189.477 ↓ 8.9 44,428 1

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

  • Buffers: shared hit=989
86. 75.263 182.510 ↓ 8.9 44,428 1

HashAggregate (cost=9,136.50..9,211.06 rows=4,971 width=48) (actual time=140.382..182.510 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
87. 33.963 107.247 ↑ 1.0 44,581 1

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

  • Buffers: shared hit=989
88. 26.083 73.284 ↑ 1.0 44,581 1

Sort (cost=6,572.98..6,684.43 rows=44,583 width=60) (actual time=66.758..73.284 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
89. 19.358 47.201 ↑ 1.0 44,581 1

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

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

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

  • Buffers: shared hit=440
91. 12.305 22.398 ↑ 1.0 48,106 1

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

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

  • Buffers: shared hit=549
93. 7.791 968.229 ↑ 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=19.258..968.229 rows=17,211 loops=1)

  • Buffers: shared hit=1118152
94. 14.786 960.438 ↑ 28.4 17,211 1

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

  • Buffers: shared hit=1118152
95. 8.706 30.992 ↑ 1.3 11,020 1

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

  • Hash Cond: (vacations_1.staff_membership_id = staff_membership_activity_links_1.staff_membership_id)
  • Buffers: shared hit=405
96. 3.157 3.157 ↑ 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.157 rows=21,792 loops=1)

  • Buffers: shared hit=283
97. 0.476 19.129 ↑ 1.3 1,591 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 109kB
  • Buffers: shared hit=122
98. 1.023 18.653 ↑ 1.3 1,591 1

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

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

  • Buffers: shared hit=37
100. 0.392 17.089 ↓ 8.6 1,591 1

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

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

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

  • Group Key: staff_activities_with_dates.link_id
  • Buffers: shared hit=85
102. 1.889 15.934 ↓ 3.4 1,591 1

Subquery Scan on staff_activities_with_dates (cost=836.52..963.99 rows=472 width=4) (actual time=12.336..15.934 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
103. 1.218 14.045 ↑ 1.0 4,221 1

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

  • Buffers: shared hit=85
104. 3.427 12.827 ↑ 1.0 4,249 1

Sort (cost=836.52..847.14 rows=4,249 width=613) (actual time=12.329..12.827 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
105. 3.453 9.400 ↑ 1.0 4,249 1

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

  • Buffers: shared hit=85
106. 2.051 5.947 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=28) (actual time=5.564..5.947 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
107. 1.494 3.896 ↑ 1.0 4,249 1

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

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

  • Buffers: shared hit=37
109. 0.992 1.929 ↑ 1.0 4,002 1

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

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

  • Buffers: shared hit=48
111. 221.886 914.660 ↑ 1.0 2 11,020

Nested Loop Left Join (cost=0.98..22.43 rows=2 width=28) (actual time=0.047..0.083 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
112. 104.100 451.820 ↓ 2.0 2 11,020

Nested Loop Anti Join (cost=0.70..20.82 rows=1 width=24) (actual time=0.031..0.041 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
113. 11.020 66.120 ↓ 2.0 2 11,020

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

  • Join Filter: (vacations_1.staff_membership_id = staff_memberships_2.id)
  • Buffers: shared hit=33060
114. 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
115. 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
116. 281.600 281.600 ↓ 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.016 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
117. 240.954 240.954 ↓ 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.014 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
118. 0.004 0.125 ↑ 9.8 12 1

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

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

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

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

Sort (cost=46,830.92..47,512.59 rows=272,669 width=44) (actual time=589.820..687.109 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
121. 32.696 299.698 ↑ 1.0 263,033 1

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

  • Buffers: shared hit=8680
122. 0.102 17.809 ↓ 9.7 474 1

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

  • Buffers: shared hit=2602
123. 0.137 17.707 ↓ 9.7 474 1

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

  • Merge Cond: (clients.brand_id = brands.id)
  • Buffers: shared hit=2602
124. 0.151 17.534 ↓ 9.7 474 1

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

  • Buffers: shared hit=2601
125. 0.008 1.413 ↓ 5.0 5 1

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

  • Merge Cond: (rate_cards.rateable_id = clients.brand_id)
  • Buffers: shared hit=144
126. 0.023 0.558 ↑ 28.8 4 1

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

  • Buffers: shared hit=44
127. 0.033 0.535 ↑ 23.0 5 1

Sort (cost=108.61..108.90 rows=115 width=29) (actual time=0.533..0.535 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
128. 0.502 0.502 ↑ 1.0 115 1

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

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4739
  • Buffers: shared hit=44
129. 0.005 0.847 ↑ 1.0 5 1

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

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

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

  • Buffers: shared hit=100
131. 0.008 0.817 ↑ 1.0 5 1

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

  • Buffers: shared hit=85
132. 0.789 0.789 ↑ 1.0 5 1

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

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

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

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

  • Index Cond: (id = projects_2.client_id)
  • Buffers: shared hit=15
135. 15.970 15.970 ↓ 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.827..3.194 rows=95 loops=5)

  • Index Cond: (rate_card_id = rate_cards.id)
  • Buffers: shared hit=2457
136. 0.019 0.036 ↑ 38.3 3 1

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

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

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

  • Buffers: shared hit=1
138. 14.576 80.489 ↓ 2.6 91,600 1

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

  • Buffers: shared hit=2036
139. 41.159 65.913 ↓ 2.6 91,600 1

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

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

  • Buffers: shared hit=1798
141. 0.153 5.521 ↑ 1.1 657 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
  • Buffers: shared hit=238
142. 0.183 5.368 ↑ 1.1 657 1

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

  • Hash Cond: (clients_1.office_id = offices.id)
  • Buffers: shared hit=238
143. 0.166 3.378 ↑ 1.0 623 1

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

  • Hash Cond: (projects_3.client_id = clients_1.id)
  • Buffers: shared hit=190
144. 0.788 2.149 ↑ 1.0 623 1

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

  • Hash Cond: (projects_3.id = pricing_models_1.project_id)
  • Buffers: shared hit=168
145. 0.573 0.573 ↑ 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.573 rows=5,812 loops=1)

  • Buffers: shared hit=98
146. 0.098 0.788 ↑ 1.0 623 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
  • Buffers: shared hit=70
147. 0.690 0.690 ↑ 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.690 rows=623 loops=1)

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

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

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

  • Buffers: shared hit=22
150. 0.165 1.807 ↑ 1.1 450 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
  • Buffers: shared hit=48
151. 0.179 1.642 ↑ 1.1 450 1

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

  • Hash Cond: (rate_cards_1.rateable_id = offices.id)
  • Buffers: shared hit=48
152. 0.503 1.216 ↑ 1.0 489 1

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

  • Buffers: shared hit=44
153. 0.189 0.713 ↑ 1.0 489 1

Sort (cost=126.52..127.74 rows=489 width=29) (actual time=0.663..0.713 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
154. 0.524 0.524 ↑ 1.0 489 1

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

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

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

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

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

  • Buffers: shared hit=4
157. 15.908 88.460 ↑ 2.2 94,593 1

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

  • Buffers: shared hit=2032
158. 42.608 72.552 ↑ 2.2 94,593 1

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

  • Hash Cond: (rates_2.rate_card_id = rate_cards_2.id)
  • Buffers: shared hit=2032
159. 19.543 19.543 ↑ 1.0 174,437 1

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

  • Buffers: shared hit=1798
160. 0.953 10.401 ↑ 1.0 4,004 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 299kB
  • Buffers: shared hit=234
161. 1.035 9.448 ↑ 1.0 4,004 1

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

  • Hash Cond: (projects_4.client_id = clients_2.id)
  • Buffers: shared hit=234
162. 1.155 4.119 ↑ 1.0 3,620 1

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

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

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

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

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

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

  • Buffers: shared hit=98
166. 0.500 4.294 ↑ 1.0 1,820 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 123kB
  • Buffers: shared hit=66
167. 0.484 3.794 ↑ 1.0 1,820 1

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

  • Hash Cond: (rate_cards_2.rateable_id = clients_2.id)
  • Buffers: shared hit=66
168. 1.372 2.695 ↑ 1.0 1,821 1

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

  • Buffers: shared hit=44
169. 0.677 1.323 ↑ 1.0 1,821 1

Sort (cost=203.29..207.84 rows=1,821 width=29) (actual time=1.188..1.323 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
170. 0.646 0.646 ↑ 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.012..0.646 rows=1,821 loops=1)

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

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

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

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

  • Buffers: shared hit=22
173. 12.628 80.244 ↓ 2.4 76,366 1

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

  • Buffers: shared hit=2010
174. 40.090 67.616 ↓ 2.4 76,366 1

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

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

  • Buffers: shared hit=1798
176. 0.431 8.304 ↓ 2.4 1,591 1

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

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

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

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

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

  • Buffers: shared hit=98
179. 0.416 5.963 ↓ 2.4 1,591 1

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

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

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

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
  • Buffers: shared hit=114
181. 1.729 3.881 ↑ 1.0 2,429 1

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

  • Buffers: shared hit=44
182. 1.145 2.152 ↑ 1.0 2,429 1

Sort (cost=241.26..247.33 rows=2,429 width=29) (actual time=1.950..2.152 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
183. 1.007 1.007 ↑ 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.010..1.007 rows=2,429 loops=1)

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

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

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

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

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

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

Seq Scan on discounts (cost=0.00..10.13 rows=306 width=26) (actual time=0.014..0.091 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
188. 2,826.473 2,827.080 ↓ 13,424.2 40,916,815 1

Sort (cost=244.86..252.48 rows=3,048 width=24) (actual time=1.713..2,827.080 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
189. 0.607 0.607 ↑ 1.0 3,048 1

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

  • Buffers: shared hit=38
Planning time : 10.906 ms
Execution time : 69,815.037 ms