explain.depesz.com

PostgreSQL's explain analyze made readable

Result: odBc

Settings
# exclusive inclusive rows x rows loops node
1. 6.687 10,140.432 ↓ 46.7 32,304 1

Subquery Scan on report_items_live (cost=1,955,364.66..1,955,437.22 rows=691 width=156) (actual time=10,093.063..10,140.432 rows=32,304 loops=1)

2. 36.617 10,133.745 ↓ 46.7 32,304 1

GroupAggregate (cost=1,955,364.66..1,955,430.31 rows=691 width=212) (actual time=10,093.061..10,133.745 rows=32,304 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, sa.activity_id)), ""*SELECT* 1"".user_id, ""*SELECT* 1"".date, (CASE WHEN (('PlanItem'::text) = 'TimeLog'::text) THEN (NULL::double precision) WHEN (((""*SELECT* 1"".vacation_user_id)::double precision) IS NOT NULL) THEN GREATEST(((((COALESCE(sa.capacity, '0'::numeric))::double precision - COALESCE(((""*SELECT* 1"".vacation_hours)::double precision), (sa.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(sa.capacity, '0'::numeric))::double precision * ((""*SELECT* 1"".utilization)::double precision)) / '100'::double precision) END), ('PlanItem'::text), ""*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, sa.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, sa.updated_at)), rates_with_dates.updated_at, rates_with_dates.rate_card_updated_at))
3. 108.638 10,097.128 ↓ 46.7 32,304 1

Sort (cost=1,955,364.66..1,955,366.39 rows=691 width=208) (actual time=10,093.035..10,097.128 rows=32,304 loops=1)

  • Sort Key: projects.account_id, projects.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, sa.activity_id)), ""*SELECT* 1"".user_id, ""*SELECT* 1"".date, (CASE WHEN (('PlanItem'::text) = 'TimeLog'::text) THEN (NULL::double precision) WHEN (((""*SELECT* 1"".vacation_user_id)::double precision) IS NOT NULL) THEN GREATEST(((((COALESCE(sa.capacity, '0'::numeric))::double precision - COALESCE(((""*SELECT* 1"".vacation_hours)::double precision), (sa.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(sa.capacity, '0'::numeric))::double precision * ((""*SELECT* 1"".utilization)::double precision)) / '100'::double precision) END), ('PlanItem'::text), ""*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, sa.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, sa.updated_at)), rates_with_dates.updated_at, rates_with_dates.rate_card_updated_at))
  • Sort Method: quicksort Memory: 9337kB
4. 13.519 9,988.490 ↓ 46.7 32,304 1

Hash Left Join (cost=1,849,878.69..1,955,332.07 rows=691 width=208) (actual time=5,126.521..9,988.490 rows=32,304 loops=1)

  • Hash 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))))
5. 13.173 9,974.795 ↓ 46.7 32,304 1

