explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mjR4 : Fixed old tag medium

Settings
# exclusive inclusive rows x rows loops node
1. 1.639 238.921 ↑ 3.9 73 1

GroupAggregate (cost=48,369.92..48,396.99 rows=285 width=610) (actual time=237.309..238.921 rows=73 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.668 237.282 ↓ 1.4 390 1

Sort (cost=48,369.92..48,370.63 rows=285 width=585) (actual time=237.247..237.282 rows=390 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: 226kB
3. 0.129 235.614 ↓ 1.4 390 1

Nested Loop Left Join (cost=46,759.99..48,358.30 rows=285 width=585) (actual time=232.984..235.614 rows=390 loops=1)

4. 0.034 232.419 ↓ 1.3 73 1

Hash Left Join (cost=46,759.27..47,471.26 rows=55 width=545) (actual time=230.866..232.419 rows=73 loops=1)

  • Hash Cond: (clip.id = clip_bookmark.clip_id)
5. 0.000 232.165 ↓ 1.3 73 1

Nested Loop (cost=46,709.27..47,420.91 rows=55 width=529) (actual time=230.634..232.165 rows=73 loops=1)

6. 0.520 231.892 ↓ 1.3 73 1

Hash Join (cost=46,708.85..46,956.02 rows=55 width=355) (actual time=230.623..231.892 rows=73 loops=1)

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

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

8. 0.014 230.530 ↓ 1.3 73 1

Hash (cost=46,708.16..46,708.16 rows=55 width=342) (actual time=230.530..230.530 rows=73 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
9. 0.009 230.516 ↓ 1.3 73 1

Limit (cost=46,707.47..46,707.61 rows=55 width=342) (actual time=230.498..230.516 rows=73 loops=1)

10. 0.994 230.507 ↓ 1.3 73 1

Sort (cost=46,707.47..46,707.61 rows=55 width=342) (actual time=230.498..230.507 rows=73 loops=1)

  • Sort Key: clip.created_at DESC, clip.id DESC
  • Sort Method: top-N heapsort Memory: 44kB
11. 1.427 229.513 ↓ 35.1 1,933 1

GroupAggregate (cost=46,704.78..46,705.88 rows=55 width=342) (actual time=227.919..229.513 rows=1,933 loops=1)

  • Group Key: clip.id
  • Filter: (array_length(array_agg(clip_tag_1.tag_id), 1) = 1)
12. 1.641 228.086 ↓ 35.1 1,933 1

Sort (cost=46,704.78..46,704.92 rows=55 width=358) (actual time=227.898..228.086 rows=1,933 loops=1)

  • Sort Key: clip.id DESC
  • Sort Method: quicksort Memory: 566kB
13. 1.160 226.445 ↓ 35.1 1,933 1

Nested Loop (cost=0.42..46,703.19 rows=55 width=358) (actual time=8.414..226.445 rows=1,933 loops=1)

14. 219.483 219.483 ↓ 12.2 1,934 1

Seq Scan on clip_tag clip_tag_1 (cost=0.00..45,370.45 rows=159 width=32) (actual time=8.400..219.483 rows=1,934 loops=1)

  • Filter: (tag_id = 'd86b5216-7f3f-4db5-81f6-4ae37ecd0838'::uuid)
  • Rows Removed by Filter: 2338454
15. 5.802 5.802 ↑ 1.0 1 1,934

Index Scan using clip_pkey on clip (cost=0.42..8.37 rows=1 width=342) (actual time=0.003..0.003 rows=1 loops=1,934)

  • Index Cond: (id = clip_tag_1.clip_id)
  • Filter: (visible AND (NOT inactive) AND (status <> 'created'::text) AND (workspace_id = '8271b633-a524-46e8-9620-c2d15c7ce3c6'::uuid))
  • Rows Removed by Filter: 0
16. 0.292 0.292 ↑ 1.0 1 73

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
18. 0.220 0.220 ↓ 0.0 0 1

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

  • Filter: (account_id = '505e48ea-de53-46a3-9d43-b974ecdedafd'::uuid)
  • Rows Removed by Filter: 2477
19. 0.267 3.066 ↑ 1.0 5 73

Nested Loop Left Join (cost=0.72..16.08 rows=5 width=56) (actual time=0.034..0.042 rows=5 loops=73)

20. 2.409 2.409 ↑ 1.0 5 73

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.032..0.033 rows=5 loops=73)

  • Index Cond: (clip_id = clip.id)
  • Heap Fetches: 390
21. 0.390 0.390 ↑ 1.0 1 390

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

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