explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vWC0

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 155,930.376 ↑ 1.0 10 1

Limit (cost=2,617,114.97..2,617,115.59 rows=10 width=572) (actual time=155,930.342..155,930.376 rows=10 loops=1)

2. 0.016 155,930.361 ↑ 1.1 10 1

Unique (cost=2,617,114.97..2,617,115.66 rows=11 width=572) (actual time=155,930.341..155,930.361 rows=10 loops=1)

3. 0.941 155,930.345 ↓ 1.1 12 1

Sort (cost=2,617,114.97..2,617,115.00 rows=11 width=572) (actual time=155,930.339..155,930.345 rows=12 loops=1)

  • Sort Key: ir.rating DESC, pt.photo_id, p.user_id, p.original_file_name, p.inventory_file_name, p.width, p.height, p.original_file_size, p.wemark_fee_percentage, p.created_at, p.is_deleted, p.title_pending, p.title, p.description_pending, p.description, p.location, p.editorial_only, p.collection_type, p.captured_at, p.rejection_reason_id, p.went_live_at, p.pricing_tier_id, p.duplicate_of, p.internal_data
  • Sort Method: quicksort Memory: 236kB
4. 283.219 155,929.404 ↓ 37.2 409 1

Nested Loop (cost=0.84..2,617,114.78 rows=11 width=572) (actual time=155,537.644..155,929.404 rows=409 loops=1)

  • Join Filter: ((p.user_id = c.user_id) AND ((pt.tag_type_id = 1) OR ((c.creator_type_id = 1000) AND (pt.tag_type_id = 10))))
  • Rows Removed by Join Filter: 1003277
5. 1.235 1.235 ↓ 1.0 2,454 1

Seq Scan on creators c (cost=0.00..121.52 rows=2,452 width=39) (actual time=0.009..1.235 rows=2,454 loops=1)

6. 198.212 155,644.950 ↓ 11.7 409 2,454

Materialize (cost=0.84..2,615,062.40 rows=35 width=574) (actual time=0.337..63.425 rows=409 loops=2,454)

7. 47.417 155,446.738 ↓ 11.7 409 1

Nested Loop (cost=0.84..2,615,062.22 rows=35 width=574) (actual time=825.935..155,446.738 rows=409 loops=1)

8. 125.468 155,167.265 ↓ 21.1 29,007 1

Nested Loop (cost=0.42..2,599,387.36 rows=1,377 width=572) (actual time=732.155..155,167.265 rows=29,007 loops=1)

9. 154,383.552 154,383.552 ↓ 11.3 43,883 1

Seq Scan on photo_tags pt (cost=0.00..2,569,051.23 rows=3,891 width=39) (actual time=2.326..154,383.552 rows=43,883 loops=1)

  • Filter: ((NOT is_deleted) AND (name = 'vacation'::text) AND ((tag_type_id = 1) OR (tag_type_id = 10)))
  • Rows Removed by Filter: 75356001
10. 658.245 658.245 ↑ 1.0 1 43,883

Index Scan using photos_pkey on photos p (cost=0.42..7.79 rows=1 width=533) (actual time=0.015..0.015 rows=1 loops=43,883)

  • Index Cond: (id = pt.photo_id)
  • Filter: ((NOT is_deleted) AND (approval_status_id = 1) AND (photo_type_id = 20))
  • Rows Removed by Filter: 0
11. 229.596 232.056 ↓ 0.0 0 29,007

Index Scan using one_rating_per_user_for_same_image on image_ratings ir (cost=0.41..11.37 rows=1 width=39) (actual time=0.008..0.008 rows=0 loops=29,007)

  • Index Cond: (photo_id = p.id)
  • Filter: ((rated_by <> 'wemark-8ae0027f-cf4c-41e8-a179-f87b81adad1f'::text) AND (NOT (SubPlan 1)))
  • Rows Removed by Filter: 0
12.          

SubPlan (forIndex Scan)

13. 2.460 2.460 ↑ 1.0 1 410

Index Only Scan using one_rating_per_user_for_same_image on image_ratings sir (cost=0.41..8.43 rows=1 width=44) (actual time=0.006..0.006 rows=1 loops=410)

  • Index Cond: (photo_id = ir.photo_id)
  • Heap Fetches: 313