explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 33Eg

Settings
# exclusive inclusive rows x rows loops node
1. 188,780.150 276,290.486 ↑ 2.0 8,395,816 1

GroupAggregate (cost=11,929,013.03..58,114,962.51 rows=16,450,917 width=146) (actual time=78,033.488..276,290.486 rows=8,395,816 loops=1)

  • Group Key: clip.id, account.first_name, account.last_name, reel.title, board_ancestor.ancestor_id
2. 60,982.943 87,510.336 ↑ 1.7 9,818,572 1

Sort (cost=11,929,013.03..11,970,140.33 rows=16,450,917 width=354) (actual time=78,033.401..87,510.336 rows=9,818,572 loops=1)

  • Sort Key: clip.id, account.first_name, account.last_name, reel.title, board_ancestor.ancestor_id
  • Sort Method: external merge Disk: 1,235,672kB
3. 10,517.066 26,527.393 ↑ 1.7 9,818,572 1

Hash Right Join (cost=1,097,985.66..1,860,295.20 rows=16,450,917 width=354) (actual time=13,631.363..26,527.393 rows=9,818,572 loops=1)

  • Hash Cond: (reel_clip.clip_id = clip.id)
4. 1,435.889 3,060.400 ↑ 1.4 7,564,462 1

Merge Left Join (cost=90,911.87..364,322.32 rows=10,884,765 width=53) (actual time=680.054..3,060.400 rows=7,564,462 loops=1)

  • Merge Cond: (reel_clip.reel_id = board_ancestor.id)
5. 363.400 363.400 ↑ 1.0 1,796,294 1

Index Only Scan using reel_clip_pkey on reel_clip (cost=0.43..104,555.05 rows=1,807,125 width=32) (actual time=0.043..363.400 rows=1,796,294 loops=1)

  • Heap Fetches: 353,533
6. 386.932 1,261.111 ↓ 16.2 7,800,628 1

Materialize (cost=90,770.94..93,185.03 rows=482,818 width=53) (actual time=680.006..1,261.111 rows=7,800,628 loops=1)

7. 486.560 874.179 ↓ 1.0 486,887 1

Sort (cost=90,770.94..91,977.98 rows=482,818 width=53) (actual time=680.002..874.179 rows=486,887 loops=1)

  • Sort Key: board_ancestor.id
  • Sort Method: external merge Disk: 27,408kB
8. 282.476 387.619 ↓ 1.0 486,887 1

Hash Left Join (cost=5,255.29..28,684.21 rows=482,818 width=53) (actual time=41.910..387.619 rows=486,887 loops=1)

  • Hash Cond: (board_ancestor.ancestor_id = reel.id)
9. 63.455 63.455 ↓ 1.0 486,887 1

Seq Scan on board_ancestor (cost=0.00..9,445.18 rows=482,818 width=32) (actual time=0.005..63.455 rows=486,887 loops=1)

10. 22.631 41.688 ↓ 1.1 99,947 1

Hash (cost=3,324.35..3,324.35 rows=95,035 width=37) (actual time=41.688..41.688 rows=99,947 loops=1)

  • Buckets: 65,536 Batches: 2 Memory Usage: 3,899kB
11. 19.057 19.057 ↓ 1.1 99,947 1

Seq Scan on reel (cost=0.00..3,324.35 rows=95,035 width=37) (actual time=0.003..19.057 rows=99,947 loops=1)

12. 4,038.201 12,949.927 ↓ 1.5 4,133,643 1

Hash (cost=858,242.34..858,242.34 rows=2,731,236 width=317) (actual time=12,949.927..12,949.927 rows=4,133,643 loops=1)

  • Buckets: 16,384 Batches: 256 Memory Usage: 2,067kB
13. 1,004.171 8,911.726 ↓ 1.5 4,133,643 1

Hash Join (cost=577,106.12..858,242.34 rows=2,731,236 width=317) (actual time=4,612.877..8,911.726 rows=4,133,643 loops=1)

  • Hash Cond: (clip.account_id = account.id)
14. 2,547.635 7,904.760 ↓ 1.5 4,133,643 1

Hash Right Join (cost=576,766.47..820,348.19 rows=2,731,236 width=320) (actual time=4,610.037..7,904.760 rows=4,133,643 loops=1)

  • Hash Cond: (clip_tag.clip_id = clip.id)
15. 600.387 763.213 ↓ 1.0 2,183,893 1

Hash Left Join (cost=1,666.36..71,241.78 rows=2,173,281 width=31) (actual time=12.727..763.213 rows=2,183,893 loops=1)

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

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

17. 6.837 12.449 ↑ 1.1 34,351 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,651kB
18. 5.612 5.612 ↑ 1.1 34,351 1

Seq Scan on tag (cost=0.00..1,191.16 rows=38,016 width=31) (actual time=0.004..5.612 rows=34,351 loops=1)

19. 2,847.464 4,593.912 ↓ 1.0 2,848,551 1

Hash (cost=428,935.66..428,935.66 rows=2,731,236 width=305) (actual time=4,593.912..4,593.912 rows=2,848,551 loops=1)

  • Buckets: 16,384 Batches: 256 Memory Usage: 1,528kB
20. 1,746.448 1,746.448 ↓ 1.0 2,848,551 1

Seq Scan on clip (cost=0.00..428,935.66 rows=2,731,236 width=305) (actual time=0.007..1,746.448 rows=2,848,551 loops=1)

  • Filter: (visible AND (NOT inactive) AND (status <> 'created'::text))
  • Rows Removed by Filter: 452,638
21. 1.630 2.795 ↓ 1.0 7,871 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 543kB
22. 1.165 1.165 ↓ 1.0 7,871 1

Seq Scan on account (cost=0.00..244.29 rows=7,629 width=29) (actual time=0.006..1.165 rows=7,871 loops=1)