explain.depesz.com

PostgreSQL's explain analyze made readable

Result: O84k

Settings
# exclusive inclusive rows x rows loops node
1. 0.880 7,633.954 ↑ 2.5 425 1

Sort (cost=6,177.35..6,180.03 rows=1,070 width=84) (actual time=7,633.586..7,633.954 rows=425 loops=1)

  • Sort Key: (count(DISTINCT topic_responses.id))
  • Sort Method: quicksort Memory: 117kB
2. 2,895.243 7,633.074 ↑ 2.5 425 1

GroupAggregate (cost=3,821.80..6,123.51 rows=1,070 width=84) (actual time=594.597..7,633.074 rows=425 loops=1)

  • Group Key: topic_elements.id
3. 2,150.047 4,737.831 ↓ 3.0 240,791 1

Merge Join (cost=3,821.80..5,315.88 rows=79,158 width=273) (actual time=594.358..4,737.831 rows=240,791 loops=1)

  • Merge Cond: (topic_elements.id = topic_topic_elements.topic_element_id)
4. 30.409 622.885 ↑ 1.0 17,672 1

Merge Join (cost=3,383.61..3,653.96 rows=17,675 width=261) (actual time=576.545..622.885 rows=17,672 loops=1)

  • Merge Cond: (topic_responses.topic_element_id = topic_elements.id)
5. 436.068 589.432 ↑ 1.0 17,672 1

Sort (cost=3,250.07..3,294.26 rows=17,675 width=12) (actual time=574.408..589.432 rows=17,672 loops=1)

  • Sort Key: topic_responses.topic_element_id
  • Sort Method: quicksort Memory: 1597kB
6. 29.813 153.364 ↑ 1.0 17,672 1

Hash Left Join (cost=1,342.73..2,003.15 rows=17,675 width=12) (actual time=48.642..153.364 rows=17,672 loops=1)

  • Hash Cond: (topic_responses.id = topic_response_claims.topic_response_id)
7. 29.773 113.865 ↑ 1.0 17,576 1

Hash Join (cost=1,149.30..1,596.36 rows=17,675 width=8) (actual time=38.939..113.865 rows=17,576 loops=1)

  • Hash Cond: (topic_response_submissions.topic_response_id = topic_responses.id)
8. 30.259 59.665 ↑ 1.0 17,576 1

Hash Join (cost=403.58..804.24 rows=17,675 width=8) (actual time=14.485..59.665 rows=17,576 loops=1)

  • Hash Cond: (topic_response_submissions.story_id = stories.id)
9. 14.947 14.947 ↑ 1.0 18,113 1

Seq Scan on topic_response_submissions (cost=0.00..351.43 rows=18,743 width=16) (actual time=0.009..14.947 rows=18,113 loops=1)

10. 6.855 14.459 ↑ 1.0 7,897 1

Hash (cost=304.26..304.26 rows=7,946 width=4) (actual time=14.458..14.459 rows=7,897 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 342kB
11. 7.604 7.604 ↑ 1.0 7,897 1

Seq Scan on stories (cost=0.00..304.26 rows=7,946 width=4) (actual time=0.007..7.604 rows=7,897 loops=1)

  • Filter: (submitted_at IS NOT NULL)
  • Rows Removed by Filter: 485
12. 11.746 24.427 ↓ 1.1 13,555 1

Hash (cost=584.76..584.76 rows=12,876 width=8) (actual time=24.427..24.427 rows=13,555 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 658kB
13. 12.681 12.681 ↓ 1.1 13,555 1

Seq Scan on topic_responses (cost=0.00..584.76 rows=12,876 width=8) (actual time=0.005..12.681 rows=13,555 loops=1)

14. 4.852 9.686 ↑ 1.0 5,767 1

Hash (cost=119.86..119.86 rows=5,886 width=4) (actual time=9.685..9.686 rows=5,767 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 267kB
15. 4.834 4.834 ↑ 1.0 5,767 1

Seq Scan on topic_response_claims (cost=0.00..119.86 rows=5,886 width=4) (actual time=0.007..4.834 rows=5,767 loops=1)

16. 2.030 3.044 ↑ 1.0 1,070 1

Sort (cost=133.54..136.21 rows=1,070 width=249) (actual time=2.129..3.044 rows=1,070 loops=1)

  • Sort Key: topic_elements.id
  • Sort Method: quicksort Memory: 465kB
17. 1.014 1.014 ↓ 1.0 1,075 1

Seq Scan on topic_elements (cost=0.00..79.70 rows=1,070 width=249) (actual time=0.010..1.014 rows=1,075 loops=1)

18. 1,951.715 1,964.899 ↓ 50.8 243,202 1

Sort (cost=438.19..450.17 rows=4,792 width=24) (actual time=17.808..1,964.899 rows=243,202 loops=1)

  • Sort Key: topic_topic_elements.topic_element_id
  • Sort Method: quicksort Memory: 556kB
19. 8.012 13.184 ↓ 1.0 4,793 1

Hash Join (cost=27.67..145.25 rows=4,792 width=24) (actual time=1.260..13.184 rows=4,793 loops=1)

  • Hash Cond: (topic_topic_elements.topic_id = topics.id)
20. 3.929 3.929 ↓ 1.0 4,793 1

Seq Scan on topic_topic_elements (cost=0.00..104.92 rows=4,792 width=16) (actual time=0.005..3.929 rows=4,793 loops=1)

21. 0.632 1.243 ↓ 1.0 665 1

Hash (cost=19.52..19.52 rows=652 width=20) (actual time=1.242..1.243 rows=665 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
22. 0.611 0.611 ↓ 1.0 665 1

Seq Scan on topics (cost=0.00..19.52 rows=652 width=20) (actual time=0.006..0.611 rows=665 loops=1)