explain.depesz.com

PostgreSQL's explain analyze made readable

Result: X23E

Settings
# exclusive inclusive rows x rows loops node
1. 0.451 7,117.308 ↑ 1.0 5,000 1

Limit (cost=1,991,489,236.76..1,991,489,249.26 rows=5,000 width=31) (actual time=7,116.535..7,117.308 rows=5,000 loops=1)

  • Buffers: shared hit=566,772, temp read=146,818 written=147,144
2. 16.270 7,116.857 ↑ 10.3 5,000 1

Sort (cost=1,991,489,236.76..1,991,489,365.48 rows=51,487 width=31) (actual time=7,116.534..7,116.857 rows=5,000 loops=1)

  • Sort Key: (sum(pc.phrasecount)) DESC
  • Sort Method: top-N heapsort Memory: 1,043kB
  • Buffers: shared hit=189,151, temp read=49,131 written=49,240
3. 23.788 7,100.587 ↑ 1.0 51,411 1

GroupAggregate (cost=1,980,286,627.94..1,991,485,816.04 rows=51,487 width=31) (actual time=4,912.354..7,100.587 rows=51,411 loops=1)

  • Group Key: p.phraseid
  • Buffers: shared hit=189,151, temp read=49,131 written=49,240
4. 125.360 7,076.799 ↓ 1.5 154,215 1

Gather Merge (cost=1,980,286,627.94..1,991,484,786.3 rows=102,974 width=31) (actual time=4,912.305..7,076.799 rows=154,215 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=566,772, temp read=146,818 written=147,144
5. 551.949 6,951.439 ↑ 1.0 51,405 3 / 3

GroupAggregate (cost=1,980,285,627.92..1,991,471,900.52 rows=51,487 width=31) (actual time=4,803.059..6,951.439 rows=51,405 loops=3)

  • Group Key: p.phraseid
  • Buffers: shared hit=566,772, temp read=146,818 written=147,144
6. 898.830 6,399.490 ↑ 91.0 6,105,699 3 / 3

Merge Join (cost=1,980,285,627.92..1,988,692,006.74 rows=555,875,783 width=25) (actual time=4,803.025..6,399.49 rows=6,105,699 loops=3)

  • Buffers: shared hit=566,772, temp read=146,818 written=147,144
7. 3,843.007 5,460.711 ↑ 91.0 6,105,699 3 / 3

Sort (cost=1,980,285,590.82..1,981,675,280.27 rows=555,875,783 width=10) (actual time=4,802.977..5,460.711 rows=6,105,699 loops=3)

  • Sort Key: pc.phraseid
  • Sort Method: external merge Disk: 155,496kB
  • Buffers: shared hit=522,487, temp read=146,818 written=147,144
8. 701.443 1,617.704 ↑ 91.0 6,105,699 3 / 3

Nested Loop (cost=805.22..1,861,544,780.78 rows=555,875,783 width=10) (actual time=0.09..1,617.704 rows=6,105,699 loops=3)

  • Buffers: shared hit=522,479
9. 16.261 16.261 ↑ 1.3 16,667 3 / 3

Index Only Scan using pk_sampledinteractions on sampledinteractions si (cost=0.42..54,020.52 rows=21,277 width=8) (actual time=0.051..16.261 rows=16,667 loops=3)

  • Index Cond: (si.jobid = 'cc55f4c2-682e-423d-b07b-ee744f23308f'::uuid)
  • Heap Fetches: 16,705
  • Buffers: shared hit=9,814
10. 583.333 900.000 ↑ 96.3 366 50,000 / 3

Bitmap Heap Scan on phrasecount pc (cost=804.8..87,136.01 rows=35,239 width=18) (actual time=0.023..0.054 rows=366 loops=50,000)

  • Heap Blocks: exact=55,407
  • Buffers: shared hit=512,665
11. 316.667 316.667 ↑ 96.3 366 50,000 / 3

Bitmap Index Scan on pk_phrasecount (cost=0..795.99 rows=35,239 width=0) (actual time=0.019..0.019 rows=366 loops=50,000)

  • Index Cond: (pc.interactionid = si.interactionid)
  • Buffers: shared hit=346,583
12. 39.949 39.949 ↓ 1.0 52,051 3 / 3

Index Scan using pk_phrase on phrase p (cost=0.29..68,151.79 rows=51,487 width=23) (actual time=0.043..39.949 rows=52,051 loops=3)

  • Buffers: shared hit=44,285
Planning time : 0.471 ms
Execution time : 7,134.926 ms