explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oM5w

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 216.871 ↑ 1.0 2 1

Limit (cost=1,206.96..1,207.03 rows=2 width=312) (actual time=216.845..216.871 rows=2 loops=1)

2. 0.074 216.871 ↑ 1.0 2 1

GroupAggregate (cost=1,206.96..1,207.03 rows=2 width=312) (actual time=216.845..216.871 rows=2 loops=1)

  • Group Key: bp1.id_post, bp1.post_title
3. 0.013 216.797 ↑ 1.0 4 1

Sort (cost=1,206.96..1,206.97 rows=4 width=312) (actual time=216.797..216.797 rows=4 loops=1)

  • Sort Key: bp1.id_post, bp1.post_title
  • Sort Method: quicksort Memory: 29kB
4. 0.007 216.784 ↑ 1.0 4 1

Nested Loop Left Join (cost=320.51..1,206.92 rows=4 width=312) (actual time=208.930..216.784 rows=4 loops=1)

5. 0.001 3.441 ↑ 1.0 2 1

Limit (cost=0.43..566.66 rows=2 width=69) (actual time=0.949..3.441 rows=2 loops=1)

6. 3.440 3.440 ↑ 1,074.0 2 1

Index Scan Backward using idx_date_last_comment on posts bp1 (cost=0.43..608,131.16 rows=2,148 width=69) (actual time=0.949..3.440 rows=2 loops=1)

  • Filter: ((NOT is_deleted) AND (id_blog = ANY ('{1,22,16}'::integer[])))
  • Rows Removed by Filter: 1290
7. 0.020 213.336 ↑ 1.0 2 2

Subquery Scan on c1 (cost=320.08..320.10 rows=2 width=251) (actual time=106.666..106.668 rows=2 loops=2)

8. 0.004 213.316 ↑ 1.0 2 2

Limit (cost=320.08..320.08 rows=2 width=478) (actual time=106.658..106.658 rows=2 loops=2)

9. 0.912 213.312 ↑ 19.0 2 2

Sort (cost=320.08..320.17 rows=38 width=478) (actual time=106.656..106.656 rows=2 loops=2)

  • Sort Key: bc1.thread_date DESC
  • Sort Method: top-N heapsort Memory: 28kB
10. 6.256 212.400 ↓ 23.6 896 2

GroupAggregate (cost=318.84..319.70 rows=38 width=478) (actual time=102.749..106.200 rows=896 loops=2)

  • Group Key: bc1.id_comment, bcv1.is_up
11. 3.182 206.144 ↓ 41.8 1,590 2

Sort (cost=318.84..318.94 rows=38 width=478) (actual time=102.730..103.072 rows=1,590 loops=2)

  • Sort Key: bc1.id_comment, bcv1.is_up
  • Sort Method: quicksort Memory: 199kB
12. 1.089 202.962 ↓ 41.8 1,590 2

Nested Loop Left Join (cost=6.05..317.84 rows=38 width=478) (actual time=0.095..101.481 rows=1,590 loops=2)

13. 2.169 10.022 ↓ 23.6 896 2

Nested Loop Left Join (cost=0.99..124.80 rows=38 width=235) (actual time=0.032..5.011 rows=896 loops=2)

14. 2.474 2.474 ↓ 23.6 896 2

Index Scan using idx_comments_posts_parents on comments bc1 (cost=0.56..31.32 rows=38 width=234) (actual time=0.022..1.237 rows=896 loops=2)

  • Index Cond: ((id_post = bp1.id_post) AND (id_parent = 0))
15. 5.379 5.379 ↓ 0.0 0 1,793

Index Scan using pk_blog_comment_votes on comment_votes bcv1 (cost=0.43..2.45 rows=1 width=9) (actual time=0.003..0.003 rows=0 loops=1,793)

  • Index Cond: ((id_user = 288) AND (id_comment = bc1.id_comment))
16. 3.586 191.851 ↓ 2.0 2 1,793

Subquery Scan on c2 (cost=5.05..5.07 rows=1 width=251) (actual time=0.106..0.107 rows=2 loops=1,793)

17. 1.793 188.265 ↓ 2.0 2 1,793

Limit (cost=5.05..5.06 rows=1 width=227) (actual time=0.105..0.105 rows=2 loops=1,793)

18. 21.516 186.472 ↓ 2.0 2 1,793

Sort (cost=5.05..5.06 rows=1 width=227) (actual time=0.104..0.104 rows=2 loops=1,793)

  • Sort Key: bc2.comment_number DESC
  • Sort Method: quicksort Memory: 25kB
19. 14.845 164.956 ↓ 18.0 18 1,793

Nested Loop Left Join (cost=0.99..5.04 rows=1 width=227) (actual time=0.010..0.092 rows=18 loops=1,793)

20. 53.790 53.790 ↓ 18.0 18 1,793

Index Scan using idx_comments_posts_roots on comments bc2 (cost=0.56..2.58 rows=1 width=226) (actual time=0.006..0.030 rows=18 loops=1,793)

  • Index Cond: ((id_post = bc1.id_post) AND (id_root = bc1.id_comment))
  • Filter: (id_parent <> 0)
  • Rows Removed by Filter: 1
21. 96.321 96.321 ↓ 0.0 0 32,107

Index Scan using pk_blog_comment_votes on comment_votes bcv2 (cost=0.43..2.45 rows=1 width=9) (actual time=0.003..0.003 rows=0 loops=32,107)

  • Index Cond: ((id_user = 288) AND (id_comment = bc2.id_comment))
Planning time : 1.714 ms
Execution time : 217.003 ms