Hash Join (cost=1,849,865.32..1,955,269.82 rows=691 width=196) (actual time=5,126.335..9,974.795 rows=32,304 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
6. 158.455 9,961.507 ↓ 15.6 32,304 1

Hash Left Join (cost=1,849,846.91..1,955,245.91 rows=2,072 width=196) (actual time=5,126.213..9,961.507 rows=32,304 loops=1)

  • Hash 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: 1345275
7. 160.255 9,802.429 ↓ 15.6 32,304 1

Hash Left Join (cost=1,849,799.51..1,954,350.48 rows=2,072 width=188) (actual time=5,125.523..9,802.429 rows=32,304 loops=1)

  • Hash Cond: ((projects.account_id = cost_exchange_rates.account_id) AND ((sa.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: 1356064
8. 158.830 9,641.502 ↓ 15.6 32,304 1

Hash Left Join (cost=1,849,752.11..1,953,478.49 rows=2,072 width=696) (actual time=5,124.115..9,641.502 rows=32,304 loops=1)

  • Hash 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: 1345275
9. 27.631 9,482.132 ↓ 15.6 32,304 1

Hash Left Join (cost=1,849,704.71..1,952,583.06 rows=2,072 width=688) (actual time=5,123.556..9,482.132 rows=32,304 loops=1)

  • Hash Cond: ((projects.id = rates_with_dates.project_id) AND ((COALESCE(pa.activity_id, ""*SELECT* 1"".activity_id, sa.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)))
10. 5.894 9,300.407 ↓ 15.6 32,304 1

Append (cost=1,840,110.89..1,941,292.83 rows=2,072 width=652) (actual time=4,967.915..9,300.407 rows=32,304 loops=1)

11. 26.491 9,294.511 ↓ 15.6 32,304 1

Nested Loop Left Join (cost=1,840,110.89..1,941,261.75 rows=2,071 width=111) (actual time=4,967.914..9,294.511 rows=32,304 loops=1)

12. 9.531 6,328.356 ↓ 15.6 32,304 1

Nested Loop (cost=1,840,110.47..1,940,020.77 rows=2,071 width=157) (actual time=4,967.886..6,328.356 rows=32,304 loops=1)

13. 0.017 0.017 ↑ 1.0 1 1

Index Scan using clients_pkey on clients (cost=0.28..8.29 rows=1 width=8) (actual time=0.017..0.017 rows=1 loops=1)

  • Index Cond: (id = 1801)
14. 15.583 6,318.808 ↓ 15.6 32,304 1

Hash Left Join (cost=1,840,110.19..1,939,991.76 rows=2,071 width=153) (actual time=4,967.866..6,318.808 rows=32,304 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(sa.capacity, accounts_1.default_capacity))::double precision))
  • Rows Removed by Filter: 207
15. 24.286 6,303.038 ↓ 5.3 32,511 1

Hash Left Join (cost=1,840,091.50..1,939,956.63 rows=6,151 width=148) (actual time=4,967.668..6,303.038 rows=32,511 loops=1)

  • Hash Cond: (("*SELECT* 1".user_id = sa.user_id) AND (projects.account_id = sa.account_id))
  • Join Filter: (("*SELECT* 1".date >= sa.start_date) AND ("*SELECT* 1".date <= sa.end_date))
  • Rows Removed by Join Filter: 17966
16. 145.121 6,262.074 ↓ 5.3 32,511 1

Hash Join (cost=1,839,126.63..1,932,632.67 rows=6,151 width=124) (actual time=4,950.931..6,262.074 rows=32,511 loops=1)

  • Hash Cond: ("*SELECT* 1".project_id = projects.id)
17. 180.502 6,116.891 ↓ 2.2 1,549,746 1

Append (cost=1,839,037.76..1,923,711.51 rows=699,480 width=120) (actual time=4,715.967..6,116.891 rows=1,549,746 loops=1)

18. 275.966 5,536.527 ↓ 80.2 860,997 1

Subquery Scan on *SELECT* 1 (cost=1,839,037.76..1,882,764.12 rows=10,731 width=120) (actual time=4,715.966..5,536.527 rows=860,997 loops=1)

19. 433.186 5,260.561 ↓ 80.2 860,997 1

Merge Right Join (cost=1,839,037.76..1,882,576.33 rows=10,731 width=138) (actual time=4,715.962..5,260.561 rows=860,997 loops=1)

  • Merge Cond: ((nw_intervals_by_dates_roles.account_id = projects_1.account_id) AND (nw_intervals_by_dates_roles.date = generate_series.generate_series) AND (nw_intervals_by_dates_roles.office_id = clients_1.office_id))
  • Filter: (COALESCE(non_working_intervals_by_dates.id, nw_intervals_by_dates_roles.id) IS NULL)
  • Rows Removed by Filter: 640596
20. 23.181 33.691 ↑ 1.0 58,766 1

Sort (cost=5,918.90..6,065.90 rows=58,799 width=16) (actual time=28.959..33.691 rows=58,766 loops=1)

  • Sort Key: nw_intervals_by_dates_roles.account_id, nw_intervals_by_dates_roles.date, nw_intervals_by_dates_roles.office_id
  • Sort Method: quicksort Memory: 4293kB
21. 10.510 10.510 ↑ 1.0 58,799 1

Seq Scan on non_working_intervals_by_dates nw_intervals_by_dates_roles (cost=0.00..1,260.99 rows=58,799 width=16) (actual time=0.006..10.510 rows=58,799 loops=1)

22. 1,046.493 4,793.684 ↓ 1.2 1,501,593 1

Sort (cost=1,833,118.86..1,836,260.80 rows=1,256,775 width=110) (actual time=4,686.992..4,793.684 rows=1,501,593 loops=1)

  • Sort Key: projects_1.account_id, generate_series.generate_series, clients_1.office_id
  • Sort Method: quicksort Memory: 146956kB
23. 196.331 3,747.191 ↑ 1.4 870,252 1

Merge Left Join (cost=1,677,030.13..1,705,799.42 rows=1,256,775 width=110) (actual time=3,191.382..3,747.191 rows=870,252 loops=1)

  • Merge Cond: ((projects_1.account_id = non_working_intervals_by_dates.account_id) AND (generate_series.generate_series = non_working_intervals_by_dates.date) AND (plan_rows.user_id = non_working_intervals_by_dates.user_id))
24. 263.069 3,504.544 ↑ 1.4 870,234 1

Merge Left Join (cost=1,671,111.23..1,689,418.84 rows=1,256,775 width=106) (actual time=3,150.309..3,504.544 rows=870,234 loops=1)

  • Merge Cond: ((projects_1.account_id = summary_vacations_by_dates.account_id) AND (generate_series.generate_series = summary_vacations_by_dates.date) AND (plan_rows.user_id = summary_vacations_by_dates.user_id))
25. 2,290.973 3,117.624 ↑ 1.4 870,234 1

Sort (cost=1,086,921.64..1,090,063.58 rows=1,256,775 width=70) (actual time=3,031.412..3,117.624 rows=870,234 loops=1)

  • Sort Key: projects_1.account_id, generate_series.generate_series, plan_rows.user_id
  • Sort Method: quicksort Memory: 146953kB
26. 137.917 826.651 ↑ 1.4 870,234 1

Nested Loop (cost=2,043.15..959,602.20 rows=1,256,775 width=70) (actual time=25.018..826.651 rows=870,234 loops=1)

27. 15.312 112.125 ↓ 1.0 52,419 1

Hash Left Join (cost=2,043.14..4,453.19 rows=50,271 width=70) (actual time=24.995..112.125 rows=52,419 loops=1)

  • Hash Cond: (projects_1.client_id = clients_1.id)
28. 14.744 96.178 ↓ 1.0 52,419 1

Hash Left Join (cost=1,981.15..4,258.92 rows=50,271 width=70) (actual time=24.319..96.178 rows=52,419 loops=1)

  • Hash Cond: (project_plans.project_id = projects_1.id)
29. 15.434 79.038 ↓ 1.0 52,419 1

Hash Left Join (cost=1,756.21..3,901.92 rows=50,271 width=62) (actual time=21.908..79.038 rows=52,419 loops=1)

  • Hash Cond: (plan_items.project_plan_id = project_plans.id)
30. 28.575 62.027 ↓ 1.0 52,419 1

Hash Join (cost=1,557.89..3,571.54 rows=50,271 width=58) (actual time=20.302..62.027 rows=52,419 loops=1)

  • Hash Cond: (plan_items.plan_row_uuid = plan_rows.uuid)
31. 13.466 13.466 ↓ 1.0 52,422 1

Seq Scan on plan_items (cost=0.00..1,314.46 rows=52,394 width=46) (actual time=0.017..13.466 rows=52,422 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 255
32. 10.629 19.986 ↓ 1.0 46,641 1

Hash (cost=998.45..998.45 rows=44,755 width=28) (actual time=19.985..19.986 rows=46,641 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3035kB
33. 9.357 9.357 ↓ 1.0 46,641 1

Seq Scan on plan_rows (cost=0.00..998.45 rows=44,755 width=28) (actual time=0.008..9.357 rows=46,641 loops=1)

  • Filter: ((user_id IS NOT NULL) OR (activity_id IS NOT NULL))
  • Rows Removed by Filter: 4
34. 0.461 1.577 ↓ 1.0 2,819 1

Hash (cost=163.10..163.10 rows=2,818 width=8) (actual time=1.577..1.577 rows=2,819 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 143kB
35. 1.116 1.116 ↓ 1.0 2,819 1

Seq Scan on project_plans (cost=0.00..163.10 rows=2,818 width=8) (actual time=0.010..1.116 rows=2,819 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3091
36. 1.109 2.396 ↑ 1.0 5,686 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 309kB
37. 1.287 1.287 ↑ 1.0 5,686 1

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

38. 0.300 0.635 ↑ 1.0 1,822 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
39. 0.335 0.335 ↑ 1.0 1,822 1

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

40. 576.609 576.609 ↑ 1.5 17 52,419

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.004..0.011 rows=17 loops=52,419)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 6
41. 38.401 123.851 ↑ 16.5 34,338 1

Sort (cost=584,189.58..585,604.08 rows=565,800 width=44) (actual time=118.890..123.851 rows=34,338 loops=1)

  • Sort Key: summary_vacations_by_dates.account_id, summary_vacations_by_dates.date, summary_vacations_by_dates.user_id
  • Sort Method: quicksort Memory: 2705kB
42. 4.248 85.450 ↑ 19.1 29,556 1

Subquery Scan on summary_vacations_by_dates (cost=515,982.59..530,127.59 rows=565,800 width=44) (actual time=64.533..85.450 rows=29,556 loops=1)

43. 31.476 81.202 ↑ 19.1 29,556 1

HashAggregate (cost=515,982.59..524,469.59 rows=565,800 width=52) (actual time=64.532..81.202 rows=29,556 loops=1)

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
44. 11.224 49.726 ↑ 142.6 29,683 1

Hash Join (cost=136.06..473,655.25 rows=4,232,734 width=21) (actual time=1.619..49.726 rows=29,683 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
45. 13.578 36.919 ↑ 661.8 31,591 1

Nested Loop (cost=0.01..418,621.08 rows=20,907,000 width=17) (actual time=0.026..36.919 rows=31,591 loops=1)

46. 2.434 2.434 ↑ 1.0 20,907 1

Seq Scan on vacations (cost=0.00..481.07 rows=20,907 width=17) (actual time=0.014..2.434 rows=20,907 loops=1)

47. 20.907 20.907 ↑ 500.0 2 20,907

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=20,907)

48. 0.847 1.583 ↑ 1.0 3,958 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 227kB
49. 0.736 0.736 ↑ 1.0 3,958 1

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

50. 36.092 46.316 ↓ 1.0 60,807 1

Sort (cost=5,918.90..6,065.90 rows=58,799 width=16) (actual time=41.064..46.316 rows=60,807 loops=1)

  • Sort Key: non_working_intervals_by_dates.account_id, non_working_intervals_by_dates.date, non_working_intervals_by_dates.user_id
  • Sort Method: quicksort Memory: 4293kB
51. 10.224 10.224 ↑ 1.0 58,799 1

Seq Scan on non_working_intervals_by_dates (cost=0.00..1,260.99 rows=58,799 width=16) (actual time=0.013..10.224 rows=58,799 loops=1)

52. 281.780 399.862 ↑ 1.0 688,749 1

Hash Join (cost=1,805.96..30,562.50 rows=688,749 width=120) (actual time=18.013..399.862 rows=688,749 loops=1)

  • Hash Cond: (time_logs.epic_id = epics.id)
53. 100.247 100.247 ↑ 1.0 688,749 1

Seq Scan on time_logs (cost=0.00..15,842.49 rows=688,749 width=28) (actual time=0.051..100.247 rows=688,749 loops=1)

54. 5.433 17.835 ↑ 1.0 26,131 1

Hash (cost=1,479.32..1,479.32 rows=26,131 width=16) (actual time=17.835..17.835 rows=26,131 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1368kB
55. 8.330 12.402 ↑ 1.0 26,131 1

Hash Left Join (cost=198.32..1,479.32 rows=26,131 width=16) (actual time=2.538..12.402 rows=26,131 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: 11264
56. 2.588 2.588 ↑ 1.0 26,131 1

Seq Scan on epics (cost=0.00..612.31 rows=26,131 width=12) (actual time=0.005..2.588 rows=26,131 loops=1)

57. 0.424 1.484 ↓ 1.0 2,819 1

Hash (cost=163.10..163.10 rows=2,818 width=8) (actual time=1.484..1.484 rows=2,819 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 143kB
58. 1.060 1.060 ↓ 1.0 2,819 1

Seq Scan on project_plans general_epic_plans (cost=0.00..163.10 rows=2,818 width=8) (actual time=0.013..1.060 rows=2,819 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3091
59. 0.009 0.062 ↑ 1.0 50 1

Hash (cost=88.24..88.24 rows=50 width=12) (actual time=0.062..0.062 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
60. 0.035 0.053 ↑ 1.0 50 1

Bitmap Heap Scan on projects (cost=4.67..88.24 rows=50 width=12) (actual time=0.023..0.053 rows=50 loops=1)

  • Recheck Cond: (client_id = 1801)
  • Heap Blocks: exact=28
61. 0.018 0.018 ↑ 1.0 50 1

Bitmap Index Scan on index_projects_on_client_id (cost=0.00..4.66 rows=50 width=0) (actual time=0.018..0.018 rows=50 loops=1)

  • Index Cond: (client_id = 1801)
62. 1.419 16.678 ↑ 1.0 4,086 1

Hash (cost=903.16..903.16 rows=4,114 width=40) (actual time=16.678..16.678 rows=4,086 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 360kB
63. 0.981 15.259 ↑ 1.0 4,086 1

Subquery Scan on sa (cost=820.88..903.16 rows=4,114 width=40) (actual time=13.036..15.259 rows=4,086 loops=1)

64. 0.925 14.278 ↑ 1.0 4,086 1

Unique (cost=820.88..862.02 rows=4,114 width=65) (actual time=13.033..14.278 rows=4,086 loops=1)

65. 3.302 13.353 ↑ 1.0 4,114 1

Sort (cost=820.88..831.17 rows=4,114 width=65) (actual time=13.032..13.353 rows=4,114 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: 771kB
66. 3.377 10.051 ↑ 1.0 4,114 1

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

67. 2.402 6.674 ↑ 1.0 4,114 1

Sort (cost=471.06..481.35 rows=4,114 width=56) (actual time=6.312..6.674 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
68. 1.668 4.272 ↑ 1.0 4,114 1

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

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships_1.id)
69. 0.505 0.505 ↑ 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.012..0.505 rows=4,121 loops=1)

70. 1.149 2.099 ↑ 1.0 3,958 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 273kB
71. 0.950 0.950 ↑ 1.0 3,958 1

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

72. 0.066 0.187 ↑ 1.0 342 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
73. 0.121 0.121 ↑ 1.0 342 1

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

74. 2,939.664 2,939.664 ↑ 1.0 1 32,304

Index Scan using pawd on planned_activities_with_dates pa (cost=0.41..0.55 rows=1 width=48) (actual time=0.052..0.091 rows=1 loops=32,304)

  • Index Cond: ((project_plan_id = "*SELECT* 1".project_plan_id) AND ("*SELECT* 1".date >= start_date) AND ("*SELECT* 1".date <= end_date))
  • Filter: (((plan_row_uuid = "*SELECT* 1".plan_row_uuid) OR ("*SELECT* 1".plan_row_uuid IS NULL)) AND ((user_id = "*SELECT* 1".user_id) OR ("*SELECT* 1".user_id IS NULL)) AND ((epic_id = "*SELECT* 1".epic_id) OR ((('PlanItem'::text) = 'PlanItem'::text) AND ("*SELECT* 1".epic_id IS NULL)) OR ((('PlanItem'::text) = 'TimeLog'::text) AND (epic_id IS NULL))))
  • Rows Removed by Filter: 229
75. 0.001 0.002 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.00..0.00 rows=1 width=111) (actual time=0.002..0.002 rows=0 loops=1)

76. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=118) (actual time=0.001..0.001 rows=0 loops=1)

  • One-Time Filter: false
77. 94.931 154.094 ↑ 1.0 253,793 1

Hash (cost=5,786.93..5,786.93 rows=253,793 width=52) (actual time=154.094..154.094 rows=253,793 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 23859kB
78. 59.163 59.163 ↑ 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.010..59.163 rows=253,793 loops=1)

79. 0.291 0.540 ↑ 1.0 1,256 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 85kB
80. 0.249 0.249 ↑ 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.010..0.249 rows=1,256 loops=1)

81. 0.381 0.672 ↑ 1.0 1,256 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 85kB
82. 0.291 0.291 ↑ 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.013..0.291 rows=1,256 loops=1)

83. 0.335 0.623 ↑ 1.0 1,256 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 85kB
84. 0.288 0.288 ↑ 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.288 rows=1,256 loops=1)

85. 0.003 0.115 ↑ 38.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
86. 0.112 0.112 ↑ 38.0 3 1

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

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 339
87. 0.089 0.176 ↓ 1.2 336 1

Hash (cost=9.79..9.79 rows=286 width=26) (actual time=0.176..0.176 rows=336 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
88. 0.087 0.087 ↓ 1.2 336 1

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

  • Filter: (((discountable_type)::text = 'User'::text) OR ((discountable_type)::text = 'Epic'::text))
  • Rows Removed by Filter: 50
Planning time : 9.160 ms
Execution time : 10,192.319 ms