explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XV9P

Settings
# exclusive inclusive rows x rows loops node
1. 4.020 2,631.548 ↓ 10.8 22,419 1

Subquery Scan on report_items_live (cost=70,555.04..70,773.13 rows=2,077 width=128) (actual time=2,602.488..2,631.548 rows=22,419 loops=1)

2. 22.593 2,627.528 ↓ 10.8 22,419 1

GroupAggregate (cost=70,555.04..70,752.36 rows=2,077 width=184) (actual time=2,602.486..2,627.528 rows=22,419 loops=1)

  • Group Key: projects.account_id, projects.id, clients.id, clients.office_id, rates_with_dates.rate_card_id, rates_with_dates.id, ""*SELECT* 1"".project_plan_id, ""*SELECT* 1"".epic_id, (COALESCE(pa.activity_id, ""*SELECT* 1"".activity_id, staff_membership_activity_links.activity_id)), ""*SELECT* 1"".user_id, ""*SELECT* 1"".date, (CASE WHEN ((1) = 2) THEN ""*SELECT* 1"".""time"" WHEN (((""*SELECT* 1"".vacation_user_id)::double precision) IS NOT NULL) THEN GREATEST(((((COALESCE(staff_membership_activity_links.capacity, '0'::numeric))::double precision - COALESCE(((""*SELECT* 1"".vacation_hours)::double precision), (staff_membership_activity_links.capacity)::double precision, '0'::double precision)) * ((""*SELECT* 1"".utilization)::double precision)) / '100'::double precision), '0'::double precision) WHEN (""*SELECT* 1"".user_id IS NULL) THEN (((accounts_1.default_capacity)::double precision * ((""*SELECT* 1"".utilization)::double precision)) / '100'::double precision) ELSE (((COALESCE(staff_membership_activity_links.capacity, '0'::numeric))::double precision * ((""*SELECT* 1"".utilization)::double precision)) / '100'::double precision) END), (1), ""*SELECT* 1"".reportable_id, ""*SELECT* 1"".plan_row_uuid, rates_with_dates.currency, (COALESCE(exchange_rates_by_months.rate, '1'::double precision)), rates_with_dates.value, 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"".updated_at, pa.updated_at, (GREATEST(staff_memberships.updated_at, staff_membership_activity_links.updated_at)))), rates_with_dates.updated_at, rates_with_dates.rate_card_updated_at))
3. 96.628 2,604.935 ↓ 10.8 22,419 1

Sort (cost=70,555.04..70,560.23 rows=2,077 width=180) (actual time=2,602.470..2,604.935 rows=22,419 loops=1)

  • Sort Key: projects.account_id, clients.id, clients.office_id, rates_with_dates.rate_card_id, rates_with_dates.id, ""*SELECT* 1"".project_plan_id, ""*SELECT* 1"".epic_id, (COALESCE(pa.activity_id, ""*SELECT* 1"".activity_id, staff_membership_activity_links.activity_id)), ""*SELECT* 1"".user_id, ""*SELECT* 1"".date, (CASE WHEN ((1) = 2) THEN ""*SELECT* 1"".""time"" WHEN (((""*SELECT* 1"".vacation_user_id)::double precision) IS NOT NULL) THEN GREATEST(((((COALESCE(staff_membership_activity_links.capacity, '0'::numeric))::double precision - COALESCE(((""*SELECT* 1"".vacation_hours)::double precision), (staff_membership_activity_links.capacity)::double precision, '0'::double precision)) * ((""*SELECT* 1"".utilization)::double precision)) / '100'::double precision), '0'::double precision) WHEN (""*SELECT* 1"".user_id IS NULL) THEN (((accounts_1.default_capacity)::double precision * ((""*SELECT* 1"".utilization)::double precision)) / '100'::double precision) ELSE (((COALESCE(staff_membership_activity_links.capacity, '0'::numeric))::double precision * ((""*SELECT* 1"".utilization)::double precision)) / '100'::double precision) END), (1), ""*SELECT* 1"".reportable_id, ""*SELECT* 1"".plan_row_uuid, rates_with_dates.currency, (COALESCE(exchange_rates_by_months.rate, '1'::double precision)), rates_with_dates.value, 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"".updated_at, pa.updated_at, (GREATEST(staff_memberships.updated_at, staff_membership_activity_links.updated_at)))), rates_with_dates.updated_at, rates_with_dates.rate_card_updated_at))
  • Sort Method: quicksort Memory: 6724kB
4. 25.930 2,508.307 ↓ 10.8 22,419 1

