explain.depesz.com

PostgreSQL's explain analyze made readable

Result: p92l

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 120.118 ↑ 204.0 1 1

Subquery Scan on report_items_live (cost=23,274.46..23,295.88 rows=204 width=128) (actual time=120.118..120.118 rows=1 loops=1)

2. 0.009 120.116 ↑ 204.0 1 1

GroupAggregate (cost=23,274.46..23,293.84 rows=204 width=184) (actual time=120.116..120.116 rows=1 loops=1)

  • Group Key: items.account_id, items.project_id, items.client_id, items.office_id, rates_with_dates.rate_card_id, rates_with_dates.id, items.project_plan_id, items.epic_id, items.activity_id, items.user_id, items.date, items.value, items.reportable_type, items.reportable_id, items.plan_row_uuid, rates_with_dates.currency, (COALESCE(exchange_rates_by_months.rate, '1'::double precision)), rates_with_dates.value, items.cost, (COALESCE(cost_exchange_rates.rate, '1'::double precision)), (COALESCE(original_exchange_rates.rate, '1'::double precision)), (GREATEST(items.updated_at, rates_with_dates.updated_at, rates_with_dates.rate_card_updated_at))
3. 0.020 120.107 ↑ 204.0 1 1

Sort (cost=23,274.46..23,274.97 rows=204 width=180) (actual time=120.107..120.107 rows=1 loops=1)

  • Sort Key: items.account_id, items.project_id, items.client_id, items.office_id, rates_with_dates.rate_card_id, rates_with_dates.id, items.project_plan_id, items.epic_id, items.activity_id, items.user_id, items.date, items.value, items.plan_row_uuid, rates_with_dates.currency, (COALESCE(exchange_rates_by_months.rate, '1'::double precision)), rates_with_dates.value, items.cost, (COALESCE(cost_exchange_rates.rate, '1'::double precision)), (COALESCE(original_exchange_rates.rate, '1'::double precision)), (GREATEST(items.updated_at, rates_with_dates.updated_at, rates_with_dates.rate_card_updated_at))
  • Sort Method: quicksort Memory: 25kB
4. 0.047 120.087 ↑ 204.0 1 1

Hash Left Join (cost=874.82..23,266.63 rows=204 width=180) (actual time=23.229..120.087 rows=1 loops=1)

  • Hash Cond: ((items.account_id = cost_exchange_rates.account_id) AND ((items.cost_currency)::text = (cost_exchange_rates.currency)::text))
  • Join Filter: ((cost_exchange_rates.start_date <= items.date) AND (cost_exchange_rates.end_date >= items.date))
  • Rows Removed by Join Filter: 88
5. 0.020 119.617 ↑ 35.0 1 1

Nested Loop Left Join (cost=827.42..23,177.33 rows=35 width=680) (actual time=22.764..119.617 rows=1 loops=1)

  • Join Filter: ((original_exchange_rates.start_date <= items.date) AND (original_exchange_rates.end_date >= items.date) AND (original_exchange_rates.account_id = items.account_id))
  • Rows Removed by Join Filter: 156
6. 0.022 119.530 ↑ 6.0 1 1

Nested Loop Left Join (cost=827.15..23,093.24 rows=6 width=672) (actual time=22.718..119.530 rows=1 loops=1)

  • Join Filter: ((exchange_rates_by_months.start_date <= items.date) AND (exchange_rates_by_months.end_date >= items.date) AND (exchange_rates_by_months.account_id = items.account_id))
  • Rows Removed by Join Filter: 156
7. 0.003 119.440 ↑ 1.0 1 1

Nested Loop Left Join (cost=826.87..23,079.22 rows=1 width=664) (actual time=22.666..119.440 rows=1 loops=1)

8. 0.009 114.017 ↑ 1.0 1 1

Nested Loop Left Join (cost=826.45..17,380.36 rows=1 width=628) (actual time=17.243..114.017 rows=1 loops=1)

9. 0.005 114.003 ↑ 1.0 1 1

Nested Loop (cost=822.15..17,372.20 rows=1 width=624) (actual time=17.229..114.003 rows=1 loops=1)

  • Join Filter: (items.account_id = accounts.id)
  • Rows Removed by Join Filter: 2
10. 0.145 0.145 ↑ 38.0 3 1

Index Scan using accounts_pkey on accounts (cost=0.15..35.85 rows=114 width=4) (actual time=0.016..0.145 rows=3 loops=1)

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 339
11. 0.003 113.853 ↑ 2.0 1 3

Materialize (cost=822.01..17,332.94 rows=2 width=624) (actual time=5.737..37.951 rows=1 loops=3)

12. 0.002 113.850 ↑ 2.0 1 1

Subquery Scan on items (cost=822.01..17,332.93 rows=2 width=624) (actual time=17.209..113.850 rows=1 loops=1)

13. 0.001 113.848 ↑ 2.0 1 1

Append (cost=822.01..17,332.91 rows=2 width=624) (actual time=17.208..113.848 rows=1 loops=1)

14. 0.008 113.845 ↑ 1.0 1 1

Nested Loop Left Join (cost=822.01..17,332.88 rows=1 width=83) (actual time=17.207..113.845 rows=1 loops=1)

15. 0.003 113.781 ↑ 1.0 1 1

Nested Loop Left Join (cost=821.59..17,324.29 rows=1 width=129) (actual time=17.155..113.781 rows=1 loops=1)

16. 0.006 113.762 ↑ 1.0 1 1

Nested Loop Left Join (cost=821.31..17,323.98 rows=1 width=125) (actual time=17.136..113.762 rows=1 loops=1)

  • Filter: ((((""*SELECT* 1"".vacation_hours)::double precision) IS NULL) OR (((""*SELECT* 1"".vacation_hours)::double precision) < (COALESCE(sa.capacity, accounts_1.default_capacity))::double precision))
