explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OXvf

Settings
# exclusive inclusive rows x rows loops node
1. 1.449 1,773.772 ↑ 5.2 72 1

GroupAggregate (cost=257,006.39..257,041.92 rows=374 width=610) (actual time=1,772.343..1,773.772 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.324 1,772.323 ↑ 1.1 346 1

Sort (cost=257,006.39..257,007.32 rows=374 width=585) (actual time=1,772.284..1,772.323 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.069 1,770.999 ↑ 1.1 346 1

Nested Loop Left Join (cost=254,975.34..256,990.40 rows=374 width=585) (actual time=1,768.291..1,770.999 rows=346 loops=1)

4. 0.030 1,769.850 ↑ 1.0 72 1

Hash Left Join (cost=254,974.62..255,830.57 rows=72 width=545) (actual time=1,768.271..1,769.850 rows=72 loops=1)

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

Nested Loop (cost=254,924.62..255,780.11 rows=72 width=529) (actual time=1,768.033..1,769.591 rows=72 loops=1)

6. 0.488 1,769.293 ↑ 1.0 72 1

Hash Join (cost=254,924.19..255,171.53 rows=72 width=355) (actual time=1,768.017..1,769.293 rows=72 loops=1)

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

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

8. 0.020 1,767.930 ↑ 1.0 72 1

Hash (cost=254,923.29..254,923.29 rows=72 width=342) (actual time=1,767.930..1,767.930 rows=72 loops=1)

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

Limit (cost=254,922.39..254,922.57 rows=72 width=342) (actual time=1,767.891..1,767.910 rows=72 loops=1)

10. 70.536 1,767.907 ↑ 373.5 72 1

Sort (cost=254,922.39..254,989.62 rows=26,891 width=342) (actual time=1,767.891..1,767.907 rows=72 loops=1)

  • Sort Key: clip.created_at DESC, clip.id DESC
  • Sort Method: top-N heapsort Memory: 44kB
11. 99.131 1,697.371 ↓ 5.6 150,446 1

Group (cost=225,793.28..253,958.36 rows=26,891 width=342) (actual time=1,208.926..1,697.371 rows=150,446 loops=1)

  • Group Key: clip.id
12. 112.391 1,598.240 ↓ 5.6 150,446 1

Merge Join (cost=225,793.28..253,891.13 rows=26,891 width=342) (actual time=1,208.922..1,598.240 rows=150,446 loops=1)

  • Merge Cond: (clip_tag_1.clip_id = clip_1.id)
13. 200.131 1,061.812 ↓ 2.8 150,446 1

Sort (cost=179,582.89..179,717.35 rows=53,783 width=358) (actual time=995.972..1,061.812 rows=150,446 loops=1)

  • Sort Key: clip.id DESC
  • Sort Method: external merge Disk: 34192kB
14. 156.755 861.681 ↓ 2.8 150,446 1

Hash Join (cost=108,525.58..166,529.93 rows=53,783 width=358) (actual time=473.643..861.681 rows=150,446 loops=1)

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

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

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

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

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

Seq Scan on clip (cost=0.00..95,731.53 rows=222,804 width=342) (actual time=0.092..308.960 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
18. 211.750 424.037 ↓ 2.0 626,236 1

Index Only Scan Backward using clip_pkey on clip clip_1 (cost=46,071.22..73,109.96 rows=319,851 width=16) (actual time=212.941..424.037 rows=626,236 loops=1)

  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 1934
  • Heap Fetches: 7875
19.          

SubPlan (forIndex Only Scan Backward)

20. 1.244 212.287 ↓ 12.2 1,934 1

Nested Loop (cost=0.42..46,070.40 rows=159 width=16) (actual time=8.520..212.287 rows=1,934 loops=1)

21. 207.175 207.175 ↓ 12.2 1,934 1

Seq Scan on clip_tag clip_tag_2 (cost=0.00..45,370.45 rows=159 width=16) (actual time=8.509..207.175 rows=1,934 loops=1)

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

Index Only Scan using clip_pkey on clip clip_2 (cost=0.42..4.39 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1,934)

  • Index Cond: (id = clip_tag_2.clip_id)
  • Heap Fetches: 1
23. 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)
24. 0.000 0.229 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
25. 0.229 0.229 ↓ 0.0 0 1

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

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

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

27. 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
28. 0.692 0.692 ↑ 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.002 rows=1 loops=346)

  • Index Cond: (id = clip_tag.tag_id)