explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Mhil

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

Merge Right Join (cost=2,545,701.32..2,597,720.51 rows=2,146,205 width=138) (actual time=4,325.509..5,209.909 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=17212 written=17226
2. 21.382 31.062 ↑ 1.0 58,766 1

Sort (cost=5,918.90..6,065.90 rows=58,799 width=16) (actual time=26.908..31.062 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.680 9.680 ↑ 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.680 rows=58,799 loops=1)

  • Buffers: shared hit=673
4. 161.280 4,681.167 ↓ 1.2 1,501,593 1

Materialize (cost=2,539,782.42..2,546,066.29 rows=1,256,775 width=110) (actual time=4,298.593..4,681.167 rows=1,501,593 loops=1)

  • Buffers: shared hit=2402, temp read=17212 written=17226
5. 1,038.039 4,519.887 ↑ 1.4 870,252 1

Sort (cost=2,539,782.42..2,542,924.36 rows=1,256,775 width=110) (actual time=4,298.588..4,519.887 rows=870,252 loops=1)

  • Sort Key: projects.account_id, generate_series.generate_series, clients.office_id
  • Sort Method: external merge Disk: 69232kB
  • Buffers: shared hit=2402, temp read=17212 written=17226
6. 181.394 3,481.848 ↑ 1.4 870,252 1

Merge Left Join (cost=2,309,251.68..2,339,435.47 rows=1,256,775 width=110) (actual time=2,652.296..3,481.848 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=8558 written=8565
7. 228.338 3,258.421 ↑ 1.4 870,234 1

Merge Left Join (cost=2,303,332.78..2,323,054.89 rows=1,256,775 width=106) (actual time=2,614.689..3,258.421 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=8558 written=8565
8. 2,188.806 2,917.534 ↑ 1.4 870,234 1

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

  • Sort Key: projects.account_id, generate_series.generate_series, plan_rows.user_id
  • Sort Method: external merge Disk: 68464kB
  • Buffers: shared hit=1410, temp read=8558 written=8565
9. 157.835 728.728 ↑ 1.4 870,234 1

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

  • Buffers: shared hit=1410
10. 13.017 99.122 ↓ 1.0 52,419 1

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

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

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

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

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

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

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

  • Hash Cond: (plan_items.plan_row_uuid = plan_rows.uuid)
  • Buffers: shared hit=1188
14. 11.309 11.309 ↓ 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.309 rows=52,422 loops=1)

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

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3035kB
  • Buffers: shared hit=532
16. 8.752 8.752 ↓ 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.752 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.472 1.552 ↓ 1.0 2,819 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 143kB
  • Buffers: shared hit=104
18. 1.080 1.080 ↓ 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.080 rows=2,819 loops=1)

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

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

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

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

  • Buffers: shared hit=97
21. 0.280 0.600 ↑ 1.0 1,822 1

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

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

Seq Scan on clients (cost=0.00..39.22 rows=1,822 width=8) (actual time=0.006..0.320 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. 4.615 112.549 ↑ 16.5 34,338 1

Materialize (cost=1,164,863.14..1,167,692.14 rows=565,800 width=44) (actual time=105.525..112.549 rows=34,338 loops=1)

  • Buffers: shared hit=319
25. 30.273 107.934 ↑ 19.1 29,556 1

Sort (cost=1,164,863.14..1,166,277.64 rows=565,800 width=44) (actual time=105.521..107.934 rows=29,556 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
26. 3.939 77.661 ↑ 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=56.271..77.661 rows=29,556 loops=1)

  • Buffers: shared hit=319
27. 15.044 73.722 ↑ 19.1 29,556 1

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

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
  • Buffers: shared hit=319
28. 14.506 58.678 ↑ 142.6 29,683 1

Sort (cost=1,026,341.47..1,036,923.30 rows=4,232,734 width=21) (actual time=56.259..58.678 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
29. 10.014 44.172 ↑ 142.6 29,683 1

Hash Join (cost=136.06..473,655.25 rows=4,232,734 width=21) (actual time=1.540..44.172 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
30. 9.692 32.672 ↑ 661.8 31,591 1

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

  • Buffers: shared hit=272
31. 2.073 2.073 ↑ 1.0 20,907 1

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

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

33. 0.790 1.486 ↑ 1.0 3,958 1

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

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

  • Buffers: shared hit=47
35. 32.752 42.033 ↓ 1.0 60,807 1

Sort (cost=5,918.90..6,065.90 rows=58,799 width=16) (actual time=37.601..42.033 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
36. 9.281 9.281 ↑ 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.281 rows=58,799 loops=1)

  • Buffers: shared hit=673
Planning time : 1.544 ms
Execution time : 5,318.042 ms