explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bHI6

Settings
# exclusive inclusive rows x rows loops node
1. 484.783 4,714.771 ↑ 1.4 1,501,593 1

Merge Right Join (cost=1,839,037.76..1,887,915.02 rows=2,146,205 width=138) (actual time=4,124.156..4,714.771 rows=1,501,593 loops=1)

  • Merge Cond: ((nw_intervals_by_dates_roles.account_id = projects.account_id) AND (nw_intervals_by_dates_roles.date = generate_series.generate_series) AND (nw_intervals_by_dates_roles.office_id = clients.office_id))
  • Buffers: shared hit=3075
2. 19.860 29.497 ↑ 1.0 58,766 1

Sort (cost=5,918.90..6,065.90 rows=58,799 width=16) (actual time=25.335..29.497 rows=58,766 loops=1)

  • Sort Key: nw_intervals_by_dates_roles.account_id, nw_intervals_by_dates_roles.date, nw_intervals_by_dates_roles.office_id
  • Sort Method: quicksort Memory: 4293kB
  • Buffers: shared hit=673
3. 9.637 9.637 ↑ 1.0 58,799 1

Seq Scan on non_working_intervals_by_dates nw_intervals_by_dates_roles (cost=0.00..1,260.99 rows=58,799 width=16) (actual time=0.016..9.637 rows=58,799 loops=1)

  • Buffers: shared hit=673
4. 912.998 4,200.491 ↓ 1.2 1,501,593 1

Sort (cost=1,833,118.86..1,836,260.80 rows=1,256,775 width=110) (actual time=4,098.812..4,200.491 rows=1,501,593 loops=1)

  • Sort Key: projects.account_id, generate_series.generate_series, clients.office_id
  • Sort Method: quicksort Memory: 146956kB
  • Buffers: shared hit=2402
5. 181.009 3,287.493 ↑ 1.4 870,252 1

Merge Left Join (cost=1,677,030.13..1,705,799.42 rows=1,256,775 width=110) (actual time=2,781.469..3,287.493 rows=870,252 loops=1)

  • Merge Cond: ((projects.account_id = non_working_intervals_by_dates.account_id) AND (generate_series.generate_series = non_working_intervals_by_dates.date) AND (plan_rows.user_id = non_working_intervals_by_dates.user_id))
  • Buffers: shared hit=2402
6. 238.981 3,063.795 ↑ 1.4 870,234 1

Merge Left Join (cost=1,671,111.23..1,689,418.84 rows=1,256,775 width=106) (actual time=2,743.197..3,063.795 rows=870,234 loops=1)

  • Merge Cond: ((projects.account_id = summary_vacations_by_dates.account_id) AND (generate_series.generate_series = summary_vacations_by_dates.date) AND (plan_rows.user_id = summary_vacations_by_dates.user_id))
  • Buffers: shared hit=1729
7. 1,978.271 2,712.244 ↑ 1.4 870,234 1

Sort (cost=1,086,921.64..1,090,063.58 rows=1,256,775 width=70) (actual time=2,635.139..2,712.244 rows=870,234 loops=1)

  • Sort Key: projects.account_id, generate_series.generate_series, plan_rows.user_id
  • Sort Method: quicksort Memory: 146953kB
  • Buffers: shared hit=1410
8. 163.808 733.973 ↑ 1.4 870,234 1

Nested Loop (cost=2,043.15..959,602.20 rows=1,256,775 width=70) (actual time=23.275..733.973 rows=870,234 loops=1)

  • Buffers: shared hit=1410
9. 13.149 98.394 ↓ 1.0 52,419 1

Hash Left Join (cost=2,043.14..4,453.19 rows=50,271 width=70) (actual time=23.252..98.394 rows=52,419 loops=1)

  • Hash Cond: (projects.client_id = clients.id)
  • Buffers: shared hit=1410
10. 13.624 84.652 ↓ 1.0 52,419 1

Hash Left Join (cost=1,981.15..4,258.92 rows=50,271 width=70) (actual time=22.653..84.652 rows=52,419 loops=1)

  • Hash Cond: (project_plans.project_id = projects.id)
  • Buffers: shared hit=1389
11. 13.198 68.842 ↓ 1.0 52,419 1

Hash Left Join (cost=1,756.21..3,901.92 rows=50,271 width=62) (actual time=20.458..68.842 rows=52,419 loops=1)

  • Hash Cond: (plan_items.project_plan_id = project_plans.id)
  • Buffers: shared hit=1292
12. 24.266 54.193 ↓ 1.0 52,419 1

Hash Join (cost=1,557.89..3,571.54 rows=50,271 width=58) (actual time=18.982..54.193 rows=52,419 loops=1)

  • Hash Cond: (plan_items.plan_row_uuid = plan_rows.uuid)
  • Buffers: shared hit=1188
13. 11.211 11.211 ↓ 1.0 52,422 1

