explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xdbz

Settings
# exclusive inclusive rows x rows loops node
1. 4.037 2,938.275 ↓ 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,910.029..2,938.275 rows=22,419 loops=1)

  • Buffers: shared hit=1218119 read=23416
2. 21.851 2,934.238 ↓ 10.8 22,419 1

GroupAggregate (cost=70,555.04..70,752.36 rows=2,077 width=184) (actual time=2,910.027..2,934.238 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))
  • Buffers: shared hit=1218119 read=23416
3. 95.204 2,912.387 ↓ 10.8 22,419 1

Sort (cost=70,555.04..70,560.23 rows=2,077 width=180) (actual time=2,910.010..2,912.387 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
  • Buffers: shared hit=1218119 read=23416
4. 26.051 2,817.183 ↓ 10.8 22,419 1

Hash Left Join (cost=70,003.77..70,440.60 rows=2,077 width=180) (actual time=1,735.728..2,817.183 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
  • Buffers: shared hit=1218119 read=23416
5. 377.400 2,791.054 ↓ 10.8 22,419 1

Merge Left Join (cost=69,995.22..70,094.53 rows=2,077 width=168) (actual time=1,735.603..2,791.054 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
  • Buffers: shared hit=1218115 read=23416
6. 374.109 2,257.787 ↓ 10.8 22,419 1

Merge Left Join (cost=69,902.01..69,954.26 rows=2,077 width=160) (actual time=1,734.823..2,257.787 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
  • Buffers: shared hit=1218099 read=23416
7. 18.985 1,735.657 ↓ 10.8 22,419 1

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

  • Sort Key: projects.account_id, rates_with_dates.currency
  • Sort Method: quicksort Memory: 6656kB
  • Buffers: shared hit=1218083 read=23416
8. 15.146 1,716.672 ↓ 10.8 22,419 1

Hash Right Join (cost=63,240.12..69,694.35 rows=2,077 width=152) (actual time=1,685.791..1,716.672 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
  • Buffers: shared hit=1218083 read=23416
9. 65.082 65.082 ↓ 1.0 59 1

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

  • Filter: (project_id = 10070)
  • Rows Removed by Filter: 253734
  • Buffers: shared hit=3249
10. 12.289 1,636.444 ↓ 10.8 22,419 1

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

  • Buckets: 32768 (originally 4096) Batches: 1 (originally 1) Memory Usage: 2957kB
  • Buffers: shared hit=1214834 read=23416
11. 356.531 1,624.155 ↓ 10.8 22,419 1

Merge Left Join (cost=63,182.99..63,208.97 rows=2,077 width=116) (actual time=1,130.539..1,624.155 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
  • Buffers: shared hit=1214834 read=23416
12. 23.376 1,130.931 ↓ 10.8 22,419 1

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

  • Sort Key: projects.account_id, staff_memberships.currency
  • Sort Method: quicksort Memory: 3921kB
  • Buffers: shared hit=1214818 read=23416
13. 9.247 1,107.555 ↓ 10.8 22,419 1

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

  • Hash Cond: (projects.account_id = accounts.id)
  • Buffers: shared hit=1214818 read=23416
14. 4.022 1,098.009 ↓ 3.6 22,419 1

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

  • Buffers: shared hit=1214807 read=23416
15. 26.572 1,093.985 ↓ 3.6 22,419 1

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

  • Buffers: shared hit=1214807 read=23416
16. 18.364 484.519 ↓ 3.6 22,419 1

Merge Left Join (cost=57,799.13..58,083.86 rows=6,230 width=129) (actual time=458.271..484.519 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
  • Buffers: shared hit=4181 read=23416
17. 27.840 444.165 ↓ 1.2 22,521 1

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

  • Sort Key: projects.account_id, ""*SELECT* 1"".user_id
  • Sort Method: quicksort Memory: 3936kB
  • Buffers: shared hit=4098 read=23416
18. 4.901 416.325 ↓ 1.2 22,521 1

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

  • Buffers: shared hit=4098 read=23416
19. 0.002 0.124 ↑ 1.0 1 1

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

  • Buffers: shared hit=8
20. 0.004 0.106 ↑ 1.0 1 1

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

  • Buffers: shared hit=6
21. 0.047 0.047 ↑ 1.0 1 1

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

  • Index Cond: (id = 10070)
  • Buffers: shared hit=3
22. 0.055 0.055 ↑ 1.0 1 1

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

  • Index Cond: (id = projects.client_id)
  • Buffers: shared hit=3
23. 0.016 0.016 ↑ 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.016..0.016 rows=1 loops=1)

  • Index Cond: (id = projects.account_id)
  • Buffers: shared hit=2
24. 3.070 411.300 ↓ 1.2 22,521 1

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

  • Buffers: shared hit=4090 read=23416
25. 3.659 223.494 ↓ 1.8 11,684 1

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

  • Buffers: shared hit=1355 read=18552
26. 219.835 219.835 ↓ 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.056..219.835 rows=11,684 loops=1)

  • Filter: ((is_non_working_day IS NULL) AND (project_id = 10070))
  • Rows Removed by Filter: 1489909
  • Buffers: shared hit=1355 read=18552
27. 184.736 184.736 ↑ 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.043..184.736 rows=10,837 loops=1)

  • Filter: ((is_non_working_day IS NULL) AND (project_id = 10070))
  • Rows Removed by Filter: 677912
  • Buffers: shared hit=2735 read=4864
28. 3.303 21.990 ↓ 6.0 24,883 1

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

  • Buffers: shared hit=83
29. 0.102 18.687 ↑ 24.6 167 1

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

  • Buffers: shared hit=83
30. 4.806 18.585 ↑ 24.5 168 1

Sort (cost=820.88..831.17 rows=4,114 width=65) (actual time=18.536..18.585 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
  • Buffers: shared hit=83
31. 5.167 13.779 ↑ 1.0 4,114 1

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

  • Buffers: shared hit=83
32. 2.783 8.612 ↑ 1.0 4,114 1

Sort (cost=471.06..481.35 rows=4,114 width=56) (actual time=8.082..8.612 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
  • Buffers: shared hit=83
33. 1.823 5.829 ↑ 1.0 4,114 1

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

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships.id)
  • Buffers: shared hit=83
34. 0.743 0.743 ↑ 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.034..0.743 rows=4,121 loops=1)

  • Buffers: shared hit=36
35. 1.330 3.263 ↑ 1.0 3,958 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 273kB
  • Buffers: shared hit=47
36. 1.933 1.933 ↑ 1.0 3,958 1

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

  • Buffers: shared hit=47
37. 582.894 582.894 ↑ 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.014..0.026 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
  • Buffers: shared hit=1210626
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.006 0.299 ↑ 38.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=11
41. 0.293 0.293 ↑ 38.0 3 1

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

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 339
  • Buffers: shared hit=11
42. 136.143 136.693 ↓ 1,511.3 1,898,193 1

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

  • Sort Key: cost_exchange_rates.account_id, cost_exchange_rates.currency
  • Sort Method: quicksort Memory: 147kB
  • Buffers: shared hit=16
43. 0.550 0.550 ↑ 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.029..0.550 rows=1,256 loops=1)

  • Buffers: shared hit=16
44. 147.649 148.021 ↓ 1,589.1 1,995,915 1

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

  • Sort Key: exchange_rates_by_months.account_id, exchange_rates_by_months.currency
  • Sort Method: quicksort Memory: 147kB
  • Buffers: shared hit=16
45. 0.372 0.372 ↑ 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.017..0.372 rows=1,256 loops=1)

  • Buffers: shared hit=16
46. 155.667 155.867 ↓ 1,589.1 1,995,915 1

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

  • Sort Key: original_exchange_rates.account_id, original_exchange_rates.currency
  • Sort Method: quicksort Memory: 147kB
  • Buffers: shared hit=16
47. 0.200 0.200 ↑ 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.005..0.200 rows=1,256 loops=1)

  • Buffers: shared hit=16
48. 0.004 0.078 ↓ 1.3 8 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=4
49. 0.027 0.074 ↓ 1.3 8 1

Bitmap Heap Scan on discounts (cost=4.33..8.47 rows=6 width=26) (actual time=0.052..0.074 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
  • Buffers: shared hit=4
50. 0.047 0.047 ↑ 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.047..0.047 rows=8 loops=1)

  • Index Cond: (project_id = 10070)
  • Buffers: shared hit=2
Planning time : 10.061 ms
Execution time : 2,943.239 ms