explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dhnD

Settings
# exclusive inclusive rows x rows loops node
1. 58,522.143 99,470.403 ↓ 3.9 850,568 1

Hash Left Join (cost=1,091,416.22..24,764,621.75 rows=216,456 width=188) (actual time=2,293.524..99,470.403 rows=850,568 loops=1)

  • Hash Cond: ((projects.id = ""*SELECT* 1"".project_id) AND ((COALESCE(""*SELECT* 1_1"".activity_id, sa.activity_id)) = ""*SELECT* 1"".activity_id))
  • Join Filter: (("*SELECT* 1_1".date >= "*SELECT* 1".start_date) AND (("*SELECT* 1_1".date <= "*SELECT* 1".end_date) OR ("*SELECT* 1".end_date IS NULL)))
  • Rows Removed by Join Filter: 238662
  • Buffers: shared hit=11841096 read=4869
2. 470.379 40,598.999 ↓ 3.9 850,568 1

Hash Join (cost=1,064,036.01..9,831,730.05 rows=216,456 width=136) (actual time=1,941.689..40,598.999 rows=850,568 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
  • Buffers: shared hit=3067509 read=4869
3. 239.959 40,128.459 ↓ 1.7 1,116,748 1

Append (cost=1,064,017.34..9,823,492.72 rows=649,367 width=136) (actual time=1,941.509..40,128.459 rows=1,116,748 loops=1)

  • Buffers: shared hit=3067498 read=4869
4. 999.462 39,427.326 ↓ 2.7 1,087,842 1

Hash Join (cost=1,064,017.34..9,302,607.62 rows=404,209 width=108) (actual time=1,941.508..39,427.326 rows=1,087,842 loops=1)

  • Hash Cond: (projects.account_id = accounts_1.id)
  • Join 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))
  • Rows Removed by Join Filter: 13596
  • Buffers: shared hit=3066446 read=4869
5. 818.606 38,427.709 ↑ 1.1 1,101,438 1

