explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mwE0

Settings
# exclusive inclusive rows x rows loops node
1. 221.653 81,829.283 ↓ 10.5 845,146 1

Subquery Scan on report_items_live (cost=2,708,785.01..2,716,415.13 rows=80,317 width=140) (actual time=80,308.067..81,829.283 rows=845,146 loops=1)

  • Buffers: shared hit=73520 read=7568, temp read=30284 written=30286
2. 1,117.338 81,607.630 ↓ 10.5 845,146 1

GroupAggregate (cost=2,708,785.01..2,715,611.96 rows=80,317 width=196) (actual time=80,308.065..81,607.630 rows=845,146 loops=1)

  • Group Key: projects.account_id, projects.id, ""*SELECT* 1_1"".project_plan_id, ""*SELECT* 1_1"".epic_id, (COALESCE(pa.activity_id, ""*SELECT* 1_1"".activity_id, staff_membership_activity_links.activity_id)), ""*SELECT* 1_1"".user_id, ""*SELECT* 1_1"".date, (CASE WHEN (('PlanItem'::text) = 'TimeLog'::text) THEN (NULL::double precision) WHEN (((""*SELECT* 1_1"".vacation_user_id)::double precision) IS NOT NULL) THEN GREATEST(((((COALESCE(staff_membership_activity_links.capacity, '0'::numeric))::double precision - COALESCE(((""*SELECT* 1_1"".vacation_hours)::double precision), (staff_membership_activity_links.capacity)::double precision, '0'::double precision)) * ((""*SELECT* 1_1"".utilization)::double precision)) / '100'::double precision), '0'::double precision) WHEN (""*SELECT* 1_1"".user_id IS NULL) THEN (((accounts_1.default_capacity)::double precision * ((""*SELECT* 1_1"".utilization)::double precision)) / '100'::double precision) ELSE (((COALESCE(staff_membership_activity_links.capacity, '0'::numeric))::double precision * ((""*SELECT* 1_1"".utilization)::double precision)) / '100'::double precision) END), ('PlanItem'::text), ""*SELECT* 1_1"".reportable_id, ""*SELECT* 1_1"".plan_row_uuid, ""*SELECT* 1"".currency, (COALESCE(exchange_rates_by_months.rate, '1'::double precision)), ""*SELECT* 1"".value, staff_membership_activity_links.cost, (COALESCE(cost_exchange_rates.rate, '1'::double precision)), (COALESCE(original_exchange_rates.rate, '1'::double precision)), (GREATEST((GREATEST(""*SELECT* 1_1"".updated_at, pa.updated_at, (GREATEST(staff_memberships_1.updated_at, staff_membership_activity_links.updated_at)))), ""*SELECT* 1"".updated_at, ""*SELECT* 1"".rate_card_updated_at))
  • Buffers: shared hit=73520 read=7568, temp read=30284 written=30286
3. 3,445.503 80,490.292 ↓ 10.5 845,234 1

Sort (cost=2,708,785.01..2,708,985.80 rows=80,317 width=192) (actual time=80,308.040..80,490.292 rows=845,234 loops=1)

  • Sort Key: projects.account_id, projects.id, ""*SELECT* 1_1"".project_plan_id, ""*SELECT* 1_1"".epic_id, (COALESCE(pa.activity_id, ""*SELECT* 1_1"".activity_id, staff_membership_activity_links.activity_id)), ""*SELECT* 1_1"".user_id, ""*SELECT* 1_1"".date, (CASE WHEN (('PlanItem'::text) = 'TimeLog'::text) THEN (NULL::double precision) WHEN (((""*SELECT* 1_1"".vacation_user_id)::double precision) IS NOT NULL) THEN GREATEST(((((COALESCE(staff_membership_activity_links.capacity, '0'::numeric))::double precision - COALESCE(((""*SELECT* 1_1"".vacation_hours)::double precision), (staff_membership_activity_links.capacity)::double precision, '0'::double precision)) * ((""*SELECT* 1_1"".utilization)::double precision)) / '100'::double precision), '0'::double precision) WHEN (""*SELECT* 1_1"".user_id IS NULL) THEN (((accounts_1.default_capacity)::double precision * ((""*SELECT* 1_1"".utilization)::double precision)) / '100'::double precision) ELSE (((COALESCE(staff_membership_activity_links.capacity, '0'::numeric))::double precision * ((""*SELECT* 1_1"".utilization)::double precision)) / '100'::double precision) END), ('PlanItem'::text), ""*SELECT* 1_1"".reportable_id, ""*SELECT* 1_1"".plan_row_uuid, ""*SELECT* 1"".currency, (COALESCE(exchange_rates_by_months.rate, '1'::double precision)), ""*SELECT* 1"".value, staff_membership_activity_links.cost, (COALESCE(cost_exchange_rates.rate, '1'::double precision)), (COALESCE(original_exchange_rates.rate, '1'::double precision)), (GREATEST((GREATEST(""*SELECT* 1_1"".updated_at, pa.updated_at, (GREATEST(staff_memberships_1.updated_at, staff_membership_activity_links.updated_at)))), ""*SELECT* 1"".updated_at, ""*SELECT* 1"".rate_card_updated_at))
  • Sort Method: external merge Disk: 118816kB
  • Buffers: shared hit=73520 read=7568, temp read=30284 written=30286
4. 16,408.167 77,044.789 ↓ 10.5 845,234 1

