explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kmV3 : Discourse Post.calculate_avg_time daily

Settings
# exclusive inclusive rows x rows loops node
1. 25.992 19,422.063 ↑ 2.7 926 1

Merge Join (cost=2,088.62..2,330,704.70 rows=2,536 width=500) (actual time=329.388..19,422.063 rows=926 loops=1)

  • Merge Cond: ((post_timings.topic_id = posts.topic_id) AND (post_timings.post_number = posts.post_number))
  • Join Filter: ((posts.avg_time <> (((round(exp(avg(ln((post_timings.msecs)::double precision))))) / '1000'::double precision))::integer) OR (posts.avg_time IS NULL))
  • Rows Removed by Join Filter: 777
2. 4,128.869 19,385.709 ↑ 10.4 103,246 1

GroupAggregate (cost=0.85..2,312,466.78 rows=1,076,516 width=12) (actual time=0.113..19,385.709 rows=103,246 loops=1)

  • Group Key: post_timings.topic_id, post_timings.post_number
3. 1,786.248 15,256.840 ↑ 12.6 10,526,516 1

Nested Loop (cost=0.85..631,700.24 rows=132,954,201 width=12) (actual time=0.039..15,256.840 rows=10,526,516 loops=1)

4. 189.312 189.312 ↓ 1.0 166,016 1

Index Scan using index_posts_on_topic_id_and_post_number on posts p2 (cost=0.42..60,888.55 rows=166,003 width=12) (actual time=0.013..189.312 rows=166,016 loops=1)

5. 13,281.280 13,281.280 ↓ 15.8 63 166,016

Index Scan using post_timings_summary on post_timings (cost=0.43..3.40 rows=4 width=16) (actual time=0.006..0.080 rows=63 loops=166,016)

  • Index Cond: ((topic_id = p2.topic_id) AND (post_number = p2.post_number))
  • Filter: (p2.user_id <> user_id)
  • Rows Removed by Filter: 1
6. 4.289 10.362 ↓ 5.7 1,717 1

Sort (cost=2,087.77..2,088.52 rows=300 width=488) (actual time=9.736..10.362 rows=1,717 loops=1)

  • Sort Key: posts.topic_id, posts.post_number
  • Sort Method: quicksort Memory: 1988kB
7. 2.017 6.073 ↓ 5.7 1,717 1

Nested Loop (cost=5.66..2,075.42 rows=300 width=488) (actual time=0.055..6.073 rows=1,717 loops=1)

8. 0.178 0.206 ↑ 1.5 70 1

Bitmap Heap Scan on topics (cost=5.24..403.38 rows=105 width=4) (actual time=0.042..0.206 rows=70 loops=1)

  • Recheck Cond: (bumped_at > (('now'::cstring)::date - 2))
  • Heap Blocks: exact=67
9. 0.028 0.028 ↑ 1.5 71 1

Bitmap Index Scan on index_topics_on_bumped_at (cost=0.00..5.21 rows=105 width=0) (actual time=0.028..0.028 rows=71 loops=1)

  • Index Cond: (bumped_at > (('now'::cstring)::date - 2))
10. 3.850 3.850 ↓ 8.3 25 70

Index Scan using index_posts_on_topic_id_and_post_number on posts (cost=0.42..15.89 rows=3 width=484) (actual time=0.007..0.055 rows=25 loops=70)

  • Index Cond: (topic_id = topics.id)
Planning time : 1.519 ms
Execution time : 19,422.530 ms