explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4TVa

Settings
# exclusive inclusive rows x rows loops node
1. 99,488.502 150,756.230 ↑ 1.0 63,600,000 1

Sort (cost=14,522,568.01..14,683,288.82 rows=64,288,324 width=12) (actual time=138,392.482..150,756.230 rows=63,600,000 loops=1)

  • Sort Key: fmstudents.did
  • Sort Method: external sort Disk: 1618008kB
2.          

CTE count_minus

3. 8.855 3,223.793 ↑ 1.0 7,950 1

Finalize GroupAggregate (cost=200,410.40..202,826.36 rows=8,018 width=20) (actual time=3,185.878..3,223.793 rows=7,950 loops=1)

  • Group Key: sr.did
4. 0.000 3,214.938 ↓ 1.3 20,607 1

Gather Merge (cost=200,410.40..202,625.91 rows=16,036 width=20) (actual time=3,185.872..3,214.938 rows=20,607 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 33.138 9,579.762 ↑ 1.2 6,869 3

Partial GroupAggregate (cost=199,410.38..199,774.93 rows=8,018 width=20) (actual time=3,178.507..3,193.254 rows=6,869 loops=3)

  • Group Key: sr.did
6. 67.635 9,546.624 ↑ 1.2 18,237 3

Sort (cost=199,410.38..199,467.25 rows=22,750 width=10) (actual time=3,178.500..3,182.208 rows=18,237 loops=3)

  • Sort Key: sr.did
  • Sort Method: quicksort Memory: 1656kB
7. 83.586 9,478.989 ↑ 1.2 18,237 3

Nested Loop (cost=5,633.48..197,764.01 rows=22,750 width=10) (actual time=182.355..3,159.663 rows=18,237 loops=3)

8. 1,069.173 2,994.216 ↑ 1.2 18,237 3

Hash Join (cost=5,633.05..146,043.99 rows=22,750 width=10) (actual time=180.824..998.072 rows=18,237 loops=3)

  • Hash Cond: (s.sid = mw1.sid)
9. 1,610.946 1,610.946 ↑ 1.2 399,904 3

Parallel Seq Scan on students s (cost=0.00..119,491.12 rows=495,579 width=6) (actual time=75.233..536.982 rows=399,904 loops=3)

  • Filter: (gender = 'F'::bpchar)
  • Rows Removed by Filter: 933429
10. 174.564 314.097 ↑ 1.0 182,669 3

Hash (cost=2,635.69..2,635.69 rows=182,669 width=8) (actual time=104.699..104.699 rows=182,669 loops=3)

  • Buckets: 131072 Batches: 4 Memory Usage: 2819kB
11. 139.533 139.533 ↑ 1.0 182,669 3

Seq Scan on mw1 (cost=0.00..2,635.69 rows=182,669 width=8) (actual time=0.024..46.511 rows=182,669 loops=3)

12. 6,401.187 6,401.187 ↑ 1.0 1 54,711

Index Scan using pk_rid on student_registrations sr (cost=0.43..2.27 rows=1 width=8) (actual time=0.117..0.117 rows=1 loops=54,711)

  • Index Cond: (rid = mw1.rid)
13.          

CTE fmstudents

14. 32.888 8,297.319 ↑ 1.0 8,000 1

Finalize GroupAggregate (cost=435,565.32..435,805.86 rows=8,018 width=20) (actual time=8,251.411..8,297.319 rows=8,000 loops=1)

  • Group Key: sr_1.did
15. 24.981 8,264.431 ↓ 1.5 24,000 1

Sort (cost=435,565.32..435,605.41 rows=16,036 width=20) (actual time=8,251.396..8,264.431 rows=24,000 loops=1)

  • Sort Key: sr_1.did
  • Sort Method: quicksort Memory: 2644kB
16. 198.160 8,239.450 ↓ 1.5 24,000 1

Gather (cost=432,761.51..434,445.29 rows=16,036 width=20) (actual time=8,227.899..8,239.450 rows=24,000 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
17. 1,493.658 8,041.290 ↑ 1.0 8,000 3

Partial HashAggregate (cost=431,761.51..431,841.69 rows=8,018 width=20) (actual time=8,036.968..8,041.290 rows=8,000 loops=3)

  • Group Key: sr_1.did
18. 3,427.658 6,547.632 ↑ 1.2 2,666,667 3

Hash Join (cost=203,842.52..398,428.18 rows=3,333,333 width=10) (actual time=2,478.899..6,547.632 rows=2,666,667 loops=3)

  • Hash Cond: (sr_1.sid = s_1.sid)
19. 672.314 672.314 ↑ 1.2 2,666,667 3

Parallel Seq Scan on student_registrations sr_1 (cost=0.00..94,666.33 rows=3,333,333 width=8) (actual time=30.426..672.314 rows=2,666,667 loops=3)

20. 1,143.420 2,447.660 ↓ 1.0 4,000,000 3

Hash (cost=138,558.23..138,558.23 rows=3,979,223 width=6) (actual time=2,447.660..2,447.660 rows=4,000,000 loops=3)

  • Buckets: 131072 Batches: 64 Memory Usage: 3351kB
21. 1,304.240 1,304.240 ↓ 1.0 4,000,000 3

Seq Scan on students s_1 (cost=0.00..138,558.23 rows=3,979,223 width=6) (actual time=199.853..1,304.240 rows=4,000,000 loops=3)

22. 25,000.279 51,267.728 ↑ 1.0 63,600,000 1

Nested Loop (cost=0.00..2,250,331.88 rows=64,288,324 width=12) (actual time=11,437.313..51,267.728 rows=63,600,000 loops=1)

23. 8,307.449 8,307.449 ↑ 1.0 8,000 1

CTE Scan on fmstudents (cost=0.00..160.36 rows=8,018 width=20) (actual time=8,251.427..8,307.449 rows=8,000 loops=1)

24. 17,960.000 17,960.000 ↑ 1.0 7,950 8,000

CTE Scan on count_minus (cost=0.00..160.36 rows=8,018 width=16) (actual time=0.399..2.245 rows=7,950 loops=8,000)

Planning time : 0.424 ms
Execution time : 155,993.726 ms