explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Hx2u

Settings
# exclusive inclusive rows x rows loops node
1. 1.176 1,268.413 ↑ 5.2 61 1

GroupAggregate (cost=185,480.41..185,510.52 rows=317 width=610) (actual time=1,267.264..1,268.413 rows=61 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.118 1,267.237 ↑ 1.1 285 1

Sort (cost=185,480.41..185,481.20 rows=317 width=585) (actual time=1,267.212..1,267.237 rows=285 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: 171kB
3. 0.088 1,266.119 ↑ 1.1 285 1

Nested Loop Left Join (cost=183,721.14..185,467.24 rows=317 width=585) (actual time=1,263.745..1,266.119 rows=285 loops=1)

4. 0.027 1,265.238 ↑ 1.0 61 1

Hash Left Join (cost=183,720.42..184,483.22 rows=61 width=545) (actual time=1,263.722..1,265.238 rows=61 loops=1)

  • Hash Cond: (clip.id = clip_bookmark.clip_id)
5. 0.019 1,264.985 ↑ 1.0 61 1

Nested Loop (cost=183,670.42..184,432.83 rows=61 width=529) (actual time=1,263.487..1,264.985 rows=61 loops=1)

6. 0.465 1,264.722 ↑ 1.0 61 1

Hash Join (cost=183,669.99..183,917.22 rows=61 width=355) (actual time=1,263.469..1,264.722 rows=61 loops=1)

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

Seq Scan on account (cost=0.00..225.18 rows=5,718 width=29) (actual time=0.006..0.883 rows=6,106 loops=1)

8. 0.018 1,263.374 ↑ 1.0 61 1

Hash (cost=183,669.23..183,669.23 rows=61 width=342) (actual time=1,263.374..1,263.374 rows=61 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
9. 0.006 1,263.356 ↑ 1.0 61 1

Limit (cost=183,668.47..183,668.62 rows=61 width=342) (actual time=1,263.343..1,263.356 rows=61 loops=1)

10. 74.800 1,263.350 ↑ 881.7 61 1

Sort (cost=183,668.47..183,802.92 rows=53,783 width=342) (actual time=1,263.343..1,263.350 rows=61 loops=1)

  • Sort Key: clip.created_at DESC, clip.id DESC
  • Sort Method: top-N heapsort Memory: 41kB
11. 112.122 1,188.550 ↓ 2.8 150,446 1

GroupAggregate (cost=180,729.03..181,804.69 rows=53,783 width=342) (actual time=1,015.417..1,188.550 rows=150,446 loops=1)

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

Sort (cost=180,729.03..180,863.48 rows=53,783 width=358) (actual time=1,015.397..1,076.428 rows=150,446 loops=1)

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

Nested Loop (cost=108,525.87..167,676.07 rows=53,783 width=358) (actual time=474.006..880.192 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.005..0.018 rows=1 loops=1)

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

Hash Join (cost=108,525.58..167,133.93 rows=53,783 width=358) (actual time=473.998..860.441 rows=150,446 loops=1)

  • Hash Cond: (clip_tag_1.clip_id = clip.id)
16. 232.580 232.580 ↓ 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.447..232.580 rows=157,514 loops=1)

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

Hash (cost=95,731.53..95,731.53 rows=222,804 width=342) (actual time=464.695..464.695 rows=244,947 loops=1)

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

Seq Scan on clip (cost=0.00..95,731.53 rows=222,804 width=342) (actual time=0.091..310.112 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: 383223
19. 0.244 0.244 ↑ 1.0 1 61

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=61)

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

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

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

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

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

Nested Loop Left Join (cost=0.72..16.08 rows=5 width=56) (actual time=0.006..0.013 rows=5 loops=61)

23. 0.305 0.305 ↑ 1.0 5 61

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

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

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=285)

  • Index Cond: (id = clip_tag.tag_id)
Planning time : 1.472 ms