explain.depesz.com

PostgreSQL's explain analyze made readable

Result: I9MO

Settings
# exclusive inclusive rows x rows loops node
1. 346.225 71,089.975 ↑ 573.5 307,091 1

Merge Join (cost=6,463,361.27..6,825,705.42 rows=176,102,390 width=18) (actual time=55,962.968..71,089.975 rows=307,091 loops=1)

  • Merge Cond: (cr.offerid = co.offerid)
  • Join Filter: ((count(sa.rid)) < (((count(cr.rid) / 50) + 1)))
  • Rows Removed by Join Filter: 38698
2. 405.256 67,878.435 ↓ 1.0 400,000 1

Finalize GroupAggregate (cost=6,294,623.86..6,646,799.97 rows=384,338 width=12) (actual time=53,264.364..67,878.435 rows=400,000 loops=1)

  • Group Key: cr.offerid
3. 0.000 67,473.179 ↓ 1.6 1,200,000 1

Gather Merge (cost=6,294,623.86..6,637,191.52 rows=768,676 width=12) (actual time=53,264.333..67,473.179 rows=1,200,000 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 19,011.120 199,749.846 ↓ 1.0 400,000 3

Partial GroupAggregate (cost=6,293,623.83..6,547,467.21 rows=384,338 width=12) (actual time=52,770.107..66,583.282 rows=400,000 loops=3)

  • Group Key: cr.offerid
5. 149,175.471 180,738.726 ↑ 1.2 26,666,667 3

Sort (cost=6,293,623.83..6,376,957.16 rows=33,333,333 width=8) (actual time=52,770.075..60,246.242 rows=26,666,667 loops=3)

  • Sort Key: cr.offerid
  • Sort Method: external merge Disk: 473096kB
6. 31,563.255 31,563.255 ↑ 1.2 26,666,667 3

Parallel Seq Scan on course_registrations cr (cost=0.00..761,352.33 rows=33,333,333 width=8) (actual time=0.041..10,521.085 rows=26,666,667 loops=3)

7. 86.416 2,865.315 ↓ 1.3 345,789 1

Materialize (cost=168,737.42..170,112.01 rows=274,918 width=30) (actual time=2,698.599..2,865.315 rows=345,789 loops=1)

8. 550.365 2,778.899 ↓ 1.3 345,789 1

Sort (cost=168,737.42..169,424.71 rows=274,918 width=30) (actual time=2,698.596..2,778.899 rows=345,789 loops=1)

  • Sort Key: sa.offerid
  • Sort Method: external sort Disk: 15160kB
9. 245.216 2,228.534 ↓ 1.3 345,789 1

Hash Join (cost=102,881.48..137,320.44 rows=274,918 width=30) (actual time=1,026.960..2,228.534 rows=345,789 loops=1)

  • Hash Cond: (co.cid = c.cid)
10. 257.019 1,744.846 ↓ 1.3 345,789 1

Merge Join (cost=100,916.48..131,575.32 rows=274,918 width=24) (actual time=788.306..1,744.846 rows=345,789 loops=1)

  • Merge Cond: (sa.offerid = co.offerid)
11. 277.434 1,338.689 ↓ 1.3 345,789 1

GroupAggregate (cost=100,916.06..109,665.24 rows=274,918 width=12) (actual time=786.151..1,338.689 rows=345,789 loops=1)

  • Group Key: sa.offerid
12. 821.024 1,061.255 ↑ 1.0 800,000 1

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

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

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

14. 149.138 149.138 ↑ 1.0 399,999 1

Index Scan using pk_offerid on course_offers co (cost=0.42..14,724.42 rows=400,000 width=12) (actual time=2.152..149.138 rows=399,999 loops=1)

15. 34.711 238.472 ↑ 1.0 40,000 1

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

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

Seq Scan on courses c (cost=0.00..1,465.00 rows=40,000 width=14) (actual time=6.037..203.761 rows=40,000 loops=1)