Hash Left Join (cost=1,063,998.38..9,284,260.78 rows=1,200,621 width=144) (actual time=1,928.652..38,427.709 rows=1,101,438 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: 259092
  • Buffers: shared hit=3066435 read=4869
6. 574.055 37,595.051 ↑ 1.1 1,101,438 1

Hash Join (cost=1,063,002.52..8,000,001.13 rows=1,200,621 width=120) (actual time=1,914.544..37,595.051 rows=1,101,438 loops=1)

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
  • Buffers: shared hit=3066350 read=4869
7. 220.077 37,018.727 ↑ 1.1 1,101,438 1

Append (cost=1,062,773.75..7,984,612.22 rows=1,200,621 width=116) (actual time=1,912.226..37,018.727 rows=1,101,438 loops=1)

  • Buffers: shared hit=3066252 read=4869
8. 176.280 19,878.845 ↑ 1.3 396,845 1

Result (cost=1,062,773.75..7,787,123.57 rows=496,660 width=116) (actual time=1,912.225..19,878.845 rows=396,845 loops=1)

  • Buffers: shared hit=3060516
9. 54.890 19,702.565 ↑ 1.3 396,845 1

Append (cost=1,062,773.75..7,780,915.32 rows=496,660 width=140) (actual time=1,912.222..19,702.565 rows=396,845 loops=1)

  • Buffers: shared hit=3060516
10. 81.176 3,862.892 ↑ 1.4 338,629 1

Subquery Scan on *SELECT* 1_1 (cost=1,062,773.75..7,714,854.75 rows=457,222 width=114) (actual time=1,912.221..3,862.892 rows=338,629 loops=1)

  • Buffers: shared hit=3454
11. 1,849.672 3,781.716 ↑ 1.4 338,629 1

Hash Right Join (cost=1,062,773.75..7,707,996.42 rows=457,222 width=134) (actual time=1,912.220..3,781.716 rows=338,629 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: 14110732
  • Buffers: shared hit=3454
12. 36.987 84.068 ↑ 17.8 32,266 1

HashAggregate (cost=537,214.32..545,839.32 rows=575,000 width=52) (actual time=62.001..84.068 rows=32,266 loops=1)

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
  • Buffers: shared hit=331
13. 11.065 47.081 ↑ 134.2 32,422 1

Hash Join (cost=138.05..493,700.50 rows=4,351,382 width=21) (actual time=1.695..47.081 rows=32,422 loops=1)

  • Hash Cond: (vacations.staff_membership_id = staff_memberships.id)
  • Join Filter: ((generate_series_1.generate_series >= staff_memberships.joined_at) AND ((staff_memberships.archived_at IS NULL) OR (generate_series_1.generate_series <= staff_memberships.archived_at)))
  • Rows Removed by Join Filter: 1908
  • Buffers: shared hit=331
14. 10.390 34.380 ↑ 634.8 34,330 1

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

  • Buffers: shared hit=283
15. 2.198 2.198 ↑ 1.0 21,792 1

Seq Scan on vacations (cost=0.00..500.92 rows=21,792 width=17) (actual time=0.013..2.198 rows=21,792 loops=1)

  • Buffers: shared hit=283
16. 21.792 21.792 ↑ 500.0 2 21,792

Function Scan on generate_series generate_series_1 (cost=0.01..10.01 rows=1,000 width=8) (actual time=0.001..0.001 rows=2 loops=21,792)

17. 0.836 1.636 ↑ 1.0 4,002 1

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

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

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

  • Buffers: shared hit=48
19. 119.897 1,847.976 ↑ 1.4 338,629 1

Hash (cost=518,701.10..518,701.10 rows=457,222 width=66) (actual time=1,847.976..1,847.976 rows=338,629 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 38679kB
  • Buffers: shared hit=3123
20. 939.407 1,728.079 ↑ 1.4 338,629 1

Hash Anti Join (cost=463,568.36..518,701.10 rows=457,222 width=66) (actual time=608.896..1,728.079 rows=338,629 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: 11449923
  • Buffers: shared hit=3123
21. 144.307 767.721 ↑ 1.7 346,275 1

Hash Right Join (cost=461,391.56..465,319.85 rows=577,750 width=66) (actual time=587.915..767.721 rows=346,275 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))))
  • Rows Removed by Join Filter: 3247
  • Buffers: shared hit=2439
22. 66.590 153.342 ↓ 9.0 44,428 1

HashAggregate (cost=8,529.07..8,603.30 rows=4,949 width=48) (actual time=113.645..153.342 rows=44,428 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)
  • Buffers: shared hit=989
23. 27.921 86.752 ↑ 1.0 44,581 1

WindowAgg (cost=6,077.00..7,080.12 rows=44,583 width=68) (actual time=53.745..86.752 rows=44,581 loops=1)

  • Buffers: shared hit=989
24. 22.061 58.831 ↑ 1.0 44,581 1

Sort (cost=6,077.00..6,188.46 rows=44,583 width=52) (actual time=53.735..58.831 rows=44,581 loops=1)

  • Sort Key: plan_roles.plan_row_uuid, plan_roles.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 5023kB
  • Buffers: shared hit=989
25. 14.989 36.770 ↑ 1.0 44,581 1

Hash Join (cost=1,631.38..2,634.26 rows=44,583 width=52) (actual time=17.944..36.770 rows=44,581 loops=1)

  • Hash Cond: (plan_roles.plan_row_uuid = plan_rows_1.uuid)
  • Buffers: shared hit=989
26. 4.078 4.078 ↑ 1.0 44,581 1

Seq Scan on plan_roles (cost=0.00..885.83 rows=44,583 width=24) (actual time=0.012..4.078 rows=44,581 loops=1)

  • Buffers: shared hit=440
27. 9.732 17.703 ↑ 1.0 48,106 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3180kB
  • Buffers: shared hit=549
28. 7.971 7.971 ↑ 1.0 48,106 1

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

  • Buffers: shared hit=549
29. 123.164 470.072 ↑ 1.7 346,275 1

Hash (cost=442,751.87..442,751.87 rows=577,750 width=70) (actual time=470.071..470.072 rows=346,275 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 45544kB
  • Buffers: shared hit=1450
30. 61.988 346.908 ↑ 1.7 346,275 1

Nested Loop (cost=1,989.96..442,751.87 rows=577,750 width=70) (actual time=23.472..346.908 rows=346,275 loops=1)

  • Buffers: shared hit=1450
31. 8.446 66.824 ↓ 1.2 27,262 1

Hash Join (cost=1,989.96..3,661.86 rows=23,110 width=70) (actual time=23.449..66.824 rows=27,262 loops=1)

  • Hash Cond: (project_plans.project_id = projects_1.id)
  • Buffers: shared hit=1450
32. 10.887 56.404 ↓ 1.2 27,262 1

Hash Join (cost=1,761.19..3,372.38 rows=23,110 width=66) (actual time=21.396..56.404 rows=27,262 loops=1)

  • Hash Cond: (plan_items.plan_row_uuid = plan_rows.uuid)
  • Buffers: shared hit=1352
33. 13.267 26.131 ↓ 1.1 29,797 1

Hash Join (cost=202.03..1,744.27 rows=26,265 width=54) (actual time=1.751..26.131 rows=29,797 loops=1)

  • Hash Cond: (plan_items.project_plan_id = project_plans.id)
  • Buffers: shared hit=803
34. 11.148 11.148 ↓ 1.0 55,664 1

Seq Scan on plan_items (cost=0.00..1,396.10 rows=55,634 width=46) (actual time=0.008..11.148 rows=55,664 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
  • Buffers: shared hit=697
35. 0.538 1.716 ↑ 1.0 2,851 1

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

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

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

  • Filter: active
  • Rows Removed by Filter: 3188
  • Buffers: shared hit=106
37. 10.134 19.386 ↓ 1.0 42,344 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2816kB
  • Buffers: shared hit=549
38. 9.252 9.252 ↓ 1.0 42,344 1

Seq Scan on plan_rows (cost=0.00..1,030.06 rows=42,328 width=28) (actual time=0.009..9.252 rows=42,344 loops=1)

  • Filter: (user_id IS NOT NULL)
  • Rows Removed by Filter: 5762
  • Buffers: shared hit=549
39. 1.031 1.974 ↑ 1.0 5,812 1

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

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

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

  • Buffers: shared hit=98
41. 218.096 218.096 ↑ 1.9 13 27,262

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.003..0.008 rows=13 loops=27,262)

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

Hash (cost=1,281.12..1,281.12 rows=59,712 width=12) (actual time=20.951..20.951 rows=58,975 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3047kB
  • Buffers: shared hit=684
43. 10.045 10.045 ↑ 1.0 59,712 1

Seq Scan on non_working_intervals_by_dates (cost=0.00..1,281.12 rows=59,712 width=12) (actual time=0.014..10.045 rows=59,712 loops=1)

  • Buffers: shared hit=684
44. 44.337 15,784.783 ↓ 1.5 58,216 1

Hash Join (cost=1,059.11..63,182.89 rows=39,438 width=114) (actual time=6.266..15,784.783 rows=58,216 loops=1)

  • Hash Cond: (project_plans_1.project_id = project.id)
  • Join Filter: (NOT (SubPlan 1))
  • Rows Removed by Join Filter: 770
  • Buffers: shared hit=3057062
45. 17.487 107.096 ↑ 1.3 58,986 1

Nested Loop (cost=830.34..62,335.05 rows=78,875 width=58) (actual time=4.149..107.096 rows=58,986 loops=1)

  • Buffers: shared hit=1251
46. 2.320 31.304 ↑ 1.2 2,535 1

Hash Join (cost=830.34..2,390.04 rows=3,155 width=58) (actual time=4.135..31.304 rows=2,535 loops=1)

  • Hash Cond: (plan_items_1.project_plan_id = project_plans_1.id)
  • Buffers: shared hit=1251
47. 14.293 27.645 ↓ 1.2 7,876 1

Hash Join (cost=628.31..2,170.46 rows=6,682 width=54) (actual time=2.763..27.645 rows=7,876 loops=1)

  • Hash Cond: (plan_items_1.plan_row_uuid = plan_rows_2.uuid)
  • Buffers: shared hit=1145
48. 10.668 10.668 ↓ 1.0 55,664 1

Seq Scan on plan_items plan_items_1 (cost=0.00..1,396.10 rows=55,634 width=46) (actual time=0.006..10.668 rows=55,664 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
  • Buffers: shared hit=697
49. 1.090 2.684 ↑ 1.0 5,762 1

Hash (cost=556.08..556.08 rows=5,778 width=24) (actual time=2.684..2.684 rows=5,762 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 366kB
  • Buffers: shared hit=448
50. 1.594 1.594 ↑ 1.0 5,762 1

Index Scan using index_plan_rows_on_user_id on plan_rows plan_rows_2 (cost=0.29..556.08 rows=5,778 width=24) (actual time=0.007..1.594 rows=5,762 loops=1)

  • Index Cond: (user_id IS NULL)
  • Buffers: shared hit=448
51. 0.409 1.339 ↑ 1.0 2,851 1

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

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

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

  • Filter: active
  • Rows Removed by Filter: 3188
  • Buffers: shared hit=106
53. 58.305 58.305 ↑ 1.1 23 2,535

Function Scan on generate_series generate_series_2 (cost=0.01..18.76 rows=25 width=8) (actual time=0.006..0.023 rows=23 loops=2,535)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 9
54. 0.974 2.060 ↑ 1.0 5,812 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 314kB
  • Buffers: shared hit=98
55. 1.086 1.086 ↑ 1.0 5,812 1

Seq Scan on projects project (cost=0.00..156.12 rows=5,812 width=12) (actual time=0.010..1.086 rows=5,812 loops=1)

  • Buffers: shared hit=98
56.          

SubPlan (for Hash Join)

57. 58.986 15,631.290 ↓ 0.0 0 58,986

Nested Loop (cost=0.69..44.76 rows=1 width=0) (actual time=0.265..0.265 rows=0 loops=58,986)

  • Join Filter: (non_working_intervals_by_dates_2.office_id = clients_3.office_id)
  • Rows Removed by Join Filter: 3
  • Buffers: shared hit=3055713
58. 117.972 117.972 ↑ 1.0 1 58,986

Index Scan using clients_pkey on clients clients_3 (cost=0.28..2.50 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=58,986)

  • Index Cond: (project.client_id = id)
  • Buffers: shared hit=177008
59. 15,454.332 15,454.332 ↑ 4.0 3 58,986

Index Scan using non_working_intervals_by_dates_idx on non_working_intervals_by_dates non_working_intervals_by_dates_2 (cost=0.41..42.12 rows=12 width=4) (actual time=0.254..0.262 rows=3 loops=58,986)

  • Index Cond: ((project.account_id = account_id) AND (generate_series_2.generate_series = date))
  • Buffers: shared hit=2878705
60. 16,106.255 16,919.805 ↓ 1.0 704,593 1

Hash Right Join (cost=45,029.23..184,445.93 rows=703,961 width=116) (actual time=720.210..16,919.805 rows=704,593 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: 62978265
  • Buffers: shared hit=5736 read=4869
61. 139.464 247.539 ↓ 9.0 44,428 1

HashAggregate (cost=8,529.07..8,603.30 rows=4,949 width=48) (actual time=150.484..247.539 rows=44,428 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)
  • Buffers: shared hit=989
62. 43.814 108.075 ↑ 1.0 44,581 1

WindowAgg (cost=6,077.00..7,080.12 rows=44,583 width=68) (actual time=56.236..108.075 rows=44,581 loops=1)

  • Buffers: shared hit=989
63. 26.423 64.261 ↑ 1.0 44,581 1

Sort (cost=6,077.00..6,188.46 rows=44,583 width=52) (actual time=56.225..64.261 rows=44,581 loops=1)

  • Sort Key: plan_roles_1.plan_row_uuid, plan_roles_1.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 5023kB
  • Buffers: shared hit=989
64. 15.904 37.838 ↑ 1.0 44,581 1

Hash Join (cost=1,631.38..2,634.26 rows=44,583 width=52) (actual time=17.935..37.838 rows=44,581 loops=1)

  • Hash Cond: (plan_roles_1.plan_row_uuid = plan_rows_3.uuid)
  • Buffers: shared hit=989
65. 4.237 4.237 ↑ 1.0 44,581 1

Seq Scan on plan_roles plan_roles_1 (cost=0.00..885.83 rows=44,583 width=24) (actual time=0.010..4.237 rows=44,581 loops=1)

  • Buffers: shared hit=440
66. 9.753 17.697 ↑ 1.0 48,106 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3180kB
  • Buffers: shared hit=549
67. 7.944 7.944 ↑ 1.0 48,106 1

Seq Scan on plan_rows plan_rows_3 (cost=0.00..1,030.06 rows=48,106 width=28) (actual time=0.007..7.944 rows=48,106 loops=1)

  • Buffers: shared hit=549
68. 190.102 566.011 ↑ 1.0 703,961 1

Hash (cost=27,700.65..27,700.65 rows=703,961 width=40) (actual time=566.010..566.011 rows=703,961 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 61640kB
  • Buffers: shared hit=4747 read=4869
69. 242.890 375.909 ↑ 1.0 703,961 1

Hash Left Join (cost=1,826.58..27,700.65 rows=703,961 width=40) (actual time=16.271..375.909 rows=703,961 loops=1)

  • Hash Cond: (time_logs.epic_id = epics.id)
  • Buffers: shared hit=4747 read=4869
70. 116.907 116.907 ↑ 1.0 703,961 1

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

  • Buffers: shared hit=4286 read=4869
71. 5.072 16.112 ↑ 1.0 26,435 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1424kB
  • Buffers: shared hit=461
72. 6.850 11.040 ↑ 1.0 26,435 1

Hash Left Join (cost=202.03..1,496.14 rows=26,435 width=16) (actual time=1.561..11.040 rows=26,435 loops=1)

  • Hash Cond: (epics.project_id = active_plan.project_id)
  • Buffers: shared hit=461
73. 2.656 2.656 ↑ 1.0 26,435 1

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

  • Buffers: shared hit=355
74. 0.463 1.534 ↑ 1.0 2,851 1

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

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

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

  • Filter: active
  • Rows Removed by Filter: 3188
  • Buffers: shared hit=106
76. 1.179 2.269 ↑ 1.0 5,812 1

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

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

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

  • Buffers: shared hit=98
78. 1.206 14.052 ↑ 1.0 4,221 1

Hash (cost=932.12..932.12 rows=4,249 width=40) (actual time=14.052..14.052 rows=4,221 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 370kB
  • Buffers: shared hit=85
79. 0.865 12.846 ↑ 1.0 4,221 1

Subquery Scan on sa (cost=847.14..932.12 rows=4,249 width=40) (actual time=10.868..12.846 rows=4,221 loops=1)

  • Buffers: shared hit=85
80. 0.835 11.981 ↑ 1.0 4,221 1

Unique (cost=847.14..889.63 rows=4,249 width=65) (actual time=10.866..11.981 rows=4,221 loops=1)

  • Buffers: shared hit=85
81. 2.759 11.146 ↑ 1.0 4,249 1

Sort (cost=847.14..857.76 rows=4,249 width=65) (actual time=10.865..11.146 rows=4,249 loops=1)

  • Sort Key: staff_memberships_1.account_id, staff_memberships_1.user_id, (COALESCE(staff_membership_activity_links.start_date, staff_memberships_1.joined_at)), (COALESCE(lag((staff_membership_activity_links.start_date - 1), 1) OVER (?), staff_memberships_1.archived_at, '3000-01-01'::date)) DESC
  • Sort Method: quicksort Memory: 790kB
  • Buffers: shared hit=85
82. 2.783 8.387 ↑ 1.0 4,249 1

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

  • Buffers: shared hit=85
83. 2.186 5.604 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=56) (actual time=5.299..5.604 rows=4,249 loops=1)

  • Sort Key: staff_membership_activity_links.staff_membership_id, staff_membership_activity_links.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 790kB
  • Buffers: shared hit=85
84. 1.344 3.418 ↑ 1.0 4,249 1

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

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

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

  • Buffers: shared hit=37
86. 0.902 1.682 ↑ 1.0 4,002 1

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

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

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

  • Buffers: shared hit=48
88. 0.060 0.155 ↑ 1.0 354 1

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

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

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

  • Buffers: shared hit=11
90. 19.910 461.174 ↑ 8.5 28,906 1

Subquery Scan on *SELECT* 2_1 (cost=424,372.25..513,596.17 rows=245,158 width=136) (actual time=147.469..461.174 rows=28,906 loops=1)

  • Buffers: shared hit=1052
91. 262.581 441.264 ↑ 8.5 28,906 1

Hash Anti Join (cost=424,372.25..510,531.69 rows=245,158 width=143) (actual time=147.466..441.264 rows=28,906 loops=1)

  • Hash Cond: ((staff_memberships_2.account_id = non_working_intervals_by_dates_1.account_id) AND (staff_memberships_2.user_id = non_working_intervals_by_dates_1.user_id))
  • Join Filter: (generate_series_3.generate_series = non_working_intervals_by_dates_1.date)
  • Rows Removed by Join Filter: 1153172
  • Buffers: shared hit=1052
92. 34.495 146.430 ↑ 9.5 29,394 1

Hash Join (cost=422,195.45..490,256.83 rows=280,181 width=64) (actual time=115.144..146.430 rows=29,394 loops=1)

  • Hash Cond: (staff_memberships_2.id = vacations_1.staff_membership_id)
  • Join Filter: ((generate_series_3.generate_series >= (COALESCE(staff_membership_activity_links_1.start_date, staff_memberships_2.joined_at))) AND (generate_series_3.generate_series <= (COALESCE(lag((staff_membership_activity_links_1.start_date - 1), 1) OVER (?), staff_memberships_2.archived_at, '3000-01-01'::date))))
  • Rows Removed by Join Filter: 10924
  • Buffers: shared hit=368
93. 1.944 20.643 ↑ 1.0 4,221 1

Unique (cost=836.52..879.01 rows=4,249 width=65) (actual time=17.968..20.643 rows=4,221 loops=1)

  • Buffers: shared hit=85
94. 4.732 18.699 ↑ 1.0 4,249 1

Sort (cost=836.52..847.14 rows=4,249 width=65) (actual time=17.967..18.699 rows=4,249 loops=1)

  • Sort Key: staff_memberships_2.account_id, staff_memberships_2.user_id, (COALESCE(staff_membership_activity_links_1.start_date, staff_memberships_2.joined_at)), (COALESCE(lag((staff_membership_activity_links_1.start_date - 1), 1) OVER (?), staff_memberships_2.archived_at, '3000-01-01'::date)) DESC
  • Sort Method: quicksort Memory: 592kB
  • Buffers: shared hit=85
95. 4.755 13.967 ↑ 1.0 4,249 1

WindowAgg (cost=484.85..580.45 rows=4,249 width=65) (actual time=8.730..13.967 rows=4,249 loops=1)

  • Buffers: shared hit=85
96. 3.209 9.212 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=44) (actual time=8.718..9.212 rows=4,249 loops=1)

  • Sort Key: staff_membership_activity_links_1.staff_membership_id, staff_membership_activity_links_1.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 561kB
  • Buffers: shared hit=85
97. 2.339 6.003 ↑ 1.0 4,249 1

Hash Join (cost=138.05..228.79 rows=4,249 width=44) (actual time=3.002..6.003 rows=4,249 loops=1)

  • Hash Cond: (staff_membership_activity_links_1.staff_membership_id = staff_memberships_2.id)
  • Buffers: shared hit=85
98. 0.694 0.694 ↑ 1.0 4,256 1

Seq Scan on staff_membership_activity_links staff_membership_activity_links_1 (cost=0.00..79.56 rows=4,256 width=20) (actual time=0.016..0.694 rows=4,256 loops=1)

  • Buffers: shared hit=37
99. 1.551 2.970 ↑ 1.0 4,002 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 245kB
  • Buffers: shared hit=48
100. 1.419 1.419 ↑ 1.0 4,002 1

Seq Scan on staff_memberships staff_memberships_2 (cost=0.00..88.02 rows=4,002 width=24) (actual time=0.010..1.419 rows=4,002 loops=1)

  • Buffers: shared hit=48
101. 15.053 91.292 ↑ 17.5 31,132 1

Hash (cost=414,548.93..414,548.93 rows=544,800 width=44) (actual time=91.292..91.292 rows=31,132 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 10776kB
  • Buffers: shared hit=283
102. 28.933 76.239 ↑ 17.5 31,132 1

Nested Loop (cost=0.01..414,548.93 rows=544,800 width=44) (actual time=0.049..76.239 rows=31,132 loops=1)

  • Buffers: shared hit=283
103. 3.722 3.722 ↑ 1.0 21,792 1

Seq Scan on vacations vacations_1 (cost=0.00..500.92 rows=21,792 width=44) (actual time=0.022..3.722 rows=21,792 loops=1)

  • Buffers: shared hit=283
104. 43.584 43.584 ↑ 25.0 1 21,792

Function Scan on generate_series generate_series_3 (cost=0.01..18.76 rows=25 width=8) (actual time=0.002..0.002 rows=1 loops=21,792)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 0
105. 16.932 32.253 ↑ 1.0 58,975 1

Hash (cost=1,281.12..1,281.12 rows=59,712 width=12) (actual time=32.253..32.253 rows=58,975 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3047kB
  • Buffers: shared hit=684
106. 15.321 15.321 ↑ 1.0 59,712 1

Seq Scan on non_working_intervals_by_dates non_working_intervals_by_dates_1 (cost=0.00..1,281.12 rows=59,712 width=12) (actual time=0.014..15.321 rows=59,712 loops=1)

  • Buffers: shared hit=684
107. 0.005 0.161 ↑ 11.8 10 1

Hash (cost=17.20..17.20 rows=118 width=4) (actual time=0.161..0.161 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=11
108. 0.156 0.156 ↑ 11.8 10 1

Seq Scan on accounts (cost=0.00..17.20 rows=118 width=4) (actual time=0.022..0.156 rows=10 loops=1)

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 344
  • Buffers: shared hit=11
109. 85.064 349.261 ↑ 1.0 263,033 1

Hash (cost=23,315.28..23,315.28 rows=270,995 width=44) (actual time=349.260..349.261 rows=263,033 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 24593kB
  • Buffers: shared hit=9627
110. 28.612 264.197 ↑ 1.0 263,033 1

Append (cost=109.73..23,315.28 rows=270,995 width=44) (actual time=1.692..264.197 rows=263,033 loops=1)

  • Buffers: shared hit=9627
111. 0.094 16.629 ↓ 9.7 474 1

Subquery Scan on *SELECT* 1 (cost=109.73..1,614.47 rows=49 width=44) (actual time=1.691..16.629 rows=474 loops=1)

  • Buffers: shared hit=3549
112. 0.127 16.535 ↓ 9.7 474 1

Nested Loop (cost=109.73..1,613.98 rows=49 width=612) (actual time=1.690..16.535 rows=474 loops=1)

  • Buffers: shared hit=3549
113. 0.117 15.934 ↓ 9.7 474 1

Nested Loop (cost=109.59..1,606.06 rows=49 width=52) (actual time=1.684..15.934 rows=474 loops=1)

  • Buffers: shared hit=2601
114. 0.042 1.342 ↓ 5.0 5 1

Nested Loop (cost=109.17..277.90 rows=1 width=32) (actual time=1.140..1.342 rows=5 loops=1)

  • Join Filter: (clients.brand_id = rate_cards.rateable_id)
  • Rows Removed by Join Filter: 570
  • Buffers: shared hit=144
115. 0.086 0.610 ↑ 1.0 115 1

WindowAgg (cost=108.61..111.49 rows=115 width=45) (actual time=0.522..0.610 rows=115 loops=1)

  • Buffers: shared hit=44
116. 0.046 0.524 ↑ 1.0 115 1

Sort (cost=108.61..108.90 rows=115 width=29) (actual time=0.515..0.524 rows=115 loops=1)

  • Sort Key: rate_cards.rateable_id, rate_cards.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 33kB
  • Buffers: shared hit=44
117. 0.478 0.478 ↑ 1.0 115 1

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

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4739
  • Buffers: shared hit=44
118. 0.080 0.690 ↑ 1.0 5 115

Materialize (cost=0.56..156.65 rows=5 width=8) (actual time=0.003..0.006 rows=5 loops=115)

  • Buffers: shared hit=100
119. 0.005 0.610 ↑ 1.0 5 1

Nested Loop (cost=0.56..156.63 rows=5 width=8) (actual time=0.334..0.610 rows=5 loops=1)

  • Buffers: shared hit=100
120. 0.006 0.595 ↑ 1.0 5 1

Nested Loop (cost=0.28..155.13 rows=5 width=8) (actual time=0.328..0.595 rows=5 loops=1)

  • Buffers: shared hit=85
121. 0.574 0.574 ↑ 1.0 5 1

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

  • Filter: ((rates_type)::text = 'brand'::text)
  • Rows Removed by Filter: 5805
  • Buffers: shared hit=70
122. 0.015 0.015 ↑ 1.0 1 5

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

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

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

  • Index Cond: (id = projects_2.client_id)
  • Buffers: shared hit=15
124. 14.475 14.475 ↓ 1.9 95 5

Index Scan using index_rates_on_activity_id_and_rate_card_id on rates (cost=0.42..1,327.66 rows=49 width=28) (actual time=0.530..2.895 rows=95 loops=5)

  • Index Cond: (rate_card_id = rate_cards.id)
  • Buffers: shared hit=2457
125. 0.474 0.474 ↑ 1.0 1 474

Index Only Scan using brands_pkey on brands (cost=0.14..0.16 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=474)

  • Index Cond: (id = clients.brand_id)
  • Heap Fetches: 474
  • Buffers: shared hit=948
126. 13.408 70.370 ↓ 2.6 91,600 1

Subquery Scan on *SELECT* 2 (cost=583.00..5,479.99 rows=35,024 width=44) (actual time=11.315..70.370 rows=91,600 loops=1)

  • Buffers: shared hit=2036
127. 35.633 56.962 ↓ 2.6 91,600 1

Hash Join (cost=583.00..5,129.75 rows=35,024 width=612) (actual time=11.314..56.962 rows=91,600 loops=1)

  • Hash Cond: (rates_1.rate_card_id = rate_cards_1.id)
  • Buffers: shared hit=2036
128. 16.526 16.526 ↑ 1.0 174,437 1

Seq Scan on rates rates_1 (cost=0.00..3,542.37 rows=174,437 width=28) (actual time=0.011..16.526 rows=174,437 loops=1)

  • Buffers: shared hit=1798
129. 0.173 4.803 ↑ 1.1 657 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
  • Buffers: shared hit=238
130. 0.160 4.630 ↑ 1.1 657 1

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

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

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

  • Hash Cond: (projects_3.client_id = clients_1.id)
  • Buffers: shared hit=190
132. 0.855 2.288 ↑ 1.0 623 1

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

  • Hash Cond: (projects_3.id = pricing_models_1.project_id)
  • Buffers: shared hit=168
133. 0.629 0.629 ↑ 1.0 5,812 1

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

  • Buffers: shared hit=98
134. 0.103 0.804 ↑ 1.0 623 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
  • Buffers: shared hit=70
135. 0.701 0.701 ↑ 1.0 623 1

Seq Scan on pricing_models pricing_models_1 (cost=0.00..142.62 rows=623 width=4) (actual time=0.013..0.701 rows=623 loops=1)

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

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

  • Buckets: 2048 Batches: 1 Memory Usage: 89kB
  • Buffers: shared hit=22
137. 0.323 0.323 ↑ 1.0 1,849 1

Seq Scan on clients clients_1 (cost=0.00..40.49 rows=1,849 width=8) (actual time=0.006..0.323 rows=1,849 loops=1)

  • Buffers: shared hit=22
138. 0.108 1.405 ↑ 1.1 450 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
  • Buffers: shared hit=48
139. 0.107 1.297 ↑ 1.1 450 1

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

  • Hash Cond: (rate_cards_1.rateable_id = offices.id)
  • Buffers: shared hit=48
140. 0.308 1.014 ↑ 1.0 489 1

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

  • Buffers: shared hit=44
141. 0.153 0.706 ↑ 1.0 489 1

Sort (cost=126.52..127.74 rows=489 width=29) (actual time=0.674..0.706 rows=489 loops=1)

  • Sort Key: rate_cards_1.rateable_id, rate_cards_1.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 63kB
  • Buffers: shared hit=44
142. 0.553 0.553 ↑ 1.0 489 1

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

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 4365
  • Buffers: shared hit=44
143. 0.114 0.176 ↑ 1.0 441 1

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

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

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

  • Buffers: shared hit=4
145. 14.400 77.546 ↑ 2.2 94,593 1

Subquery Scan on *SELECT* 3 (cost=840.78..9,338.49 rows=204,158 width=44) (actual time=9.518..77.546 rows=94,593 loops=1)

  • Buffers: shared hit=2032
146. 37.219 63.146 ↑ 2.2 94,593 1

Hash Join (cost=840.78..7,296.91 rows=204,158 width=612) (actual time=9.517..63.146 rows=94,593 loops=1)

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

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

  • Buffers: shared hit=1798
148. 0.850 9.488 ↑ 1.0 4,004 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 299kB
  • Buffers: shared hit=234
149. 0.958 8.638 ↑ 1.0 4,004 1

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

  • Hash Cond: (projects_4.client_id = clients_2.id)
  • Buffers: shared hit=234
150. 0.960 3.939 ↑ 1.0 3,620 1

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

  • Hash Cond: (pricing_models_2.project_id = projects_4.id)
  • Buffers: shared hit=168
151. 0.897 0.897 ↑ 1.0 3,621 1

Seq Scan on pricing_models pricing_models_2 (cost=0.00..142.62 rows=3,621 width=4) (actual time=0.007..0.897 rows=3,621 loops=1)

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

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

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

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

  • Buffers: shared hit=98
154. 0.427 3.741 ↑ 1.0 1,820 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 123kB
  • Buffers: shared hit=66
155. 0.418 3.314 ↑ 1.0 1,820 1

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

  • Hash Cond: (rate_cards_2.rateable_id = clients_2.id)
  • Buffers: shared hit=66
156. 1.118 2.399 ↑ 1.0 1,821 1

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

  • Buffers: shared hit=44
157. 0.644 1.281 ↑ 1.0 1,821 1

Sort (cost=203.29..207.84 rows=1,821 width=29) (actual time=1.166..1.281 rows=1,821 loops=1)

  • Sort Key: rate_cards_2.rateable_id, rate_cards_2.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 191kB
  • Buffers: shared hit=44
158. 0.637 0.637 ↑ 1.0 1,821 1

Seq Scan on rate_cards rate_cards_2 (cost=0.00..104.68 rows=1,821 width=29) (actual time=0.006..0.637 rows=1,821 loops=1)

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

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

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

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

  • Buffers: shared hit=22
161. 11.613 71.040 ↓ 2.4 76,366 1

Subquery Scan on *SELECT* 4 (cost=695.56..5,527.35 rows=31,764 width=44) (actual time=6.989..71.040 rows=76,366 loops=1)

  • Buffers: shared hit=2010
162. 35.329 59.427 ↓ 2.4 76,366 1

Hash Join (cost=695.56..5,209.71 rows=31,764 width=612) (actual time=6.989..59.427 rows=76,366 loops=1)

  • Hash Cond: (rates_3.rate_card_id = rate_cards_3.id)
  • Buffers: shared hit=2010
163. 17.129 17.129 ↑ 1.0 174,437 1

Seq Scan on rates rates_3 (cost=0.00..3,542.37 rows=174,437 width=28) (actual time=0.010..17.129 rows=174,437 loops=1)

  • Buffers: shared hit=1798
164. 0.394 6.969 ↓ 2.4 1,591 1

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

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

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

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

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

  • Buffers: shared hit=98
167. 0.346 4.997 ↓ 2.4 1,591 1

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

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

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

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
  • Buffers: shared hit=114
169. 1.514 3.139 ↑ 1.0 2,429 1

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

  • Buffers: shared hit=44
170. 0.911 1.625 ↑ 1.0 2,429 1

Sort (cost=241.26..247.33 rows=2,429 width=29) (actual time=1.445..1.625 rows=2,429 loops=1)

  • Sort Key: rate_cards_3.rateable_id, rate_cards_3.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 286kB
  • Buffers: shared hit=44
171. 0.714 0.714 ↑ 1.0 2,429 1

Seq Scan on rate_cards rate_cards_3 (cost=0.00..104.68 rows=2,429 width=29) (actual time=0.006..0.714 rows=2,429 loops=1)

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

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

  • Buckets: 2048 Batches: 1 Memory Usage: 77kB
  • Buffers: shared hit=70
173. 0.795 0.795 ↑ 1.0 1,561 1

Seq Scan on pricing_models pricing_models_3 (cost=0.00..142.62 rows=1,561 width=8) (actual time=0.013..0.795 rows=1,561 loops=1)

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 4249
  • Buffers: shared hit=70
Planning time : 6.140 ms
Execution time : 99,652.160 ms