explain.depesz.com

PostgreSQL's explain analyze made readable

Result: syBN : before

Settings

Optimization(s) for this plan:

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

Limit (cost=349,535.38..349,535.51 rows=51 width=189) (actual time=1,892.081..1,892.089 rows=51 loops=1)

2.          

CTE outfits

3. 29.035 1,888.542 ↓ 3.8 994 1

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

4. 877.150 1,859.507 ↑ 2.0 500,747 1

Sort (cost=334,619.74..337,084.78 rows=986,016 width=33) (actual time=1,496.648..1,859.507 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: 20528kB
5. 359.545 982.357 ↑ 2.0 500,747 1

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

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

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

  • Filter: ((NOT private_flag) AND (deleted_at IS NULL))
  • Rows Removed by Filter: 499253
7. 157.436 363.740 ↓ 1.0 900,000 1

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

  • Buckets: 131072 Batches: 16 Memory Usage: 3888kB
8. 206.304 206.304 ↓ 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.006..206.304 rows=900,000 loops=1)

9. 0.271 1,892.084 ↑ 1.3 101 1

Sort (cost=9,985.44..9,985.77 rows=132 width=189) (actual time=1,892.074..1,892.084 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.608 1,891.813 ↓ 6.8 895 1

Nested Loop (cost=29.91..9,980.79 rows=132 width=189) (actual time=1,496.676..1,891.813 rows=895 loops=1)

11. 0.479 1,890.211 ↓ 7.5 994 1

Nested Loop (cost=29.48..9,897.45 rows=132 width=126) (actual time=1,496.670..1,890.211 rows=994 loops=1)

12. 1,888.738 1,888.738 ↓ 3.8 994 1

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

13. 0.992 0.994 ↑ 1.0 1 994

Index Scan using user_app_pkey on user_app (cost=29.48..37.32 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) AND (NOT (hashed SubPlan 2)))
14.          

SubPlan (for Index Scan)

15. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on user_block (cost=0.00..29.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)))
16. 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.62 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.515 ms
Execution time : 1,895.187 ms