Merge Left Join (cost=2,700,534.53..2,702,241.82 rows=80,317 width=192) (actual time=45,578.024..77,044.789 rows=845,234 loops=1)

  • Merge Cond: ((projects.account_id = original_exchange_rates.account_id) AND (("*SELECT* 1".currency)::text = (original_exchange_rates.currency)::text))
  • Join Filter: ((original_exchange_rates.start_date <= "*SELECT* 1_1".date) AND (original_exchange_rates.end_date >= "*SELECT* 1_1".date))
  • Rows Removed by Join Filter: 40457427
  • Buffers: shared hit=73520 read=7568, temp read=15432 written=15433
5. 14,949.205 60,636.622 ↓ 10.5 845,234 1

Merge Left Join (cost=2,700,534.25..2,701,274.32 rows=80,317 width=176) (actual time=45,577.617..60,636.622 rows=845,234 loops=1)

  • Merge Cond: ((projects.account_id = exchange_rates_by_months.account_id) AND (("*SELECT* 1".currency)::text = (exchange_rates_by_months.currency)::text))
  • Join Filter: ((exchange_rates_by_months.start_date <= "*SELECT* 1_1".date) AND (exchange_rates_by_months.end_date >= "*SELECT* 1_1".date))
  • Rows Removed by Join Filter: 40457427
  • Buffers: shared hit=72078 read=7568, temp read=15432 written=15433
6. 981.237 45,687.417 ↓ 10.5 845,234 1

