explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Acd : two

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.005 1,822.470 ↑ 1.0 51 1

Limit (cost=341,900.92..341,901.04 rows=51 width=189) (actual time=1,822.463..1,822.470 rows=51 loops=1)

2.          

CTE outfits

3. 26.753 1,818.963 ↓ 3.8 994 1

Unique (cost=334,619.74..339,549.82 rows=265 width=33) (actual time=1,465.010..1,818.963 rows=994 loops=1)

4. 826.790 1,792.210 ↑ 2.0 500,747 1

Sort (cost=334,619.74..337,084.78 rows=986,016 width=33) (actual time=1,465.009..1,792.210 rows=500,747 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: 20,528kB
5. 348.029 965.420 ↑ 2.0 500,747 1

Hash Left Join (cost=63,602.70..182,527.67 rows=986,016 width=33) (actual time=368.138..965.420 rows=500,747 loops=1)

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

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

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

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

  • Buckets: 131,072 Batches: 16 Memory Usage: 3,888kB
8. 206.767 206.767 ↓ 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..206.767 rows=900,000 loops=1)

9. 0.254 1,822.465 ↑ 2.6 101 1

Sort (cost=2,350.97..2,351.64 rows=265 width=189) (actual time=1,822.456..1,822.465 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.643 1,822.211 ↓ 3.4 895 1

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

11. 0.431 1,820.574 ↓ 3.8 994 1

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

12. 1,819.149 1,819.149 ↓ 3.8 994 1

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

13. 0.994 0.994 ↑ 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.001..0.001 rows=1 loops=994)

  • Index Cond: (id_user_app = outfits.id_user_app)
  • Filter: (deleted_at IS NULL)
14. 0.994 0.994 ↑ 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.001..0.001 rows=1 loops=994)

  • Index Cond: (id_user_app = user_app.id_user_app)
Planning time : 0.499 ms
Execution time : 1,825.450 ms