explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VlPk

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 9,001.394 ↑ 1.0 10 1

Limit (cost=537,668.00..537,668.02 rows=10 width=319) (actual time=9,001.390..9,001.394 rows=10 loops=1)

2. 2.372 9,001.391 ↑ 205.0 10 1

Sort (cost=537,668.00..537,673.12 rows=2,050 width=319) (actual time=9,001.388..9,001.391 rows=10 loops=1)

  • Sort Key: bp1.date_last_comment DESC
  • Sort Method: top-N heapsort Memory: 61kB
3. 61.776 8,999.019 ↓ 1.9 3,878 1

GroupAggregate (cost=537,551.95..537,623.70 rows=2,050 width=319) (actual time=8,934.328..8,999.019 rows=3,878 loops=1)

  • Group Key: bp1.id_post
4. 8.768 8,937.243 ↓ 1.8 7,268 1

Sort (cost=537,551.95..537,562.20 rows=4,100 width=319) (actual time=8,934.290..8,937.243 rows=7,268 loops=1)

  • Sort Key: bp1.id_post
  • Sort Method: quicksort Memory: 8991kB
5. 5.552 8,928.475 ↓ 1.8 7,268 1

Nested Loop Left Join (cost=261.95..537,305.92 rows=4,100 width=319) (actual time=0.272..8,928.475 rows=7,268 loops=1)

6. 42.303 42.303 ↓ 1.9 3,878 1

Index Scan using fki_blog_posts_blogs on posts bp1 (cost=0.43..1,099.36 rows=2,050 width=68) (actual time=0.027..42.303 rows=3,878 loops=1)

  • Index Cond: (id_blog = ANY ('{1,22,6}'::integer[]))
  • Filter: (NOT is_deleted)
  • Rows Removed by Filter: 1750
7. 11.634 8,880.620 ↑ 1.0 2 3,878

Subquery Scan on j1 (cost=261.52..261.54 rows=2 width=251) (actual time=2.288..2.290 rows=2 loops=3,878)

8. 3.878 8,868.986 ↑ 1.0 2 3,878

Limit (cost=261.52..261.52 rows=2 width=478) (actual time=2.286..2.287 rows=2 loops=3,878)

9. 93.072 8,865.108 ↑ 19.0 2 3,878

Sort (cost=261.52..261.61 rows=38 width=478) (actual time=2.286..2.286 rows=2 loops=3,878)

  • Sort Key: bc1.thread_date DESC
  • Sort Method: top-N heapsort Memory: 25kB
10. 899.696 8,772.036 ↓ 1.1 42 3,878

GroupAggregate (cost=260.19..261.14 rows=38 width=478) (actual time=2.027..2.262 rows=42 loops=3,878)

  • Group Key: bc1.id_comment, bcv1.is_up
11. 217.168 7,872.340 ↓ 1.5 56 3,878

Sort (cost=260.19..260.28 rows=38 width=478) (actual time=2.016..2.030 rows=56 loops=3,878)

  • Sort Key: bc1.id_comment, bcv1.is_up
  • Sort Method: quicksort Memory: 48kB
12. 167.019 7,655.172 ↓ 1.5 56 3,878

Nested Loop Left Join (cost=37.28..259.19 rows=38 width=478) (actual time=0.251..1.974 rows=56 loops=3,878)

13. 83.472 880.306 ↓ 1.1 42 3,878

Hash Right Join (cost=32.23..66.15 rows=38 width=235) (actual time=0.210..0.227 rows=42 loops=3,878)

  • Hash Cond: (bcv1.id_comment = bc1.id_comment)
14. 32.868 32.868 ↑ 6.2 6 3,652

Index Scan using pk_blog_comment_votes on comment_votes bcv1 (cost=0.43..34.20 rows=37 width=9) (actual time=0.005..0.009 rows=6 loops=3,652)

  • Index Cond: (id_user = 46358758)
15. 81.438 763.966 ↓ 1.1 42 3,878

Hash (cost=31.32..31.32 rows=38 width=234) (actual time=0.197..0.197 rows=42 loops=3,878)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
16. 682.528 682.528 ↓ 1.1 42 3,878

Index Scan using idx_comments_posts_parents on comments bc1 (cost=0.56..31.32 rows=38 width=234) (actual time=0.018..0.176 rows=42 loops=3,878)

  • Index Cond: ((id_post = bp1.id_post) AND (id_parent = 0))
17. 161.167 6,607.847 ↑ 1.0 1 161,167

Subquery Scan on j2 (cost=5.05..5.07 rows=1 width=251) (actual time=0.041..0.041 rows=1 loops=161,167)

18. 0.000 6,446.680 ↑ 1.0 1 161,167

Limit (cost=5.05..5.06 rows=1 width=227) (actual time=0.040..0.040 rows=1 loops=161,167)

19. 805.835 6,446.680 ↑ 1.0 1 161,167

Sort (cost=5.05..5.06 rows=1 width=227) (actual time=0.040..0.040 rows=1 loops=161,167)

  • Sort Key: bc2.comment_number DESC
  • Sort Method: quicksort Memory: 25kB
20. 246.720 5,640.845 ↓ 4.0 4 161,167

Nested Loop Left Join (cost=0.99..5.04 rows=1 width=227) (actual time=0.009..0.035 rows=4 loops=161,167)

21. 3,223.340 3,223.340 ↓ 4.0 4 161,167

Index Scan using idx_comments_posts_roots on comments bc2 (cost=0.56..2.58 rows=1 width=226) (actual time=0.007..0.020 rows=4 loops=161,167)

  • Index Cond: ((id_post = bc1.id_post) AND (id_root = bc1.id_comment))
  • Filter: (id_parent <> 0)
  • Rows Removed by Filter: 1
22. 2,170.785 2,170.785 ↓ 0.0 0 723,595

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=723,595)

  • Index Cond: ((id_user = 46358758) AND (bc2.id_comment = id_comment))
Planning time : 4.775 ms
Execution time : 9,001.676 ms