17. 0.329 113.731 ↑ 2.0 1 1

Nested Loop Left Join (cost=821.17..17,323.60 rows=2 width=120) (actual time=17.105..113.731 rows=1 loops=1)

  • Join Filter: (("*SELECT* 1".date >= sa.start_date) AND ("*SELECT* 1".date <= sa.end_date) AND (sa.user_id = "*SELECT* 1".user_id) AND (sa.account_id = projects.account_id))
  • Rows Removed by Join Filter: 4085
18. 0.003 93.639 ↑ 2.0 1 1

Nested Loop (cost=0.28..16,225.02 rows=2 width=96) (actual time=0.047..93.639 rows=1 loops=1)

19. 0.002 93.629 ↑ 2.0 1 1

Append (cost=0.00..16,208.40 rows=2 width=92) (actual time=0.037..93.629 rows=1 loops=1)

20. 0.000 0.001 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=0.00..0.01 rows=1 width=92) (actual time=0.001..0.001 rows=0 loops=1)

21. 0.001 0.001 ↓ 0.0 0 1

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

  • One-Time Filter: false
22. 93.626 93.626 ↑ 1.0 1 1

Seq Scan on prepared_time_logs ptl (cost=0.00..16,208.37 rows=1 width=92) (actual time=0.036..93.626 rows=1 loops=1)

  • Filter: ((is_non_working_day IS NULL) AND (reportable_id = 1245749))
  • Rows Removed by Filter: 688748
23. 0.007 0.007 ↑ 1.0 1 1

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

  • Index Cond: (id = "*SELECT* 1".project_id)
24. 1.099 19.763 ↑ 1.0 4,086 1

Materialize (cost=820.88..923.73 rows=4,114 width=40) (actual time=16.910..19.763 rows=4,086 loops=1)

25. 0.741 18.664 ↑ 1.0 4,086 1

Subquery Scan on sa (cost=820.88..903.16 rows=4,114 width=40) (actual time=16.908..18.664 rows=4,086 loops=1)

26. 0.746 17.923 ↑ 1.0 4,086 1

Unique (cost=820.88..862.02 rows=4,114 width=65) (actual time=16.906..17.923 rows=4,086 loops=1)

27. 4.329 17.177 ↑ 1.0 4,114 1

Sort (cost=820.88..831.17 rows=4,114 width=65) (actual time=16.905..17.177 rows=4,114 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
28. 3.415 12.848 ↑ 1.0 4,114 1

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

29. 4.911 9.433 ↑ 1.0 4,114 1

Sort (cost=471.06..481.35 rows=4,114 width=56) (actual time=9.060..9.433 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
30. 2.275 4.522 ↑ 1.0 4,114 1

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

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships.id)
31. 0.474 0.474 ↑ 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.011..0.474 rows=4,121 loops=1)

32. 0.945 1.773 ↑ 1.0 3,958 1

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

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

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

34. 0.025 0.025 ↑ 1.0 1 1

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

  • Index Cond: (id = projects.account_id)
35. 0.016 0.016 ↑ 1.0 1 1

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

  • Index Cond: (id = projects.client_id)
36. 0.056 0.056 ↑ 1.0 1 1

Index Scan using pawd on planned_activities_with_dates pa (cost=0.41..8.55 rows=1 width=48) (actual time=0.045..0.056 rows=1 loops=1)

  • 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: 32
37. 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)

38. 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
39. 0.001 0.005 ↓ 0.0 0 1

Bitmap Heap Scan on discounts (cost=4.30..8.15 rows=1 width=26) (actual time=0.005..0.005 rows=0 loops=1)

  • Recheck Cond: (project_id = items.project_id)
  • Filter: ((items.date >= start_date) AND (items.date <= end_date) AND (((discountable_type)::text = 'User'::text) OR ((discountable_type)::text = 'Epic'::text)) AND (((discountable_id = items.user_id) AND ((discountable_type)::text = 'User'::text)) OR ((discountable_id = items.epic_id) AND ((discountable_type)::text = 'Epic'::text))))
40. 0.004 0.004 ↓ 0.0 0 1

Bitmap Index Scan on index_discounts_on_project_id (cost=0.00..4.29 rows=3 width=0) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (project_id = items.project_id)
41. 5.420 5.420 ↑ 1.0 1 1

Index Scan using rates_with_dates_index on rates_with_dates (cost=0.42..5,698.85 rows=1 width=52) (actual time=5.419..5.420 rows=1 loops=1)

  • Index Cond: ((activity_id = items.activity_id) AND (project_id = items.project_id) AND (items.date >= start_date))
  • Filter: ((items.date <= end_date) OR (end_date IS NULL))
42. 0.068 0.068 ↑ 1.0 157 1

Index Scan using exchange_rates_by_months_idx on exchange_rates_by_months (cost=0.28..11.27 rows=157 width=24) (actual time=0.032..0.068 rows=157 loops=1)

  • Index Cond: ((currency)::text = (rates_with_dates.currency)::text)
43. 0.067 0.067 ↑ 1.0 157 1

Index Scan using exchange_rates_by_months_idx on exchange_rates_by_months original_exchange_rates (cost=0.28..11.27 rows=157 width=24) (actual time=0.030..0.067 rows=157 loops=1)

  • Index Cond: ((currency)::text = (rates_with_dates.currency)::text)
44. 0.236 0.423 ↑ 1.0 1,256 1

Hash (cost=28.56..28.56 rows=1,256 width=24) (actual time=0.423..0.423 rows=1,256 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 85kB
45. 0.187 0.187 ↑ 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.007..0.187 rows=1,256 loops=1)

Planning time : 9.208 ms
Execution time : 123.169 ms