explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4gUH

Settings
# exclusive inclusive rows x rows loops node
1. 577.843 2,638.864 ↑ 1.3 495,599 1

Merge Left Join (cost=10,000,584,482.71..10,000,625,566.78 rows=657,101 width=137) (actual time=1,545.105..2,638.864 rows=495,599 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: ((items.user_id IS NULL) AND (nw_intervals_by_dates_roles.office_id = items.office_id))
  • Rows Removed by Join Filter: 2833206
2.          

CTE items

3. 104.934 462.938 ↑ 1.6 405,261 1

Nested Loop (cost=10,000,002,125.79..10,000,504,914.10 rows=657,101 width=110) (actual time=25.762..462.938 rows=405,261 loops=1)

4. 10.336 89.831 ↓ 1.1 29,797 1

Hash Join (cost=2,125.78..3,875.34 rows=26,284 width=74) (actual time=25.739..89.831 rows=29,797 loops=1)

  • Hash Cond: (projects.client_id = clients.id)
5. 10.743 78.819 ↓ 1.1 29,797 1

Hash Join (cost=2,062.18..3,742.59 rows=26,284 width=70) (actual time=25.040..78.819 rows=29,797 loops=1)

  • Hash Cond: (project_plans.project_id = projects.id)
6. 13.930 65.801 ↓ 1.1 29,797 1

Hash Join (cost=1,833.41..3,444.77 rows=26,284 width=62) (actual time=22.721..65.801 rows=29,797 loops=1)

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

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

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

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

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 144kB
10. 1.143 1.143 ↑ 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.143 rows=2,851 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3188
11. 12.395 21.055 ↑ 1.0 48,106 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3117kB
12. 8.660 8.660 ↑ 1.0 48,106 1

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

13. 0.973 2.275 ↑ 1.0 5,812 1

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

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

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

15. 0.307 0.676 ↑ 1.0 1,849 1

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

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

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

17. 268.173 268.173 ↑ 1.8 14 29,797

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.004..0.009 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.805 1,843.558 ↑ 1.6 405,337 1

Merge Left Join (cost=79,568.32..95,313.64 rows=657,101 width=137) (actual time=1,545.096..1,843.558 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))
  • Join Filter: (items.user_id IS NOT NULL)
19. 135.913 1,723.606 ↑ 1.6 405,261 1

Merge Left Join (cost=79,567.90..86,462.25 rows=657,101 width=133) (actual time=1,545.081..1,723.606 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))
  • Join Filter: (items.user_id IS NOT NULL)
20. 796.466 1,570.211 ↑ 1.6 405,261 1

Sort (cost=76,636.89..78,279.64 rows=657,101 width=124) (actual time=1,530.701..1,570.211 rows=405,261 loops=1)

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

CTE Scan on items (cost=0.00..13,142.02 rows=657,101 width=124) (actual time=25.765..773.745 rows=405,261 loops=1)

22. 11.887 17.482 ↓ 1.2 37,320 1

Sort (cost=2,931.02..3,011.68 rows=32,266 width=17) (actual time=14.372..17.482 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. 5.595 5.595 ↑ 1.0 32,266 1

Seq Scan on summary_vacations_by_dates_m summary_vacations_by_dates (cost=0.00..514.66 rows=32,266 width=17) (actual time=0.033..5.595 rows=32,266 loops=1)

24. 17.147 17.147 ↓ 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.012..17.147 rows=62,049 loops=1)

25. 206.755 217.463 ↓ 49.3 2,946,721 1

Materialize (cost=0.29..3,349.01 rows=59,712 width=16) (actual time=0.006..217.463 rows=2,946,721 loops=1)

26. 10.708 10.708 ↑ 1.0 59,679 1

Index Scan using nw_account_id_date on non_working_intervals_by_dates nw_intervals_by_dates_roles (cost=0.29..3,199.73 rows=59,712 width=16) (actual time=0.005..10.708 rows=59,679 loops=1)

Planning time : 2.736 ms
Execution time : 2,692.095 ms