explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AyHw : config1-bad

Settings
# exclusive inclusive rows x rows loops node
1. 7.563 106,852.546 ↑ 1.0 5,000 1

Limit (cost=201,423,976.73..201,423,989.23 rows=5,000 width=31) (actual time=106,844.684..106,852.546 rows=5,000 loops=1)

  • Buffers: shared hit=72,521 read=8,074,224, temp read=146,816 written=147,142
2. 15.382 106,844.983 ↑ 10.3 5,000 1

Sort (cost=201,423,976.73..201,424,105.45 rows=51,487 width=31) (actual time=106,844.683..106,844.983 rows=5,000 loops=1)

  • Sort Key: (sum(pc.phrasecount)) DESC
  • Sort Method: top-N heapsort Memory: 1,043kB
  • Buffers: shared hit=24,487 read=2,671,173, temp read=48,398 written=48,506
3. 17.123 106,829.601 ↑ 1.0 51,411 1

GroupAggregate (cost=190,287,240.69..201,420,556.01 rows=51,487 width=31) (actual time=104,705.994..106,829.601 rows=51,411 loops=1)

  • Group Key: p.phraseid
  • Buffers: shared hit=24,487 read=2,671,173, temp read=48,398 written=48,506
4. 59.599 106,812.478 ↓ 1.5 154,215 1

Gather Merge (cost=190,287,240.69..201,419,526.27 rows=102,974 width=31) (actual time=104,705.949..106,812.478 rows=154,215 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=72,521 read=8,074,224, temp read=146,816 written=147,142
5. 528.401 106,752.879 ↑ 1.0 51,405 3 / 3

GroupAggregate (cost=190,286,240.67..201,406,640.49 rows=51,487 width=31) (actual time=104,641.49..106,752.879 rows=51,405 loops=3)

  • Group Key: p.phraseid
  • Buffers: shared hit=72,521 read=8,074,224, temp read=146,816 written=147,142
6. 876.092 106,224.478 ↑ 91.0 6,105,699 3 / 3

Merge Join (cost=190,286,240.67..198,626,542.68 rows=555,916,588 width=25) (actual time=104,640.911..106,224.478 rows=6,105,699 loops=3)

  • Buffers: shared hit=72,521 read=8,074,224, temp read=146,816 written=147,142
7. 4,404.505 105,278.473 ↑ 91.0 6,105,699 3 / 3

Sort (cost=190,286,203.56..191,675,995.03 rows=555,916,588 width=10) (actual time=104,640.861..105,278.473 rows=6,105,699 loops=3)

  • Sort Key: pc.phraseid
  • Sort Method: external merge Disk: 153,888kB
  • Buffers: shared hit=28,886 read=8,073,541, temp read=146,816 written=147,142
8. 48,870.516 100,873.968 ↑ 91.0 6,105,699 3 / 3

Hash Join (cost=5,424.03..38,963,142.21 rows=555,916,588 width=10) (actual time=502.3..100,873.968 rows=6,105,699 loops=3)

  • Buffers: shared hit=28,878 read=8,073,541
9. 51,525.805 51,525.805 ↑ 1.2 422,477,055 3 / 3

Seq Scan on phrasecount pc (cost=0..37,571,570.01 rows=528,011,801 width=18) (actual time=0.881..51,525.805 rows=422,477,055 loops=3)

  • Buffers: shared hit=4 read=8,072,859
10. 8.895 477.647 ↑ 1.1 50,000 3 / 3

Hash (cost=4,764.37..4,764.37 rows=52,773 width=8) (actual time=477.647..477.647 rows=50,000 loops=3)

  • Buffers: shared hit=28,858 read=682
11. 468.752 468.752 ↑ 1.1 50,000 3 / 3

Index Only Scan using pk_sampledinteractions on sampledinteractions si (cost=0.42..4,764.37 rows=52,773 width=8) (actual time=1.308..468.752 rows=50,000 loops=3)

  • Index Cond: (si.jobid = 'cc55f4c2-682e-423d-b07b-ee744f23308f'::uuid)
  • Heap Fetches: 50,000
  • Buffers: shared hit=28,858 read=682
12. 69.913 69.913 ↓ 1.0 52,051 3 / 3

Index Scan using pk_phrase on phrase p (cost=0.29..1,461.61 rows=51,487 width=23) (actual time=0.046..69.913 rows=52,051 loops=3)

  • Buffers: shared hit=43,635 read=683
Planning time : 46.061 ms
Execution time : 106,870.467 ms