explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lWXp

Settings
# exclusive inclusive rows x rows loops node
1. 2.764 534.519 ↑ 2.0 10 1

GroupAggregate (cost=3,793.42..3,794.22 rows=20 width=3,472) (actual time=532.359..534.519 rows=10 loops=1)

  • Group Key: bp1.date_last_comment, bp1.id_post, bp1.post_title
2. 0.103 531.755 ↑ 1.0 20 1

Sort (cost=3,793.42..3,793.47 rows=20 width=3,472) (actual time=531.726..531.755 rows=20 loops=1)

  • Sort Key: bp1.date_last_comment DESC, bp1.id_post, bp1.post_title
  • Sort Method: quicksort Memory: 162kB
3. 0.024 531.652 ↑ 1.0 20 1

Nested Loop Left Join (cost=1,415.46..3,792.99 rows=20 width=3,472) (actual time=152.058..531.652 rows=20 loops=1)

4. 0.019 143.408 ↑ 1.0 10 1

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

5. 0.015 142.749 ↑ 1.0 10 1

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

  • Group Key: bp1_1.id_post
6. 0.003 142.734 ↑ 1.0 10 1

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

7. 1.370 142.731 ↑ 206.7 10 1

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

  • Sort Key: bp1_1.date_last_comment DESC
  • Sort Method: top-N heapsort Memory: 25kB
8. 141.361 141.361 ↓ 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.039..141.361 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.640 0.640 ↑ 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.047..0.064 rows=1 loops=10)

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

Subquery Scan on c1 (cost=261.42..261.45 rows=2 width=1,684) (actual time=38.820..38.822 rows=2 loops=10)

11. 0.650 388.180 ↑ 1.0 2 10

Limit (cost=261.42..261.43 rows=2 width=3,336) (actual time=38.818..38.818 rows=2 loops=10)

12. 2.040 387.530 ↑ 19.0 2 10

Sort (cost=261.42..261.52 rows=38 width=3,336) (actual time=38.753..38.753 rows=2 loops=10)

  • Sort Key: bc1.thread_date DESC
  • Sort Method: top-N heapsort Memory: 26kB
13. 7.930 385.490 ↓ 5.8 220 10

GroupAggregate (cost=260.19..261.04 rows=38 width=3,336) (actual time=37.618..38.549 rows=220 loops=10)

  • Group Key: bc1.id_comment, bcv1.is_up
14. 9.810 377.560 ↓ 10.0 381 10

Sort (cost=260.19..260.28 rows=38 width=3,336) (actual time=37.604..37.756 rows=381 loops=10)

  • Sort Key: bc1.id_comment, bcv1.is_up
  • Sort Method: quicksort Memory: 495kB
15. 1.303 367.750 ↓ 10.0 381 10

Nested Loop Left Join (cost=37.28..259.19 rows=38 width=3,336) (actual time=3.700..36.775 rows=381 loops=10)

16. 1.880 20.890 ↓ 5.8 220 10

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

  • Hash Cond: (bcv1.id_comment = bc1.id_comment)
17. 0.440 0.440 ↑ 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.036..0.044 rows=6 loops=10)

  • Index Cond: (id_user = 46358758)
18. 1.800 18.570 ↓ 5.8 220 10

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

  • Buckets: 1024 Batches: 1 Memory Usage: 141kB
19. 16.770 16.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.147..1.677 rows=220 loops=10)

  • Index Cond: ((id_post = bp1.id_post) AND (id_parent = 0))
20. 4.402 345.557 ↓ 2.0 2 2,201

Subquery Scan on c2 (cost=5.05..5.07 rows=1 width=1,684) (actual time=0.156..0.157 rows=2 loops=2,201)

21. 2.201 341.155 ↓ 2.0 2 2,201

Limit (cost=5.05..5.06 rows=1 width=1,652) (actual time=0.155..0.155 rows=2 loops=2,201)

22. 33.015 338.954 ↓ 2.0 2 2,201

Sort (cost=5.05..5.06 rows=1 width=1,652) (actual time=0.154..0.154 rows=2 loops=2,201)

  • Sort Key: bc2.comment_number DESC
  • Sort Method: quicksort Memory: 25kB
23. 16.144 305.939 ↓ 15.0 15 2,201

Nested Loop Left Join (cost=0.99..5.04 rows=1 width=1,652) (actual time=0.021..0.139 rows=15 loops=2,201)

24. 189.286 189.286 ↓ 15.0 15 2,201

Index Scan using idx_comments_posts_roots on comments bc2 (cost=0.56..2.58 rows=1 width=1,651) (actual time=0.018..0.086 rows=15 loops=2,201)

  • Index Cond: ((id_post = bc1.id_post) AND (id_root = bc1.id_comment))
  • Filter: (id_parent <> 0)
  • Rows Removed by Filter: 1
25. 100.509 100.509 ↓ 0.0 0 33,503

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=33,503)

  • Index Cond: ((id_user = 46358758) AND (id_comment = bc2.id_comment))
Planning time : 22.262 ms
Execution time : 534.792 ms