explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jEAO : Old tag 106 (biggest)

Settings
# exclusive inclusive rows x rows loops node
1. 1.406 1,272.498 ↑ 5.2 72 1

GroupAggregate (cost=185,233.59..185,269.12 rows=374 width=610) (actual time=1,271.101..1,272.498 rows=72 loops=1)

  • Group Key: clip.created_at, clip.id, clip.account_id, clip.workspace_id, clip.width, clip.height, clip.size, clip.ext, clip.type, clip.duration, clip.rotation, clip.audio, clip.remote, clip.status, clip.recorded_at, clip.updated_at, clip.digitized, clip.hash, clip.synced, clip.description, clip.source, clip.imported_name, clip_media.playback, clip_media.preview, clip_media.seek, clip_media.image, account.first_name, account.last_name, clip_bookmark.clip_id
2. 1.350 1,271.092 ↑ 1.1 346 1

Sort (cost=185,233.59..185,234.53 rows=374 width=585) (actual time=1,271.043..1,271.092 rows=346 loops=1)

  • Sort Key: clip.created_at DESC, clip.id DESC, clip.account_id, clip.workspace_id, clip.width, clip.height, clip.size, clip.ext, clip.type, clip.duration, clip.rotation, clip.audio, clip.remote, clip.status, clip.recorded_at, clip.updated_at, clip.digitized, clip.hash, clip.synced, clip.description, clip.source, clip.imported_name, clip_media.playback, clip_media.preview, clip_media.seek, clip_media.image, account.first_name, account.last_name, clip_bookmark.clip_id
  • Sort Method: quicksort Memory: 203kB
3. 0.060 1,269.742 ↑ 1.1 346 1

Nested Loop Left Join (cost=183,202.54..185,217.61 rows=374 width=585) (actual time=1,267.123..1,269.742 rows=346 loops=1)

4. 0.026 1,268.674 ↑ 1.0 72 1

Hash Left Join (cost=183,201.82..184,057.78 rows=72 width=545) (actual time=1,267.103..1,268.674 rows=72 loops=1)

  • Hash Cond: (clip.id = clip_bookmark.clip_id)
5. 0.013 1,268.397 ↑ 1.0 72 1

Nested Loop (cost=183,151.82..184,007.32 rows=72 width=529) (actual time=1,266.842..1,268.397 rows=72 loops=1)

6. 0.466 1,268.096 ↑ 1.0 72 1

Hash Join (cost=183,151.40..183,398.74 rows=72 width=355) (actual time=1,266.825..1,268.096 rows=72 loops=1)

  • Hash Cond: (account.id = clip.account_id)
7. 0.915 0.915 ↓ 1.1 6,112 1

Seq Scan on account (cost=0.00..225.18 rows=5,718 width=29) (actual time=0.004..0.915 rows=6,112 loops=1)

8. 0.019 1,266.715 ↑ 1.0 72 1

