explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UdCE

Settings
# exclusive inclusive rows x rows loops node
1. 32,808.187 152,279.567 ↑ 8.6 24,355,406 1

GroupAggregate (cost=47,446,017.64..51,636,662.30 rows=209,532,233 width=24) (actual time=105,745.971..152,279.567 rows=24,355,406 loops=1)

  • Group Key: r1.person, r2.person
2. 77,871.253 119,471.380 ↑ 4.7 44,888,458 1

Sort (cost=47,446,017.64..47,969,848.22 rows=209,532,233 width=24) (actual time=105,745.932..119,471.380 rows=44,888,458 loops=1)

  • Sort Key: r1.person, r2.person
  • Sort Method: external merge Disk: 1491976kB
3. 13,249.844 41,600.127 ↑ 4.7 44,888,458 1

Merge Join (cost=4.00..5,594,798.31 rows=209,532,233 width=24) (actual time=24.259..41,600.127 rows=44,888,458 loops=1)

  • Merge Cond: (r1.paper = r2.paper)
  • Join Filter: (r1.person <> r2.person)
  • Rows Removed by Join Filter: 14308692
4. 3,396.366 12,180.529 ↓ 1.3 14,308,692 1

Merge Join (cost=3.57..1,139,869.68 rows=11,138,009 width=24) (actual time=24.234..12,180.529 rows=14,308,692 loops=1)

  • Merge Cond: (paper.id = r1.paper)
5. 5,651.093 5,651.093 ↓ 1.0 4,787,283 1

Index Only Scan using paper_id_idx on paper (cost=0.43..214,483.29 rows=4,775,769 width=8) (actual time=24.212..5,651.093 rows=4,787,283 loops=1)

  • Heap Fetches: 4787283
6. 3,133.070 3,133.070 ↑ 1.0 16,729,599 1

Index Scan using role_paper_idx on role r1 (cost=0.43..760,253.92 rows=16,729,599 width=16) (actual time=0.018..3,133.070 rows=16,729,599 loops=1)

7. 4,229.153 16,169.754 ↓ 3.7 61,618,057 1

Materialize (cost=0.43..802,077.92 rows=16,729,599 width=16) (actual time=0.012..16,169.754 rows=61,618,057 loops=1)

8. 11,940.601 11,940.601 ↑ 1.0 16,729,599 1

Index Scan using role_paper_idx on role r2 (cost=0.43..760,253.92 rows=16,729,599 width=16) (actual time=0.009..11,940.601 rows=16,729,599 loops=1)

Planning time : 1.058 ms
Execution time : 162,973.460 ms