explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZKR8

Settings
# exclusive inclusive rows x rows loops node
1. 6.739 2,038.008 ↑ 1.8 101 1

Subquery Scan on tmp (cost=310,726.02..312,055.29 rows=177 width=180) (actual time=2,037.354..2,038.008 rows=101 loops=1)

  • Filter: ((tmp.rownumber >= 88000) AND (tmp.rownumber <= 88100))
  • Rows Removed by Filter: 88782
2. 26.484 2,031.269 ↓ 2.5 88,883 1

WindowAgg (cost=310,726.02..311,523.58 rows=35,447 width=196) (actual time=1,977.785..2,031.269 rows=88,883 loops=1)

3.          

CTE outfits

4. 35.394 1,378.514 ↓ 1.4 98,877 1

Unique (cost=191,848.55..194,425.27 rows=70,894 width=33) (actual time=1,162.452..1,378.514 rows=98,877 loops=1)

5. 416.728 1,343.120 ↑ 1.0 500,017 1

Sort (cost=191,848.55..193,136.91 rows=515,344 width=33) (actual time=1,162.451..1,343.120 rows=500,017 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: 20496kB
6. 428.902 926.392 ↑ 1.0 500,017 1

Hash Right Join (cost=61,204.37..128,860.33 rows=515,344 width=33) (actual time=299.386..926.392 rows=500,017 loops=1)

  • Hash Cond: (outfit_ranking_temp_a.id_outfit = outfit.id_outfit)
7. 198.458 198.458 ↑ 1.0 900,000 1

Seq Scan on outfit_ranking_temp_a (cost=0.00..47,959.94 rows=900,094 width=16) (actual time=0.007..198.458 rows=900,000 loops=1)

8. 91.848 299.032 ↑ 1.0 500,017 1

Hash (cost=51,742.57..51,742.57 rows=515,344 width=17) (actual time=299.032..299.032 rows=500,017 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 1768kB
9. 207.184 207.184 ↑ 1.0 500,017 1

Seq Scan on outfit (cost=0.00..51,742.57 rows=515,344 width=17) (actual time=0.043..207.184 rows=500,017 loops=1)

  • Filter: ((NOT private_flag) AND (deleted_at IS NULL))
  • Rows Removed by Filter: 499983
10. 100.988 2,004.785 ↓ 2.5 88,883 1

Sort (cost=116,300.76..116,389.37 rows=35,447 width=188) (actual time=1,977.781..2,004.785 rows=88,883 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: external merge Disk: 17520kB
11. 72.577 1,903.797 ↓ 2.5 88,883 1

Nested Loop (cost=7,159.49..110,349.64 rows=35,447 width=188) (actual time=1,443.780..1,903.797 rows=88,883 loops=1)

12. 33.971 1,534.595 ↓ 2.8 98,875 1

Merge Join (cost=7,159.07..87,751.59 rows=35,447 width=125) (actual time=1,443.760..1,534.595 rows=98,875 loops=1)

  • Merge Cond: (user_app.id_user_app = outfits.id_user_app)
13. 37.224 37.230 ↑ 5.0 99,999 1

Index Scan using user_app_pkey on user_app (cost=29.48..78,661.98 rows=500,431 width=117) (actual time=0.017..37.230 rows=99,999 loops=1)

  • Filter: ((deleted_at IS NULL) AND (NOT (hashed SubPlan 2)))
  • Rows Removed by Filter: 2
14.          

SubPlan (for Index Scan)

15. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on user_block (cost=0.00..29.05 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=1)

  • Filter: ((deleted_at IS NULL) AND ((id_block_from = 22) OR (id_block_to = 22)))
  • Rows Removed by Filter: 1
16. 59.801 1,463.394 ↓ 1.4 98,877 1

Sort (cost=7,129.59..7,306.82 rows=70,894 width=8) (actual time=1,443.740..1,463.394 rows=98,877 loops=1)

  • Sort Key: outfits.id_user_app
  • Sort Method: external sort Disk: 2128kB
17. 1,403.593 1,403.593 ↓ 1.4 98,877 1

CTE Scan on outfits (cost=0.00..1,417.88 rows=70,894 width=8) (actual time=1,162.453..1,403.593 rows=98,877 loops=1)

18. 296.625 296.625 ↑ 1.0 1 98,875

Index Scan using user_ranking_temp_a_id_user_app_key on user_ranking_temp_a (cost=0.42..0.63 rows=1 width=71) (actual time=0.003..0.003 rows=1 loops=98,875)

  • Index Cond: (id_user_app = user_app.id_user_app)
Planning time : 0.499 ms
Execution time : 2,045.912 ms