explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WSZi

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 4,703.882 ↑ 1.0 51 1

Limit (cost=579,037.01..579,037.14 rows=51 width=159) (actual time=4,703.873..4,703.882 rows=51 loops=1)

2.          

CTE outfits

3. 120.421 2,231.350 ↓ 1.2 714,482 1

Unique (cost=295,937.56..300,939.12 rows=579,090 width=33) (actual time=1,857.110..2,231.350 rows=714,482 loops=1)

4. 809.682 2,110.929 ↓ 1.0 1,000,348 1

Sort (cost=295,937.56..298,438.34 rows=1,000,313 width=33) (actual time=1,857.109..2,110.929 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: 41,040kB
5. 662.924 1,301.247 ↓ 1.0 1,000,348 1

Hash Left Join (cost=64,309.28..141,541.26 rows=1,000,313 width=33) (actual time=396.613..1,301.247 rows=1,000,348 loops=1)

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

Seq Scan on outfit (cost=0.00..48,213.13 rows=1,000,313 width=17) (actual time=0.007..242.481 rows=1,000,348 loops=1)

  • Filter: ((NOT private_flag) AND (deleted_at IS NULL))
7. 170.159 395.842 ↓ 1.0 910,000 1

Hash (cost=48,490.68..48,490.68 rows=909,968 width=16) (actual time=395.842..395.842 rows=910,000 loops=1)

  • Buckets: 131,072 Batches: 16 Memory Usage: 3,917kB
8. 225.683 225.683 ↓ 1.0 910,000 1

Seq Scan on outfit_ranking_temp_a (cost=0.00..48,490.68 rows=909,968 width=16) (actual time=0.005..225.683 rows=910,000 loops=1)

9. 111.251 4,703.875 ↑ 5,733.6 101 1

Sort (cost=278,097.77..279,545.49 rows=579,090 width=159) (actual time=4,703.866..4,703.875 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
10. 704.782 4,592.624 ↓ 1.1 612,366 1

Hash Join (cost=120,298.91..255,923.80 rows=579,090 width=159) (actual time=3,577.697..4,592.624 rows=612,366 loops=1)

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

Seq Scan on user_app (cost=0.00..78,949.99 rows=1,399,999 width=88) (actual time=0.008..310.757 rows=1,400,032 loops=1)

  • Filter: (deleted_at IS NULL)
12. 146.625 3,577.085 ↓ 1.1 612,366 1

Hash (cost=105,708.29..105,708.29 rows=579,090 width=79) (actual time=3,577.085..3,577.085 rows=612,366 loops=1)

  • Buckets: 65,536 Batches: 32 Memory Usage: 2,677kB
13. 482.821 3,430.460 ↓ 1.1 612,366 1

Hash Join (cost=67,575.00..105,708.29 rows=579,090 width=79) (actual time=2,389.009..3,430.460 rows=612,366 loops=1)

  • Hash Cond: (outfits.id_user_app = user_ranking_temp_a.id_user_app)
14. 2,416.460 2,416.460 ↓ 1.2 714,482 1

CTE Scan on outfits (cost=0.00..11,581.80 rows=579,090 width=8) (actual time=1,857.112..2,416.460 rows=714,482 loops=1)

15. 277.141 531.179 ↑ 1.0 1,200,000 1

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

  • Buckets: 65,536 Batches: 64 Memory Usage: 2,469kB
16. 254.038 254.038 ↑ 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.009..254.038 rows=1,200,000 loops=1)

Planning time : 0.722 ms
Execution time : 4,713.899 ms