Seq Scan on plan_items (cost=0.00..1,314.46 rows=52,394 width=46) (actual time=0.016..11.211 rows=52,422 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 255
  • Buffers: shared hit=656
14. 10.146 18.716 ↓ 1.0 46,641 1

Hash (cost=998.45..998.45 rows=44,755 width=28) (actual time=18.715..18.716 rows=46,641 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3035kB
  • Buffers: shared hit=532
15. 8.570 8.570 ↓ 1.0 46,641 1

Seq Scan on plan_rows (cost=0.00..998.45 rows=44,755 width=28) (actual time=0.007..8.570 rows=46,641 loops=1)

  • Filter: ((user_id IS NOT NULL) OR (activity_id IS NOT NULL))
  • Rows Removed by Filter: 4
  • Buffers: shared hit=532
16. 0.435 1.451 ↓ 1.0 2,819 1

Hash (cost=163.10..163.10 rows=2,818 width=8) (actual time=1.451..1.451 rows=2,819 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 143kB
  • Buffers: shared hit=104
17. 1.016 1.016 ↓ 1.0 2,819 1

Seq Scan on project_plans (cost=0.00..163.10 rows=2,818 width=8) (actual time=0.009..1.016 rows=2,819 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3091
  • Buffers: shared hit=104
18. 1.017 2.186 ↑ 1.0 5,686 1

Hash (cost=153.86..153.86 rows=5,686 width=12) (actual time=2.186..2.186 rows=5,686 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 309kB
  • Buffers: shared hit=97
19. 1.169 1.169 ↑ 1.0 5,686 1

Seq Scan on projects (cost=0.00..153.86 rows=5,686 width=12) (actual time=0.005..1.169 rows=5,686 loops=1)

  • Buffers: shared hit=97
20. 0.282 0.593 ↑ 1.0 1,822 1

Hash (cost=39.22..39.22 rows=1,822 width=8) (actual time=0.593..0.593 rows=1,822 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
  • Buffers: shared hit=21
21. 0.311 0.311 ↑ 1.0 1,822 1

Seq Scan on clients (cost=0.00..39.22 rows=1,822 width=8) (actual time=0.007..0.311 rows=1,822 loops=1)

  • Buffers: shared hit=21
22. 471.771 471.771 ↑ 1.5 17 52,419

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.004..0.009 rows=17 loops=52,419)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 6
23. 33.408 112.570 ↑ 16.5 34,338 1

Sort (cost=584,189.58..585,604.08 rows=565,800 width=44) (actual time=108.050..112.570 rows=34,338 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: 2705kB
  • Buffers: shared hit=319
24. 4.072 79.162 ↑ 19.1 29,556 1

Subquery Scan on summary_vacations_by_dates (cost=515,982.59..530,127.59 rows=565,800 width=44) (actual time=59.749..79.162 rows=29,556 loops=1)

  • Buffers: shared hit=319
25. 29.222 75.090 ↑ 19.1 29,556 1

HashAggregate (cost=515,982.59..524,469.59 rows=565,800 width=52) (actual time=59.748..75.090 rows=29,556 loops=1)

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
  • Buffers: shared hit=319
26. 10.676 45.868 ↑ 142.6 29,683 1

Hash Join (cost=136.06..473,655.25 rows=4,232,734 width=21) (actual time=1.544..45.868 rows=29,683 loops=1)

  • Hash Cond: (vacations.staff_membership_id = staff_memberships.id)
  • Join Filter: ((generate_series_1.generate_series >= staff_memberships.joined_at) AND ((staff_memberships.archived_at IS NULL) OR (generate_series_1.generate_series <= staff_memberships.archived_at)))
  • Rows Removed by Join Filter: 1908
  • Buffers: shared hit=319
27. 10.497 33.683 ↑ 661.8 31,591 1

Nested Loop (cost=0.01..418,621.08 rows=20,907,000 width=17) (actual time=0.023..33.683 rows=31,591 loops=1)

  • Buffers: shared hit=272
28. 2.279 2.279 ↑ 1.0 20,907 1

Seq Scan on vacations (cost=0.00..481.07 rows=20,907 width=17) (actual time=0.011..2.279 rows=20,907 loops=1)

  • Buffers: shared hit=272
29. 20.907 20.907 ↑ 500.0 2 20,907

Function Scan on generate_series generate_series_1 (cost=0.01..10.01 rows=1,000 width=8) (actual time=0.001..0.001 rows=2 loops=20,907)

30. 0.788 1.509 ↑ 1.0 3,958 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 227kB
  • Buffers: shared hit=47
31. 0.721 0.721 ↑ 1.0 3,958 1

Seq Scan on staff_memberships (cost=0.00..86.58 rows=3,958 width=20) (actual time=0.006..0.721 rows=3,958 loops=1)

  • Buffers: shared hit=47
32. 33.335 42.689 ↓ 1.0 60,807 1

Sort (cost=5,918.90..6,065.90 rows=58,799 width=16) (actual time=38.266..42.689 rows=60,807 loops=1)

  • Sort Key: non_working_intervals_by_dates.account_id, non_working_intervals_by_dates.date, non_working_intervals_by_dates.user_id
  • Sort Method: quicksort Memory: 4293kB
  • Buffers: shared hit=673
33. 9.354 9.354 ↑ 1.0 58,799 1

Seq Scan on non_working_intervals_by_dates (cost=0.00..1,260.99 rows=58,799 width=16) (actual time=0.013..9.354 rows=58,799 loops=1)

  • Buffers: shared hit=673
Planning time : 1.497 ms
Execution time : 4,863.847 ms