explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FcUN

Settings
# exclusive inclusive rows x rows loops node
1. 165.259 696.828 ↓ 5.3 4,563 1

Nested Loop Left Join (cost=547,331.33..633,535.54 rows=863 width=188) (actual time=260.140..696.828 rows=4,563 loops=1)

  • 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)) AND (""*SELECT* 1"".project_id = projects.id) AND (""*SELECT* 1"".activity_id = (COALESCE(""*SELECT* 1_1"".activity_id, sa.activity_id))))
2. 1.207 522.443 ↓ 5.3 4,563 1

Hash Join (cost=547,215.67..622,574.34 rows=863 width=136) (actual time=250.660..522.443 rows=4,563 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
3. 0.400 521.120 ↓ 1.8 4,563 1

Append (cost=547,187.00..622,512.89 rows=2,590 width=136) (actual time=250.537..521.120 rows=4,563 loops=1)

4. 3.742 520.718 ↓ 1.8 4,563 1

Hash Left Join (cost=547,187.00..622,474.05 rows=2,589 width=108) (actual time=250.536..520.718 rows=4,563 loops=1)

  • Hash Cond: ((projects.account_id = sa.account_id) AND ("*SELECT* 1_1".user_id = sa.user_id))
  • Join Filter: (("*SELECT* 1_1".date >= sa.start_date) AND ("*SELECT* 1_1".date <= sa.end_date))
  • Rows Removed by Join Filter: 8
  • Filter: ((((""*SELECT* 1_1"".vacation_user_id)::double precision) IS NULL) OR (((""*SELECT* 1_1"".vacation_hours)::double precision) < (COALESCE(sa.capacity, accounts_1.default_capacity))::double precision))
5. 0.859 505.610 ↑ 1.7 4,563 1

Nested Loop (cost=546,180.42..613,166.90 rows=7,690 width=125) (actual time=239.142..505.610 rows=4,563 loops=1)

6. 0.103 0.210 ↑ 1.0 1 1

Nested Loop (cost=0.55..90.35 rows=1 width=13) (actual time=0.021..0.210 rows=1 loops=1)

  • Join Filter: (projects.account_id = accounts_1.id)
  • Rows Removed by Join Filter: 353
7. 0.107 0.107 ↑ 1.0 354 1

Index Scan using accounts_pkey on accounts accounts_1 (cost=0.27..76.74 rows=354 width=9) (actual time=0.007..0.107 rows=354 loops=1)

8. 0.000 0.000 ↑ 1.0 1 354

Materialize (cost=0.28..8.31 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=354)

9. 0.008 0.008 ↑ 1.0 1 1

Index Scan using projects_pkey on projects (cost=0.28..8.30 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=1)

  • Index Cond: (id = 10047)
10. 0.406 504.541 ↑ 1.7 4,563 1

Append (cost=546,179.86..612,922.74 rows=7,690 width=116) (actual time=239.117..504.541 rows=4,563 loops=1)

11. 0.661 240.979 ↓ 3.6 1,838 1

Result (cost=546,179.86..580,967.25 rows=511 width=116) (actual time=239.116..240.979 rows=1,838 loops=1)

12. 0.155 240.318 ↓ 3.6 1,838 1

Append (cost=546,179.86..580,960.87 rows=511 width=140) (actual time=239.111..240.318 rows=1,838 loops=1)

13. 0.336 240.054 ↓ 3.9 1,838 1

Subquery Scan on *SELECT* 1_1 (cost=546,179.86..573,422.72 rows=473 width=114) (actual time=239.110..240.054 rows=1,838 loops=1)

14. 6.094 239.718 ↓ 3.9 1,838 1

Hash Right Join (cost=546,179.86..573,415.62 rows=473 width=134) (actual time=239.108..239.718 rows=1,838 loops=1)

  • Hash Cond: ((staff_memberships.account_id = projects_1.account_id) AND (staff_memberships.user_id = plan_rows.user_id))
  • Join Filter: (generate_series.generate_series = ((generate_series_1.generate_series)::date))
  • Rows Removed by Join Filter: 27036
15. 29.032 67.387 ↑ 18.0 31,859 1

HashAggregate (cost=533,709.04..542,307.04 rows=573,200 width=52) (actual time=51.523..67.387 rows=31,859 loops=1)

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
16. 9.548 38.355 ↑ 135.1 32,014 1

Hash Join (cost=145.85..490,443.54 rows=4,326,550 width=21) (actual time=1.192..38.355 rows=32,014 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
17. 4.363 27.656 ↑ 638.1 33,922 1

Nested Loop (cost=0.01..433,459.47 rows=21,646,000 width=17) (actual time=0.028..27.656 rows=33,922 loops=1)

18. 1.647 1.647 ↑ 1.0 21,646 1

Seq Scan on vacations (cost=0.00..539.46 rows=21,646 width=17) (actual time=0.012..1.647 rows=21,646 loops=1)

19. 21.646 21.646 ↑ 500.0 2 21,646

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,646)

20. 0.493 1.151 ↑ 1.0 3,993 1

Hash (cost=95.93..95.93 rows=3,993 width=20) (actual time=1.150..1.151 rows=3,993 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 229kB
21. 0.658 0.658 ↑ 1.0 3,993 1

Seq Scan on staff_memberships (cost=0.00..95.93 rows=3,993 width=20) (actual time=0.006..0.658 rows=3,993 loops=1)

22. 0.414 166.237 ↓ 3.9 1,838 1

Hash (cost=12,463.73..12,463.73 rows=473 width=66) (actual time=166.237..166.237 rows=1,838 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 203kB
23. 4.895 165.823 ↓ 3.9 1,838 1

Hash Right Join (cost=12,174.31..12,463.73 rows=473 width=66) (actual time=134.075..165.823 rows=1,838 loops=1)

  • Hash Cond: ((plan_rows_1.project_plan_id = project_plans.id) AND (((min(((plan_rows_1.uuid)::character varying)::text))::uuid) = plan_items.plan_row_uuid) AND (plan_rows_1.user_id = plan_rows.user_id))
  • Join Filter: (((plan_rows_1.epic_id = plan_rows.epic_id) OR (plan_rows.epic_id IS NULL)) AND ((generate_series.generate_series)::date >= (COALESCE(plan_roles.start_date, '1980-01-01'::date))) AND ((generate_series.generate_series)::date <= (COALESCE(lag((plan_roles.start_date - 1), 1) OVER (?), '3000-01-01'::date))))
24. 55.543 135.008 ↓ 9.0 44,115 1

HashAggregate (cost=9,327.60..9,401.49 rows=4,926 width=48) (actual time=102.358..135.008 rows=44,115 loops=1)

  • Group Key: plan_rows_1.project_plan_id, plan_rows_1.epic_id, plan_rows_1.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)
25. 26.746 79.465 ↑ 1.0 44,268 1

WindowAgg (cost=6,892.75..7,888.83 rows=44,270 width=68) (actual time=47.862..79.465 rows=44,268 loops=1)

26. 20.279 52.719 ↑ 1.0 44,268 1

Sort (cost=6,892.75..7,003.43 rows=44,270 width=52) (actual time=47.851..52.719 rows=44,268 loops=1)

  • Sort Key: plan_roles.plan_row_uuid, plan_roles.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 4998kB
27. 14.881 32.440 ↑ 1.0 44,268 1

Hash Join (cost=1,661.02..3,476.43 rows=44,270 width=52) (actual time=14.550..32.440 rows=44,268 loops=1)

  • Hash Cond: (plan_roles.plan_row_uuid = plan_rows_1.uuid)
28. 3.075 3.075 ↑ 1.0 44,270 1

Seq Scan on plan_roles (cost=0.00..1,206.70 rows=44,270 width=24) (actual time=0.006..3.075 rows=44,270 loops=1)

29. 6.990 14.484 ↑ 1.0 47,734 1

Hash (cost=1,064.34..1,064.34 rows=47,734 width=28) (actual time=14.484..14.484 rows=47,734 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3159kB
30. 7.494 7.494 ↑ 1.0 47,734 1

Seq Scan on plan_rows plan_rows_1 (cost=0.00..1,064.34 rows=47,734 width=28) (actual time=0.004..7.494 rows=47,734 loops=1)

31. 0.463 25.920 ↓ 3.9 1,838 1

Hash (cost=2,838.43..2,838.43 rows=473 width=70) (actual time=25.920..25.920 rows=1,838 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 218kB
32. 7.545 25.457 ↓ 3.9 1,838 1

Hash Anti Join (cost=2,250.35..2,838.43 rows=473 width=70) (actual time=16.865..25.457 rows=1,838 loops=1)

  • Hash Cond: ((projects_1.account_id = non_working_intervals_by_dates.account_id) AND (plan_rows.user_id = non_working_intervals_by_dates.user_id))
  • Join Filter: (generate_series.generate_series = non_working_intervals_by_dates.date)
  • Rows Removed by Join Filter: 99252
33. 0.211 1.263 ↓ 3.1 1,838 1

Nested Loop (cost=1.15..538.28 rows=600 width=70) (actual time=0.055..1.263 rows=1,838 loops=1)

34. 0.013 0.124 ↓ 1.3 32 1

Nested Loop (cost=1.15..82.28 rows=24 width=70) (actual time=0.027..0.124 rows=32 loops=1)

35. 0.008 0.047 ↓ 1.2 32 1

Nested Loop (cost=0.85..72.06 rows=27 width=58) (actual time=0.020..0.047 rows=32 loops=1)

36. 0.002 0.016 ↑ 3.0 1 1

Nested Loop (cost=0.56..20.98 rows=3 width=12) (actual time=0.010..0.016 rows=1 loops=1)

37. 0.004 0.004 ↑ 1.0 1 1

Index Scan using projects_pkey on projects projects_1 (cost=0.28..8.30 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=1)

  • Index Cond: (id = 10047)
38. 0.010 0.010 ↑ 3.0 1 1

Index Scan using index_project_plans_on_project_id on project_plans (cost=0.28..12.65 rows=3 width=8) (actual time=0.005..0.010 rows=1 loops=1)

  • Index Cond: (project_id = 10047)
  • Filter: active
  • Rows Removed by Filter: 5
39. 0.023 0.023 ↓ 1.6 32 1

Index Scan using index_plan_items_on_project_plan_id on plan_items (cost=0.29..16.83 rows=20 width=46) (actual time=0.007..0.023 rows=32 loops=1)

  • Index Cond: (project_plan_id = project_plans.id)
  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 1
40. 0.064 0.064 ↑ 1.0 1 32

Index Scan using index_plan_rows_on_uuid on plan_rows (cost=0.29..0.37 rows=1 width=28) (actual time=0.002..0.002 rows=1 loops=32)

  • Index Cond: (uuid = plan_items.plan_row_uuid)
  • Filter: (user_id IS NOT NULL)
41. 0.928 0.928 ↓ 2.3 57 32

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.009..0.029 rows=57 loops=32)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 23
42. 7.372 16.649 ↑ 1.0 58,669 1

Hash (cost=1,358.08..1,358.08 rows=59,408 width=12) (actual time=16.649..16.649 rows=58,669 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3033kB
43. 9.277 9.277 ↑ 1.0 59,408 1

Seq Scan on non_working_intervals_by_dates (cost=0.00..1,358.08 rows=59,408 width=12) (actual time=0.005..9.277 rows=59,408 loops=1)

44. 0.001 0.109 ↓ 0.0 0 1

Nested Loop (cost=1.15..7,535.21 rows=38 width=114) (actual time=0.108..0.109 rows=0 loops=1)

  • Join Filter: (NOT (SubPlan 1))
45. 0.000 0.108 ↓ 0.0 0 1

Nested Loop (cost=1.15..82.28 rows=3 width=66) (actual time=0.108..0.108 rows=0 loops=1)

46. 0.006 0.006 ↑ 1.0 1 1

Index Scan using projects_pkey on projects project (cost=0.28..8.30 rows=1 width=12) (actual time=0.005..0.006 rows=1 loops=1)

  • Index Cond: (id = 10047)
47. 0.000 0.102 ↓ 0.0 0 1

Nested Loop (cost=0.86..73.95 rows=3 width=58) (actual time=0.102..0.102 rows=0 loops=1)

48. 0.008 0.039 ↓ 1.2 32 1

Nested Loop (cost=0.57..63.73 rows=27 width=50) (actual time=0.016..0.039 rows=32 loops=1)

49. 0.010 0.010 ↑ 3.0 1 1

Index Scan using index_project_plans_on_project_id on project_plans project_plans_1 (cost=0.28..12.65 rows=3 width=8) (actual time=0.006..0.010 rows=1 loops=1)

  • Index Cond: (project_id = 10047)
  • Filter: active
  • Rows Removed by Filter: 5
50. 0.021 0.021 ↓ 1.6 32 1

Index Scan using index_plan_items_on_project_plan_id on plan_items plan_items_1 (cost=0.29..16.83 rows=20 width=46) (actual time=0.008..0.021 rows=32 loops=1)

  • Index Cond: (project_plan_id = project_plans_1.id)
  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 1
51. 0.064 0.064 ↓ 0.0 0 32

Index Scan using index_plan_rows_on_uuid on plan_rows plan_rows_2 (cost=0.29..0.37 rows=1 width=24) (actual time=0.002..0.002 rows=0 loops=32)

  • Index Cond: (uuid = plan_items_1.plan_row_uuid)
  • Filter: (user_id IS NULL)
  • Rows Removed by Filter: 1
52. 0.000 0.000 ↓ 0.0 0

Function Scan on generate_series generate_series_2 (cost=0.01..18.76 rows=25 width=8) (never executed)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
53.          

SubPlan (for Nested Loop)

54. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.69..98.61 rows=1 width=0) (never executed)

  • Join Filter: (non_working_intervals_by_dates_1.office_id = clients_3.office_id)
55. 0.000 0.000 ↓ 0.0 0

Index Scan using clients_pkey on clients clients_3 (cost=0.28..8.29 rows=1 width=4) (never executed)

  • Index Cond: (project.client_id = id)
56. 0.000 0.000 ↓ 0.0 0

Index Scan using non_working_intervals_by_dates_idx on non_working_intervals_by_dates non_working_intervals_by_dates_1 (cost=0.41..90.18 rows=11 width=4) (never executed)

  • Index Cond: ((project.account_id = account_id) AND (generate_series_2.generate_series = date))
57. 11.029 263.156 ↑ 2.6 2,725 1

Hash Right Join (cost=30,275.26..31,845.25 rows=7,179 width=116) (actual time=225.754..263.156 rows=2,725 loops=1)

  • Hash Cond: (plan_rows_3.user_id = time_logs.user_id)
  • Join Filter: ((time_logs.date >= (COALESCE(plan_roles_1.start_date, '1980-01-01'::date))) AND (time_logs.date <= (COALESCE(lag((plan_roles_1.start_date - 1), 1) OVER (?), '3000-01-01'::date))) AND (((plan_rows_3.epic_id = time_logs.epic_id) AND (plan_rows_3.project_plan_id = epics.project_plan_id)) OR ((plan_rows_3.epic_id IS NULL) AND (plan_rows_3.project_plan_id = active_plan.id))))
  • Rows Removed by Join Filter: 57735
58. 57.190 136.298 ↓ 9.0 44,115 1

HashAggregate (cost=9,327.60..9,401.49 rows=4,926 width=48) (actual time=103.649..136.298 rows=44,115 loops=1)

  • Group Key: plan_rows_3.project_plan_id, plan_rows_3.epic_id, plan_rows_3.user_id, plan_roles_1.activity_id, COALESCE(plan_roles_1.start_date, '1980-01-01'::date), COALESCE(lag((plan_roles_1.start_date - 1), 1) OVER (?), '3000-01-01'::date)
59. 25.610 79.108 ↑ 1.0 44,268 1

WindowAgg (cost=6,892.75..7,888.83 rows=44,270 width=68) (actual time=48.725..79.108 rows=44,268 loops=1)

60. 22.125 53.498 ↑ 1.0 44,268 1

Sort (cost=6,892.75..7,003.43 rows=44,270 width=52) (actual time=48.714..53.498 rows=44,268 loops=1)

  • Sort Key: plan_roles_1.plan_row_uuid, plan_roles_1.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 4998kB
61. 13.417 31.373 ↑ 1.0 44,268 1

Hash Join (cost=1,661.02..3,476.43 rows=44,270 width=52) (actual time=15.084..31.373 rows=44,268 loops=1)

  • Hash Cond: (plan_roles_1.plan_row_uuid = plan_rows_3.uuid)
62. 3.113 3.113 ↑ 1.0 44,270 1

Seq Scan on plan_roles plan_roles_1 (cost=0.00..1,206.70 rows=44,270 width=24) (actual time=0.008..3.113 rows=44,270 loops=1)

63. 7.844 14.843 ↑ 1.0 47,734 1

Hash (cost=1,064.34..1,064.34 rows=47,734 width=28) (actual time=14.843..14.843 rows=47,734 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3159kB
64. 6.999 6.999 ↑ 1.0 47,734 1

Seq Scan on plan_rows plan_rows_3 (cost=0.00..1,064.34 rows=47,734 width=28) (actual time=0.005..6.999 rows=47,734 loops=1)

65. 0.539 115.829 ↑ 2.6 2,725 1

Hash (cost=20,857.92..20,857.92 rows=7,179 width=40) (actual time=115.828..115.829 rows=2,725 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 268kB
66. 0.891 115.290 ↑ 2.6 2,725 1

Nested Loop Left Join (cost=49.00..20,857.92 rows=7,179 width=40) (actual time=22.973..115.290 rows=2,725 loops=1)

  • Join Filter: (active_plan.project_id = epics.project_id)
67. 64.499 114.399 ↓ 1.1 2,725 1

Hash Join (cost=48.72..20,737.57 rows=2,393 width=36) (actual time=22.955..114.399 rows=2,725 loops=1)

  • Hash Cond: (time_logs.epic_id = epics.id)
68. 49.188 49.188 ↑ 1.0 700,321 1

Seq Scan on time_logs (cost=0.00..18,850.21 rows=700,321 width=28) (actual time=0.005..49.188 rows=700,321 loops=1)

69. 0.017 0.712 ↑ 1.0 90 1

Hash (cost=47.60..47.60 rows=90 width=12) (actual time=0.712..0.712 rows=90 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
70. 0.695 0.695 ↑ 1.0 90 1

Index Scan using index_epics_on_project_id on epics (cost=0.29..47.60 rows=90 width=12) (actual time=0.663..0.695 rows=90 loops=1)

  • Index Cond: (project_id = 10047)
71. 0.000 0.000 ↑ 3.0 1 2,725

Materialize (cost=0.28..12.67 rows=3 width=8) (actual time=0.000..0.000 rows=1 loops=2,725)

72. 0.015 0.015 ↑ 3.0 1 1

Index Scan using index_project_plans_on_project_id on project_plans active_plan (cost=0.28..12.65 rows=3 width=8) (actual time=0.011..0.015 rows=1 loops=1)

  • Index Cond: (project_id = 10047)
  • Filter: active
  • Rows Removed by Filter: 5
73. 0.912 11.366 ↑ 1.0 4,211 1

Hash (cost=942.99..942.99 rows=4,239 width=40) (actual time=11.366..11.366 rows=4,211 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 369kB
74. 0.660 10.454 ↑ 1.0 4,211 1

Subquery Scan on sa (cost=858.21..942.99 rows=4,239 width=40) (actual time=8.951..10.454 rows=4,211 loops=1)

75. 0.654 9.794 ↑ 1.0 4,211 1

Unique (cost=858.21..900.60 rows=4,239 width=65) (actual time=8.950..9.794 rows=4,211 loops=1)

76. 2.266 9.140 ↑ 1.0 4,239 1

Sort (cost=858.21..868.81 rows=4,239 width=65) (actual time=8.949..9.140 rows=4,239 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: 788kB
77. 2.514 6.874 ↑ 1.0 4,239 1

WindowAgg (cost=496.85..602.82 rows=4,239 width=65) (actual time=4.110..6.874 rows=4,239 loops=1)

78. 1.611 4.360 ↑ 1.0 4,239 1

Sort (cost=496.85..507.45 rows=4,239 width=56) (actual time=4.100..4.360 rows=4,239 loops=1)

  • Sort Key: staff_membership_activity_links.staff_membership_id, staff_membership_activity_links.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 789kB
79. 1.125 2.749 ↑ 1.0 4,239 1

Hash Join (cost=145.84..241.46 rows=4,239 width=56) (actual time=1.335..2.749 rows=4,239 loops=1)

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships_1.id)
80. 0.309 0.309 ↑ 1.0 4,246 1

Seq Scan on staff_membership_activity_links (cost=0.00..84.46 rows=4,246 width=28) (actual time=0.013..0.309 rows=4,246 loops=1)

81. 0.592 1.315 ↑ 1.0 3,993 1

Hash (cost=95.93..95.93 rows=3,993 width=32) (actual time=1.315..1.315 rows=3,993 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 276kB
82. 0.723 0.723 ↑ 1.0 3,993 1

Seq Scan on staff_memberships staff_memberships_1 (cost=0.00..95.93 rows=3,993 width=32) (actual time=0.005..0.723 rows=3,993 loops=1)

83. 0.001 0.002 ↓ 0.0 0 1

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

84. 0.001 0.001 ↓ 0.0 0 1

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

  • One-Time Filter: false
85. 0.003 0.116 ↑ 11.8 10 1

Hash (cost=27.20..27.20 rows=118 width=4) (actual time=0.116..0.116 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
86. 0.113 0.113 ↑ 11.8 10 1

Seq Scan on accounts (cost=0.00..27.20 rows=118 width=4) (actual time=0.015..0.113 rows=10 loops=1)

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 344
87. 0.374 9.126 ↓ 0.0 0 4,563

Materialize (cost=115.67..6,288.54 rows=196 width=44) (actual time=0.002..0.002 rows=0 loops=4,563)

88. 0.003 8.752 ↓ 0.0 0 1

Append (cost=115.67..6,287.56 rows=196 width=44) (actual time=8.752..8.752 rows=0 loops=1)

89. 0.000 0.022 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=115.67..1,527.05 rows=49 width=44) (actual time=0.022..0.022 rows=0 loops=1)

90. 0.001 0.022 ↓ 0.0 0 1

Nested Loop (cost=115.67..1,526.56 rows=49 width=612) (actual time=0.022..0.022 rows=0 loops=1)

91. 0.000 0.021 ↓ 0.0 0 1

Nested Loop (cost=115.25..144.81 rows=1 width=24) (actual time=0.021..0.021 rows=0 loops=1)

  • Join Filter: (clients.brand_id = rate_cards.rateable_id)
92. 0.000 0.021 ↓ 0.0 0 1

Nested Loop (cost=0.99..25.08 rows=1 width=12) (actual time=0.021..0.021 rows=0 loops=1)

93. 0.001 0.021 ↓ 0.0 0 1

Nested Loop (cost=0.84..24.92 rows=1 width=8) (actual time=0.021..0.021 rows=0 loops=1)

94. 0.001 0.020 ↓ 0.0 0 1

Nested Loop (cost=0.56..16.61 rows=1 width=8) (actual time=0.020..0.020 rows=0 loops=1)

95. 0.012 0.012 ↑ 1.0 1 1

Index Scan using projects_pkey on projects projects_2 (cost=0.28..8.30 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=1)

  • Index Cond: (id = 10047)
96. 0.007 0.007 ↓ 0.0 0 1

Index Scan using index_pricing_models_on_project_id on pricing_models (cost=0.28..8.30 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: (project_id = 10047)
  • Filter: ((rates_type)::text = 'brand'::text)
  • Rows Removed by Filter: 1
97. 0.000 0.000 ↓ 0.0 0

Index Scan using clients_pkey on clients (cost=0.28..8.29 rows=1 width=8) (never executed)

  • Index Cond: (id = projects_2.client_id)
98. 0.000 0.000 ↓ 0.0 0

Index Only Scan using brands_pkey on brands (cost=0.14..0.17 rows=1 width=4) (never executed)

  • Index Cond: (id = clients.brand_id)
  • Heap Fetches: 0
99. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=114.26..117.14 rows=115 width=45) (never executed)

100. 0.000 0.000 ↓ 0.0 0

Sort (cost=114.26..114.55 rows=115 width=29) (never executed)

  • Sort Key: rate_cards.rateable_id, rate_cards.start_date DESC NULLS LAST
101. 0.000 0.000 ↓ 0.0 0

Seq Scan on rate_cards (cost=0.00..110.33 rows=115 width=29) (never executed)

  • Filter: ((rateable_type)::text = 'Brand'::text)
102. 0.000 0.000 ↓ 0.0 0

Index Scan using index_rates_on_activity_id_and_rate_card_id on rates (cost=0.42..1,381.27 rows=49 width=28) (never executed)

  • Index Cond: (rate_card_id = rate_cards.id)
103. 0.000 1.038 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=149.76..1,498.15 rows=49 width=44) (actual time=1.038..1.038 rows=0 loops=1)

104. 0.001 1.038 ↓ 0.0 0 1

Nested Loop (cost=149.76..1,497.66 rows=49 width=612) (actual time=1.038..1.038 rows=0 loops=1)

105. 0.000 1.037 ↓ 0.0 0 1

Nested Loop (cost=149.34..176.36 rows=1 width=24) (actual time=1.037..1.037 rows=0 loops=1)

106. 0.000 1.037 ↓ 0.0 0 1

Nested Loop (cost=149.07..176.06 rows=1 width=32) (actual time=1.037..1.037 rows=0 loops=1)

107. 0.041 1.029 ↑ 1.0 1 1

Hash Join (cost=148.78..167.74 rows=1 width=32) (actual time=0.757..1.029 rows=1 loops=1)

  • Hash Cond: (rate_cards_1.rateable_id = clients_1.office_id)
108. 0.253 0.975 ↑ 1.0 489 1

WindowAgg (cost=132.17..144.39 rows=489 width=45) (actual time=0.706..0.975 rows=489 loops=1)

109. 0.142 0.722 ↑ 1.0 489 1

Sort (cost=132.17..133.39 rows=489 width=29) (actual time=0.699..0.722 rows=489 loops=1)

  • Sort Key: rate_cards_1.rateable_id, rate_cards_1.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 63kB
110. 0.580 0.580 ↑ 1.0 489 1

Seq Scan on rate_cards rate_cards_1 (cost=0.00..110.33 rows=489 width=29) (actual time=0.106..0.580 rows=489 loops=1)

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 4337
111. 0.002 0.013 ↑ 1.0 1 1

Hash (cost=16.60..16.60 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
112. 0.003 0.011 ↑ 1.0 1 1

Nested Loop (cost=0.56..16.60 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=1)

113. 0.003 0.003 ↑ 1.0 1 1

Index Scan using projects_pkey on projects projects_3 (cost=0.28..8.30 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: (id = 10047)
114. 0.005 0.005 ↑ 1.0 1 1

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

  • Index Cond: (id = projects_3.client_id)
115. 0.008 0.008 ↓ 0.0 0 1

Index Scan using index_pricing_models_on_project_id on pricing_models pricing_models_1 (cost=0.28..8.30 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (project_id = 10047)
  • Filter: ((rates_type)::text = 'office'::text)
  • Rows Removed by Filter: 1
116. 0.000 0.000 ↓ 0.0 0

Index Only Scan using offices_pkey on offices (cost=0.27..0.31 rows=1 width=4) (never executed)

  • Index Cond: (id = clients_1.office_id)
  • Heap Fetches: 0
117. 0.000 0.000 ↓ 0.0 0

Index Scan using index_rates_on_activity_id_and_rate_card_id on rates rates_1 (cost=0.42..1,320.80 rows=49 width=28) (never executed)

  • Index Cond: (rate_card_id = rate_cards_1.id)
118. 0.000 2.248 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=217.98..1,602.12 rows=49 width=44) (actual time=2.248..2.248 rows=0 loops=1)

119. 0.001 2.248 ↓ 0.0 0 1

Nested Loop (cost=217.98..1,601.63 rows=49 width=612) (actual time=2.247..2.248 rows=0 loops=1)

120. 0.000 2.247 ↓ 0.0 0 1

Nested Loop (cost=217.56..294.08 rows=1 width=24) (actual time=2.247..2.247 rows=0 loops=1)

  • Join Filter: (projects_4.client_id = clients_2.id)
121. 0.001 2.247 ↓ 0.0 0 1

Nested Loop (cost=217.29..293.69 rows=1 width=32) (actual time=2.247..2.247 rows=0 loops=1)

122. 0.129 2.240 ↑ 1.0 1 1

Hash Join (cost=217.00..285.38 rows=1 width=32) (actual time=1.387..2.240 rows=1 loops=1)

  • Hash Cond: (rate_cards_2.rateable_id = projects_4.client_id)
123. 0.934 2.102 ↑ 1.0 1,817 1

WindowAgg (cost=208.69..254.12 rows=1,817 width=45) (actual time=1.091..2.102 rows=1,817 loops=1)

124. 0.593 1.168 ↑ 1.0 1,817 1

Sort (cost=208.69..213.23 rows=1,817 width=29) (actual time=1.086..1.168 rows=1,817 loops=1)

  • Sort Key: rate_cards_2.rateable_id, rate_cards_2.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 190kB
125. 0.575 0.575 ↑ 1.0 1,817 1

Seq Scan on rate_cards rate_cards_2 (cost=0.00..110.33 rows=1,817 width=29) (actual time=0.004..0.575 rows=1,817 loops=1)

  • Filter: ((rateable_type)::text = 'Client'::text)
  • Rows Removed by Filter: 3009
126. 0.003 0.009 ↑ 1.0 1 1

Hash (cost=8.30..8.30 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
127. 0.006 0.006 ↑ 1.0 1 1

Index Scan using projects_pkey on projects projects_4 (cost=0.28..8.30 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (id = 10047)
128. 0.006 0.006 ↓ 0.0 0 1

Index Scan using index_pricing_models_on_project_id on pricing_models pricing_models_2 (cost=0.28..8.30 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (project_id = 10047)
  • Filter: ((rates_type)::text = 'client'::text)
  • Rows Removed by Filter: 1
129. 0.000 0.000 ↓ 0.0 0

Index Only Scan using clients_pkey on clients clients_2 (cost=0.28..0.37 rows=1 width=4) (never executed)

  • Index Cond: (id = rate_cards_2.rateable_id)
  • Heap Fetches: 0
130. 0.000 0.000 ↓ 0.0 0

Index Scan using index_rates_on_activity_id_and_rate_card_id on rates rates_2 (cost=0.42..1,307.06 rows=49 width=28) (never executed)

  • Index Cond: (rate_card_id = rate_cards_2.id)
131. 0.001 5.441 ↓ 0.0 0 1

Subquery Scan on *SELECT* 4 (cost=254.41..1,659.27 rows=49 width=44) (actual time=5.441..5.441 rows=0 loops=1)

132. 0.001 5.440 ↓ 0.0 0 1

Nested Loop (cost=254.41..1,658.78 rows=49 width=612) (actual time=5.440..5.440 rows=0 loops=1)

133. 0.003 2.872 ↑ 1.0 1 1

Nested Loop (cost=253.99..352.50 rows=1 width=24) (actual time=1.827..2.872 rows=1 loops=1)

134. 0.166 2.864 ↑ 1.0 1 1

Hash Join (cost=253.70..344.19 rows=1 width=24) (actual time=1.820..2.864 rows=1 loops=1)

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
135. 1.257 2.690 ↑ 1.0 2,405 1

WindowAgg (cost=245.39..305.51 rows=2,405 width=45) (actual time=1.303..2.690 rows=2,405 loops=1)

136. 0.834 1.433 ↑ 1.0 2,405 1

Sort (cost=245.39..251.40 rows=2,405 width=29) (actual time=1.298..1.433 rows=2,405 loops=1)

  • Sort Key: rate_cards_3.rateable_id, rate_cards_3.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 284kB
137. 0.599 0.599 ↑ 1.0 2,405 1

Seq Scan on rate_cards rate_cards_3 (cost=0.00..110.33 rows=2,405 width=29) (actual time=0.004..0.599 rows=2,405 loops=1)

  • Filter: ((rateable_type)::text = 'PricingModel'::text)
  • Rows Removed by Filter: 2421
138. 0.003 0.008 ↑ 1.0 1 1

Hash (cost=8.30..8.30 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
139. 0.005 0.005 ↑ 1.0 1 1

Index Scan using index_pricing_models_on_project_id on pricing_models pricing_models_3 (cost=0.28..8.30 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: (project_id = 10047)
  • Filter: ((rates_type)::text = 'custom'::text)
140. 0.005 0.005 ↑ 1.0 1 1

Index Only Scan using projects_pkey on projects projects_5 (cost=0.28..8.30 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: (id = 10047)
  • Heap Fetches: 1
141. 2.567 2.567 ↓ 0.0 0 1

Index Scan using index_rates_on_activity_id_and_rate_card_id on rates rates_3 (cost=0.42..1,305.79 rows=49 width=28) (actual time=2.567..2.567 rows=0 loops=1)

  • Index Cond: (rate_card_id = rate_cards_3.id)
Planning time : 5.607 ms
Execution time : 708.547 ms