explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ww3Lp : Explain get user explore old

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.002 6,981.696 ↑ 1.0 51 1

Limit (cost=632,351.24..632,351.36 rows=51 width=159) (actual time=6,981.688..6,981.696 rows=51 loops=1)

2.          

CTE outfits

3. 140.267 2,627.904 ↓ 1.3 714,489 1

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

4. 1,194.613 2,487.637 ↑ 1.0 1,000,348 1

Sort (cost=335,140.83..337,642.53 rows=1,000,681 width=33) (actual time=1,832.338..2,487.637 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. 630.698 1,293.024 ↑ 1.0 1,000,348 1

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

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

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

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

  • Buckets: 131072 Batches: 16 Memory Usage: 3917kB
8. 203.940 203.940 ↑ 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.008..203.940 rows=910,000 loops=1)

9. 110.961 6,981.694 ↑ 5,563.5 51 1

Sort (cost=292,207.00..292,916.35 rows=283,738 width=159) (actual time=6,981.687..6,981.694 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. 683.870 6,870.733 ↓ 2.2 612,372 1

Hash Join (cost=205,016.95..282,740.90 rows=283,738 width=159) (actual time=5,956.327..6,870.733 rows=612,372 loops=1)

  • Hash Cond: (user_ranking_temp_a.id_user_app = user_app.id_user_app)
11. 230.940 230.940 ↑ 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.006..230.940 rows=1,200,000 loops=1)

12. 183.427 5,955.923 ↓ 2.5 714,488 1

Hash (cost=197,313.22..197,313.22 rows=283,738 width=96) (actual time=5,955.923..5,955.923 rows=714,488 loops=1)

  • Buckets: 32768 (originally 32768) Batches: 32 (originally 16) Memory Usage: 3841kB
13. 697.479 5,772.496 ↓ 2.5 714,488 1

Hash Join (cost=166,977.29..197,313.22 rows=283,738 width=96) (actual time=4,076.693..5,772.496 rows=714,488 loops=1)

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

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

15. 436.023 2,243.428 ↓ 2.0 1,400,031 1

Hash (cost=148,625.54..148,625.54 rows=701,180 width=88) (actual time=2,243.428..2,243.428 rows=1,400,031 loops=1)

  • Buckets: 32768 (originally 32768) Batches: 64 (originally 32) Memory Usage: 3841kB
16. 1,807.399 1,807.405 ↓ 2.0 1,400,031 1

Seq Scan on user_app (cost=1.05..148,625.54 rows=701,180 width=88) (actual time=0.028..1,807.405 rows=1,400,031 loops=1)

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

SubPlan (for Seq Scan)

18. 0.006 0.006 ↑ 1.0 1 1

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

  • Filter: ((deleted_at IS NULL) AND ((id_block_from = 56) OR (id_block_to = 56)))
  • Rows Removed by Filter: 2
Planning time : 0.634 ms
Execution time : 6,988.306 ms