Hash Left Join (cost=70,003.77..70,440.60 rows=2,077 width=180) (actual time=1,500.608..2,508.307 rows=22,419 loops=1)

  • Hash Cond: (projects.id = discounts.project_id)
  • Join Filter: (("*SELECT* 1".date >= discounts.start_date) AND ("*SELECT* 1".date <= discounts.end_date) AND (((discounts.discountable_id = "*SELECT* 1".user_id) AND ((discounts.discountable_type)::text = 'User'::text)) OR ((discounts.discountable_id = "*SELECT* 1".epic_id) AND ((discounts.discountable_type)::text = 'Epic'::text))))
  • Rows Removed by Join Filter: 179055
5. 351.849 2,482.325 ↓ 10.8 22,419 1

Merge Left Join (cost=69,995.22..70,094.53 rows=2,077 width=168) (actual time=1,500.521..2,482.325 rows=22,419 loops=1)

  • Merge Cond: ((projects.account_id = original_exchange_rates.account_id) AND ((rates_with_dates.currency)::text = (original_exchange_rates.currency)::text))
  • Join Filter: ((original_exchange_rates.start_date <= "*SELECT* 1".date) AND (original_exchange_rates.end_date >= "*SELECT* 1".date))
  • Rows Removed by Join Filter: 1972872
6. 350.110 1,990.699 ↓ 10.8 22,419 1

Merge Left Join (cost=69,902.01..69,954.26 rows=2,077 width=160) (actual time=1,499.867..1,990.699 rows=22,419 loops=1)

  • Merge Cond: ((projects.account_id = exchange_rates_by_months.account_id) AND ((rates_with_dates.currency)::text = (exchange_rates_by_months.currency)::text))
  • Join Filter: ((exchange_rates_by_months.start_date <= "*SELECT* 1".date) AND (exchange_rates_by_months.end_date >= "*SELECT* 1".date))
  • Rows Removed by Join Filter: 1972872
7. 18.787 1,501.313 ↓ 10.8 22,419 1

Sort (cost=69,808.80..69,813.99 rows=2,077 width=152) (actual time=1,499.223..1,501.313 rows=22,419 loops=1)

  • Sort Key: projects.account_id, rates_with_dates.currency
  • Sort Method: quicksort Memory: 6656kB
8. 15.998 1,482.526 ↓ 10.8 22,419 1

Hash Right Join (cost=63,240.12..69,694.35 rows=2,077 width=152) (actual time=1,459.194..1,482.526 rows=22,419 loops=1)

  • Hash Cond: ((rates_with_dates.project_id = projects.id) AND (rates_with_dates.activity_id = (COALESCE(pa.activity_id, ""*SELECT* 1"".activity_id, staff_membership_activity_links.activity_id))))
  • Join Filter: (("*SELECT* 1".date >= rates_with_dates.start_date) AND (("*SELECT* 1".date <= rates_with_dates.end_date) OR (rates_with_dates.end_date IS NULL)))
  • Rows Removed by Join Filter: 63666
9. 30.263 30.263 ↓ 1.0 59 1

Seq Scan on rates_with_dates (cost=0.00..6,421.41 rows=58 width=52) (actual time=22.892..30.263 rows=59 loops=1)

  • Filter: (project_id = 10070)
  • Rows Removed by Filter: 253734
10. 12.284 1,436.265 ↓ 10.8 22,419 1

Hash (cost=63,208.97..63,208.97 rows=2,077 width=116) (actual time=1,436.265..1,436.265 rows=22,419 loops=1)

  • Buckets: 32768 (originally 4096) Batches: 1 (originally 1) Memory Usage: 2957kB
11. 338.293 1,423.981 ↓ 10.8 22,419 1

Merge Left Join (cost=63,182.99..63,208.97 rows=2,077 width=116) (actual time=950.937..1,423.981 rows=22,419 loops=1)

  • Merge Cond: ((projects.account_id = cost_exchange_rates.account_id) AND ((staff_memberships.currency)::text = (cost_exchange_rates.currency)::text))
  • Join Filter: ((cost_exchange_rates.start_date <= "*SELECT* 1".date) AND (cost_exchange_rates.end_date >= "*SELECT* 1".date))
  • Rows Removed by Join Filter: 1876248
12. 19.593 951.144 ↓ 10.8 22,419 1

Sort (cost=63,089.78..63,094.98 rows=2,077 width=624) (actual time=949.329..951.144 rows=22,419 loops=1)

  • Sort Key: projects.account_id, staff_memberships.currency
  • Sort Method: quicksort Memory: 3921kB
13. 7.796 931.551 ↓ 10.8 22,419 1

