explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cSnl

Settings
# exclusive inclusive rows x rows loops node
1. 4.075 124.013 ↑ 2.0 10 1

GroupAggregate (cost=5,758.51..5,759.31 rows=20 width=3,472) (actual time=120.490..124.013 rows=10 loops=1)

  • Group Key: bp1.date_last_comment, bp1.id_post, bp1.post_title
2. 0.132 119.938 ↑ 1.0 20 1

Sort (cost=5,758.51..5,758.56 rows=20 width=3,472) (actual time=119.910..119.938 rows=20 loops=1)

  • Sort Key: bp1.date_last_comment DESC, bp1.id_post, bp1.post_title
  • Sort Method: quicksort Memory: 168kB
3. 0.017 119.806 ↑ 1.0 20 1

Nested Loop Left Join (cost=1,611.96..5,758.08 rows=20 width=3,472) (actual time=15.013..119.806 rows=20 loops=1)

4. 0.012 14.999 ↑ 1.0 10 1

Nested Loop (cost=1,154.03..1,178.30 rows=10 width=1,788) (actual time=14.437..14.999 rows=10 loops=1)

5. 0.018 14.427 ↑ 1.0 10 1

HashAggregate (cost=1,153.60..1,153.70 rows=10 width=8) (actual time=14.418..14.427 rows=10 loops=1)

  • Group Key: bp1_1.id_post
6. 0.002 14.409 ↑ 1.0 10 1

Limit (cost=1,153.45..1,153.47 rows=10 width=16) (actual time=14.405..14.409 rows=10 loops=1)

7. 1.079 14.407 ↑ 206.7 10 1

Sort (cost=1,153.45..1,158.62 rows=2,067 width=16) (actual time=14.404..14.407 rows=10 loops=1)

  • Sort Key: bp1_1.date_last_comment DESC
  • Sort Method: top-N heapsort Memory: 25kB
8. 13.328 13.328 ↓ 2.3 4,748 1

Index Scan using fki_blog_posts_blogs on posts bp1_1 (cost=0.43..1,108.78 rows=2,067 width=16) (actual time=0.036..13.328 rows=4,748 loops=1)

  • Index Cond: (id_blog = ANY ('{1,22,16}'::integer[]))
  • Filter: (NOT is_deleted)
  • Rows Removed by Filter: 1914
9. 0.560 0.560 ↑ 1.0 1 10

Index Scan using pk_blog_posts on posts bp1 (cost=0.43..2.45 rows=1 width=1,788) (actual time=0.038..0.056 rows=1 loops=10)

  • Index Cond: (id_post = bp1_1.id_post)
10. 0.040 104.790 ↑ 1.0 2 10

Subquery Scan on c1 (cost=457.93..457.96 rows=2 width=1,684) (actual time=10.477..10.479 rows=2 loops=10)

11. 0.010 104.750 ↑ 1.0 2 10

Limit (cost=457.93..457.94 rows=2 width=3,336) (actual time=10.475..10.475 rows=2 loops=10)

12. 3.190 104.740 ↑ 38.0 2 10

Sort (cost=457.93..458.12 rows=76 width=3,336) (actual time=10.473..10.474 rows=2 loops=10)

  • Sort Key: bc1.thread_date DESC
  • Sort Method: top-N heapsort Memory: 29kB
13. 13.780 101.550 ↓ 2.9 220 10

GroupAggregate (cost=455.46..457.17 rows=76 width=3,336) (actual time=8.574..10.155 rows=220 loops=10)

  • Group Key: bc1.id_comment, bcv1.is_up
14. 15.800 87.770 ↓ 5.8 440 10

Sort (cost=455.46..455.65 rows=76 width=3,336) (actual time=8.534..8.777 rows=440 loops=10)

  • Sort Key: bc1.id_comment, bcv1.is_up
  • Sort Method: quicksort Memory: 612kB
15. 1.872 71.970 ↓ 5.8 440 10

Nested Loop Left Join (cost=33.22..453.09 rows=76 width=3,336) (actual time=1.673..7.197 rows=440 loops=10)

16. 1.930 8.470 ↓ 5.8 220 10

Hash Right Join (cost=32.23..66.15 rows=38 width=1,652) (actual time=0.664..0.847 rows=220 loops=10)

  • Hash Cond: (bcv1.id_comment = bc1.id_comment)
17. 0.170 0.170 ↑ 6.2 6 10

Index Scan using pk_blog_comment_votes on comment_votes bcv1 (cost=0.43..34.20 rows=37 width=9) (actual time=0.010..0.017 rows=6 loops=10)

  • Index Cond: (id_user = 46358758)
18. 2.600 6.370 ↓ 5.8 220 10

Hash (cost=31.32..31.32 rows=38 width=1,651) (actual time=0.637..0.637 rows=220 loops=10)

  • Buckets: 1024 Batches: 1 Memory Usage: 141kB
19. 3.770 3.770 ↓ 5.8 220 10

Index Scan using idx_comments_posts_parents on comments bc1 (cost=0.56..31.32 rows=38 width=1,651) (actual time=0.018..0.377 rows=220 loops=10)

  • Index Cond: ((id_post = bp1.id_post) AND (id_parent = 0))
20. 6.603 61.628 ↑ 1.0 2 2,201

Subquery Scan on c2 (cost=0.99..10.16 rows=2 width=1,684) (actual time=0.021..0.028 rows=2 loops=2,201)

21. 2.201 55.025 ↑ 1.0 2 2,201

Limit (cost=0.99..10.14 rows=2 width=1,652) (actual time=0.019..0.025 rows=2 loops=2,201)

22. 8.804 52.824 ↑ 10.0 2 2,201

Nested Loop Left Join (cost=0.99..92.49 rows=20 width=1,652) (actual time=0.019..0.024 rows=2 loops=2,201)

  • Join Filter: (bcv2.id_comment = bc2.id_comment)
  • Rows Removed by Join Filter: 12
23. 39.618 39.618 ↑ 10.0 2 2,201

Index Scan Backward using idx_blog_comments_numbers on comments bc2 (cost=0.56..47.10 rows=20 width=1,651) (actual time=0.016..0.018 rows=2 loops=2,201)

  • Index Cond: (id_post = bc1.id_post)
  • Filter: (id_parent <> 0)
  • Rows Removed by Filter: 0
24. 4.392 4.402 ↑ 6.2 6 4,402

Materialize (cost=0.43..34.39 rows=37 width=9) (actual time=0.000..0.001 rows=6 loops=4,402)

25. 0.010 0.010 ↑ 6.2 6 1

Index Scan using pk_blog_comment_votes on comment_votes bcv2 (cost=0.43..34.20 rows=37 width=9) (actual time=0.006..0.010 rows=6 loops=1)

  • Index Cond: (id_user = 46358758)
Planning time : 7.737 ms
Execution time : 124.425 ms