explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aFaf

Settings
# exclusive inclusive rows x rows loops node
1. 491.070 5,222.475 ↑ 1.4 1,501,593 1

Merge Right Join (cost=2,526,881.32..2,578,900.51 rows=2,146,205 width=138) (actual time=4,428.559..5,222.475 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, temp read=17206 written=17208
2. 19.927 29.822 ↑ 1.0 58,766 1

Sort (cost=5,918.90..6,065.90 rows=58,799 width=16) (actual time=25.704..29.822 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.895 9.895 ↑ 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.008..9.895 rows=58,799 loops=1)

  • Buffers: shared hit=673
4. 159.527 4,701.583 ↓ 1.2 1,501,593 1

Materialize (cost=2,520,962.42..2,527,246.29 rows=1,256,775 width=110) (actual time=4,402.846..4,701.583 rows=1,501,593 loops=1)

  • Buffers: shared hit=2402, temp read=17206 written=17208
5. 1,064.377 4,542.056 ↑ 1.4 870,252 1

Sort (cost=2,520,962.42..2,524,104.36 rows=1,256,775 width=110) (actual time=4,402.841..4,542.056 rows=870,252 loops=1)

  • Sort Key: projects.account_id, generate_series.generate_series, clients.office_id
  • Sort Method: external merge Disk: 69216kB
  • Buffers: shared hit=2402, temp read=17206 written=17208
6. 178.708 3,477.679 ↑ 1.4 870,252 1

Merge Left Join (cost=2,291,846.18..2,320,615.47 rows=1,256,775 width=110) (actual time=2,858.366..3,477.679 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, temp read=8554 written=8555
7. 225.843 3,258.151 ↑ 1.4 870,234 1

Merge Left Join (cost=2,285,927.28..2,304,234.89 rows=1,256,775 width=106) (actual time=2,821.784..3,258.151 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, temp read=8554 written=8555
8. 2,182.170 2,921.861 ↑ 1.4 870,234 1

Sort (cost=1,138,469.64..1,141,611.58 rows=1,256,775 width=70) (actual time=2,713.817..2,921.861 rows=870,234 loops=1)

  • Sort Key: projects.account_id, generate_series.generate_series, plan_rows.user_id
  • Sort Method: external merge Disk: 68432kB
  • Buffers: shared hit=1410, temp read=8554 written=8555
9. 168.028 739.691 ↑ 1.4 870,234 1

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

  • Buffers: shared hit=1410
10. 13.409 99.892 ↓ 1.0 52,419 1

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

  • Hash Cond: (projects.client_id = clients.id)
  • Buffers: shared hit=1410
11. 13.640 85.910 ↓ 1.0 52,419 1

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

  • Hash Cond: (project_plans.project_id = projects.id)
  • Buffers: shared hit=1389
12. 13.489 70.064 ↓ 1.0 52,419 1

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

  • Hash Cond: (plan_items.project_plan_id = project_plans.id)
  • Buffers: shared hit=1292
13. 25.135 55.167 ↓ 1.0 52,419 1

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

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

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

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

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

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

Seq Scan on plan_rows (cost=0.00..998.45 rows=44,755 width=28) (actual time=0.006..8.401 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
17. 0.423 1.408 ↓ 1.0 2,819 1

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

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

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

  • Filter: active
  • Rows Removed by Filter: 3091
  • Buffers: shared hit=104
19. 1.040 2.206 ↑ 1.0 5,686 1

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

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

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

  • Buffers: shared hit=97
21. 0.274 0.573 ↑ 1.0 1,822 1

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

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

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

  • Buffers: shared hit=21
23. 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
24. 31.610 110.447 ↑ 16.5 34,338 1

Sort (cost=1,147,457.64..1,148,872.14 rows=565,800 width=44) (actual time=107.960..110.447 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
25. 3.937 78.837 ↑ 19.1 29,556 1

Subquery Scan on summary_vacations_by_dates (cost=1,026,341.47..1,093,395.64 rows=565,800 width=44) (actual time=57.289..78.837 rows=29,556 loops=1)

  • Buffers: shared hit=319
26. 15.130 74.900 ↑ 19.1 29,556 1

GroupAggregate (cost=1,026,341.47..1,087,737.64 rows=565,800 width=52) (actual time=57.287..74.900 rows=29,556 loops=1)

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
  • Buffers: shared hit=319
27. 14.787 59.770 ↑ 142.6 29,683 1

Sort (cost=1,026,341.47..1,036,923.30 rows=4,232,734 width=21) (actual time=57.277..59.770 rows=29,683 loops=1)

  • Sort Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
  • Sort Method: quicksort Memory: 2714kB
  • Buffers: shared hit=319
28. 10.240 44.983 ↑ 142.6 29,683 1

Hash Join (cost=136.06..473,655.25 rows=4,232,734 width=21) (actual time=1.668..44.983 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
29. 10.023 33.112 ↑ 661.8 31,591 1

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

  • Buffers: shared hit=272
30. 2.182 2.182 ↑ 1.0 20,907 1

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

  • Buffers: shared hit=272
31. 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)

32. 0.855 1.631 ↑ 1.0 3,958 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 227kB
  • Buffers: shared hit=47
33. 0.776 0.776 ↑ 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.776 rows=3,958 loops=1)

  • Buffers: shared hit=47
34. 31.965 40.820 ↓ 1.0 60,807 1

Sort (cost=5,918.90..6,065.90 rows=58,799 width=16) (actual time=36.576..40.820 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
35. 8.855 8.855 ↑ 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.018..8.855 rows=58,799 loops=1)

  • Buffers: shared hit=673
Planning time : 1.789 ms
Execution time : 5,333.236 ms