explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Zp4Q

Settings
# exclusive inclusive rows x rows loops node
1. 653.426 2,761.052 ↓ 1.7 1,088,681 1

Merge Left Join (cost=10,000,559,653.45..10,000,599,538.36 rows=630,192 width=164) (actual time=1,588.934..2,761.052 rows=1,088,681 loops=1)

  • Merge Cond: ((items.account_id = nw_intervals_by_dates_roles.account_id) AND (items.date = nw_intervals_by_dates_roles.date))
  • Join Filter: (nw_intervals_by_dates_roles.office_id = items.office_id)
  • Rows Removed by Join Filter: 2233646
2.          

CTE items

3. 103.454 494.007 ↑ 1.6 405,261 1

Nested Loop (cost=10,000,002,101.17..10,000,484,372.54 rows=630,192 width=110) (actual time=27.102..494.007 rows=405,261 loops=1)

4. 10.871 92.583 ↓ 1.2 29,797 1

Hash Left Join (cost=2,101.16..3,845.06 rows=25,208 width=74) (actual time=27.077..92.583 rows=29,797 loops=1)

  • Hash Cond: (projects.client_id = clients.id)
5. 10.919 80.965 ↓ 1.2 29,797 1

Hash Left Join (cost=2,037.56..3,715.13 rows=25,208 width=70) (actual time=26.323..80.965 rows=29,797 loops=1)

  • Hash Cond: (project_plans.project_id = projects.id)
6. 14.128 67.420 ↓ 1.2 29,797 1

Hash Join (cost=1,808.79..3,420.14 rows=25,208 width=62) (actual time=23.654..67.420 rows=29,797 loops=1)

  • Hash Cond: (plan_items.plan_row_uuid = plan_rows.uuid)
7. 15.953 31.391 ↓ 1.1 29,797 1

Hash Join (cost=202.03..1,744.37 rows=26,284 width=50) (actual time=1.711..31.391 rows=29,797 loops=1)

  • Hash Cond: (plan_items.project_plan_id = project_plans.id)
8. 13.759 13.759 ↑ 1.0 55,664 1

Seq Scan on plan_items (cost=0.00..1,396.10 rows=55,675 width=46) (actual time=0.007..13.759 rows=55,664 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
9. 0.472 1.679 ↑ 1.0 2,851 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 144kB
10. 1.207 1.207 ↑ 1.0 2,851 1

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

  • Filter: active
  • Rows Removed by Filter: 3188
11. 12.258 21.901 ↓ 1.0 48,102 1

Hash (cost=1,030.06..1,030.06 rows=46,136 width=28) (actual time=21.901..21.901 rows=48,102 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3117kB
12. 9.643 9.643 ↓ 1.0 48,102 1

Seq Scan on plan_rows (cost=0.00..1,030.06 rows=46,136 width=28) (actual time=0.011..9.643 rows=48,102 loops=1)

  • Filter: ((user_id IS NOT NULL) OR (activity_id IS NOT NULL))
  • Rows Removed by Filter: 4
13. 1.219 2.626 ↑ 1.0 5,812 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 314kB
14. 1.407 1.407 ↑ 1.0 5,812 1

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

15. 0.348 0.747 ↑ 1.0 1,849 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 89kB
16. 0.399 0.399 ↑ 1.0 1,849 1

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

17. 297.970 297.970 ↑ 1.8 14 29,797

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

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 5
18. 102.370 1,887.531 ↑ 1.6 405,337 1

Merge Left Join (cost=75,280.49..90,465.31 rows=630,192 width=164) (actual time=1,588.921..1,887.531 rows=405,337 loops=1)

  • Merge Cond: ((items.account_id = non_working_intervals_by_dates.account_id) AND (items.date = non_working_intervals_by_dates.date) AND (items.user_id = non_working_intervals_by_dates.user_id))
19. 135.154 1,766.297 ↑ 1.6 405,261 1

Merge Left Join (cost=75,280.08..81,816.05 rows=630,192 width=160) (actual time=1,588.909..1,766.297 rows=405,261 loops=1)

  • Merge Cond: ((items.account_id = summary_vacations_by_dates.account_id) AND (items.date = summary_vacations_by_dates.date) AND (items.user_id = summary_vacations_by_dates.user_id))
20. 795.204 1,615.335 ↑ 1.6 405,261 1

Sort (cost=73,308.45..74,883.93 rows=630,192 width=124) (actual time=1,576.076..1,615.335 rows=405,261 loops=1)

  • Sort Key: items.account_id, items.date, items.user_id
  • Sort Method: quicksort Memory: 69278kB
21. 820.131 820.131 ↑ 1.6 405,261 1

CTE Scan on items (cost=0.00..12,603.84 rows=630,192 width=124) (actual time=27.105..820.131 rows=405,261 loops=1)

22. 11.323 15.808 ↓ 1.7 37,320 1

Sort (cost=1,971.63..2,025.87 rows=21,696 width=44) (actual time=12.827..15.808 rows=37,320 loops=1)

  • Sort Key: summary_vacations_by_dates.account_id, summary_vacations_by_dates.date, summary_vacations_by_dates.user_id
  • Sort Method: quicksort Memory: 2844kB
23. 4.485 4.485 ↓ 1.5 32,266 1

Seq Scan on summary_vacations_by_dates_m summary_vacations_by_dates (cost=0.00..408.96 rows=21,696 width=44) (actual time=0.016..4.485 rows=32,266 loops=1)

24. 18.864 18.864 ↓ 1.0 62,049 1

Index Scan using account_id_user_id on non_working_intervals_by_dates (cost=0.41..3,467.85 rows=59,712 width=16) (actual time=0.009..18.864 rows=62,049 loops=1)

25. 208.884 220.095 ↓ 49.3 2,946,721 1

Materialize (cost=0.41..3,617.13 rows=59,712 width=16) (actual time=0.010..220.095 rows=2,946,721 loops=1)

26. 11.211 11.211 ↑ 1.0 59,679 1

Index Scan using account_id_office_id on non_working_intervals_by_dates nw_intervals_by_dates_roles (cost=0.41..3,467.85 rows=59,712 width=16) (actual time=0.005..11.211 rows=59,679 loops=1)

Planning time : 1.108 ms
Execution time : 2,857.242 ms