explain.depesz.com

PostgreSQL's explain analyze made readable

Result: drrp

Settings
# exclusive inclusive rows x rows loops node
1. 38.635 42,278.466 ↑ 1.0 10 1

Nested Loop (cost=3,873,143.69..3,880,767.43 rows=10 width=41) (actual time=42,176.046..42,278.466 rows=10 loops=1)

  • Join Filter: (tag_data.name = (v2.tag_name)::text)
  • Rows Removed by Join Filter: 442,370
  • Functions: 72
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 18.269 ms, Inlining 465.514 ms, Optimization 989.183 ms, Emission 557.392 ms, Total 2030.357 ms
2. 499.820 42,195.721 ↑ 1.0 10 1

Limit (cost=3,873,143.69..3,873,143.72 rows=10 width=26) (actual time=42,175.833..42,195.721 rows=10 loops=1)

3. 9.774 41,695.901 ↑ 1,795,156.8 10 1

Sort (cost=3,873,143.69..3,918,022.61 rows=17,951,568 width=26) (actual time=41,676.020..41,695.901 rows=10 loops=1)

  • Sort Key: (count(1)) DESC
  • Sort Method: top-N heapsort Memory: 25kB
4. 27.930 41,686.127 ↑ 585.3 30,673 1

Finalize GroupAggregate (cost=1,317,193.74..3,485,216.76 rows=17,951,568 width=26) (actual time=33,779.906..41,686.127 rows=30,673 loops=1)

  • Group Key: v1.tag_name, v2.tag_name
5. 1,243.555 41,658.197 ↑ 329.6 45,390 1

Gather Merge (cost=1,317,193.74..3,193,503.78 rows=14,959,640 width=26) (actual time=33,619.278..41,658.197 rows=45,390 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 2,993.101 40,414.642 ↑ 494.4 15,130 3 / 3

Partial GroupAggregate (cost=1,316,193.72..1,465,790.12 rows=7,479,820 width=26) (actual time=32,150.907..40,414.642 rows=15,130 loops=3)

  • Group Key: v1.tag_name, v2.tag_name
7. 32,867.263 37,421.541 ↓ 1.4 10,164,031 3 / 3

Sort (cost=1,316,193.72..1,334,893.27 rows=7,479,820 width=18) (actual time=32,069.624..37,421.541 rows=10,164,031 loops=3)

  • Sort Key: v2.tag_name
  • Sort Method: external merge Disk: 236,392kB
  • Worker 0: Sort Method: external merge Disk: 227,160kB
  • Worker 1: Sort Method: external merge Disk: 235,360kB
8. 3,275.889 4,554.278 ↓ 1.4 10,164,031 3 / 3

Parallel Hash Join (cost=43,705.16..155,405.27 rows=7,479,820 width=18) (actual time=1,301.659..4,554.278 rows=10,164,031 loops=3)

  • Hash Cond: (v1.video_id = v2.video_id)
  • Join Filter: ((v1.tag_name)::text <> (v2.tag_name)::text)
9. 39.271 42.180 ↑ 1.3 29,670 3 / 3

Parallel Bitmap Heap Scan on video_tag v1 (cost=1,925.05..14,094.53 rows=37,238 width=17) (actual time=11.638..42.180 rows=29,670 loops=3)

  • Recheck Cond: ((tag_name)::text = 'fyp'::text)
  • Heap Blocks: exact=3,001
10. 2.909 2.909 ↑ 1.0 89,011 1 / 3

Bitmap Index Scan on video_tag_tag_name (cost=0.00..1,902.71 rows=89,371 width=0) (actual time=8.727..8.728 rows=89,011 loops=1)

  • Index Cond: ((tag_name)::text = 'fyp'::text)
11. 234.454 1,236.209 ↑ 1.2 469,391 3 / 3

Parallel Hash (cost=31,040.33..31,040.33 rows=584,942 width=17) (actual time=1,236.208..1,236.209 rows=469,391 loops=3)

  • Buckets: 65,536 Batches: 32 Memory Usage: 3,552kB
12. 1,001.755 1,001.755 ↑ 1.2 469,391 3 / 3

Parallel Seq Scan on video_tag v2 (cost=0.00..31,040.33 rows=584,942 width=17) (actual time=504.615..1,001.755 rows=469,391 loops=3)

  • Filter: ((tag_name)::text <> ALL ('{fyp,foryou,foryoupage,xyzbca,viral,foryourpage,viralvideo,trend,greenscreen,duet,reply,forupage,fypage,4upage,4yu}'::text[]))
  • Rows Removed by Filter: 68,663
13. 35.862 44.110 ↑ 1.0 44,238 10

Materialize (cost=0.00..1,076.78 rows=44,385 width=27) (actual time=0.003..4.411 rows=44,238 loops=10)

14. 8.248 8.248 ↑ 1.0 44,238 1

Seq Scan on tag_data (cost=0.00..854.85 rows=44,385 width=27) (actual time=0.025..8.248 rows=44,238 loops=1)

Planning time : 0.645 ms