Hash Join (cost=57,817.95..62,975.34 rows=2,077 width=624) (actual time=380.223..931.551 rows=22,419 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
14. 3.200 923.638 ↓ 3.6 22,419 1

Append (cost=57,799.54..62,878.06 rows=6,231 width=624) (actual time=379.603..923.638 rows=22,419 loops=1)

15. 26.350 920.436 ↓ 3.6 22,419 1

Nested Loop Left Join (cost=57,799.54..62,784.60 rows=6,230 width=83) (actual time=379.602..920.436 rows=22,419 loops=1)

16. 14.747 400.868 ↓ 3.6 22,419 1

Merge Left Join (cost=57,799.13..58,083.86 rows=6,230 width=129) (actual time=379.524..400.868 rows=22,419 loops=1)

  • Merge Cond: ((projects.account_id = staff_memberships.account_id) AND ("*SELECT* 1".user_id = staff_memberships.user_id))
  • Join Filter: ((""*SELECT* 1"".date >= (COALESCE(staff_membership_activity_links.start_date, staff_memberships.joined_at))) AND (""*SELECT* 1"".date <= (COALESCE(lag((staff_membership_activity_links.start_date - 1), 1) OVER (?), staff_memberships.archived_at, '3000-01-01'::date))))
  • Rows Removed by Join Filter: 3348
  • Filter: ((((""*SELECT* 1"".vacation_hours)::double precision) IS NULL) OR (((""*SELECT* 1"".vacation_hours)::double precision) < (COALESCE(staff_membership_activity_links.capacity, accounts_1.default_capacity))::double precision))
  • Rows Removed by Filter: 102
17. 20.696 368.580 ↓ 1.2 22,521 1

Sort (cost=56,978.24..57,024.50 rows=18,504 width=105) (actual time=364.690..368.580 rows=22,521 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1"".user_id
  • Sort Method: quicksort Memory: 3936kB
18. 3.957 347.884 ↓ 1.2 22,521 1

Nested Loop (cost=0.71..55,666.72 rows=18,504 width=105) (actual time=0.059..347.884 rows=22,521 loops=1)

19. 0.002 0.023 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.71..24.82 rows=1 width=21) (actual time=0.019..0.023 rows=1 loops=1)

20. 0.004 0.018 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.56..16.60 rows=1 width=16) (actual time=0.015..0.018 rows=1 loops=1)

21. 0.010 0.010 ↑ 1.0 1 1

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

  • Index Cond: (id = 10070)
22. 0.004 0.004 ↑ 1.0 1 1

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

  • Index Cond: (id = projects.client_id)
23. 0.003 0.003 ↑ 1.0 1 1

Index Scan using accounts_pkey on accounts accounts_1 (cost=0.15..8.17 rows=1 width=9) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: (id = projects.account_id)
24. 2.597 343.904 ↓ 1.2 22,521 1

Append (cost=0.00..55,271.83 rows=18,504 width=92) (actual time=0.037..343.904 rows=22,521 loops=1)

25. 3.811 230.221 ↓ 1.8 11,684 1

Subquery Scan on *SELECT* 1 (cost=0.00..38,792.22 rows=6,589 width=92) (actual time=0.037..230.221 rows=11,684 loops=1)

26. 226.410 226.410 ↓ 1.8 11,684 1

Seq Scan on prepared_plan_items ppi (cost=0.00..38,676.91 rows=6,589 width=82) (actual time=0.034..226.410 rows=11,684 loops=1)

  • Filter: ((is_non_working_day IS NULL) AND (project_id = 10070))
  • Rows Removed by Filter: 1489909
27. 111.086 111.086 ↑ 1.1 10,837 1

Seq Scan on prepared_time_logs ptl (cost=0.00..16,267.94 rows=11,915 width=92) (actual time=0.036..111.086 rows=10,837 loops=1)

  • Filter: ((is_non_working_day IS NULL) AND (project_id = 10070))
  • Rows Removed by Filter: 677912
28. 2.615 17.541 ↓ 6.0 24,883 1

Materialize (cost=820.88..913.45 rows=4,114 width=40) (actual time=14.818..17.541 rows=24,883 loops=1)

29. 0.081 14.926 ↑ 24.6 167 1

Unique (cost=820.88..862.02 rows=4,114 width=65) (actual time=14.814..14.926 rows=167 loops=1)

30. 2.916 14.845 ↑ 24.5 168 1

Sort (cost=820.88..831.17 rows=4,114 width=65) (actual time=14.813..14.845 rows=168 loops=1)

  • Sort Key: staff_memberships.account_id, staff_memberships.user_id, (COALESCE(staff_membership_activity_links.start_date, staff_memberships.joined_at)), (COALESCE(lag((staff_membership_activity_links.start_date - 1), 1) OVER (?), staff_memberships.archived_at, '3000-01-01'::date)) DESC
  • Sort Method: quicksort Memory: 771kB
