explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S5ic

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 0.243 ↓ 7.0 7 1

Limit (cost=21.84..21.84 rows=1 width=825) (actual time=0.242..0.243 rows=7 loops=1)

2.          

CTE outfits

3. 0.008 0.188 ↑ 2.0 7 1

Unique (cost=17.53..18.00 rows=14 width=33) (actual time=0.176..0.188 rows=7 loops=1)

4. 0.064 0.180 ↑ 1.0 93 1

Sort (cost=17.53..17.77 rows=93 width=33) (actual time=0.176..0.180 rows=93 loops=1)

  • Sort Key: outfit.id_user_app, (COALESCE(outfit_ranking_temp_a.star_count_at_week, 0)) DESC, (COALESCE(outfit_ranking_temp_a.outfit_post_datetime, '1970-07-16 00:00:00'::timestamp without time zone)) DESC
  • Sort Method: quicksort Memory: 32kB
5. 0.041 0.116 ↑ 1.0 93 1

Hash Left Join (cost=2.49..14.49 rows=93 width=33) (actual time=0.032..0.116 rows=93 loops=1)

  • Hash Cond: (outfit.id_outfit = outfit_ranking_temp_a.id_outfit)
6. 0.056 0.056 ↑ 1.0 93 1

Seq Scan on outfit (cost=0.00..11.48 rows=93 width=17) (actual time=0.006..0.056 rows=93 loops=1)

  • Filter: ((NOT private_flag) AND (deleted_at IS NULL))
  • Rows Removed by Filter: 255
7. 0.009 0.019 ↑ 1.0 66 1

Hash (cost=1.66..1.66 rows=66 width=16) (actual time=0.019..0.019 rows=66 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
8. 0.010 0.010 ↑ 1.0 66 1

Seq Scan on outfit_ranking_temp_a (cost=0.00..1.66 rows=66 width=16) (actual time=0.003..0.010 rows=66 loops=1)

9. 0.013 0.241 ↓ 7.0 7 1

Sort (cost=3.84..3.84 rows=1 width=825) (actual time=0.241..0.241 rows=7 loops=1)

  • Sort Key: user_ranking_temp_b.follower_count_at_week DESC, user_ranking_temp_b.outfit_star_count_at_week DESC, (COALESCE(user_ranking_temp_b.outfit_post_updated_at, '1970-07-16 00:00:00'::timestamp without time zone)) DESC
  • Sort Method: quicksort Memory: 26kB
10. 0.005 0.228 ↓ 7.0 7 1

Nested Loop (cost=2.60..3.83 rows=1 width=825) (actual time=0.205..0.228 rows=7 loops=1)

  • Join Filter: (user_app.id_user_app = user_ranking_temp_b.id_user_app)
11. 0.010 0.216 ↓ 7.0 7 1

Hash Join (cost=2.46..2.81 rows=1 width=744) (actual time=0.201..0.216 rows=7 loops=1)

  • Hash Cond: (outfits.id_user_app = user_app.id_user_app)
12. 0.190 0.190 ↑ 2.0 7 1

CTE Scan on outfits (cost=0.00..0.28 rows=14 width=8) (actual time=0.177..0.190 rows=7 loops=1)

13. 0.004 0.016 ↓ 21.0 21 1

Hash (cost=2.45..2.45 rows=1 width=736) (actual time=0.016..0.016 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
14. 0.010 0.012 ↓ 21.0 21 1

Seq Scan on user_app (cost=1.05..2.45 rows=1 width=736) (actual time=0.007..0.012 rows=21 loops=1)

  • Filter: ((deleted_at IS NULL) AND (NOT (hashed SubPlan 2)))
  • Rows Removed by Filter: 11
15.          

SubPlan (for Seq Scan)

16. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on user_block (cost=0.00..1.05 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: ((deleted_at IS NULL) AND ((id_block_from = 22) OR (id_block_to = 22)))
  • Rows Removed by Filter: 3
17. 0.007 0.007 ↑ 1.0 1 7

Index Scan using user_ranking_temp_b_id_user_app_key on user_ranking_temp_b (cost=0.14..1.01 rows=1 width=89) (actual time=0.001..0.001 rows=1 loops=7)

  • Index Cond: (id_user_app = outfits.id_user_app)
Planning time : 0.388 ms
Execution time : 0.326 ms