explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S3EX

Settings
# exclusive inclusive rows x rows loops node
1. 1.819 1,775.817 ↑ 5.2 72 1

GroupAggregate (cost=257,008.27..257,043.80 rows=374 width=610) (actual time=1,774.033..1,775.817 rows=72 loops=1)

  • Group Key: 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.created_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. 2.009 1,773.998 ↑ 1.1 346 1

Sort (cost=257,008.27..257,009.21 rows=374 width=585) (actual time=1,773.947..1,773.998 rows=346 loops=1)

  • Sort Key: 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.created_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.100 1,771.989 ↑ 1.1 346 1

Hash Left Join (cost=254,975.34..256,992.29 rows=374 width=585) (actual time=1,769.236..1,771.989 rows=346 loops=1)

  • Hash Cond: (clip.id = clip_bookmark.clip_id)
4. 0.119 1,771.662 ↑ 1.1 346 1

Nested Loop Left Join (cost=254,925.34..256,939.94 rows=374 width=569) (actual time=1,769.001..1,771.662 rows=346 loops=1)

5. 0.001 1,770.535 ↑ 1.0 72 1

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

6. 0.492 1,770.246 ↑ 1.0 72 1

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

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

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

8. 0.021 1,768.878 ↑ 1.0 72 1

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

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

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

10. 67.417 1,768.851 ↑ 373.5 72 1

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

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

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

  • Group Key: clip.id
12. 112.038 1,600.824 ↓ 5.6 150,446 1

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

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

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

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

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

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

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

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

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

Seq Scan on clip (cost=0.00..95,731.53 rows=222,804 width=342) (actual time=0.091..309.981 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. 206.582 423.583 ↓ 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=217.691..423.583 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.322 217.001 ↓ 12.2 1,934 1

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

21. 211.811 211.811 ↓ 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.504..211.811 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.003..0.004 rows=1 loops=72)

  • Index Cond: (id = clip.media_id)
24. 0.000 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)

25. 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
26. 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)
27. 0.000 0.227 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
28. 0.227 0.227 ↓ 0.0 0 1

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

  • Filter: (account_id = '505e48ea-de53-46a3-9d43-b974ecdedafd'::uuid)
  • Rows Removed by Filter: 2477
Planning time : 1.924 ms