explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OzPt

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 4,329.418 ↑ 1.0 51 1

Limit (cost=582,719.04..582,719.17 rows=51 width=159) (actual time=4,329.414..4,329.418 rows=51 loops=1)

2.          

CTE outfits

3. 120.934 1,992.779 ↓ 1.2 714,482 1

Unique (cost=295,951.83..300,953.57 rows=607,855 width=33) (actual time=1,630.863..1,992.779 rows=714,482 loops=1)

4. 733.144 1,871.845 ↑ 1.0 1,000,348 1

Sort (cost=295,951.83..298,452.70 rows=1,000,348 width=33) (actual time=1,630.862..1,871.845 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. 565.462 1,138.701 ↑ 1.0 1,000,348 1

Hash Left Join (cost=64,311.40..141,544.80 rows=1,000,348 width=33) (actual time=346.074..1,138.701 rows=1,000,348 loops=1)

  • Hash Cond: (outfit.id_outfit = outfit_ranking_temp_a.id_outfit)
6. 227.783 227.783 ↑ 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.031..227.783 rows=1,000,348 loops=1)

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

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

  • Buckets: 131,072 Batches: 16 Memory Usage: 3,917kB
8. 192.487 192.487 ↑ 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.011..192.487 rows=910,000 loops=1)

9. 110.787 4,329.413 ↑ 6,018.4 101 1

Sort (cost=281,765.34..283,284.98 rows=607,855 width=159) (actual time=4,329.408..4,329.413 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. 690.642 4,218.626 ↓ 1.0 612,366 1

Hash Join (cost=122,218.29..258,489.93 rows=607,855 width=159) (actual time=3,231.173..4,218.626 rows=612,366 loops=1)

  • Hash Cond: (user_app.id_user_app = user_ranking_temp_a.id_user_app)
11. 297.312 297.312 ↓ 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.007..297.312 rows=1,400,032 loops=1)

  • Filter: (deleted_at IS NULL)
12. 134.791 3,230.672 ↓ 1.0 612,366 1

Hash (cost=106,903.11..106,903.11 rows=607,855 width=79) (actual time=3,230.672..3,230.672 rows=612,366 loops=1)

  • Buckets: 65,536 Batches: 32 Memory Usage: 2,677kB
13. 439.211 3,095.881 ↓ 1.0 612,366 1

Hash Join (cost=67,575.00..106,903.11 rows=607,855 width=79) (actual time=2,112.970..3,095.881 rows=612,366 loops=1)

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

CTE Scan on outfits (cost=0.00..12,157.10 rows=607,855 width=8) (actual time=1,630.865..2,175.206 rows=714,482 loops=1)

15. 255.563 481.464 ↑ 1.0 1,200,000 1

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

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