explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zqIg

Settings
# exclusive inclusive rows x rows loops node
1. 0.152 551.573 ↑ 1.0 2 1

GroupAggregate (cost=6,596.23..6,596.34 rows=2 width=3,476) (actual time=551.531..551.573 rows=2 loops=1)

  • Group Key: bp1.date_last_comment, bp1.id_post, bp1.post_title
2. 0.028 551.421 ↑ 2.0 2 1

Sort (cost=6,596.23..6,596.24 rows=4 width=3,476) (actual time=551.410..551.421 rows=2 loops=1)

  • Sort Key: bp1.date_last_comment DESC, bp1.id_post, bp1.post_title
  • Sort Method: quicksort Memory: 28kB
3. 0.008 551.393 ↑ 2.0 2 1

Nested Loop Left Join (cost=312.90..6,596.19 rows=4 width=3,476) (actual time=423.870..551.393 rows=2 loops=1)

4. 0.022 551.307 ↑ 1.0 2 1

Subquery Scan on bp1 (cost=0.43..5,971.16 rows=2 width=1,800) (actual time=423.830..551.307 rows=2 loops=1)

5. 0.001 551.285 ↑ 1.0 2 1

Limit (cost=0.43..5,971.14 rows=2 width=1,699) (actual time=423.812..551.285 rows=2 loops=1)

6. 163.149 551.284 ↑ 196,278,380.0 2 1

Nested Loop Left Join (cost=0.43..1,171,921,621,000.86 rows=392,556,760 width=1,699) (actual time=423.811..551.284 rows=2 loops=1)

  • Join Filter: ((bba11.id_blog = bp11.id_blog) AND ((bba11.id_post = 0) OR (bba11.id_post = bp11.id_post)) AND (((bba11.id_user = bp11.id_user) AND (bba11.id_group = 0)) OR (SubPlan 1)))
  • Rows Removed by Join Filter: 1561004
  • Filter: ((bba11.access_mode IS NULL) OR (bba11.access_mode >= 1))
7. 0.059 0.059 ↑ 353,287.0 2 1

Index Scan Backward using idx_date_last_comment on posts bp11 (cost=0.43..851,347.06 rows=706,574 width=1,695) (actual time=0.053..0.059 rows=2 loops=1)

  • Filter: ((NOT is_deleted) AND (id_blog = ANY ('{1,102,104,111,12,143,16,17,18,2,22,23,25,29,3,30,31,32,33,34,36,37,38,4,42,44,5,51,53,57,59,62,64,65,66,67,68,7}'::integer[])))
  • Rows Removed by Filter: 5
8. 241.744 388.024 ↑ 1.0 780,502 2

Materialize (cost=0.00..18,244.66 rows=780,844 width=36) (actual time=0.009..194.012 rows=780,502 loops=2)

9. 146.280 146.280 ↑ 1.0 780,502 1

Seq Scan on blog_access bba11 (cost=0.00..14,340.44 rows=780,844 width=36) (actual time=0.014..146.280 rows=780,502 loops=1)

10.          

SubPlan (forNested Loop Left Join)

11. 0.052 0.052 ↓ 1.5 34 2

Index Only Scan using idx_author_list on white_list (cost=0.43..3.70 rows=23 width=8) (actual time=0.011..0.026 rows=34 loops=2)

  • Index Cond: (id_author = bp11.id_user)
  • Heap Fetches: 5
12. 0.002 0.078 ↓ 0.0 0 2

Subquery Scan on c1 (cost=312.47..312.49 rows=2 width=1,684) (actual time=0.039..0.039 rows=0 loops=2)

13. 0.006 0.076 ↓ 0.0 0 2

Limit (cost=312.47..312.47 rows=2 width=3,336) (actual time=0.038..0.038 rows=0 loops=2)

14. 0.020 0.070 ↓ 0.0 0 2

Sort (cost=312.47..312.56 rows=37 width=3,336) (actual time=0.035..0.035 rows=0 loops=2)

  • Sort Key: bc1.thread_date DESC
  • Sort Method: quicksort Memory: 25kB
15. 0.004 0.050 ↓ 0.0 0 2

GroupAggregate (cost=311.27..312.10 rows=37 width=3,336) (actual time=0.025..0.025 rows=0 loops=2)

  • Group Key: bc1.id_comment, bcv1.is_up
16. 0.012 0.046 ↓ 0.0 0 2

Sort (cost=311.27..311.36 rows=37 width=3,336) (actual time=0.023..0.023 rows=0 loops=2)

  • Sort Key: bc1.id_comment, bcv1.is_up
  • Sort Method: quicksort Memory: 25kB
17. 0.004 0.034 ↓ 0.0 0 2

Nested Loop Left Join (cost=6.05..310.30 rows=37 width=3,336) (actual time=0.017..0.017 rows=0 loops=2)

18. 0.006 0.030 ↓ 0.0 0 2

Nested Loop Left Join (cost=0.99..122.34 rows=37 width=1,652) (actual time=0.015..0.015 rows=0 loops=2)

19. 0.024 0.024 ↓ 0.0 0 2

Index Scan using idx_comments_posts_parents on comments bc1 (cost=0.56..31.32 rows=37 width=1,651) (actual time=0.012..0.012 rows=0 loops=2)

  • Index Cond: ((id_post = bp1.id_post) AND (id_parent = 0))
  • Filter: (NOT is_deleted)
20. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_blog_comment_votes on comment_votes bcv1 (cost=0.43..2.45 rows=1 width=9) (never executed)

  • Index Cond: ((id_user = bp1.id_user) AND (id_comment = bc1.id_comment))
21. 0.000 0.000 ↓ 0.0 0

Subquery Scan on c2 (cost=5.05..5.07 rows=1 width=1,684) (never executed)

22. 0.000 0.000 ↓ 0.0 0

Limit (cost=5.05..5.06 rows=1 width=1,652) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Sort (cost=5.05..5.06 rows=1 width=1,652) (never executed)

  • Sort Key: bc2.comment_number DESC
24. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.99..5.04 rows=1 width=1,652) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_comments_posts_roots on comments bc2 (cost=0.56..2.58 rows=1 width=1,651) (never executed)

  • Index Cond: ((id_post = bc1.id_post) AND (id_root = bc1.id_comment))
  • Filter: ((NOT is_deleted) AND (id_parent <> 0))
26. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_blog_comment_votes on comment_votes bcv2 (cost=0.43..2.45 rows=1 width=9) (never executed)

  • Index Cond: ((id_user = bp1.id_user) AND (id_comment = bc2.id_comment))
Planning time : 15.317 ms
Execution time : 566.866 ms