explain.depesz.com

PostgreSQL's explain analyze made readable

Result: g3Zv

Settings
# exclusive inclusive rows x rows loops node
1. 179.080 63,839.645 ↓ 3.4 596,906 1

Subquery Scan on report_items_live (cost=851,444.55..857,973.30 rows=174,100 width=128) (actual time=62,953.853..63,839.645 rows=596,906 loops=1)

  • Buffers: shared hit=64947 read=13706, temp read=15120 written=15121
2. 1,738.244 63,660.565 ↓ 3.4 596,906 1

HashAggregate (cost=851,444.55..856,232.30 rows=174,100 width=184) (actual time=62,953.850..63,660.565 rows=596,906 loops=1)

  • Group Key: projects.account_id, projects.id, clients.id, clients.office_id, rates_with_dates.rate_card_id, rates_with_dates.id, ""*SELECT* 1"".project_plan_id, ""*SELECT* 1"".epic_id, (COALESCE(pa.activity_id, ""*SELECT* 1"".activity_id, staff_membership_activity_links.activity_id)), ""*SELECT* 1"".user_id, ""*SELECT* 1"".date, (CASE WHEN ((1) = 2) THEN ""*SELECT* 1"".""time"" WHEN (((""*SELECT* 1"".vacation_user_id)::double precision) IS NOT NULL) THEN GREATEST(((((COALESCE(staff_membership_activity_links.capacity, '0'::numeric))::double precision - COALESCE(((""*SELECT* 1"".vacation_hours)::double precision), (staff_membership_activity_links.capacity)::double precision, '0'::double precision)) * ((""*SELECT* 1"".utilization)::double precision)) / '100'::double precision), '0'::double precision) WHEN (""*SELECT* 1"".user_id IS NULL) THEN (((accounts_1.default_capacity)::double precision * ((""*SELECT* 1"".utilization)::double precision)) / '100'::double precision) ELSE (((COALESCE(staff_membership_activity_links.capacity, '0'::numeric))::double precision * ((""*SELECT* 1"".utilization)::double precision)) / '100'::double precision) END), (1), ""*SELECT* 1"".reportable_id, ""*SELECT* 1"".plan_row_uuid, rates_with_dates.currency, COALESCE(exchange_rates_by_months.rate, '1'::double precision), rates_with_dates.value, staff_membership_activity_links.cost, COALESCE(cost_exchange_rates.rate, '1'::double precision), COALESCE(original_exchange_rates.rate, '1'::double precision), GREATEST((GREATEST(""*SELECT* 1"".updated_at, pa.updated_at, (GREATEST(staff_memberships.updated_at, staff_membership_activity_links.updated_at)))), rates_with_dates.updated_at, rates_with_dates.rate_card_updated_at)
  • Buffers: shared hit=64947 read=13706, temp read=15120 written=15121
3. 8,637.198 61,922.321 ↓ 3.4 596,906 1

Merge Left Join (cost=831,366.44..840,128.05 rows=174,100 width=180) (actual time=37,710.712..61,922.321 rows=596,906 loops=1)

  • Merge Cond: ((projects.account_id = original_exchange_rates.account_id) AND ((rates_with_dates.currency)::text = (original_exchange_rates.currency)::text))
  • Join Filter: ((original_exchange_rates.start_date <= "*SELECT* 1".date) AND (original_exchange_rates.end_date >= "*SELECT* 1".date))
  • Rows Removed by Join Filter: 31275040
  • Buffers: shared hit=64947 read=13706, temp read=15120 written=15121
4. 8,765.728 49,952.673 ↓ 3.4 596,906 1

Merge Left Join (cost=831,273.23..835,654.04 rows=174,100 width=164) (actual time=37,709.551..49,952.673 rows=596,906 loops=1)

  • Merge Cond: ((projects.account_id = exchange_rates_by_months.account_id) AND ((rates_with_dates.currency)::text = (exchange_rates_by_months.currency)::text))
  • Join Filter: ((exchange_rates_by_months.start_date <= "*SELECT* 1".date) AND (exchange_rates_by_months.end_date >= "*SELECT* 1".date))
  • Rows Removed by Join Filter: 31275040
  • Buffers: shared hit=64931 read=13706, temp read=15120 written=15121
5. 773.553 37,793.914 ↓ 3.4 596,906 1