Sort (cost=2,700,289.38..2,700,490.18 rows=80,317 width=168) (actual time=45,575.031..45,687.417 rows=845,234 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1"".currency
  • Sort Method: quicksort Memory: 194603kB
  • Buffers: shared hit=72040 read=7568, temp read=15432 written=15433
7. 15,035.191 44,706.180 ↓ 10.5 845,234 1

Merge Left Join (cost=2,693,006.11..2,693,746.19 rows=80,317 width=168) (actual time=29,580.218..44,706.180 rows=845,234 loops=1)

  • Merge Cond: ((projects.account_id = cost_exchange_rates.account_id) AND ((staff_memberships_1.currency)::text = (cost_exchange_rates.currency)::text))
  • Join Filter: ((cost_exchange_rates.start_date <= "*SELECT* 1_1".date) AND (cost_exchange_rates.end_date >= "*SELECT* 1_1".date))
  • Rows Removed by Join Filter: 43416546
  • Buffers: shared hit=72040 read=7568, temp read=15432 written=15433
8. 866.590 29,670.989 ↓ 10.5 845,234 1

Sort (cost=2,692,761.25..2,692,962.04 rows=80,317 width=676) (actual time=29,578.549..29,670.989 rows=845,234 loops=1)

  • Sort Key: projects.account_id, staff_memberships_1.currency
  • Sort Method: quicksort Memory: 170825kB
  • Buffers: shared hit=72002 read=7568, temp read=15432 written=15433
9. 430.529 28,804.399 ↓ 10.5 845,234 1

Merge Left Join (cost=2,669,409.61..2,686,218.06 rows=80,317 width=676) (actual time=27,468.508..28,804.399 rows=845,234 loops=1)

  • Merge Cond: (projects.id = discounts.project_id)
  • Join Filter: (("*SELECT* 1_1".date >= discounts.start_date) AND ("*SELECT* 1_1".date <= discounts.end_date) AND (((discounts.discountable_id = "*SELECT* 1_1".user_id) AND ((discounts.discountable_type)::text = 'User'::text)) OR ((discounts.discountable_id = "*SELECT* 1_1".epic_id) AND ((discounts.discountable_type)::text = 'Epic'::text))))
  • Rows Removed by Join Filter: 650036
  • Buffers: shared hit=72002 read=7568, temp read=15432 written=15433
10. 678.179 28,373.870 ↓ 10.5 845,146 1

Merge Left Join (cost=2,669,386.84..2,682,307.95 rows=80,317 width=672) (actual time=27,468.270..28,373.870 rows=845,146 loops=1)

  • Merge Cond: ((projects.id = ""*SELECT* 1"".project_id) AND ((COALESCE(pa.activity_id, ""*SELECT* 1_1"".activity_id, staff_membership_activity_links.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: 243907
  • Buffers: shared hit=71998 read=7568, temp read=15432 written=15433
11. 711.315 26,932.917 ↓ 10.5 845,146 1

Sort (cost=2,622,555.93..2,622,756.72 rows=80,317 width=644) (actual time=26,823.031..26,932.917 rows=845,146 loops=1)

  • Sort Key: projects.id, (COALESCE(pa.activity_id, ""*SELECT* 1_1"".activity_id, staff_membership_activity_links.activity_id))
  • Sort Method: quicksort Memory: 143425kB
  • Buffers: shared hit=63318 read=7568, temp read=15432 written=15433
12. 300.329 26,221.602 ↓ 10.5 845,146 1

Hash Join (cost=2,150,249.78..2,616,012.73 rows=80,317 width=644) (actual time=13,529.213..26,221.602 rows=845,146 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
  • Buffers: shared hit=63318 read=7568, temp read=15432 written=15433
13. 170.142 25,921.121 ↓ 4.6 1,111,239 1

Append (cost=2,150,231.11..2,612,944.47 rows=240,952 width=644) (actual time=13,526.997..25,921.121 rows=1,111,239 loops=1)

  • Buffers: shared hit=63307 read=7568, temp read=15432 written=15433
14. 8,707.298 25,600.199 ↓ 4.5 1,094,018 1

Merge Left Join (cost=2,150,231.11..2,354,506.35 rows=240,951 width=103) (actual time=13,526.996..25,600.199 rows=1,094,018 loops=1)

  • Merge Cond: ("*SELECT* 1_1".project_plan_id = pa.project_plan_id)
  • Join Filter: (("*SELECT* 1_1".date >= pa.start_date) AND ("*SELECT* 1_1".date <= pa.end_date) AND ((pa.plan_row_uuid = "*SELECT* 1_1".plan_row_uuid) OR ("*SELECT* 1_1".plan_row_uuid IS NULL)) AND ((pa.user_id = "*SELECT* 1_1".user_id) OR ("*SELECT* 1_1".user_id IS NULL)) AND ((pa.epic_id = "*SELECT* 1_1".epic_id) OR ((('PlanItem'::text) = 'PlanItem'::text) AND ("*SELECT* 1_1".epic_id IS NULL)) OR ((('PlanItem'::text) = 'TimeLog'::text) AND (pa.epic_id IS NULL))))
  • Rows Removed by Join Filter: 35938192
  • Buffers: shared hit=6402 read=7568, temp read=15432 written=15433
15. 1,640.715 13,474.935 ↓ 4.5 1,094,018 1

Sort (cost=2,140,665.13..2,141,267.51 rows=240,951 width=149) (actual time=13,242.202..13,474.935 rows=1,094,018 loops=1)

  • Sort Key: "*SELECT* 1_1".project_plan_id
  • Sort Method: external merge Disk: 123456kB
  • Buffers: shared hit=5413 read=7568, temp read=15432 written=15433
16. 457.348 11,834.220 ↓ 4.5 1,094,018 1

Hash Left Join (cost=2,110,330.64..2,119,126.06 rows=240,951 width=149) (actual time=10,204.372..11,834.220 rows=1,094,018 loops=1)

  • Hash Cond: (projects.account_id = accounts_1.id)
  • Filter: ((((""*SELECT* 1_1"".vacation_hours)::double precision) IS NULL) OR (((""*SELECT* 1_1"".vacation_hours)::double precision) < (COALESCE(staff_membership_activity_links.capacity, accounts_1.default_capacity))::double precision))
  • Rows Removed by Filter: 5433
  • Buffers: shared hit=5413 read=7568
17. 840.748 11,376.626 ↓ 1.5 1,099,451 1

Merge Left Join (cost=2,110,311.68..2,117,195.80 rows=715,695 width=144) (actual time=10,204.110..11,376.626 rows=1,099,451 loops=1)

  • Merge Cond: ((projects.account_id = staff_memberships_1.account_id) AND ("*SELECT* 1_1".user_id = staff_memberships_1.user_id))
  • Join Filter: ((""*SELECT* 1_1"".date >= (COALESCE(staff_membership_activity_links.start_date, staff_memberships_1.joined_at))) AND (""*SELECT* 1_1"".date <= (COALESCE(lag((staff_membership_activity_links.start_date - 1), 1) OVER (?), staff_memberships_1.archived_at, '3000-01-01'::date))))
  • Rows Removed by Join Filter: 258502
  • Buffers: shared hit=5402 read=7568
18. 1,644.393 10,378.226 ↓ 1.5 1,099,451 1

Sort (cost=2,109,464.53..2,111,253.77 rows=715,695 width=120) (actual time=10,184.612..10,378.226 rows=1,099,451 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1_1"".user_id
  • Sort Method: quicksort Memory: 183868kB
  • Buffers: shared hit=5317 read=7568
19. 467.173 8,733.833 ↓ 1.5 1,099,451 1

Hash Join (cost=1,941,223.46..2,039,866.83 rows=715,695 width=120) (actual time=6,257.608..8,733.833 rows=1,099,451 loops=1)

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
  • Buffers: shared hit=5317 read=7568
20. 273.848 8,264.602 ↓ 2.2 1,596,015 1

Append (cost=1,940,994.69..2,030,601.04 rows=715,695 width=120) (actual time=6,255.520..8,264.602 rows=1,596,015 loops=1)

  • Buffers: shared hit=5219 read=7568
21. 356.414 7,349.134 ↓ 76.0 892,054 1

Subquery Scan on *SELECT* 1_1 (cost=1,940,994.69..1,988,762.50 rows=11,734 width=120) (actual time=6,255.520..7,349.134 rows=892,054 loops=1)

  • Buffers: shared hit=3171
22. 585.307 6,992.720 ↓ 76.0 892,054 1

Merge Right Join (cost=1,940,994.69..1,988,557.15 rows=11,734 width=138) (actual time=6,255.513..6,992.720 rows=892,054 loops=1)

  • Merge Cond: ((nw_intervals_by_dates_roles.office_id = clients.office_id) AND (nw_intervals_by_dates_roles.date = generate_series.generate_series) AND (nw_intervals_by_dates_roles.account_id = projects_1.account_id))
  • Filter: (COALESCE(non_working_intervals_by_dates.id, nw_intervals_by_dates_roles.id) IS NULL)
  • Rows Removed by Filter: 693191
  • Buffers: shared hit=3171
23. 29.510 44.103 ↑ 1.0 59,709 1

Sort (cost=6,017.99..6,167.27 rows=59,712 width=16) (actual time=39.251..44.103 rows=59,709 loops=1)

  • Sort Key: nw_intervals_by_dates_roles.office_id, nw_intervals_by_dates_roles.date, nw_intervals_by_dates_roles.account_id
  • Sort Method: quicksort Memory: 4336kB
  • Buffers: shared hit=684
24. 14.593 14.593 ↑ 1.0 59,712 1

Seq Scan on non_working_intervals_by_dates nw_intervals_by_dates_roles (cost=0.00..1,281.12 rows=59,712 width=16) (actual time=0.009..14.593 rows=59,712 loops=1)

  • Buffers: shared hit=684
25. 1,968.078 6,363.310 ↓ 1.2 1,585,245 1

Sort (cost=1,934,976.70..1,938,317.13 rows=1,336,175 width=110) (actual time=6,216.250..6,363.310 rows=1,585,245 loops=1)

  • Sort Key: clients.office_id, generate_series.generate_series, projects_1.account_id
  • Sort Method: quicksort Memory: 151406kB
  • Buffers: shared hit=2487
26. 269.727 4,395.232 ↑ 1.5 901,901 1

Merge Left Join (cost=1,768,724.66..1,799,023.04 rows=1,336,175 width=110) (actual time=3,305.114..4,395.232 rows=901,901 loops=1)

  • Merge Cond: ((generate_series.generate_series = non_working_intervals_by_dates.date) AND (projects_1.account_id = non_working_intervals_by_dates.account_id) AND (plan_rows.user_id = non_working_intervals_by_dates.user_id))
  • Buffers: shared hit=2487
27. 392.999 4,040.322 ↑ 1.5 901,825 1

Merge Left Join (cost=1,762,706.67..1,781,902.75 rows=1,336,175 width=106) (actual time=3,305.111..4,040.322 rows=901,825 loops=1)

  • Merge Cond: ((generate_series.generate_series = summary_vacations_by_dates.date) AND (projects_1.account_id = summary_vacations_by_dates.account_id) AND (plan_rows.user_id = summary_vacations_by_dates.user_id))
  • Buffers: shared hit=1803
28. 2,258.546 3,448.166 ↑ 1.5 901,825 1

Sort (cost=1,156,109.39..1,159,449.83 rows=1,336,175 width=70) (actual time=3,305.104..3,448.166 rows=901,825 loops=1)

  • Sort Key: generate_series.generate_series, projects_1.account_id, plan_rows.user_id
  • Sort Method: quicksort Memory: 151396kB
  • Buffers: shared hit=1472
29. 202.777 1,189.620 ↑ 1.5 901,825 1

Nested Loop (cost=2,102.11..1,020,155.74 rows=1,336,175 width=70) (actual time=25.661..1,189.620 rows=901,825 loops=1)

  • Buffers: shared hit=1472
30. 22.290 151.928 ↓ 1.0 55,661 1

Hash Left Join (cost=2,102.10..4,662.73 rows=53,447 width=70) (actual time=25.638..151.928 rows=55,661 loops=1)

  • Hash Cond: (projects_1.client_id = clients.id)
  • Buffers: shared hit=1472
31. 21.326 129.049 ↓ 1.0 55,661 1

Hash Left Join (cost=2,038.50..4,458.51 rows=53,447 width=70) (actual time=25.041..129.049 rows=55,661 loops=1)

  • Hash Cond: (project_plans.project_id = projects_1.id)
  • Buffers: shared hit=1450
32. 22.392 105.404 ↓ 1.0 55,661 1

Hash Left Join (cost=1,809.73..4,089.34 rows=53,447 width=62) (actual time=22.712..105.404 rows=55,661 loops=1)

  • Hash Cond: (plan_items.project_plan_id = project_plans.id)
  • Buffers: shared hit=1352
33. 40.681 81.421 ↓ 1.0 55,661 1

Hash Join (cost=1,607.70..3,746.91 rows=53,447 width=58) (actual time=21.068..81.421 rows=55,661 loops=1)

  • Hash Cond: (plan_items.plan_row_uuid = plan_rows.uuid)
  • Buffers: shared hit=1246
34. 20.054 20.054 ↓ 1.0 55,664 1

Seq Scan on plan_items (cost=0.00..1,396.10 rows=55,639 width=46) (actual time=0.018..20.054 rows=55,664 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
  • Buffers: shared hit=697
35. 11.040 20.686 ↓ 1.0 48,102 1

Hash (cost=1,030.06..1,030.06 rows=46,211 width=28) (actual time=20.685..20.686 rows=48,102 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3117kB
  • Buffers: shared hit=549
36. 9.646 9.646 ↓ 1.0 48,102 1

Seq Scan on plan_rows (cost=0.00..1,030.06 rows=46,211 width=28) (actual time=0.009..9.646 rows=48,102 loops=1)

  • Filter: ((user_id IS NOT NULL) OR (activity_id IS NOT NULL))
  • Rows Removed by Filter: 4
  • Buffers: shared hit=549
37. 0.492 1.591 ↑ 1.0 2,851 1

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

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

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

  • Filter: active
  • Rows Removed by Filter: 3188
  • Buffers: shared hit=106
39. 1.061 2.319 ↑ 1.0 5,812 1

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

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

Seq Scan on projects projects_1 (cost=0.00..156.12 rows=5,812 width=12) (actual time=0.008..1.258 rows=5,812 loops=1)

  • Buffers: shared hit=98
41. 0.279 0.589 ↑ 1.0 1,849 1

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

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

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

  • Buffers: shared hit=22
43. 834.915 834.915 ↑ 1.6 16 55,661

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.006..0.015 rows=16 loops=55,661)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 6
44. 63.952 199.157 ↑ 15.4 37,319 1

Sort (cost=606,597.28..608,034.78 rows=575,000 width=44) (actual time=191.629..199.157 rows=37,319 loops=1)

  • Sort Key: summary_vacations_by_dates.date, summary_vacations_by_dates.account_id, summary_vacations_by_dates.user_id
  • Sort Method: quicksort Memory: 2844kB
  • Buffers: shared hit=331
45. 5.519 135.205 ↑ 17.8 32,266 1

Subquery Scan on summary_vacations_by_dates (cost=537,214.32..551,589.32 rows=575,000 width=44) (actual time=108.490..135.205 rows=32,266 loops=1)

  • Buffers: shared hit=331
46. 43.898 129.686 ↑ 17.8 32,266 1

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

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
  • Buffers: shared hit=331
47. 19.052 85.788 ↑ 134.2 32,422 1

Hash Join (cost=138.05..493,700.50 rows=4,351,382 width=21) (actual time=3.235..85.788 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
48. 16.249 63.589 ↑ 634.8 34,330 1

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

  • Buffers: shared hit=283
49. 3.756 3.756 ↑ 1.0 21,792 1

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

  • Buffers: shared hit=283
50. 43.584 43.584 ↑ 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.002..0.002 rows=2 loops=21,792)

51. 1.458 3.147 ↑ 1.0 4,002 1

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

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

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

  • Buffers: shared hit=48
53. 59.880 85.183 ↓ 1.0 62,082 1

Sort (cost=6,017.99..6,167.27 rows=59,712 width=16) (actual time=78.579..85.183 rows=62,082 loops=1)

  • Sort Key: non_working_intervals_by_dates.date, non_working_intervals_by_dates.account_id, non_working_intervals_by_dates.user_id
  • Sort Method: quicksort Memory: 4336kB
  • Buffers: shared hit=684
54. 25.303 25.303 ↑ 1.0 59,712 1

Seq Scan on non_working_intervals_by_dates (cost=0.00..1,281.12 rows=59,712 width=16) (actual time=0.017..25.303 rows=59,712 loops=1)

  • Buffers: shared hit=684
55. 461.680 641.620 ↑ 1.0 703,961 1

Hash Join (cost=1,826.58..31,220.45 rows=703,961 width=120) (actual time=28.474..641.620 rows=703,961 loops=1)

  • Hash Cond: (time_logs.epic_id = epics.id)
  • Buffers: shared hit=2048 read=7568
56. 151.724 151.724 ↑ 1.0 703,961 1

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

  • Buffers: shared hit=1587 read=7568
57. 9.067 28.216 ↑ 1.0 26,435 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1381kB
  • Buffers: shared hit=461
58. 12.314 19.149 ↑ 1.0 26,435 1

Hash Left Join (cost=202.03..1,496.14 rows=26,435 width=16) (actual time=2.654..19.149 rows=26,435 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: 11144
  • Buffers: shared hit=461
59. 4.221 4.221 ↑ 1.0 26,435 1

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

  • Buffers: shared hit=355
60. 0.807 2.614 ↑ 1.0 2,851 1

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

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

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

  • Filter: active
  • Rows Removed by Filter: 3188
  • Buffers: shared hit=106
62. 1.011 2.058 ↑ 1.0 5,812 1

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

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

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

  • Buffers: shared hit=98
64. 134.435 157.652 ↓ 295.0 1,253,450 1

Materialize (cost=847.14..942.74 rows=4,249 width=40) (actual time=19.489..157.652 rows=1,253,450 loops=1)

  • Buffers: shared hit=85
65. 2.077 23.217 ↑ 1.0 4,218 1

Unique (cost=847.14..889.63 rows=4,249 width=65) (actual time=19.485..23.217 rows=4,218 loops=1)

  • Buffers: shared hit=85
66. 6.189 21.140 ↑ 1.0 4,246 1

Sort (cost=847.14..857.76 rows=4,249 width=65) (actual time=19.484..21.140 rows=4,246 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
67. 5.169 14.951 ↑ 1.0 4,249 1

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

  • Buffers: shared hit=85
68. 3.657 9.782 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=56) (actual time=9.255..9.782 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
69. 2.424 6.125 ↑ 1.0 4,249 1

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

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships_1.id)
  • Buffers: shared hit=85
70. 0.705 0.705 ↑ 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.020..0.705 rows=4,256 loops=1)

  • Buffers: shared hit=37
71. 1.621 2.996 ↑ 1.0 4,002 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 276kB
  • Buffers: shared hit=48
72. 1.375 1.375 ↑ 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.011..1.375 rows=4,002 loops=1)

  • Buffers: shared hit=48
73. 0.094 0.246 ↑ 1.0 354 1

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

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

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

  • Buffers: shared hit=11
75. 3,159.326 3,417.966 ↓ 7,420.1 36,885,409 1

Sort (cost=9,565.98..9,578.40 rows=4,971 width=48) (actual time=284.771..3,417.966 rows=36,885,409 loops=1)

  • Sort Key: pa.project_plan_id
  • Sort Method: quicksort Memory: 5645kB
  • Buffers: shared hit=989
76. 11.290 258.640 ↓ 8.9 44,428 1

Subquery Scan on pa (cost=9,136.50..9,260.77 rows=4,971 width=48) (actual time=182.970..258.640 rows=44,428 loops=1)

  • Buffers: shared hit=989
77. 104.697 247.350 ↓ 8.9 44,428 1

HashAggregate (cost=9,136.50..9,211.06 rows=4,971 width=48) (actual time=182.968..247.350 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
78. 41.038 142.653 ↑ 1.0 44,581 1

WindowAgg (cost=6,572.98..7,576.09 rows=44,583 width=68) (actual time=94.156..142.653 rows=44,581 loops=1)

  • Buffers: shared hit=989
79. 37.969 101.615 ↑ 1.0 44,581 1

Sort (cost=6,572.98..6,684.43 rows=44,583 width=60) (actual time=94.140..101.615 rows=44,581 loops=1)

  • Sort Key: plan_roles.plan_row_uuid, plan_roles.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 7154kB
  • Buffers: shared hit=989
80. 27.882 63.646 ↑ 1.0 44,581 1

Hash Join (cost=1,631.38..3,130.23 rows=44,583 width=60) (actual time=28.397..63.646 rows=44,581 loops=1)

  • Hash Cond: (plan_roles.plan_row_uuid = plan_rows_1.uuid)
  • Buffers: shared hit=989
81. 7.496 7.496 ↑ 1.0 44,583 1

Seq Scan on plan_roles (cost=0.00..885.83 rows=44,583 width=32) (actual time=0.017..7.496 rows=44,583 loops=1)

  • Buffers: shared hit=440
82. 15.731 28.268 ↑ 1.0 48,106 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3180kB
  • Buffers: shared hit=549
83. 12.537 12.537 ↑ 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.011..12.537 rows=48,106 loops=1)

  • Buffers: shared hit=549
84. 6.705 150.780 ↓ 17,221.0 17,221 1

Subquery Scan on *SELECT* 2_1 (cost=247,975.87..254,823.85 rows=1 width=103) (actual time=95.718..150.780 rows=17,221 loops=1)

  • Buffers: shared hit=56905
85. 20.147 144.075 ↓ 17,221.0 17,221 1

Merge Right Join (cost=247,975.87..254,823.84 rows=1 width=110) (actual time=95.713..144.075 rows=17,221 loops=1)

  • Merge Cond: ((non_working_intervals_by_dates_1.account_id = staff_memberships_2.account_id) AND (non_working_intervals_by_dates_1.user_id = staff_memberships_2.user_id) AND (non_working_intervals_by_dates_1.date = generate_series_2.generate_series))
  • Filter: (non_working_intervals_by_dates_1.id IS NULL)
  • Rows Removed by Filter: 389
  • Buffers: shared hit=56905
86. 26.314 26.314 ↑ 1.0 59,669 1

Index Scan using non_working_intervals_by_dates_idx on non_working_intervals_by_dates non_working_intervals_by_dates_1 (cost=0.41..3,467.10 rows=59,712 width=16) (actual time=0.014..26.314 rows=59,669 loops=1)

  • Buffers: shared hit=56452
87. 38.924 97.614 ↑ 16.4 17,610 1

Sort (cost=247,975.46..248,698.42 rows=289,183 width=64) (actual time=95.679..97.614 rows=17,610 loops=1)

  • Sort Key: staff_memberships_2.account_id, staff_memberships_2.user_id, generate_series_2.generate_series
  • Sort Method: quicksort Memory: 3245kB
  • Buffers: shared hit=453
88. 7.541 58.690 ↑ 16.4 17,610 1

Nested Loop (cost=1,253.91..221,744.21 rows=289,183 width=64) (actual time=19.226..58.690 rows=17,610 loops=1)

  • Buffers: shared hit=453
89. 7.045 29.105 ↑ 1.0 11,022 1

Hash Join (cost=1,253.91..1,952.21 rows=11,568 width=64) (actual time=19.211..29.105 rows=11,022 loops=1)

  • Hash Cond: (vacations_1.staff_membership_id = staff_memberships_2.id)
  • Buffers: shared hit=453
90. 2.882 2.882 ↑ 1.0 21,792 1

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

  • Buffers: shared hit=283
91. 0.451 19.178 ↑ 1.3 1,592 1

Hash (cost=1,227.36..1,227.36 rows=2,124 width=32) (actual time=19.178..19.178 rows=1,592 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 134kB
  • Buffers: shared hit=170
92. 0.493 18.727 ↑ 1.3 1,592 1

Hash Join (cost=1,107.35..1,227.36 rows=2,124 width=32) (actual time=17.074..18.727 rows=1,592 loops=1)

  • Hash Cond: (staff_membership_activity_links_1.staff_membership_id = staff_memberships_2.id)
  • Buffers: shared hit=170
93. 0.834 16.480 ↑ 1.3 1,592 1

Hash Join (cost=969.31..1,083.71 rows=2,128 width=16) (actual time=15.291..16.480 rows=1,592 loops=1)

  • Hash Cond: (staff_membership_activity_links_1.id = staff_activities_with_dates.link_id)
  • Buffers: shared hit=122
94. 0.395 0.395 ↑ 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.008..0.395 rows=4,256 loops=1)

  • Buffers: shared hit=37
95. 0.251 15.251 ↓ 8.7 1,592 1

Hash (cost=967.01..967.01 rows=184 width=4) (actual time=15.251..15.251 rows=1,592 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 72kB
  • Buffers: shared hit=85
96. 0.531 15.000 ↓ 8.7 1,592 1

HashAggregate (cost=965.17..967.01 rows=184 width=4) (actual time=14.789..15.000 rows=1,592 loops=1)

  • Group Key: staff_activities_with_dates.link_id
  • Buffers: shared hit=85
97. 1.330 14.469 ↓ 3.4 1,592 1

Subquery Scan on staff_activities_with_dates (cost=836.52..963.99 rows=472 width=4) (actual time=12.065..14.469 rows=1,592 loops=1)

  • Filter: ((CURRENT_DATE >= staff_activities_with_dates.start_date) AND (CURRENT_DATE <= staff_activities_with_dates.end_date))
  • Rows Removed by Filter: 2629
  • Buffers: shared hit=85
98. 0.803 13.139 ↑ 1.0 4,221 1

Unique (cost=836.52..879.01 rows=4,249 width=613) (actual time=12.060..13.139 rows=4,221 loops=1)

  • Buffers: shared hit=85
99. 2.641 12.336 ↑ 1.0 4,249 1

Sort (cost=836.52..847.14 rows=4,249 width=613) (actual time=12.059..12.336 rows=4,249 loops=1)

  • Sort Key: staff_memberships_3.account_id, staff_memberships_3.user_id, (COALESCE(staff_membership_activity_links_2.start_date, staff_memberships_3.joined_at)), (COALESCE(lag((staff_membership_activity_links_2.start_date - 1), 1) OVER (?), staff_memberships_3.archived_at, '3000-01-01'::date)) DESC
  • Sort Method: quicksort Memory: 524kB
  • Buffers: shared hit=85
100. 2.725 9.695 ↑ 1.0 4,249 1

WindowAgg (cost=484.85..580.45 rows=4,249 width=613) (actual time=6.675..9.695 rows=4,249 loops=1)

  • Buffers: shared hit=85
101. 2.503 6.970 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=28) (actual time=6.656..6.970 rows=4,249 loops=1)

  • Sort Key: staff_membership_activity_links_2.staff_membership_id, staff_membership_activity_links_2.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 524kB
  • Buffers: shared hit=85
102. 2.178 4.467 ↑ 1.0 4,249 1

Hash Join (cost=138.05..228.79 rows=4,249 width=28) (actual time=1.707..4.467 rows=4,249 loops=1)

  • Hash Cond: (staff_membership_activity_links_2.staff_membership_id = staff_memberships_3.id)
  • Buffers: shared hit=85
103. 0.619 0.619 ↑ 1.0 4,256 1

Seq Scan on staff_membership_activity_links staff_membership_activity_links_2 (cost=0.00..79.56 rows=4,256 width=12) (actual time=0.004..0.619 rows=4,256 loops=1)

  • Buffers: shared hit=37
104. 0.892 1.670 ↑ 1.0 4,002 1

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

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

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

  • Buffers: shared hit=48
106. 0.864 1.754 ↑ 1.0 4,002 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 220kB
  • Buffers: shared hit=48
107. 0.890 0.890 ↑ 1.0 4,002 1

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

  • Buffers: shared hit=48
108. 22.044 22.044 ↑ 12.5 2 11,022

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

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 0
109. 0.005 0.152 ↑ 9.8 12 1

Hash (cost=17.20..17.20 rows=118 width=4) (actual time=0.151..0.152 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=11
110. 0.147 0.147 ↑ 9.8 12 1

Seq Scan on accounts (cost=0.00..17.20 rows=118 width=4) (actual time=0.020..0.147 rows=12 loops=1)

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 342
  • Buffers: shared hit=11
111. 447.397 762.774 ↓ 4.3 1,164,207 1

Sort (cost=46,830.92..47,512.59 rows=272,669 width=44) (actual time=645.218..762.774 rows=1,164,207 loops=1)

  • Sort Key: ""*SELECT* 1"".project_id, ""*SELECT* 1"".activity_id
  • Sort Method: quicksort Memory: 32838kB
  • Buffers: shared hit=8680
112. 33.711 315.377 ↑ 1.0 263,033 1

Append (cost=300.88..22,213.28 rows=272,669 width=44) (actual time=2.358..315.377 rows=263,033 loops=1)

  • Buffers: shared hit=8680
113. 0.113 17.953 ↓ 9.7 474 1

Subquery Scan on *SELECT* 1 (cost=300.88..470.62 rows=49 width=44) (actual time=2.356..17.953 rows=474 loops=1)

  • Buffers: shared hit=2602
114. 0.167 17.840 ↓ 9.7 474 1

Merge Join (cost=300.88..470.13 rows=49 width=612) (actual time=2.354..17.840 rows=474 loops=1)

  • Merge Cond: (clients_1.brand_id = brands.id)
  • Buffers: shared hit=2602
115. 0.203 17.624 ↓ 9.7 474 1

Nested Loop (cost=294.80..1,671.71 rows=49 width=52) (actual time=2.302..17.624 rows=474 loops=1)

  • Buffers: shared hit=2601
116. 0.012 1.566 ↓ 5.0 5 1

Merge Join (cost=294.38..294.67 rows=1 width=32) (actual time=1.535..1.566 rows=5 loops=1)

  • Merge Cond: (rate_cards.rateable_id = clients_1.brand_id)
  • Buffers: shared hit=144
117. 0.032 0.743 ↑ 28.8 4 1

WindowAgg (cost=108.61..111.49 rows=115 width=45) (actual time=0.719..0.743 rows=4 loops=1)

  • Buffers: shared hit=44
118. 0.087 0.711 ↑ 23.0 5 1

Sort (cost=108.61..108.90 rows=115 width=29) (actual time=0.710..0.711 rows=5 loops=1)

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

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

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4739
  • Buffers: shared hit=44
120. 0.033 0.811 ↑ 1.0 5 1

Sort (cost=185.76..185.78 rows=5 width=8) (actual time=0.807..0.811 rows=5 loops=1)

  • Sort Key: clients_1.brand_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=100
121. 0.007 0.778 ↑ 1.0 5 1

Nested Loop (cost=0.56..185.71 rows=5 width=8) (actual time=0.451..0.778 rows=5 loops=1)

  • Buffers: shared hit=100
122. 0.005 0.761 ↑ 1.0 5 1

Nested Loop (cost=0.28..184.13 rows=5 width=8) (actual time=0.445..0.761 rows=5 loops=1)

  • Buffers: shared hit=85
123. 0.736 0.736 ↑ 1.0 5 1

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

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

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

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

Index Scan using clients_pkey on clients clients_1 (cost=0.28..0.32 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
126. 15.855 15.855 ↓ 1.9 95 5

Index Scan using index_rates_on_activity_id_and_rate_card_id on rates (cost=0.42..1,376.55 rows=49 width=28) (actual time=0.818..3.171 rows=95 loops=5)

  • Index Cond: (rate_card_id = rate_cards.id)
  • Buffers: shared hit=2457
127. 0.027 0.049 ↑ 38.3 3 1

Sort (cost=6.09..6.37 rows=115 width=4) (actual time=0.048..0.049 rows=3 loops=1)

  • Sort Key: brands.id
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=1
128. 0.022 0.022 ↑ 1.0 115 1

Seq Scan on brands (cost=0.00..2.15 rows=115 width=4) (actual time=0.010..0.022 rows=115 loops=1)

  • Buffers: shared hit=1
129. 16.529 90.660 ↓ 2.6 91,600 1

Subquery Scan on *SELECT* 2 (cost=583.00..5,484.31 rows=35,240 width=44) (actual time=13.036..90.660 rows=91,600 loops=1)

  • Buffers: shared hit=2036
130. 47.364 74.131 ↓ 2.6 91,600 1

Hash Join (cost=583.00..5,131.91 rows=35,240 width=612) (actual time=13.035..74.131 rows=91,600 loops=1)

  • Hash Cond: (rates_1.rate_card_id = rate_cards_1.id)
  • Buffers: shared hit=2036
131. 20.980 20.980 ↑ 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.013..20.980 rows=174,437 loops=1)

  • Buffers: shared hit=1798
132. 0.227 5.787 ↑ 1.1 657 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
  • Buffers: shared hit=238
133. 0.201 5.560 ↑ 1.1 657 1

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

  • Hash Cond: (clients_2.office_id = offices.id)
  • Buffers: shared hit=238
134. 0.228 3.620 ↑ 1.0 623 1

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

  • Hash Cond: (projects_3.client_id = clients_2.id)
  • Buffers: shared hit=190
135. 0.974 2.568 ↑ 1.0 623 1

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

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

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

  • Buffers: shared hit=98
137. 0.109 0.907 ↑ 1.0 623 1

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

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

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

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

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

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

Seq Scan on clients clients_2 (cost=0.00..40.49 rows=1,849 width=8) (actual time=0.011..0.422 rows=1,849 loops=1)

  • Buffers: shared hit=22
141. 0.151 1.739 ↑ 1.1 450 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
  • Buffers: shared hit=48
142. 0.183 1.588 ↑ 1.1 450 1

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

  • Hash Cond: (rate_cards_1.rateable_id = offices.id)
  • Buffers: shared hit=48
143. 0.419 1.279 ↑ 1.0 489 1

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

  • Buffers: shared hit=44
144. 0.200 0.860 ↑ 1.0 489 1

Sort (cost=126.52..127.74 rows=489 width=29) (actual time=0.822..0.860 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
145. 0.660 0.660 ↑ 1.0 489 1

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

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 4365
  • Buffers: shared hit=44
146. 0.065 0.126 ↑ 1.0 441 1

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

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

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

  • Buffers: shared hit=4
148. 16.220 96.381 ↑ 2.2 94,593 1

Subquery Scan on *SELECT* 3 (cost=840.78..9,363.71 rows=205,419 width=44) (actual time=12.538..96.381 rows=94,593 loops=1)

  • Buffers: shared hit=2032
149. 48.281 80.161 ↑ 2.2 94,593 1

Hash Join (cost=840.78..7,309.52 rows=205,419 width=612) (actual time=12.537..80.161 rows=94,593 loops=1)

  • Hash Cond: (rates_2.rate_card_id = rate_cards_2.id)
  • Buffers: shared hit=2032
150. 19.394 19.394 ↑ 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.011..19.394 rows=174,437 loops=1)

  • Buffers: shared hit=1798
151. 1.342 12.486 ↑ 1.0 4,004 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 299kB
  • Buffers: shared hit=234
152. 1.355 11.144 ↑ 1.0 4,004 1

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

  • Hash Cond: (projects_4.client_id = clients_3.id)
  • Buffers: shared hit=234
153. 1.555 5.556 ↑ 1.0 3,620 1

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

  • Hash Cond: (pricing_models_2.project_id = projects_4.id)
  • Buffers: shared hit=168
154. 1.294 1.294 ↑ 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.021..1.294 rows=3,621 loops=1)

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

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

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

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

  • Buffers: shared hit=98
157. 0.481 4.233 ↑ 1.0 1,820 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 123kB
  • Buffers: shared hit=66
158. 0.470 3.752 ↑ 1.0 1,820 1

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

  • Hash Cond: (rate_cards_2.rateable_id = clients_3.id)
  • Buffers: shared hit=66
159. 1.269 2.653 ↑ 1.0 1,821 1

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

  • Buffers: shared hit=44
160. 0.697 1.384 ↑ 1.0 1,821 1

Sort (cost=203.29..207.84 rows=1,821 width=29) (actual time=1.236..1.384 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
161. 0.687 0.687 ↑ 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.012..0.687 rows=1,821 loops=1)

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

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

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

Seq Scan on clients clients_3 (cost=0.00..40.49 rows=1,849 width=4) (actual time=0.009..0.290 rows=1,849 loops=1)

  • Buffers: shared hit=22
164. 11.979 76.672 ↓ 2.4 76,366 1

Subquery Scan on *SELECT* 4 (cost=695.56..5,531.29 rows=31,961 width=44) (actual time=7.243..76.672 rows=76,366 loops=1)

  • Buffers: shared hit=2010
165. 46.263 64.693 ↓ 2.4 76,366 1

Hash Join (cost=695.56..5,211.68 rows=31,961 width=612) (actual time=7.242..64.693 rows=76,366 loops=1)

  • Hash Cond: (rates_3.rate_card_id = rate_cards_3.id)
  • Buffers: shared hit=2010
166. 18.430 18.430 ↑ 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.011..18.430 rows=174,437 loops=1)

  • Buffers: shared hit=1798