explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 359R : Optimization for: Optimization for: Optimization for: Optimization for: plan #TeKD; plan #aMrH; plan #0vt9; plan #K3Qt

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 4,995.486 59,041.892 ↓ 1.6 543,746 1

Merge Left Join (cost=4,521,993.05..4,529,287.62 rows=335,931 width=140) (actual time=51,753.177..59,041.892 rows=543,746 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: 26335303
  • Buffers: shared hit=3078040 read=6864, temp read=471761 written=88541
2. 748.601 52,006.070 ↓ 1.6 543,746 1

Sort (cost=4,521,748.19..4,522,588.01 rows=335,931 width=136) (actual time=51,751.510..52,006.070 rows=543,746 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1"".currency
  • Sort Method: external merge Disk: 61200kB
  • Buffers: shared hit=3078002 read=6864, temp read=471761 written=88541
3. 183.841 51,257.469 ↓ 1.6 543,746 1

Merge Left Join (cost=4,396,962.35..4,467,946.41 rows=335,931 width=136) (actual time=50,433.778..51,257.469 rows=543,746 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: 357131
  • Buffers: shared hit=3078002 read=6864, temp read=461095 written=77853
4. 353.180 51,047.829 ↓ 1.6 543,658 1

Merge Left Join (cost=4,396,939.58..4,451,664.59 rows=335,931 width=132) (actual time=50,433.589..51,047.829 rows=543,658 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: 147215
  • Buffers: shared hit=3077998 read=6864, temp read=461095 written=77853
5. 506.127 49,936.160 ↓ 1.6 543,658 1

Sort (cost=4,341,719.17..4,342,559.00 rows=335,931 width=104) (actual time=49,824.699..49,936.160 rows=543,658 loops=1)

  • Sort Key: projects.id, (COALESCE(pa.activity_id, ""*SELECT* 1_1"".activity_id, staff_membership_activity_links.activity_id))
  • Sort Method: external merge Disk: 47256kB
  • Buffers: shared hit=3069318 read=6864, temp read=459235 written=75984
6. 189.836 49,430.033 ↓ 1.6 543,658 1

Hash Join (cost=3,456,481.63..4,292,512.89 rows=335,931 width=104) (actual time=37,994.782..49,430.033 rows=543,658 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
  • Buffers: shared hit=3069318 read=6864, temp read=450955 written=67682
7. 114.006 49,240.080 ↑ 1.3 764,880 1

Append (cost=3,456,462.96..4,279,739.15 rows=1,007,794 width=104) (actual time=37,992.660..49,240.080 rows=764,880 loops=1)

  • Buffers: shared hit=3069307 read=6864, temp read=450955 written=67682
8. 6,798.047 49,050.947 ↓ 1.3 763,144 1

Merge Left Join (cost=3,456,462.96..3,955,219.99 rows=584,855 width=104) (actual time=37,992.659..49,050.947 rows=763,144 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: 27848423
  • Buffers: shared hit=3063500 read=6864, temp read=450955 written=67682
9. 715.734 37,924.158 ↓ 1.3 763,144 1

Sort (cost=3,446,896.98..3,448,359.12 rows=584,855 width=153) (actual time=37,760.036..37,924.158 rows=763,144 loops=1)

  • Sort Key: "*SELECT* 1_1".project_plan_id
  • Sort Method: external merge Disk: 86616kB
  • Buffers: shared hit=3062511 read=6864, temp read=66542 written=66677
10. 5,349.278 37,208.424 ↓ 1.3 763,144 1

Merge Left Join (cost=3,293,526.39..3,298,915.54 rows=584,855 width=153) (actual time=29,153.853..37,208.424 rows=763,144 loops=1)

  • Merge Cond: ((staff_memberships_1.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: 27603972
  • Buffers: shared hit=3062511 read=6864, temp read=49225 written=49329
11. 1,285.670 29,519.481 ↓ 1.3 763,144 1

Sort (cost=3,293,281.53..3,294,743.67 rows=584,855 width=153) (actual time=29,152.342..29,519.481 rows=763,144 loops=1)

  • Sort Key: staff_memberships_1.account_id, staff_memberships_1.currency
  • Sort Method: external merge Disk: 88664kB
  • Buffers: shared hit=3062473 read=6864, temp read=49225 written=49329
12. 440.290 28,233.811 ↓ 1.3 763,144 1

Hash Left Join (cost=3,123,861.01..3,145,300.09 rows=584,855 width=153) (actual time=26,709.795..28,233.811 rows=763,144 loops=1)

  • Hash Cond: (projects.account_id = accounts_1.id)
  • Filter: (((""*SELECT* 1_1"".vacation_hours)::double precision) < (COALESCE(staff_membership_activity_links.capacity, accounts_1.default_capacity))::double precision)
  • Rows Removed by Filter: 337662
  • Buffers: shared hit=3062473 read=6864, temp read=31547 written=31620
13. 605.667 27,793.365 ↑ 1.6 1,100,806 1

Merge Left Join (cost=3,123,842.05..3,140,595.48 rows=1,754,565 width=148) (actual time=26,709.628..27,793.365 rows=1,100,806 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: 258899
  • Buffers: shared hit=3062462 read=6864, temp read=31547 written=31620
14. 1,568.425 27,071.472 ↑ 1.6 1,100,806 1

Sort (cost=3,122,994.90..3,127,381.32 rows=1,754,565 width=120) (actual time=26,696.266..27,071.472 rows=1,100,806 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1_1"".user_id
  • Sort Method: external merge Disk: 94240kB
  • Buffers: shared hit=3062377 read=6864, temp read=31547 written=31620
15. 380.538 25,503.047 ↑ 1.6 1,100,806 1

Hash Join (cost=2,461,117.20..2,725,128.99 rows=1,754,565 width=120) (actual time=2,100.925..25,503.047 rows=1,100,806 loops=1)

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
  • Buffers: shared hit=3062377 read=6864, temp read=9895 written=9923
16. 218.477 25,120.584 ↑ 1.1 1,597,377 1

Append (cost=2,460,888.43..2,702,745.47 rows=1,754,565 width=116) (actual time=1,932.828..25,120.584 rows=1,597,377 loops=1)

  • Buffers: shared hit=3062279 read=6864, temp read=9895 written=9923
17. 318.618 24,460.662 ↑ 1.2 893,416 1

Result (cost=2,460,888.43..2,655,712.58 rows=1,050,604 width=116) (actual time=1,932.827..24,460.662 rows=893,416 loops=1)

  • Buffers: shared hit=3059527, temp read=9895 written=9923
18. 116.955 24,142.044 ↑ 1.2 893,416 1

Append (cost=2,460,888.43..2,642,580.03 rows=1,050,604 width=140) (actual time=1,932.824..24,142.044 rows=893,416 loops=1)

  • Buffers: shared hit=3059527, temp read=9895 written=9923
19. 131.499 3,066.404 ↑ 1.5 661,732 1

Subquery Scan on *SELECT* 1_1 (cost=2,460,888.43..2,505,659.34 rows=968,516 width=114) (actual time=1,932.823..3,066.404 rows=661,732 loops=1)

  • Buffers: shared hit=2465, temp read=9895 written=9923
20. 189.600 2,934.905 ↑ 1.5 661,732 1

Merge Left Join (cost=2,460,888.43..2,491,131.60 rows=968,516 width=134) (actual time=1,932.821..2,934.905 rows=661,732 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=2465, temp read=9895 written=9923
21. 214.041 2,615.315 ↑ 1.5 661,732 1

Merge Anti Join (cost=1,167,438.64..1,180,748.02 rows=968,516 width=66) (actual time=1,932.818..2,615.315 rows=661,732 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=2134, temp read=9895 written=9923
22. 1,644.303 2,346.763 ↑ 1.8 669,378 1

Sort (cost=1,161,420.65..1,164,487.65 rows=1,226,800 width=66) (actual time=1,932.814..2,346.763 rows=669,378 loops=1)

  • Sort Key: generate_series.generate_series, projects_1.account_id, plan_rows.user_id
  • Sort Method: external merge Disk: 52024kB
  • Buffers: shared hit=1450, temp read=9734 written=9761
23. 125.337 702.460 ↑ 1.8 669,378 1

Nested Loop (cost=1,991.21..936,712.49 rows=1,226,800 width=66) (actual time=25.645..702.460 rows=669,378 loops=1)

  • Buffers: shared hit=1450
24. 14.835 99.243 ↑ 1.0 47,788 1

Hash Left Join (cost=1,991.21..4,344.48 rows=49,072 width=66) (actual time=25.594..99.243 rows=47,788 loops=1)

  • Hash Cond: (project_plans.project_id = projects_1.id)
  • Buffers: shared hit=1450
25. 15.200 82.060 ↑ 1.0 47,788 1

Hash Left Join (cost=1,762.44..3,986.81 rows=49,072 width=62) (actual time=23.210..82.060 rows=47,788 loops=1)

  • Hash Cond: (plan_items.project_plan_id = project_plans.id)
  • Buffers: shared hit=1352
26. 28.938 65.117 ↑ 1.0 47,788 1

Hash Join (cost=1,560.41..3,655.88 rows=49,072 width=58) (actual time=21.408..65.117 rows=47,788 loops=1)

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

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

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
  • Buffers: shared hit=697
28. 11.404 21.141 ↑ 1.0 42,344 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2816kB
  • Buffers: shared hit=549
29. 9.737 9.737 ↑ 1.0 42,344 1

Seq Scan on plan_rows (cost=0.00..1,030.06 rows=42,428 width=28) (actual time=0.007..9.737 rows=42,344 loops=1)

  • Filter: (user_id IS NOT NULL)
  • Rows Removed by Filter: 5762
  • Buffers: shared hit=549
30. 0.562 1.743 ↑ 1.0 2,851 1

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

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

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

  • Filter: active
  • Rows Removed by Filter: 3188
  • Buffers: shared hit=106
32. 1.178 2.348 ↑ 1.0 5,812 1

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

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

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

  • Buffers: shared hit=98
34. 477.880 477.880 ↑ 1.8 14 47,788

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.004..0.010 rows=14 loops=47,788)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 5
35. 43.823 54.511 ↑ 1.0 59,712 1

Sort (cost=6,017.99..6,167.27 rows=59,712 width=12) (actual time=45.338..54.511 rows=59,712 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: external merge Disk: 1288kB
  • Buffers: shared hit=684, temp read=161 written=162
36. 10.688 10.688 ↑ 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.011..10.688 rows=59,712 loops=1)

  • Buffers: shared hit=684
37. 6.545 129.990 ↑ 15.5 37,179 1

Materialize (cost=1,293,449.79..1,296,324.79 rows=575,000 width=44) (actual time=120.335..129.990 rows=37,179 loops=1)

  • Buffers: shared hit=331
38. 26.746 123.445 ↑ 17.8 32,265 1

Sort (cost=1,293,449.79..1,294,887.29 rows=575,000 width=44) (actual time=120.332..123.445 rows=32,265 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
39. 4.897 96.699 ↑ 17.8 32,266 1

Subquery Scan on summary_vacations_by_dates (cost=1,151,985.56..1,220,752.83 rows=575,000 width=44) (actual time=70.091..96.699 rows=32,266 loops=1)

  • Buffers: shared hit=331
40. 18.830 91.802 ↑ 17.8 32,266 1

GroupAggregate (cost=1,151,985.56..1,215,002.83 rows=575,000 width=52) (actual time=70.090..91.802 rows=32,266 loops=1)

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
  • Buffers: shared hit=331
41. 18.454 72.972 ↑ 134.2 32,422 1

Sort (cost=1,151,985.56..1,162,864.01 rows=4,351,382 width=21) (actual time=70.078..72.972 rows=32,422 loops=1)

  • Sort Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
  • Sort Method: quicksort Memory: 2855kB
  • Buffers: shared hit=331
42. 12.909 54.518 ↑ 134.2 32,422 1

Hash Join (cost=138.05..493,700.50 rows=4,351,382 width=21) (actual time=2.190..54.518 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
43. 15.071 39.453 ↑ 634.8 34,330 1

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

  • Buffers: shared hit=283
44. 2.590 2.590 ↑ 1.0 21,792 1

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

  • Buffers: shared hit=283
45. 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)

46. 1.127 2.156 ↑ 1.0 4,002 1

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

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

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

  • Buffers: shared hit=48
48. 226.682 20,958.685 ↓ 2.8 231,684 1

Hash Left Join (cost=1,215.85..130,846.80 rows=82,088 width=114) (actual time=6.883..20,958.685 rows=231,684 loops=1)

  • Hash Cond: (project_plans_1.project_id = projects_1_1.id)
  • Filter: (NOT (SubPlan 1))
  • Rows Removed by Filter: 770
  • Buffers: shared hit=3057062
49. 52.267 273.836 ↓ 1.4 232,454 1

Nested Loop (cost=987.08..127,447.75 rows=164,175 width=62) (actual time=4.620..273.836 rows=232,454 loops=1)

  • Buffers: shared hit=1251
50. 3.597 40.421 ↓ 1.2 7,876 1

Hash Left Join (cost=987.08..2,674.74 rows=6,567 width=62) (actual time=4.596..40.421 rows=7,876 loops=1)

  • Hash Cond: (plan_items_1.project_plan_id = project_plans_1.id)
  • Buffers: shared hit=1251
51. 18.507 35.199 ↓ 1.2 7,876 1

Hash Join (cost=785.05..2,455.47 rows=6,567 width=58) (actual time=2.948..35.199 rows=7,876 loops=1)

  • Hash Cond: (plan_items_1.plan_row_uuid = plan_rows_1.uuid)
  • Buffers: shared hit=1145
52. 13.807 13.807 ↓ 1.0 55,664 1

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

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
  • Buffers: shared hit=697
53. 1.246 2.885 ↓ 1.0 5,762 1

Hash (cost=714.07..714.07 rows=5,678 width=28) (actual time=2.885..2.885 rows=5,762 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 366kB
  • Buffers: shared hit=448
54. 1.437 1.639 ↓ 1.0 5,762 1

Bitmap Heap Scan on plan_rows plan_rows_1 (cost=108.29..714.07 rows=5,678 width=28) (actual time=0.240..1.639 rows=5,762 loops=1)

  • Recheck Cond: (user_id IS NULL)
  • Heap Blocks: exact=430
  • Buffers: shared hit=448
55. 0.202 0.202 ↓ 1.0 5,762 1

Bitmap Index Scan on index_plan_rows_on_user_id (cost=0.00..106.88 rows=5,678 width=0) (actual time=0.202..0.202 rows=5,762 loops=1)

  • Index Cond: (user_id IS NULL)
  • Buffers: shared hit=18
56. 0.474 1.625 ↑ 1.0 2,851 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 144kB
  • Buffers: shared hit=106
57. 1.151 1.151 ↑ 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.009..1.151 rows=2,851 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3188
  • Buffers: shared hit=106
58. 181.148 181.148 ↓ 1.2 30 7,876

Function Scan on generate_series generate_series_2 (cost=0.01..18.76 rows=25 width=8) (actual time=0.008..0.023 rows=30 loops=7,876)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 12
59. 1.035 2.215 ↑ 1.0 5,812 1

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

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

Seq Scan on projects projects_1_1 (cost=0.00..156.12 rows=5,812 width=12) (actual time=0.007..1.180 rows=5,812 loops=1)

  • Buffers: shared hit=98
61.          

SubPlan (for Hash Left Join)

62. 0.000 20,455.952 ↓ 0.0 0 232,454

Nested Loop (cost=0.69..90.49 rows=1 width=0) (actual time=0.088..0.088 rows=0 loops=232,454)

  • Join Filter: (non_working_intervals_by_dates_2.office_id = clients_3.office_id)
  • Rows Removed by Join Filter: 1
  • Buffers: shared hit=3055713
63. 232.454 232.454 ↓ 0.0 0 232,454

Index Scan using clients_pkey on clients clients_3 (cost=0.28..8.29 rows=1 width=4) (actual time=0.000..0.001 rows=0 loops=232,454)

  • Index Cond: (projects_1_1.client_id = id)
  • Buffers: shared hit=177008
64. 20,291.184 20,291.184 ↑ 3.7 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..82.06 rows=11 width=4) (actual time=0.336..0.344 rows=3 loops=58,986)

  • Index Cond: ((projects_1_1.account_id = account_id) AND (generate_series_2.generate_series = date))
  • Buffers: shared hit=2878705
65. 315.987 441.445 ↑ 1.0 703,961 1

Hash Join (cost=1,826.58..31,220.45 rows=703,961 width=116) (actual time=20.172..441.445 rows=703,961 loops=1)

  • Hash Cond: (time_logs.epic_id = epics.id)
  • Buffers: shared hit=2752 read=6864
66. 105.322 105.322 ↑ 1.0 703,961 1

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

  • Buffers: shared hit=2291 read=6864
67. 6.332 20.136 ↑ 1.0 26,435 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1381kB
  • Buffers: shared hit=461
68. 8.859 13.804 ↑ 1.0 26,435 1

Hash Left Join (cost=202.03..1,496.14 rows=26,435 width=16) (actual time=1.893..13.804 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
69. 3.086 3.086 ↑ 1.0 26,435 1

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

  • Buffers: shared hit=355
70. 0.540 1.859 ↑ 1.0 2,851 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 144kB
  • Buffers: shared hit=106
71. 1.319 1.319 ↑ 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.006..1.319 rows=2,851 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3188
  • Buffers: shared hit=106
72. 0.987 1.925 ↑ 1.0 5,812 1

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

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

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

  • Buffers: shared hit=98
74. 100.805 116.226 ↓ 295.4 1,255,026 1

Materialize (cost=847.14..942.74 rows=4,249 width=40) (actual time=13.357..116.226 rows=1,255,026 loops=1)

  • Buffers: shared hit=85
75. 1.488 15.421 ↑ 1.0 4,218 1

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

  • Buffers: shared hit=85
76. 3.311 13.933 ↑ 1.0 4,246 1

Sort (cost=847.14..857.76 rows=4,249 width=65) (actual time=13.354..13.933 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
77. 3.530 10.622 ↑ 1.0 4,249 1

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

  • Buffers: shared hit=85
78. 2.606 7.092 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=56) (actual time=6.726..7.092 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
79. 1.874 4.486 ↑ 1.0 4,249 1

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

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

  • Buffers: shared hit=37
81. 1.132 2.090 ↑ 1.0 4,002 1

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

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

  • Buffers: shared hit=48
83. 0.063 0.156 ↑ 1.0 354 1

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

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

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

  • Buffers: shared hit=11
85. 2,339.148 2,339.665 ↓ 9,193.4 28,021,499 1

Sort (cost=244.86..252.48 rows=3,048 width=24) (actual time=1.379..2,339.665 rows=28,021,499 loops=1)

  • Sort Key: cost_exchange_rates.account_id, cost_exchange_rates.currency
  • Sort Method: quicksort Memory: 335kB
  • Buffers: shared hit=38
86. 0.517 0.517 ↑ 1.0 3,048 1

Seq Scan on exchange_rates_by_months cost_exchange_rates (cost=0.00..68.48 rows=3,048 width=24) (actual time=0.013..0.517 rows=3,048 loops=1)

  • Buffers: shared hit=38
87. 4,131.872 4,328.742 ↓ 5,726.4 28,465,800 1

Sort (cost=9,565.98..9,578.40 rows=4,971 width=48) (actual time=232.600..4,328.742 rows=28,465,800 loops=1)

  • Sort Key: pa.project_plan_id
  • Sort Method: external sort Disk: 2632kB
  • Buffers: shared hit=989, temp read=218682 written=1005
88. 8.192 196.870 ↓ 8.9 44,428 1

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

  • Buffers: shared hit=989, temp read=346 written=347
89. 79.171 188.678 ↓ 8.9 44,428 1

HashAggregate (cost=9,136.50..9,211.06 rows=4,971 width=48) (actual time=140.410..188.678 rows=44,428 loops=1)

  • Group Key: plan_rows_2.project_plan_id, plan_rows_2.epic_id, plan_rows_2.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, temp read=346 written=347
90. 31.824 109.507 ↑ 1.0 44,581 1

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

  • Buffers: shared hit=989, temp read=346 written=347
91. 33.316 77.683 ↑ 1.0 44,581 1

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

  • Sort Key: plan_roles.plan_row_uuid, plan_roles.start_date DESC NULLS LAST
  • Sort Method: external merge Disk: 2768kB
  • Buffers: shared hit=989, temp read=346 written=347
92. 18.103 44.367 ↑ 1.0 44,581 1

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

  • Hash Cond: (plan_roles.plan_row_uuid = plan_rows_2.uuid)
  • Buffers: shared hit=989
93. 4.396 4.396 ↑ 1.0 44,583 1

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

  • Buffers: shared hit=440
94. 12.535 21.868 ↑ 1.0 48,106 1

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

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

Seq Scan on plan_rows plan_rows_2 (cost=0.00..1,030.06 rows=48,106 width=28) (actual time=0.009..9.333 rows=48,106 loops=1)

  • Buffers: shared hit=549
96. 0.590 75.127 ↑ 243.6 1,736 1

Subquery Scan on *SELECT* 2_1 (cost=198,845.55..313,631.64 rows=422,939 width=104) (actual time=32.758..75.127 rows=1,736 loops=1)

  • Buffers: shared hit=5807
97. 33.867 74.537 ↑ 243.6 1,736 1

Merge Left Join (cost=198,845.55..308,344.90 rows=422,939 width=111) (actual time=32.755..74.537 rows=1,736 loops=1)

  • Merge Cond: ((staff_memberships_2.account_id = cost_exchange_rates_1.account_id) AND ((staff_memberships_2.currency)::text = (cost_exchange_rates_1.currency)::text))
  • Join Filter: ((cost_exchange_rates_1.start_date <= (generate_series_3.generate_series)::date) AND (cost_exchange_rates_1.end_date >= (generate_series_3.generate_series)::date))
  • Rows Removed by Join Filter: 105620
  • Buffers: shared hit=5807
98. 1.178 31.332 ↑ 126.7 1,736 1

Sort (cost=198,600.69..199,150.56 rows=219,947 width=64) (actual time=31.166..31.332 rows=1,736 loops=1)

  • Sort Key: staff_memberships_2.account_id, staff_memberships_2.currency
  • Sort Method: quicksort Memory: 293kB
  • Buffers: shared hit=5769
99. 0.949 30.154 ↑ 126.7 1,736 1

Nested Loop (cost=2,980.84..170,813.42 rows=219,947 width=64) (actual time=22.494..30.154 rows=1,736 loops=1)

  • Buffers: shared hit=5769
100. 2.963 27.637 ↑ 11.2 784 1

Hash Join (cost=2,980.83..3,651.41 rows=8,798 width=64) (actual time=22.477..27.637 rows=784 loops=1)

  • Hash Cond: (vacations_1.staff_membership_id = staff_memberships_2.id)
  • Buffers: shared hit=5769
101. 2.228 2.228 ↑ 1.0 21,792 1

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

  • Buffers: shared hit=283
102. 0.329 22.446 ↑ 1.5 1,053 1

Hash (cost=2,960.64..2,960.64 rows=1,615 width=32) (actual time=22.446..22.446 rows=1,053 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 82kB
  • Buffers: shared hit=5486
103. 0.325 22.117 ↑ 1.5 1,053 1

Nested Loop Anti Join (cost=1,107.77..2,960.64 rows=1,615 width=32) (actual time=16.581..22.117 rows=1,053 loops=1)

  • Buffers: shared hit=5486
104. 0.601 18.608 ↑ 1.3 1,592 1

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

  • Hash Cond: (staff_membership_activity_links_1.staff_membership_id = staff_memberships_2.id)
  • Buffers: shared hit=170
105. 0.914 15.992 ↑ 1.3 1,592 1

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

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

  • Buffers: shared hit=37
107. 0.247 14.488 ↓ 8.7 1,592 1

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

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

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

  • Group Key: staff_activities_with_dates.link_id
  • Buffers: shared hit=85
109. 1.328 13.662 ↓ 3.4 1,592 1

Subquery Scan on staff_activities_with_dates (cost=836.52..963.99 rows=472 width=4) (actual time=11.117..13.662 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
110. 0.874 12.334 ↑ 1.0 4,221 1

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

  • Buffers: shared hit=85
111. 2.987 11.460 ↑ 1.0 4,249 1

Sort (cost=836.52..847.14 rows=4,249 width=613) (actual time=11.110..11.460 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
112. 3.422 8.473 ↑ 1.0 4,249 1

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

  • Buffers: shared hit=85
113. 1.859 5.051 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=28) (actual time=4.650..5.051 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
114. 1.236 3.192 ↑ 1.0 4,249 1

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

  • Hash Cond: (staff_membership_activity_links_2.staff_membership_id = staff_memberships_3.id)
  • Buffers: shared hit=85
115. 0.414 0.414 ↑ 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.003..0.414 rows=4,256 loops=1)

  • Buffers: shared hit=37
116. 0.807 1.542 ↑ 1.0 4,002 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 229kB
  • Buffers: shared hit=48
117. 0.735 0.735 ↑ 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.005..0.735 rows=4,002 loops=1)

  • Buffers: shared hit=48
118. 0.952 2.015 ↑ 1.0 4,002 1

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

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

  • Buffers: shared hit=48
120. 3.184 3.184 ↓ 0.0 0 1,592

Index Only Scan using non_working_intervals_by_dates_idx on non_working_intervals_by_dates non_working_intervals_by_dates_1 (cost=0.41..1.38 rows=2 width=8) (actual time=0.002..0.002 rows=0 loops=1,592)

  • Index Cond: ((account_id = staff_memberships_2.account_id) AND (user_id = staff_memberships_2.user_id) AND (date IS NOT NULL))
  • Heap Fetches: 539
  • Buffers: shared hit=5316
121. 1.568 1.568 ↑ 12.5 2 784

Function Scan on generate_series generate_series_3 (cost=0.01..18.76 rows=25 width=8) (actual time=0.002..0.002 rows=2 loops=784)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 0
122. 8.818 9.338 ↓ 35.9 109,489 1

Sort (cost=244.86..252.48 rows=3,048 width=24) (actual time=1.460..9.338 rows=109,489 loops=1)

  • Sort Key: cost_exchange_rates_1.account_id, cost_exchange_rates_1.currency
  • Sort Method: quicksort Memory: 335kB
  • Buffers: shared hit=38
123. 0.520 0.520 ↑ 1.0 3,048 1

Seq Scan on exchange_rates_by_months cost_exchange_rates_1 (cost=0.00..68.48 rows=3,048 width=24) (actual time=0.012..0.520 rows=3,048 loops=1)

  • Buffers: shared hit=38
124. 0.004 0.117 ↑ 9.8 12 1

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

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

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

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 342
  • Buffers: shared hit=11
126. 85.028 758.489 ↓ 3.1 838,241 1

Materialize (cost=55,220.42..56,583.76 rows=272,669 width=44) (actual time=608.853..758.489 rows=838,241 loops=1)

  • Buffers: shared hit=8680, temp read=1860 written=1869
127. 363.129 673.461 ↑ 1.0 261,899 1

Sort (cost=55,220.42..55,902.09 rows=272,669 width=44) (actual time=608.850..673.461 rows=261,899 loops=1)

  • Sort Key: ""*SELECT* 1"".project_id, ""*SELECT* 1"".activity_id
  • Sort Method: external merge Disk: 14904kB
  • Buffers: shared hit=8680, temp read=1860 written=1869
128. 34.475 310.332 ↑ 1.0 263,033 1

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

  • Buffers: shared hit=8680
129. 0.089 17.579 ↓ 9.7 474 1

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

  • Buffers: shared hit=2602
130. 0.144 17.490 ↓ 9.7 474 1

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

  • Merge Cond: (clients.brand_id = brands.id)
  • Buffers: shared hit=2602
131. 0.136 17.303 ↓ 9.7 474 1

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

  • Buffers: shared hit=2601
132. 0.009 1.247 ↓ 5.0 5 1

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

  • Merge Cond: (rate_cards.rateable_id = clients.brand_id)
  • Buffers: shared hit=144
133. 0.024 0.540 ↑ 28.8 4 1

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

  • Buffers: shared hit=44
134. 0.034 0.516 ↑ 23.0 5 1

Sort (cost=108.61..108.90 rows=115 width=29) (actual time=0.515..0.516 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
135. 0.482 0.482 ↑ 1.0 115 1

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

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4739
  • Buffers: shared hit=44
136. 0.011 0.698 ↑ 1.0 5 1

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

  • Sort Key: clients.brand_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=100
137. 0.003 0.687 ↑ 1.0 5 1

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

  • Buffers: shared hit=100
138. 0.006 0.664 ↑ 1.0 5 1

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

  • Buffers: shared hit=85
139. 0.638 0.638 ↑ 1.0 5 1

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

  • Filter: ((rates_type)::text = 'brand'::text)
  • Rows Removed by Filter: 5805
  • Buffers: shared hit=70
140. 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
141. 0.020 0.020 ↑ 1.0 1 5

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

  • Index Cond: (id = projects_2.client_id)
  • Buffers: shared hit=15
142. 15.920 15.920 ↓ 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.554..3.184 rows=95 loops=5)

  • Index Cond: (rate_card_id = rate_cards.id)
  • Buffers: shared hit=2457
143. 0.026 0.043 ↑ 38.3 3 1

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

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

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

  • Buffers: shared hit=1
145. 14.625 78.928 ↓ 2.6 91,600 1

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

  • Buffers: shared hit=2036
146. 39.831 64.303 ↓ 2.6 91,600 1

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

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

  • Buffers: shared hit=1798
148. 0.189 5.353 ↑ 1.1 657 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
  • Buffers: shared hit=238
149. 0.205 5.164 ↑ 1.1 657 1

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

  • Hash Cond: (clients_1.office_id = offices.id)
  • Buffers: shared hit=238
150. 0.216 3.424 ↑ 1.0 623 1

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

  • Hash Cond: (projects_3.client_id = clients_1.id)
  • Buffers: shared hit=190
151. 1.006 2.564 ↑ 1.0 623 1

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

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

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

  • Buffers: shared hit=98
153. 0.101 0.792 ↑ 1.0 623 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
  • Buffers: shared hit=70
154. 0.691 0.691 ↑ 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.691 rows=623 loops=1)

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

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

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

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

  • Buffers: shared hit=22
157. 0.166 1.535 ↑ 1.1 450 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
  • Buffers: shared hit=48
158. 0.146 1.369 ↑ 1.1 450 1

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

  • Hash Cond: (rate_cards_1.rateable_id = offices.id)
  • Buffers: shared hit=48
159. 0.341 1.112 ↑ 1.0 489 1

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

  • Buffers: shared hit=44
160. 0.183 0.771 ↑ 1.0 489 1

Sort (cost=126.52..127.74 rows=489 width=29) (actual time=0.736..0.771 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
161. 0.588 0.588 ↑ 1.0 489 1

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

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 4365
  • Buffers: shared hit=44
162. 0.057 0.111 ↑ 1.0 441 1

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

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

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

  • Buffers: shared hit=4
164. 17.630 98.587 ↑ 2.2 94,593 1

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

  • Buffers: shared hit=2032
165. 48.640 80.957 ↑ 2.2 94,593 1

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

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

  • Buffers: shared hit=1798
167. 0.990 11.382 ↑ 1.0 4,004 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 299kB
  • Buffers: shared hit=234
168. 1.114 10.392 ↑ 1.0 4,004 1

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

  • Hash Cond: (projects_4.client_id = clients_2.id)
  • Buffers: shared hit=234
169. 1.194 5.035 ↑ 1.0 3,620 1

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

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

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

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

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

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

  • Buffers: shared hit=98
173. 0.499 4.243 ↑ 1.0 1,820 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 123kB
  • Buffers: shared hit=66
174. 0.540 3.744 ↑ 1.0 1,820 1

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

  • Hash Cond: (rate_cards_2.rateable_id = clients_2.id)
  • Buffers: shared hit=66
175. 1.284 2.606 ↑ 1.0 1,821 1

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

  • Buffers: shared hit=44
176. 0.683 1.322 ↑ 1.0 1,821 1

Sort (cost=203.29..207.84 rows=1,821 width=29) (actual time=1.169..1.322 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
177. 0.639 0.639 ↑ 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.639 rows=1,821 loops=1)

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

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

  • Buckets: 2048 Batches: 1 Memory Usage: 82kB
  • Buffers: shared hit=22
179. 0.276 0.276 ↑ 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.276 rows=1,849 loops=1)

  • Buffers: shared hit=22
180. 12.804 80.763 ↓ 2.4 76,366 1

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

  • Buffers: shared hit=2010
181. 40.870 67.959 ↓ 2.4 76,366 1

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

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

  • Buffers: shared hit=1798
183. 0.428 7.541 ↓ 2.4 1,591 1

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

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

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

  • Hash Cond: (projects_5.id = pricing_models_3.project_id)
  • Buffers: shared hit=212
185. 0.590 0.590 ↑ 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.590 rows=5,812 loops=1)

  • Buffers: shared hit=98
186. 0.381 5.518 ↓ 2.4 1,591 1

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

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

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

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
  • Buffers: shared hit=114
188. 1.763 3.554 ↑ 1.0 2,429 1

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

  • Buffers: shared hit=44
189. 1.007 1.791 ↑ 1.0 2,429 1

Sort (cost=241.26..247.33 rows=2,429 width=29) (actual time=1.598..1.791 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
190. 0.784 0.784 ↑ 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.007..0.784 rows=2,429 loops=1)

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

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

  • Buckets: 2048 Batches: 1 Memory Usage: 77kB
  • Buffers: shared hit=70
192. 0.788 0.788 ↑ 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.008..0.788 rows=1,561 loops=1)

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 4249
  • Buffers: shared hit=70
193. 25.712 25.799 ↓ 1,191.9 364,727 1

Sort (cost=22.77..23.53 rows=306 width=26) (actual time=0.187..25.799 rows=364,727 loops=1)

  • Sort Key: discounts.project_id
  • Sort Method: quicksort Memory: 53kB
  • Buffers: shared hit=4
194. 0.087 0.087 ↓ 1.2 359 1

Seq Scan on discounts (cost=0.00..10.13 rows=306 width=26) (actual time=0.013..0.087 rows=359 loops=1)

  • Filter: (((discountable_type)::text = 'User'::text) OR ((discountable_type)::text = 'Epic'::text))
  • Rows Removed by Filter: 50
  • Buffers: shared hit=4
195. 2,039.785 2,040.336 ↓ 8,771.0 26,733,906 1

Sort (cost=244.86..252.48 rows=3,048 width=24) (actual time=1.569..2,040.336 rows=26,733,906 loops=1)

  • Sort Key: exchange_rates_by_months.account_id, exchange_rates_by_months.currency
  • Sort Method: quicksort Memory: 335kB
  • Buffers: shared hit=38
196. 0.551 0.551 ↑ 1.0 3,048 1

Seq Scan on exchange_rates_by_months (cost=0.00..68.48 rows=3,048 width=24) (actual time=0.014..0.551 rows=3,048 loops=1)

  • Buffers: shared hit=38
Planning time : 12.195 ms
Execution time : 59,155.991 ms