Hash (cost=183,150.50..183,150.50 rows=72 width=342) (actual time=1,266.715..1,266.715 rows=72 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
9. 0.005 1,266.696 ↑ 1.0 72 1

Limit (cost=183,149.60..183,149.78 rows=72 width=342) (actual time=1,266.679..1,266.696 rows=72 loops=1)

10. 68.984 1,266.691 ↑ 747.0 72 1

Sort (cost=183,149.60..183,284.05 rows=53,783 width=342) (actual time=1,266.677..1,266.691 rows=72 loops=1)

  • Sort Key: clip.created_at DESC, clip.id DESC
  • Sort Method: top-N heapsort Memory: 44kB
11. 108.322 1,197.707 ↓ 2.8 150,446 1

GroupAggregate (cost=180,145.83..181,221.49 rows=53,783 width=342) (actual time=1,030.793..1,197.707 rows=150,446 loops=1)

  • Group Key: clip.id
  • Filter: (array_length(array_agg(tag_1.id), 1) = 1)
12. 203.484 1,089.385 ↓ 2.8 150,446 1

Sort (cost=180,145.83..180,280.29 rows=53,783 width=358) (actual time=1,030.770..1,089.385 rows=150,446 loops=1)

  • Sort Key: clip.id DESC
  • Sort Method: external merge Disk: 34192kB
13. 20.855 885.901 ↓ 2.8 150,446 1

Nested Loop (cost=108,122.68..167,092.87 rows=53,783 width=358) (actual time=482.607..885.901 rows=150,446 loops=1)

14. 0.018 0.018 ↑ 1.0 1 1

Index Only Scan using tag_pkey on tag tag_1 (cost=0.29..4.31 rows=1 width=16) (actual time=0.006..0.018 rows=1 loops=1)

  • Index Cond: (id = '106b95a6-53ea-4591-8c33-1e2e227cedca'::uuid)
  • Heap Fetches: 0
15. 165.803 865.028 ↓ 2.8 150,446 1

Hash Join (cost=108,122.39..166,550.73 rows=53,783 width=358) (actual time=482.599..865.028 rows=150,446 loops=1)

  • Hash Cond: (clip_tag_1.clip_id = clip.id)
16. 225.949 225.949 ↓ 1.0 157,514 1

Seq Scan on clip_tag clip_tag_1 (cost=0.00..45,370.45 rows=154,418 width=32) (actual time=7.484..225.949 rows=157,514 loops=1)

  • Filter: (tag_id = '106b95a6-53ea-4591-8c33-1e2e227cedca'::uuid)
  • Rows Removed by Filter: 2182844
17. 158.489 473.276 ↓ 1.1 244,947 1

Hash (cost=95,558.55..95,558.55 rows=218,787 width=342) (actual time=473.276..473.276 rows=244,947 loops=1)

  • Buckets: 16384 Batches: 32 Memory Usage: 1937kB
18. 314.787 314.787 ↓ 1.1 244,947 1

Seq Scan on clip (cost=0.00..95,558.55 rows=218,787 width=342) (actual time=0.102..314.787 rows=244,947 loops=1)

  • Filter: (visible AND (NOT inactive) AND (status <> 'created'::text) AND (workspace_id = '8271b633-a524-46e8-9620-c2d15c7ce3c6'::uuid))
  • Rows Removed by Filter: 383417
19. 0.288 0.288 ↑ 1.0 1 72

Index Scan using clip_media_pkey on clip_media (cost=0.42..8.44 rows=1 width=206) (actual time=0.004..0.004 rows=1 loops=72)

  • Index Cond: (id = clip.media_id)
20. 0.001 0.251 ↓ 0.0 0 1

Hash (cost=49.96..49.96 rows=3 width=16) (actual time=0.251..0.251 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
21. 0.250 0.250 ↓ 0.0 0 1

Seq Scan on clip_bookmark (cost=0.00..49.96 rows=3 width=16) (actual time=0.250..0.250 rows=0 loops=1)

  • Filter: (account_id = '505e48ea-de53-46a3-9d43-b974ecdedafd'::uuid)
  • Rows Removed by Filter: 2477
22. 0.302 1.008 ↑ 1.0 5 72

Nested Loop Left Join (cost=0.72..16.06 rows=5 width=56) (actual time=0.006..0.014 rows=5 loops=72)

23. 0.360 0.360 ↑ 1.0 5 72

Index Only Scan using clip_tag_clip_id_tag_id_key on clip_tag (cost=0.43..14.46 rows=5 width=32) (actual time=0.004..0.005 rows=5 loops=72)

  • Index Cond: (clip_id = clip.id)
  • Heap Fetches: 346
24. 0.346 0.346 ↑ 1.0 1 346

Index Scan using tag_pkey on tag (cost=0.29..0.31 rows=1 width=40) (actual time=0.001..0.001 rows=1 loops=346)

  • Index Cond: (id = clip_tag.tag_id)