Sort (cost=831,180.02..831,615.27 rows=174,100 width=156) (actual time=37,708.398..37,793.914 rows=596,906 loops=1)

  • Sort Key: projects.account_id, rates_with_dates.currency
  • Sort Method: quicksort Memory: 163698kB
  • Buffers: shared hit=64915 read=13706, temp read=15120 written=15121
6. 313.407 37,020.361 ↓ 3.4 596,906 1

Merge Left Join (cost=804,697.68..816,025.00 rows=174,100 width=156) (actual time=35,858.180..37,020.361 rows=596,906 loops=1)

  • Merge Cond: (projects.id = discounts.project_id)
  • Join Filter: (("*SELECT* 1".date >= discounts.start_date) AND ("*SELECT* 1".date <= discounts.end_date) AND (((discounts.discountable_id = "*SELECT* 1".user_id) AND ((discounts.discountable_type)::text = 'User'::text)) OR ((discounts.discountable_id = "*SELECT* 1".epic_id) AND ((discounts.discountable_type)::text = 'Epic'::text))))
  • Rows Removed by Join Filter: 414717
  • Buffers: shared hit=64915 read=13706, temp read=15120 written=15121
7. 612.587 36,661.206 ↓ 3.4 596,906 1

Merge Left Join (cost=804,676.22..808,099.41 rows=174,100 width=152) (actual time=35,857.852..36,661.206 rows=596,906 loops=1)

  • Merge Cond: ((projects.id = rates_with_dates.project_id) AND ((COALESCE(pa.activity_id, ""*SELECT* 1"".activity_id, staff_membership_activity_links.activity_id)) = rates_with_dates.activity_id))
  • Join Filter: (("*SELECT* 1".date >= rates_with_dates.start_date) AND (("*SELECT* 1".date <= rates_with_dates.end_date) OR (rates_with_dates.end_date IS NULL)))
  • Rows Removed by Join Filter: 229495
  • Buffers: shared hit=64911 read=13706, temp read=15120 written=15121
8. 672.148 35,405.371 ↓ 3.4 596,906 1

Sort (cost=776,107.19..776,542.44 rows=174,100 width=116) (actual time=35,319.721..35,405.371 rows=596,906 loops=1)

  • Sort Key: projects.id, (COALESCE(pa.activity_id, ""*SELECT* 1"".activity_id, staff_membership_activity_links.activity_id))
  • Sort Method: quicksort Memory: 108516kB
  • Buffers: shared hit=61662 read=13706, temp read=15120 written=15121
9. 8,934.609 34,733.223 ↓ 3.4 596,906 1

Merge Left Join (cost=759,523.41..760,952.17 rows=174,100 width=116) (actual time=22,300.161..34,733.223 rows=596,906 loops=1)

  • Merge Cond: ((projects.account_id = cost_exchange_rates.account_id) AND ((staff_memberships.currency)::text = (cost_exchange_rates.currency)::text))
  • Join Filter: ((cost_exchange_rates.start_date <= "*SELECT* 1".date) AND (cost_exchange_rates.end_date >= "*SELECT* 1".date))
  • Rows Removed by Join Filter: 32367257
  • Buffers: shared hit=61662 read=13706, temp read=15120 written=15121
10. 595.496 22,365.940 ↓ 3.4 596,906 1

Sort (cost=759,430.20..759,865.45 rows=174,100 width=624) (actual time=22,298.921..22,365.940 rows=596,906 loops=1)

  • Sort Key: projects.account_id, staff_memberships.currency
  • Sort Method: quicksort Memory: 108516kB
  • Buffers: shared hit=61646 read=13706, temp read=15120 written=15121
11. 319.903 21,770.444 ↓ 3.4 596,906 1

