explain.depesz.com

PostgreSQL's explain analyze made readable

Result: i3JU : Discourse Post.calculate_avg_time daily Optimized

Settings
# exclusive inclusive rows x rows loops node
1. 30.528 1,360.331 ↑ 1.2 929 1

Merge Join (cost=24,593.54..97,750.67 rows=1,133 width=500) (actual time=1,032.172..1,360.331 rows=929 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: 774
2. 128.122 1,206.978 ↑ 141.2 1,703 1

GroupAggregate (cost=24,593.12..32,406.22 rows=240,403 width=12) (actual time=1,025.651..1,206.978 rows=1,703 loops=1)

  • Group Key: post_timings.topic_id, post_timings.post_number
3. 541.569 1,078.856 ↓ 1.4 337,955 1

Sort (cost=24,593.12..25,194.13 rows=240,403 width=12) (actual time=1,025.388..1,078.856 rows=337,955 loops=1)

  • Sort Key: post_timings.topic_id, post_timings.post_number
  • Sort Method: external merge Disk: 7,264kB
4. 59.924 537.287 ↓ 1.4 337,955 1

Nested Loop (cost=6.09..3,106.99 rows=240,403 width=12) (actual time=0.082..537.287 rows=337,955 loops=1)

5. 0.706 5.188 ↓ 5.7 1,717 1

Nested Loop (cost=5.66..2,075.42 rows=300 width=16) (actual time=0.058..5.188 rows=1,717 loops=1)

6. 0.250 0.282 ↑ 1.5 70 1

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

  • Recheck Cond: (bumped_at > (('now'::cstring)::date - 2))
  • Heap Blocks: exact=67
7. 0.032 0.032 ↑ 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.032..0.032 rows=71 loops=1)

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

Index Scan using index_posts_on_topic_id_and_post_number on posts p2 (cost=0.42..15.89 rows=3 width=12) (actual time=0.008..0.060 rows=25 loops=70)

  • Index Cond: (topic_id = topics.id)
9. 472.175 472.175 ↓ 49.2 197 1,717

Index Scan using post_timings_summary on post_timings (cost=0.43..3.40 rows=4 width=16) (actual time=0.011..0.275 rows=197 loops=1,717)

  • Index Cond: ((topic_id = p2.topic_id) AND (post_number = p2.post_number))
  • Filter: (p2.user_id <> user_id)
  • Rows Removed by Filter: 1
10. 122.825 122.825 ↓ 1.0 166,016 1

Index Scan using index_posts_on_topic_id_and_post_number on posts (cost=0.42..60,888.55 rows=166,003 width=484) (actual time=0.024..122.825 rows=166,016 loops=1)

Planning time : 1.423 ms
Execution time : 1,363.006 ms