explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZNKM

Settings
# exclusive inclusive rows x rows loops node
1. 18,112.759 126,137.091 ↑ 12.9 24,355,406 1

GroupAggregate (cost=73,203,750.02..80,285,023.07 rows=314,723,247 width=48) (actual time=94,699.840..126,137.091 rows=24,355,406 loops=1)

  • Group Key: r1.person, r2.person
2. 78,182.044 108,024.332 ↑ 7.0 44,888,458 1

Sort (cost=73,203,750.02..73,990,558.13 rows=314,723,247 width=22) (actual time=94,699.818..108,024.332 rows=44,888,458 loops=1)

  • Sort Key: r1.person, r2.person
  • Sort Method: external merge Disk: 1515192kB
3. 13,202.324 29,842.288 ↑ 7.0 44,888,458 1

Merge Join (cost=22.54..2,964,189.13 rows=314,723,247 width=22) (actual time=0.053..29,842.288 rows=44,888,458 loops=1)

  • Merge Cond: (coauthor_count.paper = r1.paper)
  • Join Filter: (r1.person <> r2.person)
  • Rows Removed by Join Filter: 14308692
4. 3,458.599 7,650.590 ↑ 1.2 14,308,692 1

Merge Join (cost=3.46..1,131,137.38 rows=16,729,599 width=30) (actual time=0.032..7,650.590 rows=14,308,692 loops=1)

  • Merge Cond: (coauthor_count.paper = r2.paper)
5. 1,159.088 1,159.088 ↑ 1.0 4,750,427 1

Index Scan using coauthor_count_paper_idx on coauthor_count (cost=0.43..149,896.84 rows=4,750,427 width=14) (actual time=0.014..1,159.088 rows=4,750,427 loops=1)

6. 3,032.903 3,032.903 ↑ 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.012..3,032.903 rows=16,729,599 loops=1)

7. 4,201.675 8,989.374 ↓ 3.7 61,618,057 1

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

8. 4,787.699 4,787.699 ↑ 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.008..4,787.699 rows=16,729,599 loops=1)

Planning time : 0.928 ms
Execution time : 136,895.003 ms