31. 2.708 11.929 ↑ 1.0 4,114 1

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

32. 5.487 9.221 ↑ 1.0 4,114 1

Sort (cost=471.06..481.35 rows=4,114 width=56) (actual time=8.910..9.221 rows=4,114 loops=1)

  • Sort Key: staff_membership_activity_links.staff_membership_id, staff_membership_activity_links.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 771kB
33. 1.580 3.734 ↑ 1.0 4,114 1

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

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships.id)
34. 0.433 0.433 ↑ 1.0 4,121 1

Seq Scan on staff_membership_activity_links (cost=0.00..77.21 rows=4,121 width=28) (actual time=0.014..0.433 rows=4,121 loops=1)

35. 0.923 1.721 ↑ 1.0 3,958 1

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

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

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

37. 493.218 493.218 ↑ 1.0 1 22,419

Index Scan using pawd on planned_activities_with_dates pa (cost=0.41..0.71 rows=1 width=48) (actual time=0.012..0.022 rows=1 loops=22,419)

  • Index Cond: ((project_plan_id = "*SELECT* 1".project_plan_id) AND ("*SELECT* 1".date >= start_date) AND ("*SELECT* 1".date <= end_date))
  • Filter: (((plan_row_uuid = "*SELECT* 1".plan_row_uuid) OR ("*SELECT* 1".plan_row_uuid IS NULL)) AND ((user_id = "*SELECT* 1".user_id) OR ("*SELECT* 1".user_id IS NULL)) AND ((epic_id = "*SELECT* 1".epic_id) OR (((1) = 1) AND ("*SELECT* 1".epic_id IS NULL)) OR (((1) = 2) AND (epic_id IS NULL))))
  • Rows Removed by Filter: 49
38. 0.001 0.002 ↓ 0.0 0 1

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

39. 0.001 0.001 ↓ 0.0 0 1

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

  • One-Time Filter: false
40. 0.003 0.117 ↑ 38.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
41. 0.114 0.114 ↑ 38.0 3 1

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

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 339
42. 134.294 134.544 ↓ 1,511.3 1,898,193 1

Sort (cost=93.21..96.35 rows=1,256 width=24) (actual time=1.399..134.544 rows=1,898,193 loops=1)

  • Sort Key: cost_exchange_rates.account_id, cost_exchange_rates.currency
  • Sort Method: quicksort Memory: 147kB
43. 0.250 0.250 ↑ 1.0 1,256 1

Seq Scan on exchange_rates_by_months cost_exchange_rates (cost=0.00..28.56 rows=1,256 width=24) (actual time=0.016..0.250 rows=1,256 loops=1)

44. 139.069 139.276 ↓ 1,589.1 1,995,915 1

Sort (cost=93.21..96.35 rows=1,256 width=24) (actual time=0.519..139.276 rows=1,995,915 loops=1)

  • Sort Key: exchange_rates_by_months.account_id, exchange_rates_by_months.currency
  • Sort Method: quicksort Memory: 147kB
45. 0.207 0.207 ↑ 1.0 1,256 1

Seq Scan on exchange_rates_by_months (cost=0.00..28.56 rows=1,256 width=24) (actual time=0.010..0.207 rows=1,256 loops=1)

46. 139.585 139.777 ↓ 1,589.1 1,995,915 1

Sort (cost=93.21..96.35 rows=1,256 width=24) (actual time=0.531..139.777 rows=1,995,915 loops=1)

  • Sort Key: original_exchange_rates.account_id, original_exchange_rates.currency
  • Sort Method: quicksort Memory: 147kB
47. 0.192 0.192 ↑ 1.0 1,256 1

Seq Scan on exchange_rates_by_months original_exchange_rates (cost=0.00..28.56 rows=1,256 width=24) (actual time=0.004..0.192 rows=1,256 loops=1)

48. 0.013 0.052 ↓ 1.3 8 1

Hash (cost=8.47..8.47 rows=6 width=26) (actual time=0.052..0.052 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
49. 0.032 0.039 ↓ 1.3 8 1

Bitmap Heap Scan on discounts (cost=4.33..8.47 rows=6 width=26) (actual time=0.034..0.039 rows=8 loops=1)

  • Recheck Cond: (project_id = 10070)
  • Filter: (((discountable_type)::text = 'User'::text) OR ((discountable_type)::text = 'Epic'::text))
  • Heap Blocks: exact=2
50. 0.007 0.007 ↑ 1.0 8 1

Bitmap Index Scan on index_discounts_on_project_id (cost=0.00..4.33 rows=8 width=0) (actual time=0.007..0.007 rows=8 loops=1)

  • Index Cond: (project_id = 10070)
Planning time : 10.899 ms
Execution time : 2,639.435 ms