explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XNyB

Settings
# exclusive inclusive rows x rows loops node
1. 174,075.141 249,440.653 ↑ 2.0 8,496,175 1

GroupAggregate (cost=12,209,147.52..60,503,332.97 rows=17,201,847 width=146) (actual time=71,588.289..249,440.653 rows=8,496,175 loops=1)

  • Group Key: clip.id, account.first_name, account.last_name, reel.title, board_ancestor.ancestor_id
2. 59,383.094 75,365.512 ↑ 1.7 9,915,384 1

Sort (cost=12,209,147.52..12,252,152.14 rows=17,201,847 width=352) (actual time=71,342.670..75,365.512 rows=9,915,384 loops=1)

  • Sort Key: clip.id, account.first_name, account.last_name, reel.title, board_ancestor.ancestor_id
  • Sort Method: external merge Disk: 1243720kB
3. 5,673.941 15,982.418 ↑ 1.7 9,915,384 1

Hash Right Join (cost=1,084,564.32..1,851,675.37 rows=17,201,847 width=352) (actual time=8,074.862..15,982.418 rows=9,915,384 loops=1)

  • Hash Cond: (reel_clip.clip_id = clip.id)
4. 1,355.688 2,790.582 ↑ 1.4 7,664,097 1

Merge Left Join (cost=92,517.18..365,530.79 rows=10,956,441 width=53) (actual time=555.676..2,790.582 rows=7,664,097 loops=1)

  • Merge Cond: (reel_clip.reel_id = board_ancestor.id)
5. 339.816 339.816 ↑ 1.0 1,789,138 1

Index Only Scan using reel_clip_pkey on reel_clip (cost=0.43..103,107.84 rows=1,789,310 width=32) (actual time=0.050..339.816 rows=1,789,138 loops=1)

  • Heap Fetches: 340184
6. 373.722 1,095.078 ↓ 16.1 7,902,294 1

Materialize (cost=92,375.26..94,830.86 rows=491,119 width=53) (actual time=555.619..1,095.078 rows=7,902,294 loops=1)

7. 452.778 721.356 ↓ 1.0 491,126 1

Sort (cost=92,375.26..93,603.06 rows=491,119 width=53) (actual time=555.615..721.356 rows=491,126 loops=1)

  • Sort Key: board_ancestor.id
  • Sort Method: external merge Disk: 27696kB
8. 163.697 268.578 ↓ 1.0 491,126 1

Hash Left Join (cost=5,391.91..29,160.99 rows=491,119 width=53) (actual time=41.300..268.578 rows=491,126 loops=1)

  • Hash Cond: (board_ancestor.ancestor_id = reel.id)
9. 63.804 63.804 ↓ 1.0 491,126 1

Seq Scan on board_ancestor (cost=0.00..9,524.19 rows=491,119 width=32) (actual time=0.005..63.804 rows=491,126 loops=1)

10. 22.642 41.077 ↓ 1.0 99,289 1

Hash (cost=3,374.85..3,374.85 rows=99,285 width=37) (actual time=41.077..41.077 rows=99,289 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 3879kB
11. 18.435 18.435 ↓ 1.0 99,289 1

Seq Scan on reel (cost=0.00..3,374.85 rows=99,285 width=37) (actual time=0.004..18.435 rows=99,289 loops=1)

12. 1,286.162 7,517.895 ↓ 1.5 4,126,299 1

Hash (cost=838,964.46..838,964.46 rows=2,809,255 width=315) (actual time=7,517.895..7,517.895 rows=4,126,299 loops=1)

  • Buckets: 16384 Batches: 256 Memory Usage: 2060kB
13. 996.890 6,231.733 ↓ 1.5 4,126,299 1

Hash Join (cost=556,406.96..838,964.46 rows=2,809,255 width=315) (actual time=2,680.631..6,231.733 rows=4,126,299 loops=1)

  • Hash Cond: (clip.account_id = account.id)
14. 1,848.463 5,232.098 ↓ 1.5 4,126,299 1

Hash Right Join (cost=556,067.31..799,997.55 rows=2,809,255 width=318) (actual time=2,677.848..5,232.098 rows=4,126,299 loops=1)

  • Hash Cond: (clip_tag.clip_id = clip.id)
15. 567.386 721.745 ↓ 1.0 2,183,790 1

Hash Left Join (cost=1,666.36..71,241.78 rows=2,173,281 width=31) (actual time=12.644..721.745 rows=2,183,790 loops=1)

  • Hash Cond: (clip_tag.tag_id = tag.id)
16. 141.997 141.997 ↓ 1.0 2,183,790 1

Seq Scan on clip_tag (cost=0.00..39,692.81 rows=2,173,281 width=32) (actual time=0.006..141.997 rows=2,183,790 loops=1)

17. 6.846 12.362 ↑ 1.1 34,348 1

Hash (cost=1,191.16..1,191.16 rows=38,016 width=31) (actual time=12.362..12.362 rows=34,348 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2651kB
18. 5.516 5.516 ↑ 1.1 34,348 1

Seq Scan on tag (cost=0.00..1,191.16 rows=38,016 width=31) (actual time=0.003..5.516 rows=34,348 loops=1)

19. 971.606 2,661.890 ↓ 1.0 2,841,276 1

Hash (cost=406,805.26..406,805.26 rows=2,809,255 width=303) (actual time=2,661.890..2,661.890 rows=2,841,276 loops=1)

  • Buckets: 16384 Batches: 256 Memory Usage: 1522kB
20. 1,690.284 1,690.284 ↓ 1.0 2,841,276 1

Seq Scan on clip (cost=0.00..406,805.26 rows=2,809,255 width=303) (actual time=0.007..1,690.284 rows=2,841,276 loops=1)

  • Filter: (visible AND (NOT inactive) AND (status <> 'created'::text))
  • Rows Removed by Filter: 452208
21. 1.551 2.745 ↓ 1.0 7,809 1

Hash (cost=244.29..244.29 rows=7,629 width=29) (actual time=2.745..2.745 rows=7,809 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 539kB
22. 1.194 1.194 ↓ 1.0 7,809 1

Seq Scan on account (cost=0.00..244.29 rows=7,629 width=29) (actual time=0.005..1.194 rows=7,809 loops=1)

Planning time : 1.649 ms