explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WfJd

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 2,820.012 ↑ 1.0 51 1

Limit (cost=241,141.77..241,141.90 rows=51 width=189) (actual time=2,819.999..2,820.012 rows=51 loops=1)

2.          

CTE outfits

3. 31.575 2,807.707 ↓ 3.8 994 1

Unique (cost=236,561.18..238,790.67 rows=265 width=33) (actual time=2,305.024..2,807.707 rows=994 loops=1)

4. 1,255.091 2,776.132 ↓ 1.0 450,662 1

Sort (cost=236,561.18..237,675.92 rows=445,899 width=33) (actual time=2,305.023..2,776.132 rows=450,662 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: external merge Disk: 18,472kB
5. 563.199 1,521.041 ↓ 1.0 450,662 1

Hash Join (cost=63,602.70..182,527.67 rows=445,899 width=33) (actual time=513.240..1,521.041 rows=450,662 loops=1)

  • Hash Cond: (outfit.id_outfit = outfit_ranking_temp_a.id_outfit)
6. 445.039 445.039 ↑ 2.0 500,747 1

Seq Scan on outfit (cost=0.00..94,817.42 rows=986,016 width=17) (actual time=0.009..445.039 rows=500,747 loops=1)

  • Filter: ((NOT private_flag) AND (deleted_at IS NULL))
  • Rows Removed by Filter: 499,253
7. 198.706 512.803 ↓ 1.0 900,000 1

Hash (cost=47,958.98..47,958.98 rows=899,898 width=16) (actual time=512.803..512.803 rows=900,000 loops=1)

  • Buckets: 131,072 Batches: 16 Memory Usage: 3,888kB
8. 314.097 314.097 ↓ 1.0 900,000 1

Seq Scan on outfit_ranking_temp_a (cost=0.00..47,958.98 rows=899,898 width=16) (actual time=0.007..314.097 rows=900,000 loops=1)

  • Filter: (id_outfit IS NOT NULL)
9. 0.485 2,820.003 ↑ 2.6 101 1

Sort (cost=2,350.97..2,351.64 rows=265 width=189) (actual time=2,819.977..2,820.003 rows=101 loops=1)

  • Sort Key: user_ranking_temp_a.follower_count_at_week DESC, user_ranking_temp_a.outfit_star_count_at_week DESC, (COALESCE(user_ranking_temp_a.outfit_post_updated_at, '1970-07-16 00:00:00'::timestamp without time zone)) DESC
  • Sort Method: top-N heapsort Memory: 55kB
10. 0.233 2,819.518 ↓ 3.4 895 1

Nested Loop (cost=0.85..2,340.83 rows=265 width=189) (actual time=2,305.048..2,819.518 rows=895 loops=1)

11. 0.388 2,810.339 ↓ 3.8 994 1

Nested Loop (cost=0.42..2,197.21 rows=265 width=126) (actual time=2,305.042..2,810.339 rows=994 loops=1)

12. 2,807.963 2,807.963 ↓ 3.8 994 1

CTE Scan on outfits (cost=0.00..5.30 rows=265 width=8) (actual time=2,305.026..2,807.963 rows=994 loops=1)

13. 1.988 1.988 ↑ 1.0 1 994

Index Scan using user_app_pkey on user_app (cost=0.42..8.26 rows=1 width=118) (actual time=0.002..0.002 rows=1 loops=994)

  • Index Cond: (id_user_app = outfits.id_user_app)
  • Filter: (deleted_at IS NULL)
14. 8.946 8.946 ↑ 1.0 1 994

Index Scan using user_ranking_temp_a_id_user_app_key on user_ranking_temp_a (cost=0.42..0.53 rows=1 width=71) (actual time=0.009..0.009 rows=1 loops=994)

  • Index Cond: (id_user_app = user_app.id_user_app)
Planning time : 0.455 ms
Execution time : 2,825.514 ms