explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 38T0

Settings
# exclusive inclusive rows x rows loops node
1. 37.330 55,869.689 ↑ 1.0 10 1

Nested Loop (cost=3,874,475.86..3,882,099.60 rows=10 width=41) (actual time=55,772.121..55,869.689 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 26.884 ms, Inlining 643.529 ms, Optimization 2522.388 ms, Emission 1478.407 ms, Total 4671.207 ms
2. 1,977.295 55,792.009 ↑ 1.0 10 1

Limit (cost=3,874,475.86..3,874,475.89 rows=10 width=26) (actual time=55,771.909..55,792.009 rows=10 loops=1)

3. 9.840 53,814.714 ↑ 1,793,001.2 10 1

Sort (cost=3,874,475.86..3,919,300.89 rows=17,930,012 width=26) (actual time=53,794.618..53,814.714 rows=10 loops=1)

  • Sort Key: (count(1)) DESC
  • Sort Method: top-N heapsort Memory: 25kB
4. 22.410 53,804.874 ↑ 584.6 30,673 1

Finalize GroupAggregate (cost=1,321,595.15..3,487,014.75 rows=17,930,012 width=26) (actual time=45,334.701..53,804.874 rows=30,673 loops=1)

  • Group Key: v1.tag_name, v2.tag_name
5. 1,724.600 53,782.464 ↑ 328.1 45,542 1

Gather Merge (cost=1,321,595.15..3,195,652.06 rows=14,941,676 width=26) (actual time=45,302.379..53,782.464 rows=45,542 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 2,841.353 52,057.864 ↑ 492.1 15,181 3 / 3

Partial GroupAggregate (cost=1,320,595.13..1,470,011.89 rows=7,470,838 width=26) (actual time=44,121.582..52,057.864 rows=15,181 loops=3)

  • Group Key: v1.tag_name, v2.tag_name
7. 41,450.380 49,216.511 ↓ 1.4 10,164,031 3 / 3

Sort (cost=1,320,595.13..1,339,272.22 rows=7,470,838 width=18) (actual time=44,037.038..49,216.511 rows=10,164,031 loops=3)

  • Sort Key: v2.tag_name
  • Sort Method: external merge Disk: 227,960kB
  • Worker 0: Sort Method: external merge Disk: 230,640kB
  • Worker 1: Sort Method: external merge Disk: 240,296kB
8. 5,137.320 7,766.131 ↓ 1.4 10,164,031 3 / 3

Parallel Hash Join (cost=41,762.19..161,267.93 rows=7,470,838 width=18) (actual time=2,676.032..7,766.131 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. 244.144 244.144 ↑ 1.3 29,670 3 / 3

Parallel Seq Scan on video_tag v1 (cost=0.00..20,106.08 rows=37,215 width=17) (actual time=0.090..244.144 rows=29,670 loops=3)

  • Filter: ((tag_name)::text = 'fyp'::text)
  • Rows Removed by Filter: 508,384
10. 492.869 2,384.667 ↑ 1.2 469,391 3 / 3

Parallel Hash (cost=31,028.78..31,028.78 rows=584,593 width=17) (actual time=2,384.666..2,384.667 rows=469,391 loops=3)

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

Parallel Seq Scan on video_tag v2 (cost=0.00..31,028.78 rows=584,593 width=17) (actual time=890.271..1,891.798 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
12. 34.398 40.350 ↑ 1.0 44,238 10

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

13. 5.952 5.952 ↑ 1.0 44,238 1

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

Planning time : 0.591 ms