explain.depesz.com

PostgreSQL's explain analyze made readable

Result: X9UJ

Settings
# exclusive inclusive rows x rows loops node
1. 107.173 107,954.871 ↑ 554.6 307,091 1

Merge Join (cost=6,869,569.27..6,876,678.31 rows=170,326,623 width=18) (actual time=107,749.008..107,954.871 rows=307,091 loops=1)

  • Merge Cond: (ra.offerid = co.offerid)
  • Join Filter: (aa.nr < ra.nr)
  • Rows Removed by Join Filter: 38698
2.          

CTE required_assistants

3. 290.522 106,286.575 ↓ 1.0 400,000 1

Finalize GroupAggregate (cost=6,294,768.23..6,648,000.13 rows=388,287 width=12) (actual time=83,034.597..106,286.575 rows=400,000 loops=1)

  • Group Key: cr.offerid
4. 0.000 105,996.053 ↓ 1.5 1,200,000 1

Gather Merge (cost=6,294,768.23..6,638,292.96 rows=776,574 width=12) (actual time=83,034.545..105,996.053 rows=1,200,000 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 10,522.479 280,848.900 ↓ 1.0 400,000 3

Partial GroupAggregate (cost=6,293,768.21..6,547,657.03 rows=388,287 width=12) (actual time=82,737.787..93,616.300 rows=400,000 loops=3)

  • Group Key: cr.offerid
6. 191,562.705 270,326.421 ↑ 1.3 26,666,667 3

Sort (cost=6,293,768.21..6,377,103.52 rows=33,334,127 width=8) (actual time=82,737.184..90,108.807 rows=26,666,667 loops=3)

  • Sort Key: cr.offerid
  • Sort Method: external merge Disk: 472128kB
7. 78,763.716 78,763.716 ↑ 1.3 26,666,667 3

Parallel Seq Scan on course_registrations cr (cost=0.00..761,360.27 rows=33,334,127 width=8) (actual time=7.209..26,254.572 rows=26,666,667 loops=3)

8.          

CTE actual_assistants

9. 116.293 467.647 ↓ 1.3 345,789 1

GroupAggregate (cost=100,916.06..109,548.03 rows=263,197 width=12) (actual time=251.221..467.647 rows=345,789 loops=1)

  • Group Key: sa.offerid
10. 303.560 351.354 ↑ 1.0 800,000 1

Sort (cost=100,916.06..102,916.06 rows=800,000 width=8) (actual time=251.215..351.354 rows=800,000 loops=1)

  • Sort Key: sa.offerid
  • Sort Method: external merge Disk: 14168kB
11. 47.794 47.794 ↑ 1.0 800,000 1

Seq Scan on student_assistants sa (cost=0.00..11,540.00 rows=800,000 width=8) (actual time=0.006..47.794 rows=800,000 loops=1)

12. 325.372 106,788.918 ↓ 1.0 400,000 1

Sort (cost=50,447.91..51,418.62 rows=388,287 width=12) (actual time=106,758.782..106,788.918 rows=400,000 loops=1)

  • Sort Key: ra.offerid
  • Sort Method: external sort Disk: 10184kB
13. 106,463.546 106,463.546 ↓ 1.0 400,000 1

CTE Scan on required_assistants ra (cost=0.00..7,765.74 rows=388,287 width=12) (actual time=83,034.600..106,463.546 rows=400,000 loops=1)

14. 27.613 1,058.780 ↓ 1.3 345,789 1

Materialize (cost=61,573.20..62,889.18 rows=263,197 width=30) (actual time=989.712..1,058.780 rows=345,789 loops=1)

15. 128.754 1,031.167 ↓ 1.3 345,789 1

Sort (cost=61,573.20..62,231.19 rows=263,197 width=30) (actual time=989.708..1,031.167 rows=345,789 loops=1)

  • Sort Key: aa.offerid
  • Sort Method: external merge Disk: 15224kB
16. 99.248 902.413 ↓ 1.3 345,789 1

Hash Join (cost=14,550.00..31,577.86 rows=263,197 width=30) (actual time=363.119..902.413 rows=345,789 loops=1)

  • Hash Cond: (co.cid = c.cid)
17. 147.890 794.570 ↓ 1.3 345,789 1

Hash Join (cost=13,117.00..26,525.90 rows=263,197 width=24) (actual time=354.458..794.570 rows=345,789 loops=1)

  • Hash Cond: (aa.offerid = co.offerid)
18. 543.731 543.731 ↓ 1.3 345,789 1

CTE Scan on actual_assistants aa (cost=0.00..5,263.94 rows=263,197 width=12) (actual time=251.224..543.731 rows=345,789 loops=1)

19. 59.596 102.949 ↑ 1.0 400,000 1

Hash (cost=6,163.00..6,163.00 rows=400,000 width=12) (actual time=102.949..102.949 rows=400,000 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 3173kB
20. 43.353 43.353 ↑ 1.0 400,000 1

Seq Scan on course_offers co (cost=0.00..6,163.00 rows=400,000 width=12) (actual time=0.008..43.353 rows=400,000 loops=1)

21. 4.772 8.595 ↑ 1.0 40,000 1

Hash (cost=933.00..933.00 rows=40,000 width=14) (actual time=8.595..8.595 rows=40,000 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2387kB
22. 3.823 3.823 ↑ 1.0 40,000 1

Seq Scan on courses c (cost=0.00..933.00 rows=40,000 width=14) (actual time=0.139..3.823 rows=40,000 loops=1)