explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Z4q

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 6,533.611 ↑ 1.0 10 1

Limit (cost=287,566.29..287,566.32 rows=10 width=319) (actual time=6,533.607..6,533.611 rows=10 loops=1)

2. 3.047 6,533.608 ↑ 214.8 10 1

Sort (cost=287,566.29..287,571.66 rows=2,148 width=319) (actual time=6,533.605..6,533.608 rows=10 loops=1)

  • Sort Key: bp1.date_last_comment DESC
  • Sort Method: top-N heapsort Memory: 55kB
3. 72.480 6,530.561 ↓ 2.2 4,748 1

GroupAggregate (cost=287,444.69..287,519.87 rows=2,148 width=319) (actual time=6,453.846..6,530.561 rows=4,748 loops=1)

  • Group Key: bp1.id_post
4. 10.981 6,458.081 ↓ 2.1 8,835 1

Sort (cost=287,444.69..287,455.43 rows=4,296 width=319) (actual time=6,453.809..6,458.081 rows=8,835 loops=1)

  • Sort Key: bp1.id_post
  • Sort Method: quicksort Memory: 10775kB
5. 3.853 6,447.100 ↓ 2.1 8,835 1

Nested Loop Left Join (cost=133.55..287,185.46 rows=4,296 width=319) (actual time=2.496..6,447.100 rows=8,835 loops=1)

6. 42.943 42.943 ↓ 2.2 4,748 1

Index Scan using fki_blog_posts_blogs on posts bp1 (cost=0.43..1,155.97 rows=2,148 width=69) (actual time=0.027..42.943 rows=4,748 loops=1)

  • Index Cond: (id_blog = ANY ('{1,22,16}'::integer[]))
  • Filter: (NOT is_deleted)
  • Rows Removed by Filter: 1914
7. 14.244 6,400.304 ↑ 1.0 2 4,748

Subquery Scan on j1 (cost=133.12..133.14 rows=2 width=250) (actual time=1.346..1.348 rows=2 loops=4,748)

8. 4.748 6,386.060 ↑ 1.0 2 4,748

Limit (cost=133.12..133.12 rows=2 width=476) (actual time=1.344..1.345 rows=2 loops=4,748)

9. 99.708 6,381.312 ↑ 19.0 2 4,748

Sort (cost=133.12..133.21 rows=38 width=476) (actual time=1.344..1.344 rows=2 loops=4,748)

  • Sort Key: bc1.thread_date DESC
  • Sort Method: top-N heapsort Memory: 25kB
10. 959.096 6,281.604 ↑ 1.1 35 4,748

GroupAggregate (cost=131.88..132.74 rows=38 width=476) (actual time=1.116..1.323 rows=35 loops=4,748)

  • Group Key: bc1.id_comment
11. 213.660 5,322.508 ↓ 1.3 48 4,748

Sort (cost=131.88..131.98 rows=38 width=476) (actual time=1.107..1.121 rows=48 loops=4,748)

  • Sort Key: bc1.id_comment
  • Sort Method: quicksort Memory: 48kB
12. 200.444 5,108.848 ↓ 1.3 48 4,748

Nested Loop Left Join (cost=3.16..130.88 rows=38 width=476) (actual time=0.048..1.076 rows=48 loops=4,748)

13. 702.704 702.704 ↑ 1.1 35 4,748

Index Scan using idx_comments_posts_parents on comments bc1 (cost=0.56..31.32 rows=38 width=234) (actual time=0.017..0.148 rows=35 loops=4,748)

  • Index Cond: ((id_post = bp1.id_post) AND (id_parent = 0))
14. 168.228 4,205.700 ↑ 1.0 1 168,228

Subquery Scan on j2 (cost=2.59..2.61 rows=1 width=250) (actual time=0.025..0.025 rows=1 loops=168,228)

15. 0.000 4,037.472 ↑ 1.0 1 168,228

Limit (cost=2.59..2.60 rows=1 width=226) (actual time=0.024..0.024 rows=1 loops=168,228)

16. 672.912 4,037.472 ↑ 1.0 1 168,228

Sort (cost=2.59..2.60 rows=1 width=226) (actual time=0.024..0.024 rows=1 loops=168,228)

  • Sort Key: bc2.comment_number DESC
  • Sort Method: quicksort Memory: 25kB
17. 3,364.560 3,364.560 ↓ 4.0 4 168,228

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=168,228)

  • Index Cond: ((id_post = bc1.id_post) AND (id_root = bc1.id_comment))
  • Filter: (id_parent <> 0)
  • Rows Removed by Filter: 1
Planning time : 2.460 ms
Execution time : 6,534.541 ms