explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ELRV

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 0.420 ↓ 7.0 7 1

Subquery Scan on tmp (cost=21.84..21.88 rows=1 width=817) (actual time=0.414..0.420 rows=7 loops=1)

  • Filter: ((tmp.rownumber >= 0) AND (tmp.rownumber <= 51))
2. 0.011 0.416 ↓ 7.0 7 1

WindowAgg (cost=21.84..21.86 rows=1 width=833) (actual time=0.413..0.416 rows=7 loops=1)

3.          

CTE outfits

4. 0.011 0.301 ↑ 2.0 7 1

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

5. 0.118 0.290 ↑ 1.0 93 1

Sort (cost=17.53..17.77 rows=93 width=33) (actual time=0.279..0.290 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
6. 0.045 0.172 ↑ 1.0 93 1

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

  • Hash Cond: (outfit.id_outfit = outfit_ranking_temp_a.id_outfit)
7. 0.088 0.088 ↑ 1.0 93 1

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

  • Filter: ((NOT private_flag) AND (deleted_at IS NULL))
  • Rows Removed by Filter: 255
8. 0.024 0.039 ↑ 1.0 66 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
9. 0.015 0.015 ↑ 1.0 66 1

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

10. 0.022 0.405 ↓ 7.0 7 1

Sort (cost=3.84..3.84 rows=1 width=825) (actual time=0.404..0.405 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
11. 0.008 0.383 ↓ 7.0 7 1

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

  • Join Filter: (user_app.id_user_app = user_ranking_temp_b.id_user_app)
12. 0.015 0.368 ↓ 7.0 7 1

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

  • Hash Cond: (outfits.id_user_app = user_app.id_user_app)
13. 0.306 0.306 ↑ 2.0 7 1

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

14. 0.012 0.047 ↓ 21.0 21 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
15. 0.028 0.035 ↓ 21.0 21 1

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

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

SubPlan (for Seq Scan)

17. 0.007 0.007 ↓ 0.0 0 1

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

  • Filter: ((deleted_at IS NULL) AND ((id_block_from = 22) OR (id_block_to = 22)))
  • Rows Removed by Filter: 3
18. 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.603 ms
Execution time : 0.547 ms