explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xFm9

Settings
# exclusive inclusive rows x rows loops node
1. 161.588 60,109.666 ↓ 3.4 596,906 1

Subquery Scan on report_items_live (cost=1,194,017.27..1,212,297.77 rows=174,100 width=128) (actual time=58,941.506..60,109.666 rows=596,906 loops=1)

  • Buffers: shared hit=64826 read=13834, temp read=532093 written=100789
2. 801.415 59,948.078 ↓ 3.4 596,906 1

GroupAggregate (cost=1,194,017.27..1,210,556.77 rows=174,100 width=184) (actual time=58,941.504..59,948.078 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=64826 read=13834, temp read=532093 written=100789
3. 2,984.432 59,146.663 ↓ 3.4 596,906 1

Sort (cost=1,194,017.27..1,194,452.52 rows=174,100 width=180) (actual time=58,941.489..59,146.663 rows=596,906 loops=1)

  • Sort 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))
  • Sort Method: external merge Disk: 90464kB
  • Buffers: shared hit=64826 read=13834, temp read=532093 written=100789
4. 8,120.091 56,162.231 ↓ 3.4 596,906 1

Merge Left Join (cost=1,154,627.14..1,163,388.75 rows=174,100 width=180) (actual time=33,145.886..56,162.231 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=64826 read=13834, temp read=511938 written=80591
5. 8,205.665 44,992.627 ↓ 3.4 596,906 1

Merge Left Join (cost=1,154,533.93..1,158,914.74 rows=174,100 width=164) (actual time=33,145.134..44,992.627 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=64810 read=13834, temp read=511938 written=80591
6. 1,314.256 33,602.857 ↓ 3.4 596,906 1

Sort (cost=1,154,440.72..1,154,875.97 rows=174,100 width=156) (actual time=33,144.141..33,602.857 rows=596,906 loops=1)

  • Sort Key: projects.account_id, rates_with_dates.currency
  • Sort Method: external merge Disk: 77176kB
  • Buffers: shared hit=64794 read=13834, temp read=511938 written=80591
7. 278.488 32,288.601 ↓ 3.4 596,906 1

Merge Left Join (cost=1,113,635.40..1,125,597.21 rows=174,100 width=156) (actual time=31,024.537..32,288.601 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=64794 read=13834, temp read=495517 written=64129
8. 547.241 31,971.081 ↓ 3.4 596,906 1

Merge Left Join (cost=1,113,613.94..1,117,671.61 rows=174,100 width=152) (actual time=31,024.195..31,971.081 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=64790 read=13834, temp read=495517 written=64129
9. 873.715 30,710.694 ↓ 3.4 596,906 1

Sort (cost=1,076,368.41..1,076,803.66 rows=174,100 width=116) (actual time=30,510.207..30,710.694 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: external merge Disk: 56984kB
  • Buffers: shared hit=61541 read=13834, temp read=493460 written=62062
10. 5,076.037 29,836.979 ↓ 3.4 596,906 1

Hash Left Join (cost=548,132.96..1,050,499.89 rows=174,100 width=116) (actual time=7,759.304..29,836.979 rows=596,906 loops=1)

  • Hash 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=61541 read=13834, temp read=483168 written=51746
11. 328.863 24,760.419 ↓ 3.4 596,906 1

Hash Join (cost=548,085.56..981,165.73 rows=174,100 width=624) (actual time=7,758.715..24,760.419 rows=596,906 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
  • Buffers: shared hit=61525 read=13834, temp read=483168 written=51746
12. 207.592 24,431.451 ↓ 2.1 1,093,075 1

Append (cost=548,067.15..974,536.31 rows=522,299 width=624) (actual time=6,093.063..24,431.451 rows=1,093,075 loops=1)

  • Buffers: shared hit=61514 read=13834, temp read=483168 written=51746
13. 656.648 24,027.885 ↓ 2.1 1,076,373 1

Hash Left Join (cost=548,067.15..717,289.26 rows=522,298 width=83) (actual time=6,093.063..24,027.885 rows=1,076,373 loops=1)

  • Hash Cond: (projects.client_id = clients.id)
  • Buffers: shared hit=5520 read=13834, temp read=483168 written=51746
14. 10,314.464 23,368.189 ↓ 2.1 1,076,373 1

Merge Left Join (cost=548,005.16..697,572.61 rows=522,298 width=137) (actual time=6,090.000..23,368.189 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=5499 read=13834, temp read=483168 written=51746
15. 1,504.828 6,508.516 ↓ 2.1 1,076,373 1

Sort (cost=543,863.98..545,169.72 rows=522,298 width=125) (actual time=6,053.708..6,508.516 rows=1,076,373 loops=1)

  • Sort Key: "*SELECT* 1".project_plan_id
  • Sort Method: external merge Disk: 121032kB
  • Buffers: shared hit=5107 read=13834, temp read=51008 written=51109
16. 416.983 5,003.688 ↓ 2.1 1,076,373 1

Hash Left Join (cost=407,457.96..426,415.99 rows=522,298 width=125) (actual time=3,323.909..5,003.688 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=5107 read=13834, temp read=19619 written=19663
17. 722.563 4,586.468 ↑ 1.4 1,081,752 1

Merge Left Join (cost=407,439.27..422,251.79 rows=1,551,379 width=120) (actual time=3,323.646..4,586.468 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=5096 read=13834, temp read=19619 written=19663
18. 1,932.274 3,731.297 ↑ 1.4 1,081,752 1

Sort (cost=406,618.38..410,496.83 rows=1,551,379 width=96) (actual time=3,305.806..3,731.297 rows=1,081,752 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1"".user_id
  • Sort Method: external merge Disk: 86888kB
  • Buffers: shared hit=5013 read=13834, temp read=19619 written=19663
19. 477.454 1,799.023 ↑ 1.4 1,081,752 1

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

  • Hash Cond: ("*SELECT* 1".project_id = projects.id)
  • Buffers: shared hit=5013 read=13834
20. 282.463 1,319.079 ↑ 1.0 1,551,079 1

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

  • Buffers: shared hit=4916 read=13834
21. 409.777 756.096 ↑ 1.0 862,330 1

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

  • Buffers: shared hit=2240 read=8911
22. 346.319 346.319 ↑ 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.060..346.319 rows=862,330 loops=1)

  • Filter: (is_non_working_day IS NULL)
  • Rows Removed by Filter: 75583
  • Buffers: shared hit=2240 read=8911
23. 280.520 280.520 ↑ 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.072..280.520 rows=688,749 loops=1)

  • Filter: (is_non_working_day IS NULL)
  • Buffers: shared hit=2676 read=4923
24. 0.964 2.490 ↑ 1.0 5,686 1

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

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

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

  • Buffers: shared hit=97
26. 112.177 132.608 ↓ 285.8 1,175,756 1

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

  • Buffers: shared hit=83
27. 1.890 20.431 ↑ 1.0 4,053 1

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

  • Buffers: shared hit=83
28. 4.765 18.541 ↑ 1.0 4,075 1

Sort (cost=820.88..831.17 rows=4,114 width=65) (actual time=17.826..18.541 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.717 13.776 ↑ 1.0 4,114 1

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

  • Buffers: shared hit=83
30. 3.294 9.059 ↑ 1.0 4,114 1

Sort (cost=471.06..481.35 rows=4,114 width=56) (actual time=8.554..9.059 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.230 5.765 ↑ 1.0 4,114 1

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

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships.id)
  • Buffers: shared hit=83
32. 0.646 0.646 ↑ 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.015..0.646 rows=4,121 loops=1)

  • Buffers: shared hit=36
33. 1.570 2.889 ↑ 1.0 3,958 1

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

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

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

  • Buffers: shared hit=47
35. 0.091 0.237 ↑ 1.0 342 1

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

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

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

  • Buffers: shared hit=11
37. 6,536.874 6,545.209 ↓ 818.5 35,281,794 1

Sort (cost=4,141.18..4,248.94 rows=43,105 width=48) (actual time=36.280..6,545.209 rows=35,281,794 loops=1)

  • Sort Key: pa.project_plan_id
  • Sort Method: external sort Disk: 2544kB
  • Buffers: shared hit=392, temp read=246668 written=637
38. 8.335 8.335 ↑ 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.018..8.335 rows=43,105 loops=1)

  • Buffers: shared hit=392
39. 0.325 3.048 ↑ 1.0 1,822 1

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

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

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

  • Buffers: shared hit=21
41. 8.075 195.974 ↓ 16,702.0 16,702 1

Subquery Scan on *SELECT* 2 (cost=242,121.82..249,412.57 rows=1 width=83) (actual time=114.010..195.974 rows=16,702 loops=1)

  • Buffers: shared hit=55994
42. 26.279 187.899 ↓ 16,702.0 16,702 1

Merge Right Join (cost=242,121.82..249,412.56 rows=1 width=107) (actual time=114.006..187.899 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=55994
43. 40.265 40.265 ↑ 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..40.265 rows=58,756 loops=1)

  • Buffers: shared hit=55549
44. 4.761 121.355 ↑ 16.0 17,028 1

Materialize (cost=242,121.40..243,479.50 rows=271,620 width=49) (actual time=113.963..121.355 rows=17,028 loops=1)

  • Buffers: shared hit=445
45. 39.088 116.594 ↑ 16.0 17,028 1

Sort (cost=242,121.40..242,800.45 rows=271,620 width=49) (actual time=113.959..116.594 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: 2980kB
  • Buffers: shared hit=445
46. 14.302 77.506 ↑ 16.0 17,028 1

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

  • Buffers: shared hit=438
47. 8.577 40.748 ↓ 1.0 11,228 1

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

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

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

  • Buffers: shared hit=272
49. 0.772 27.740 ↑ 1.3 1,635 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 137kB
  • Buffers: shared hit=166
50. 0.802 26.968 ↑ 1.3 1,635 1

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

  • Hash Cond: (staff_membership_activity_links_1.staff_membership_id = staff_memberships_1.id)
  • Buffers: shared hit=166
51. 1.368 23.650 ↑ 1.3 1,635 1

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

  • Hash Cond: (staff_membership_activity_links_1.id = staff_activities_with_dates.link_id)
  • Buffers: shared hit=119
52. 0.635 0.635 ↑ 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.008..0.635 rows=4,121 loops=1)

  • Buffers: shared hit=36
53. 0.424 21.647 ↓ 9.0 1,635 1

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

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

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

  • Group Key: staff_activities_with_dates.link_id
  • Buffers: shared hit=83
55. 2.153 20.297 ↓ 3.6 1,635 1

Subquery Scan on staff_activities_with_dates (cost=810.60..934.02 rows=457 width=4) (actual time=16.427..20.297 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
56. 1.279 18.144 ↑ 1.0 4,086 1

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

  • Buffers: shared hit=83
57. 4.236 16.865 ↑ 1.0 4,114 1

Sort (cost=810.60..820.88 rows=4,114 width=613) (actual time=16.417..16.865 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
58. 4.508 12.629 ↑ 1.0 4,114 1

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

  • Buffers: shared hit=83
59. 2.906 8.121 ↑ 1.0 4,114 1

Sort (cost=471.06..481.35 rows=4,114 width=28) (actual time=7.596..8.121 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
60. 1.991 5.215 ↑ 1.0 4,114 1

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

  • Hash Cond: (staff_membership_activity_links_2.staff_membership_id = staff_memberships_2.id)
  • Buffers: shared hit=83
61. 0.639 0.639 ↑ 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.005..0.639 rows=4,121 loops=1)

  • Buffers: shared hit=36
62. 1.372 2.585 ↑ 1.0 3,958 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 227kB
  • Buffers: shared hit=47
63. 1.213 1.213 ↑ 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.007..1.213 rows=3,958 loops=1)

  • Buffers: shared hit=47
64. 1.271 2.516 ↑ 1.0 3,958 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 218kB
  • Buffers: shared hit=47
65. 1.245 1.245 ↑ 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.009..1.245 rows=3,958 loops=1)

  • Buffers: shared hit=47
66. 22.456 22.456 ↑ 12.5 2 11,228

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.002..0.002 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
67. 0.002 0.105 ↑ 38.0 3 1

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

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

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

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 339
  • Buffers: shared hit=11
69. 0.289 0.523 ↑ 1.0 1,256 1

Hash (cost=28.56..28.56 rows=1,256 width=24) (actual time=0.523..0.523 rows=1,256 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 85kB
  • Buffers: shared hit=16
70. 0.234 0.234 ↑ 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.011..0.234 rows=1,256 loops=1)

  • Buffers: shared hit=16
71. 125.326 713.146 ↓ 3.8 975,898 1

Materialize (cost=37,245.53..38,514.50 rows=253,793 width=52) (actual time=492.975..713.146 rows=975,898 loops=1)

  • Buffers: shared hit=3249, temp read=2057 written=2067
72. 512.809 587.820 ↑ 1.0 253,593 1

Sort (cost=37,245.53..37,880.01 rows=253,793 width=52) (actual time=492.972..587.820 rows=253,593 loops=1)

  • Sort Key: rates_with_dates.project_id, rates_with_dates.activity_id
  • Sort Method: external merge Disk: 16456kB
  • Buffers: shared hit=3249, temp read=2057 written=2067
73. 75.011 75.011 ↑ 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.015..75.011 rows=253,793 loops=1)

  • Buffers: shared hit=3249
74. 38.850 39.032 ↓ 1,460.5 417,700 1

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

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

Seq Scan on discounts (cost=0.00..9.79 rows=286 width=26) (actual time=0.040..0.182 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
76. 3,183.792 3,184.105 ↓ 25,265.6 31,733,610 1

Sort (cost=93.21..96.35 rows=1,256 width=24) (actual time=0.888..3,184.105 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
77. 0.313 0.313 ↑ 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.013..0.313 rows=1,256 loops=1)

  • Buffers: shared hit=16
78. 3,049.274 3,049.513 ↓ 25,265.6 31,733,610 1

Sort (cost=93.21..96.35 rows=1,256 width=24) (actual time=0.653..3,049.513 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
79. 0.239 0.239 ↑ 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.010..0.239 rows=1,256 loops=1)

  • Buffers: shared hit=16
Planning time : 4.655 ms
Execution time : 60,232.093 ms