explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HRHX

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 4,744.557 ↑ 1.0 51 1

Limit (cost=680,203.40..680,203.53 rows=51 width=159) (actual time=4,744.549..4,744.557 rows=51 loops=1)

2.          

CTE outfits

3. 119.608 2,197.430 ↓ 1.3 714,489 1

Unique (cost=335,140.83..340,144.24 rows=567,476 width=33) (actual time=1,813.424..2,197.430 rows=714,489 loops=1)

4. 803.289 2,077.822 ↑ 1.0 1,000,348 1

Sort (cost=335,140.83..337,642.53 rows=1,000,681 width=33) (actual time=1,813.424..2,077.822 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: 41040kB
5. 618.869 1,274.533 ↑ 1.0 1,000,348 1

Hash Left Join (cost=64,317.35..180,684.21 rows=1,000,681 width=33) (actual time=360.298..1,274.533 rows=1,000,348 loops=1)

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

Seq Scan on outfit (cost=0.00..87,339.81 rows=1,000,681 width=17) (actual time=0.019..295.866 rows=1,000,348 loops=1)

  • Filter: ((NOT private_flag) AND (deleted_at IS NULL))
7. 160.431 359.798 ↑ 1.0 910,000 1

Hash (cost=48,495.49..48,495.49 rows=910,149 width=16) (actual time=359.798..359.798 rows=910,000 loops=1)

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

Seq Scan on outfit_ranking_temp_a (cost=0.00..48,495.49 rows=910,149 width=16) (actual time=0.007..199.367 rows=910,000 loops=1)

9. 109.807 4,744.555 ↑ 11,127.0 51 1

Sort (cost=340,059.17..341,477.86 rows=567,476 width=159) (actual time=4,744.549..4,744.555 rows=51 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: 38kB
10. 705.935 4,634.748 ↓ 1.1 612,373 1

Hash Join (cost=119,523.76..321,126.96 rows=567,476 width=159) (actual time=3,525.931..4,634.748 rows=612,373 loops=1)

  • Hash Cond: (user_app.id_user_app = user_ranking_temp_a.id_user_app)
11. 406.269 406.269 ↑ 1.0 1,400,032 1

Seq Scan on user_app (cost=0.00..145,118.59 rows=1,402,359 width=88) (actual time=0.009..406.269 rows=1,400,032 loops=1)

  • Filter: (deleted_at IS NULL)
12. 140.699 3,522.544 ↓ 1.1 612,373 1

Hash (cost=105,225.32..105,225.32 rows=567,476 width=79) (actual time=3,522.544..3,522.544 rows=612,373 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 2677kB
13. 492.317 3,381.845 ↓ 1.1 612,373 1

Hash Join (cost=67,576.00..105,225.32 rows=567,476 width=79) (actual time=2,321.261..3,381.845 rows=612,373 loops=1)

  • Hash Cond: (outfits.id_user_app = user_ranking_temp_a.id_user_app)
14. 2,382.309 2,382.309 ↓ 1.3 714,489 1

CTE Scan on outfits (cost=0.00..11,349.52 rows=567,476 width=8) (actual time=1,813.426..2,382.309 rows=714,489 loops=1)

15. 270.083 507.219 ↑ 1.0 1,200,000 1

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

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

Seq Scan on user_ranking_temp_a (cost=0.00..38,513.00 rows=1,200,000 width=71) (actual time=0.009..237.136 rows=1,200,000 loops=1)

Planning time : 0.412 ms
Execution time : 4,753.742 ms