Hash Join (cost=321,159.56..744,275.18 rows=174,100 width=624) (actual time=7,810.952..21,770.444 rows=596,906 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
  • Buffers: shared hit=61646 read=13706, temp read=15120 written=15121
12. 214.774 21,450.423 ↓ 2.1 1,093,075 1

Append (cost=321,141.15..737,645.76 rows=522,299 width=624) (actual time=6,252.769..21,450.423 rows=1,093,075 loops=1)

  • Buffers: shared hit=61635 read=13706, temp read=15120 written=15121
13. 644.998 21,048.766 ↓ 2.1 1,076,373 1

Hash Left Join (cost=321,141.15..490,363.26 rows=522,298 width=83) (actual time=6,252.768..21,048.766 rows=1,076,373 loops=1)

  • Hash Cond: (projects.client_id = clients.id)
  • Buffers: shared hit=5648 read=13706, temp read=15120 written=15121
14. 10,174.010 20,402.765 ↓ 2.1 1,076,373 1

Merge Left Join (cost=321,079.16..470,646.61 rows=522,298 width=137) (actual time=6,251.746..20,402.765 rows=1,076,373 loops=1)

  • Merge Cond: ("*SELECT* 1".project_plan_id = pa.project_plan_id)
  • Join Filter: (("*SELECT* 1".date >= pa.start_date) AND ("*SELECT* 1".date <= pa.end_date) AND ((pa.plan_row_uuid = "*SELECT* 1".plan_row_uuid) OR ("*SELECT* 1".plan_row_uuid IS NULL)) AND ((pa.user_id = "*SELECT* 1".user_id) OR ("*SELECT* 1".user_id IS NULL)) AND ((pa.epic_id = "*SELECT* 1".epic_id) OR (((1) = 1) AND ("*SELECT* 1".epic_id IS NULL)) OR (((1) = 2) AND (pa.epic_id IS NULL))))
  • Rows Removed by Join Filter: 34351586
  • Buffers: shared hit=5627 read=13706, temp read=15120 written=15121
15. 1,725.860 6,497.501 ↓ 2.1 1,076,373 1

Sort (cost=316,937.98..318,243.72 rows=522,298 width=125) (actual time=6,217.703..6,497.501 rows=1,076,373 loops=1)

  • Sort Key: "*SELECT* 1".project_plan_id
  • Sort Method: external merge Disk: 120960kB
  • Buffers: shared hit=5235 read=13706, temp read=15120 written=15121
16. 478.204 4,771.641 ↓ 2.1 1,076,373 1

Hash Left Join (cost=248,375.96..267,333.99 rows=522,298 width=125) (actual time=3,126.493..4,771.641 rows=1,076,373 loops=1)

  • Hash Cond: (projects.account_id = accounts_1.id)
  • Filter: ((((""*SELECT* 1"".vacation_hours)::double precision) IS NULL) OR (((""*SELECT* 1"".vacation_hours)::double precision) < (COALESCE(staff_membership_activity_links.capacity, accounts_1.default_capacity))::double precision))
  • Rows Removed by Filter: 5379
  • Buffers: shared hit=5235 read=13706
17. 846.391 4,293.206 ↑ 1.4 1,081,752 1

Merge Left Join (cost=248,357.27..263,169.79 rows=1,551,379 width=120) (actual time=3,126.249..4,293.206 rows=1,081,752 loops=1)

  • Merge Cond: ((projects.account_id = staff_memberships.account_id) AND ("*SELECT* 1".user_id = staff_memberships.user_id))
  • Join Filter: ((""*SELECT* 1"".date >= (COALESCE(staff_membership_activity_links.start_date, staff_memberships.joined_at))) AND (""*SELECT* 1"".date <= (COALESCE(lag((staff_membership_activity_links.start_date - 1), 1) OVER (?), staff_memberships.archived_at, '3000-01-01'::date))))
  • Rows Removed by Join Filter: 199291
  • Buffers: shared hit=5224 read=13706
18. 1,540.775 3,293.153 ↑ 1.4 1,081,752 1

Sort (cost=247,536.38..251,414.83 rows=1,551,379 width=96) (actual time=3,107.993..3,293.153 rows=1,081,752 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1"".user_id
  • Sort Method: quicksort Memory: 181379kB
  • Buffers: shared hit=5141 read=13706
19. 473.066 1,752.378 ↑ 1.4 1,081,752 1

Hash Join (cost=224.94..88,014.91 rows=1,551,379 width=96) (actual time=2.594..1,752.378 rows=1,081,752 loops=1)

  • Hash Cond: ("*SELECT* 1".project_id = projects.id)
  • Buffers: shared hit=5141 read=13706
20. 279.181 1,276.780 ↑ 1.0 1,551,079 1

Append (cost=0.00..68,200.77 rows=1,551,379 width=92) (actual time=0.049..1,276.780 rows=1,551,079 loops=1)

  • Buffers: shared hit=5044 read=13706
21. 382.921 725.529 ↑ 1.0 862,330 1

Subquery Scan on *SELECT* 1 (cost=0.00..35,626.15 rows=862,630 width=92) (actual time=0.049..725.529 rows=862,330 loops=1)

  • Buffers: shared hit=2304 read=8847
22. 342.608 342.608 ↑ 1.0 862,330 1

Seq Scan on prepared_plan_items ppi (cost=0.00..20,530.13 rows=862,630 width=82) (actual time=0.046..342.608 rows=862,330 loops=1)

  • Filter: (is_non_working_day IS NULL)
  • Rows Removed by Filter: 75583
  • Buffers: shared hit=2304 read=8847
23. 272.070 272.070 ↑ 1.0 688,749 1

Seq Scan on prepared_time_logs ptl (cost=0.00..17,930.24 rows=688,749 width=92) (actual time=0.080..272.070 rows=688,749 loops=1)

  • Filter: (is_non_working_day IS NULL)
  • Buffers: shared hit=2740 read=4859
24. 1.157 2.532 ↑ 1.0 5,686 1

Hash (cost=153.86..153.86 rows=5,686 width=12) (actual time=2.532..2.532 rows=5,686 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 309kB
  • Buffers: shared hit=97
25. 1.375 1.375 ↑ 1.0 5,686 1

Seq Scan on projects (cost=0.00..153.86 rows=5,686 width=12) (actual time=0.006..1.375 rows=5,686 loops=1)

  • Buffers: shared hit=97
26. 132.718 153.662 ↓ 285.8 1,175,756 1

Materialize (cost=820.88..913.45 rows=4,114 width=40) (actual time=18.248..153.662 rows=1,175,756 loops=1)

  • Buffers: shared hit=83
27. 1.926 20.944 ↑ 1.0 4,053 1

Unique (cost=820.88..862.02 rows=4,114 width=65) (actual time=18.233..20.944 rows=4,053 loops=1)

  • Buffers: shared hit=83
28. 4.989 19.018 ↑ 1.0 4,075 1

Sort (cost=820.88..831.17 rows=4,114 width=65) (actual time=18.232..19.018 rows=4,075 loops=1)

  • Sort Key: staff_memberships.account_id, staff_memberships.user_id, (COALESCE(staff_membership_activity_links.start_date, staff_memberships.joined_at)), (COALESCE(lag((staff_membership_activity_links.start_date - 1), 1) OVER (?), staff_memberships.archived_at, '3000-01-01'::date)) DESC
  • Sort Method: quicksort Memory: 771kB
  • Buffers: shared hit=83
29. 4.874 14.029 ↑ 1.0 4,114 1

WindowAgg (cost=471.06..573.91 rows=4,114 width=65) (actual time=8.646..14.029 rows=4,114 loops=1)

  • Buffers: shared hit=83
30. 3.254 9.155 ↑ 1.0 4,114 1

Sort (cost=471.06..481.35 rows=4,114 width=56) (actual time=8.632..9.155 rows=4,114 loops=1)

  • Sort Key: staff_membership_activity_links.staff_membership_id, staff_membership_activity_links.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 771kB
  • Buffers: shared hit=83
31. 2.353 5.901 ↑ 1.0 4,114 1

Hash Join (cost=136.06..224.09 rows=4,114 width=56) (actual time=2.981..5.901 rows=4,114 loops=1)

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships.id)
  • Buffers: shared hit=83
32. 0.637 0.637 ↑ 1.0 4,121 1

Seq Scan on staff_membership_activity_links (cost=0.00..77.21 rows=4,121 width=28) (actual time=0.018..0.637 rows=4,121 loops=1)

  • Buffers: shared hit=36
33. 1.588 2.911 ↑ 1.0 3,958 1

Hash (cost=86.58..86.58 rows=3,958 width=32) (actual time=2.911..2.911 rows=3,958 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 273kB
  • Buffers: shared hit=47
34. 1.323 1.323 ↑ 1.0 3,958 1

Seq Scan on staff_memberships (cost=0.00..86.58 rows=3,958 width=32) (actual time=0.009..1.323 rows=3,958 loops=1)

  • Buffers: shared hit=47
35. 0.090 0.231 ↑ 1.0 342 1

Hash (cost=14.42..14.42 rows=342 width=9) (actual time=0.231..0.231 rows=342 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
  • Buffers: shared hit=11
36. 0.141 0.141 ↑ 1.0 342 1

Seq Scan on accounts accounts_1 (cost=0.00..14.42 rows=342 width=9) (actual time=0.012..0.141 rows=342 loops=1)

  • Buffers: shared hit=11
37. 3,719.429 3,731.254 ↓ 818.5 35,281,794 1

Sort (cost=4,141.18..4,248.94 rows=43,105 width=48) (actual time=34.028..3,731.254 rows=35,281,794 loops=1)

  • Sort Key: pa.project_plan_id
  • Sort Method: quicksort Memory: 5488kB
  • Buffers: shared hit=392
38. 11.825 11.825 ↑ 1.0 43,105 1

Seq Scan on planned_activities_with_dates pa (cost=0.00..823.05 rows=43,105 width=48) (actual time=0.016..11.825 rows=43,105 loops=1)

  • Buffers: shared hit=392
39. 0.464 1.003 ↑ 1.0 1,822 1

Hash (cost=39.22..39.22 rows=1,822 width=8) (actual time=1.003..1.003 rows=1,822 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
  • Buffers: shared hit=21
40. 0.539 0.539 ↑ 1.0 1,822 1

Seq Scan on clients (cost=0.00..39.22 rows=1,822 width=8) (actual time=0.013..0.539 rows=1,822 loops=1)

  • Buffers: shared hit=21
41. 8.462 186.883 ↓ 16,702.0 16,702 1

Subquery Scan on *SELECT* 2 (cost=232,836.32..239,448.02 rows=1 width=83) (actual time=115.490..186.883 rows=16,702 loops=1)

  • Buffers: shared hit=55987
42. 28.282 178.421 ↓ 16,702.0 16,702 1

Merge Right Join (cost=232,836.32..239,448.01 rows=1 width=107) (actual time=115.483..178.421 rows=16,702 loops=1)

  • Merge Cond: ((non_working_intervals_by_dates.account_id = staff_memberships_1.account_id) AND (non_working_intervals_by_dates.user_id = staff_memberships_1.user_id) AND (non_working_intervals_by_dates.date = generate_series.generate_series))
  • Filter: (non_working_intervals_by_dates.id IS NULL)
  • Rows Removed by Filter: 326
  • Buffers: shared hit=55987
43. 32.142 32.142 ↑ 1.0 58,756 1

Index Scan using non_working_intervals_by_dates_idx on non_working_intervals_by_dates (cost=0.41..3,416.17 rows=58,799 width=16) (actual time=0.020..32.142 rows=58,756 loops=1)

  • Buffers: shared hit=55549
44. 37.959 117.997 ↑ 16.0 17,028 1

Sort (cost=232,835.90..233,514.95 rows=271,620 width=49) (actual time=115.435..117.997 rows=17,028 loops=1)

  • Sort Key: staff_memberships_1.account_id, staff_memberships_1.user_id, generate_series.generate_series
  • Sort Method: quicksort Memory: 3163kB
  • Buffers: shared hit=438
45. 5.748 80.038 ↑ 16.0 17,028 1

Nested Loop (cost=1,217.40..208,320.53 rows=271,620 width=49) (actual time=28.105..80.038 rows=17,028 loops=1)

  • Buffers: shared hit=438
46. 8.966 40.606 ↓ 1.0 11,228 1

Hash Join (cost=1,217.39..1,885.52 rows=10,865 width=49) (actual time=28.085..40.606 rows=11,228 loops=1)

  • Hash Cond: (vacations.staff_membership_id = staff_memberships_1.id)
  • Buffers: shared hit=438
47. 3.618 3.618 ↑ 1.0 20,907 1

Seq Scan on vacations (cost=0.00..481.07 rows=20,907 width=29) (actual time=0.019..3.618 rows=20,907 loops=1)

  • Buffers: shared hit=272
48. 0.758 28.022 ↑ 1.3 1,635 1

Hash (cost=1,191.68..1,191.68 rows=2,057 width=32) (actual time=28.022..28.022 rows=1,635 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 137kB
  • Buffers: shared hit=166
49. 0.795 27.264 ↑ 1.3 1,635 1

Hash Join (cost=1,075.31..1,191.68 rows=2,057 width=32) (actual time=24.601..27.264 rows=1,635 loops=1)

  • Hash Cond: (staff_membership_activity_links_1.staff_membership_id = staff_memberships_1.id)
  • Buffers: shared hit=166
50. 1.331 23.913 ↑ 1.3 1,635 1

Hash Join (cost=939.26..1,050.20 rows=2,060 width=16) (actual time=21.981..23.913 rows=1,635 loops=1)

  • Hash Cond: (staff_membership_activity_links_1.id = staff_activities_with_dates.link_id)
  • Buffers: shared hit=119
51. 0.618 0.618 ↑ 1.0 4,121 1

Seq Scan on staff_membership_activity_links staff_membership_activity_links_1 (cost=0.00..77.21 rows=4,121 width=20) (actual time=0.007..0.618 rows=4,121 loops=1)

  • Buffers: shared hit=36
52. 0.468 21.964 ↓ 9.0 1,635 1

Hash (cost=936.98..936.98 rows=182 width=4) (actual time=21.964..21.964 rows=1,635 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 74kB
  • Buffers: shared hit=83
53. 0.971 21.496 ↓ 9.0 1,635 1

HashAggregate (cost=935.16..936.98 rows=182 width=4) (actual time=21.132..21.496 rows=1,635 loops=1)

  • Group Key: staff_activities_with_dates.link_id
  • Buffers: shared hit=83
54. 2.251 20.525 ↓ 3.6 1,635 1

Subquery Scan on staff_activities_with_dates (cost=810.60..934.02 rows=457 width=4) (actual time=16.479..20.525 rows=1,635 loops=1)

  • Filter: ((CURRENT_DATE >= staff_activities_with_dates.start_date) AND (CURRENT_DATE <= staff_activities_with_dates.end_date))
  • Rows Removed by Filter: 2451
  • Buffers: shared hit=83
55. 1.341 18.274 ↑ 1.0 4,086 1

Unique (cost=810.60..851.74 rows=4,114 width=613) (actual time=16.471..18.274 rows=4,086 loops=1)

  • Buffers: shared hit=83
56. 4.211 16.933 ↑ 1.0 4,114 1

Sort (cost=810.60..820.88 rows=4,114 width=613) (actual time=16.470..16.933 rows=4,114 loops=1)

  • Sort Key: staff_memberships_2.account_id, staff_memberships_2.user_id, (COALESCE(staff_membership_activity_links_2.start_date, staff_memberships_2.joined_at)), (COALESCE(lag((staff_membership_activity_links_2.start_date - 1), 1) OVER (?), staff_memberships_2.archived_at, '3000-01-01'::date)) DESC
  • Sort Method: quicksort Memory: 514kB
  • Buffers: shared hit=83
57. 4.474 12.722 ↑ 1.0 4,114 1

WindowAgg (cost=471.06..563.63 rows=4,114 width=613) (actual time=7.765..12.722 rows=4,114 loops=1)

  • Buffers: shared hit=83
58. 3.021 8.248 ↑ 1.0 4,114 1

Sort (cost=471.06..481.35 rows=4,114 width=28) (actual time=7.751..8.248 rows=4,114 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: 514kB
  • Buffers: shared hit=83
59. 2.027 5.227 ↑ 1.0 4,114 1

Hash Join (cost=136.06..224.09 rows=4,114 width=28) (actual time=2.583..5.227 rows=4,114 loops=1)

  • Hash Cond: (staff_membership_activity_links_2.staff_membership_id = staff_memberships_2.id)
  • Buffers: shared hit=83
60. 0.659 0.659 ↑ 1.0 4,121 1

Seq Scan on staff_membership_activity_links staff_membership_activity_links_2 (cost=0.00..77.21 rows=4,121 width=12) (actual time=0.007..0.659 rows=4,121 loops=1)

  • Buffers: shared hit=36
61. 1.343 2.541 ↑ 1.0 3,958 1

Hash (cost=86.58..86.58 rows=3,958 width=20) (actual time=2.541..2.541 rows=3,958 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 227kB
  • Buffers: shared hit=47
62. 1.198 1.198 ↑ 1.0 3,958 1

Seq Scan on staff_memberships staff_memberships_2 (cost=0.00..86.58 rows=3,958 width=20) (actual time=0.005..1.198 rows=3,958 loops=1)

  • Buffers: shared hit=47
63. 1.288 2.556 ↑ 1.0 3,958 1

Hash (cost=86.58..86.58 rows=3,958 width=16) (actual time=2.556..2.556 rows=3,958 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 218kB
  • Buffers: shared hit=47
64. 1.268 1.268 ↑ 1.0 3,958 1

Seq Scan on staff_memberships staff_memberships_1 (cost=0.00..86.58 rows=3,958 width=16) (actual time=0.010..1.268 rows=3,958 loops=1)

  • Buffers: shared hit=47
65. 33.684 33.684 ↑ 12.5 2 11,228

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

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 0
66. 0.003 0.118 ↑ 38.0 3 1

Hash (cost=16.98..16.98 rows=114 width=4) (actual time=0.118..0.118 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=11
67. 0.115 0.115 ↑ 38.0 3 1

Seq Scan on accounts (cost=0.00..16.98 rows=114 width=4) (actual time=0.017..0.115 rows=3 loops=1)

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 339
  • Buffers: shared hit=11
68. 3,432.291 3,432.674 ↓ 26,147.3 32,841,023 1

Sort (cost=93.21..96.35 rows=1,256 width=24) (actual time=0.999..3,432.674 rows=32,841,023 loops=1)

  • Sort Key: cost_exchange_rates.account_id, cost_exchange_rates.currency
  • Sort Method: quicksort Memory: 147kB
  • Buffers: shared hit=16
69. 0.383 0.383 ↑ 1.0 1,256 1

Seq Scan on exchange_rates_by_months cost_exchange_rates (cost=0.00..28.56 rows=1,256 width=24) (actual time=0.021..0.383 rows=1,256 loops=1)

  • Buffers: shared hit=16
70. 568.084 643.248 ↓ 3.8 975,898 1

Sort (cost=28,569.03..29,203.51 rows=253,793 width=52) (actual time=527.545..643.248 rows=975,898 loops=1)

  • Sort Key: rates_with_dates.project_id, rates_with_dates.activity_id
  • Sort Method: quicksort Memory: 41834kB
  • Buffers: shared hit=3249
71. 75.164 75.164 ↑ 1.0 253,793 1

Seq Scan on rates_with_dates (cost=0.00..5,786.93 rows=253,793 width=52) (actual time=0.031..75.164 rows=253,793 loops=1)

  • Buffers: shared hit=3249
72. 45.606 45.748 ↓ 1,460.5 417,700 1

Sort (cost=21.46..22.17 rows=286 width=26) (actual time=0.302..45.748 rows=417,700 loops=1)

  • Sort Key: discounts.project_id
  • Sort Method: quicksort Memory: 51kB
  • Buffers: shared hit=4
73. 0.142 0.142 ↓ 1.2 336 1

Seq Scan on discounts (cost=0.00..9.79 rows=286 width=26) (actual time=0.023..0.142 rows=336 loops=1)

  • Filter: (((discountable_type)::text = 'User'::text) OR ((discountable_type)::text = 'Epic'::text))
  • Rows Removed by Filter: 50
  • Buffers: shared hit=4
74. 3,392.653 3,393.031 ↓ 25,265.6 31,733,610 1

Sort (cost=93.21..96.35 rows=1,256 width=24) (actual time=1.001..3,393.031 rows=31,733,610 loops=1)

  • Sort Key: exchange_rates_by_months.account_id, exchange_rates_by_months.currency
  • Sort Method: quicksort Memory: 147kB
  • Buffers: shared hit=16
75. 0.378 0.378 ↑ 1.0 1,256 1

Seq Scan on exchange_rates_by_months (cost=0.00..28.56 rows=1,256 width=24) (actual time=0.021..0.378 rows=1,256 loops=1)

  • Buffers: shared hit=16
76. 3,332.101 3,332.450 ↓ 25,265.6 31,733,610 1

Sort (cost=93.21..96.35 rows=1,256 width=24) (actual time=1.008..3,332.450 rows=31,733,610 loops=1)

  • Sort Key: original_exchange_rates.account_id, original_exchange_rates.currency
  • Sort Method: quicksort Memory: 147kB
  • Buffers: shared hit=16
77. 0.349 0.349 ↑ 1.0 1,256 1

Seq Scan on exchange_rates_by_months original_exchange_rates (cost=0.00..28.56 rows=1,256 width=24) (actual time=0.007..0.349 rows=1,256 loops=1)

  • Buffers: shared hit=16
Planning time : 5.280 ms
Execution time : 64,035.093 ms