explain.depesz.com

PostgreSQL's explain analyze made readable

Result: W25s

Settings
# exclusive inclusive rows x rows loops node
1. 275.757 3,266.521 ↑ 1.0 688,749 1

Merge Left Join (cost=278,770.79..287,686.93 rows=688,749 width=148) (actual time=2,799.343..3,266.521 rows=688,749 loops=1)

  • Merge Cond: ((projects.account_id = staff_memberships.account_id) AND (time_logs.user_id = staff_memberships.user_id))
  • Join Filter: ((time_logs.date >= (COALESCE(staff_membership_activity_links.start_date, staff_memberships.joined_at))) AND (time_logs.date <= (COALESCE(lag((staff_membership_activity_links.start_date - 1), 1) OVER (?), staff_memberships.archived_at, '3000-01-01'::date))))
  • Rows Removed by Join Filter: 130736
2. 530.012 2,931.549 ↑ 1.0 688,749 1

Sort (cost=277,960.19..279,682.06 rows=688,749 width=44) (actual time=2,790.446..2,931.549 rows=688,749 loops=1)

  • Sort Key: projects.account_id, time_logs.user_id
  • Sort Method: external merge Disk: 41008kB
3. 215.942 2,401.537 ↑ 1.0 688,749 1

Gather (cost=56,808.81..189,984.51 rows=688,749 width=44) (actual time=270.181..2,401.537 rows=688,749 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 51.495 2,185.595 ↑ 1.3 229,583 3 / 3

Hash Join (cost=55,808.81..120,109.61 rows=286,979 width=44) (actual time=264.774..2,185.595 rows=229,583 loops=3)

  • Hash Cond: (epics.project_id = projects.id)
5. 1,288.668 2,131.402 ↑ 1.3 229,583 3 / 3

Merge Left Join (cost=55,583.88..119,130.80 rows=286,979 width=40) (actual time=261.950..2,131.402 rows=229,583 loops=3)

  • Merge Cond: ((COALESCE(epics.project_plan_id, general_epic_plans.id)) = pa.project_plan_id)
  • Join Filter: ((time_logs.date >= pa.start_date) AND (time_logs.date <= pa.end_date) AND ((pa.epic_id = time_logs.epic_id) OR (pa.epic_id IS NULL)) AND ((pa.user_id = time_logs.user_id) OR (time_logs.user_id IS NULL)))
  • Rows Removed by Join Filter: 8687848
6. 157.697 287.629 ↑ 1.3 229,583 3 / 3

Sort (cost=51,442.70..52,160.15 rows=286,979 width=40) (actual time=244.092..287.629 rows=229,583 loops=3)

  • Sort Key: (COALESCE(epics.project_plan_id, general_epic_plans.id))
  • Sort Method: external merge Disk: 14560kB
  • Worker 0: Sort Method: external merge Disk: 13176kB
  • Worker 1: Sort Method: external merge Disk: 13120kB
7. 78.572 129.932 ↑ 1.3 229,583 3 / 3

Hash Join (cost=1,805.96..17,576.71 rows=286,979 width=40) (actual time=20.711..129.932 rows=229,583 loops=3)

  • Hash Cond: (time_logs.epic_id = epics.id)
8. 30.770 30.770 ↑ 1.3 229,583 3 / 3

Parallel Seq Scan on time_logs (cost=0.00..11,824.79 rows=286,979 width=28) (actual time=0.010..30.770 rows=229,583 loops=3)

9. 6.272 20.590 ↑ 1.0 26,131 3 / 3

Hash (cost=1,479.32..1,479.32 rows=26,131 width=16) (actual time=20.590..20.590 rows=26,131 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 1368kB
10. 8.242 14.318 ↑ 1.0 26,131 3 / 3

Hash Left Join (cost=198.32..1,479.32 rows=26,131 width=16) (actual time=2.092..14.318 rows=26,131 loops=3)

  • Hash Cond: (epics.project_id = general_epic_plans.project_id)
  • Join Filter: (epics.project_plan_id IS NULL)
  • Rows Removed by Join Filter: 11264
11. 4.044 4.044 ↑ 1.0 26,131 3 / 3

Seq Scan on epics (cost=0.00..612.31 rows=26,131 width=12) (actual time=0.030..4.044 rows=26,131 loops=3)

12. 0.495 2.032 ↓ 1.0 2,819 3 / 3

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

  • Buckets: 4096 Batches: 1 Memory Usage: 143kB
13. 1.537 1.537 ↓ 1.0 2,819 3 / 3

Seq Scan on project_plans general_epic_plans (cost=0.00..163.10 rows=2,818 width=8) (actual time=0.023..1.537 rows=2,819 loops=3)

  • Filter: active
  • Rows Removed by Filter: 3091
14. 547.743 555.105 ↓ 206.3 8,892,151 3 / 3

Sort (cost=4,141.18..4,248.94 rows=43,105 width=20) (actual time=17.845..555.105 rows=8,892,151 loops=3)

  • Sort Key: pa.project_plan_id
  • Sort Method: quicksort Memory: 3611kB
  • Worker 0: Sort Method: quicksort Memory: 3611kB
  • Worker 1: Sort Method: quicksort Memory: 3611kB
15. 7.362 7.362 ↑ 1.0 43,105 3 / 3

Seq Scan on planned_activities_with_dates pa (cost=0.00..823.05 rows=43,105 width=20) (actual time=0.052..7.362 rows=43,105 loops=3)

16. 1.055 2.698 ↑ 1.0 5,686 3 / 3

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

  • Buckets: 8192 Batches: 1 Memory Usage: 309kB
17. 1.643 1.643 ↑ 1.0 5,686 3 / 3

Seq Scan on projects (cost=0.00..153.86 rows=5,686 width=12) (actual time=0.044..1.643 rows=5,686 loops=3)

18. 49.057 59.215 ↓ 192.4 791,611 1

Materialize (cost=810.60..903.16 rows=4,114 width=16) (actual time=8.891..59.215 rows=791,611 loops=1)

19. 0.924 10.158 ↑ 1.0 4,053 1

Unique (cost=810.60..851.74 rows=4,114 width=613) (actual time=8.888..10.158 rows=4,053 loops=1)

20. 2.404 9.234 ↑ 1.0 4,075 1

Sort (cost=810.60..820.88 rows=4,114 width=613) (actual time=8.886..9.234 rows=4,075 loops=1)

  • Sort Key: staff_memberships.account_id, staff_memberships.user_id, (COALESCE(staff_membership_activity_links.start_date, staff_memberships.joined_at)), (COALESCE(lag((staff_membership_activity_links.start_date - 1), 1) OVER (?), staff_memberships.archived_at, '3000-01-01'::date)) DESC
  • Sort Method: quicksort Memory: 514kB
21. 2.471 6.830 ↑ 1.0 4,114 1

WindowAgg (cost=471.06..563.63 rows=4,114 width=613) (actual time=4.088..6.830 rows=4,114 loops=1)

22. 1.510 4.359 ↑ 1.0 4,114 1

Sort (cost=471.06..481.35 rows=4,114 width=24) (actual time=4.080..4.359 rows=4,114 loops=1)

  • Sort Key: staff_membership_activity_links.staff_membership_id, staff_membership_activity_links.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 463kB
23. 1.050 2.849 ↑ 1.0 4,114 1

Hash Join (cost=136.06..224.09 rows=4,114 width=24) (actual time=1.460..2.849 rows=4,114 loops=1)

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships.id)
24. 0.358 0.358 ↑ 1.0 4,121 1

Seq Scan on staff_membership_activity_links (cost=0.00..77.21 rows=4,121 width=8) (actual time=0.009..0.358 rows=4,121 loops=1)

25. 0.743 1.441 ↑ 1.0 3,958 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 227kB
26. 0.698 0.698 ↑ 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.698 rows=3,958 loops=1)

Planning time : 1.343 ms
Execution time : 3,322.059 ms