explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PnYf

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 4,368.016 ↑ 1.0 51 1

Limit (cost=599,581.38..599,581.50 rows=51 width=159) (actual time=4,368.004..4,368.016 rows=51 loops=1)

2.          

CTE outfits

3. 690.018 1,768.814 ↑ 1.0 1,000,348 1

Sort (cost=265,176.33..267,677.20 rows=1,000,348 width=29) (actual time=1,536.194..1,768.814 rows=1,000,348 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: 33208kB
4. 522.460 1,078.796 ↑ 1.0 1,000,348 1

Hash Left Join (cost=64,311.40..141,544.80 rows=1,000,348 width=29) (actual time=333.752..1,078.796 rows=1,000,348 loops=1)

  • Hash Cond: (outfit.id_outfit = outfit_ranking_temp_a.id_outfit)
5. 223.143 223.143 ↑ 1.0 1,000,348 1

Seq Scan on outfit (cost=0.00..48,213.48 rows=1,000,348 width=17) (actual time=0.037..223.143 rows=1,000,348 loops=1)

  • Filter: ((NOT private_flag) AND (deleted_at IS NULL))
6. 147.478 333.193 ↑ 1.0 910,000 1

Hash (cost=48,491.62..48,491.62 rows=910,062 width=16) (actual time=333.193..333.193 rows=910,000 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 3917kB
7. 185.715 185.715 ↑ 1.0 910,000 1

Seq Scan on outfit_ranking_temp_a (cost=0.00..48,491.62 rows=910,062 width=16) (actual time=0.008..185.715 rows=910,000 loops=1)

8. 151.332 4,368.004 ↑ 9,904.4 101 1

Sort (cost=331,904.05..334,404.92 rows=1,000,348 width=159) (actual time=4,367.997..4,368.004 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: 50kB
9. 741.810 4,216.672 ↑ 1.2 857,035 1

Hash Join (cost=148,420.10..293,599.66 rows=1,000,348 width=159) (actual time=3,187.177..4,216.672 rows=857,035 loops=1)

  • Hash Cond: (user_app.id_user_app = user_ranking_temp_a.id_user_app)
10. 288.200 288.200 ↓ 1.0 1,400,032 1

Seq Scan on user_app (cost=0.00..78,948.61 rows=1,399,861 width=88) (actual time=0.005..288.200 rows=1,400,032 loops=1)

  • Filter: (deleted_at IS NULL)
11. 183.825 3,186.662 ↑ 1.2 857,035 1

Hash (cost=123,215.74..123,215.74 rows=1,000,348 width=79) (actual time=3,186.662..3,186.662 rows=857,035 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 3540kB
12. 514.904 3,002.837 ↑ 1.2 857,035 1

Hash Join (cost=67,575.00..123,215.74 rows=1,000,348 width=79) (actual time=2,006.260..3,002.837 rows=857,035 loops=1)

  • Hash Cond: (outfits.id_user_app = user_ranking_temp_a.id_user_app)
13. 2,018.537 2,018.537 ↑ 1.0 1,000,348 1

CTE Scan on outfits (cost=0.00..20,006.96 rows=1,000,348 width=8) (actual time=1,536.209..2,018.537 rows=1,000,348 loops=1)

14. 249.752 469.396 ↑ 1.0 1,200,000 1

Hash (cost=38,512.00..38,512.00 rows=1,200,000 width=71) (actual time=469.396..469.396 rows=1,200,000 loops=1)

  • Buckets: 65536 Batches: 64 Memory Usage: 2469kB
15. 219.644 219.644 ↑ 1.0 1,200,000 1

Seq Scan on user_ranking_temp_a (cost=0.00..38,512.00 rows=1,200,000 width=71) (actual time=0.024..219.644 rows=1,200,000 loops=1)