explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gEb3

Settings
# exclusive inclusive rows x rows loops node
1. 38.211 13,343.842 ↓ 0.0 0 1

Insert on user_ranking_temp_a (cost=276,324.91..278,952.98 rows=10,000 width=148) (actual time=13,343.842..13,343.842 rows=0 loops=1)

2. 14.213 13,305.631 ↑ 1.0 10,000 1

Subquery Scan on *SELECT* (cost=276,324.91..278,952.98 rows=10,000 width=148) (actual time=13,211.171..13,305.631 rows=10,000 loops=1)

3. 20.064 13,291.418 ↑ 1.0 10,000 1

Limit (cost=276,324.91..278,602.98 rows=10,000 width=80) (actual time=13,208.944..13,291.418 rows=10,000 loops=1)

4. 5,717.685 13,271.354 ↑ 1.0 600,000 1

GroupAggregate (cost=141,918.46..278,681.35 rows=600,344 width=80) (actual time=1,663.826..13,271.354 rows=600,000 loops=1)

  • Group Key: user_app.id_user_app
5. 1,208.151 7,553.669 ↓ 11.5 7,966,919 1

Merge Left Join (cost=141,918.46..248,955.52 rows=690,689 width=40) (actual time=1,661.384..7,553.669 rows=7,966,919 loops=1)

  • Merge Cond: (user_app.id_user_app = user_follower.id_user_follow_to)
6. 265.276 2,936.175 ↓ 2.6 1,623,846 1

Merge Left Join (cost=141,916.13..198,267.32 rows=623,910 width=28) (actual time=1,661.369..2,936.175 rows=1,623,846 loops=1)

  • Merge Cond: (user_app.id_user_app = outfit.id_user_app)
7. 525.207 525.207 ↑ 1.0 600,000 1

Index Scan using user_app_pkey on user_app (cost=0.42..43,941.39 rows=600,344 width=8) (actual time=0.005..525.207 rows=600,000 loops=1)

  • Filter: (deleted_at IS NULL)
8. 128.544 2,145.692 ↓ 1.8 1,123,596 1

Materialize (cost=141,915.56..145,032.56 rows=623,400 width=24) (actual time=1,661.360..2,145.692 rows=1,123,596 loops=1)

9. 845.309 2,017.148 ↓ 1.8 1,123,596 1

Sort (cost=141,915.56..143,474.06 rows=623,400 width=24) (actual time=1,661.355..2,017.148 rows=1,123,596 loops=1)

  • Sort Key: outfit.id_user_app
  • Sort Method: external merge Disk: 31464kB
10. 370.115 1,171.839 ↓ 1.8 1,123,596 1

Hash Right Join (cost=40,097.69..69,128.44 rows=623,400 width=24) (actual time=702.644..1,171.839 rows=1,123,596 loops=1)

  • Hash Cond: (post_star_history.id_outfit = outfit.id_outfit)
11. 100.594 100.594 ↑ 1.0 623,400 1

Seq Scan on post_star_history (cost=0.00..11,442.00 rows=623,400 width=16) (actual time=0.991..100.594 rows=623,400 loops=1)

  • Filter: (deleted_at IS NULL)
12. 163.214 701.130 ↓ 1.0 600,000 1

Hash (cost=29,670.53..29,670.53 rows=599,853 width=16) (actual time=701.130..701.130 rows=600,000 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 2793kB
13. 537.916 537.916 ↓ 1.0 600,000 1

Seq Scan on outfit (cost=0.00..29,670.53 rows=599,853 width=16) (actual time=0.007..537.916 rows=600,000 loops=1)

  • Filter: ((deleted_at IS NULL) AND (private_flag IS FALSE))
14. 3,409.343 3,409.343 ↓ 11.2 7,465,526 1

Index Scan using user_follow_id_user_follow_to_idx on user_follow user_follower (cost=0.42..38,965.38 rows=664,600 width=16) (actual time=0.012..3,409.343 rows=7,465,526 loops=1)

  • Filter: (deleted_at IS NULL)
Planning time : 18.556 ms
Execution time : 13,459.444 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint user_ranking_temp_a_id_user_app_fkey 108.020 ms 10000 0.011 ms