explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lqRa

Settings
# exclusive inclusive rows x rows loops node
1. 0.315 98.336 ↑ 50,069.5 100 1

Hash Right Join (cost=735,023.35..1,022,921.96 rows=5,006,950 width=158) (actual time=97.880..98.336 rows=100 loops=1)

  • Hash Cond: (cte.from_user_id = u.user_id)
  • Buffers: shared hit=2040
2.          

CTE cte

3. 0.404 97.170 ↑ 25,034.8 400 1

Hash Left Join (cost=280,322.56..507,444.07 rows=10,013,900 width=76) (actual time=92.888..97.170 rows=400 loops=1)

  • Hash Cond: (c.comment_id = comment_likes_2019_10.fk_comment_id)
  • Buffers: shared hit=2037
4.          

CTE included_childs

5. 0.332 3.477 ↑ 25,034.8 400 1

Recursive Union (cost=0.58..278,800.37 rows=10,013,900 width=49) (actual time=0.015..3.477 rows=400 loops=1)

  • Buffers: shared hit=1668
6. 0.091 0.303 ↑ 1.0 100 1

Limit (cost=0.58..31.51 rows=100 width=49) (actual time=0.014..0.303 rows=100 loops=1)

  • Buffers: shared hit=13
7. 0.065 0.212 ↑ 497.1 100 1

Merge Append (cost=0.58..15,378.03 rows=49,714 width=49) (actual time=0.013..0.212 rows=100 loops=1)

  • Sort Key: comments_2019_10.created DESC
  • Buffers: shared hit=13
8. 0.145 0.145 ↑ 497.1 100 1

Index Scan using comments_2019_10_created_idx on comments_2019_10 (cost=0.42..14,831.07 rows=49,713 width=49) (actual time=0.011..0.145 rows=100 loops=1)

  • Index Cond: (created >= '2019-10-01 00:00:00'::timestamp without time zone)
  • Filter: ((parent_comment_id IS NULL) AND (fk_topic_id = 1))
  • Rows Removed by Filter: 309
  • Buffers: shared hit=12
9. 0.002 0.002 ↓ 0.0 0 1

Index Scan using comments_2019_11_created_idx on comments_2019_11 (cost=0.15..49.81 rows=1 width=68) (actual time=0.001..0.002 rows=0 loops=1)

  • Index Cond: (created >= '2019-10-01 00:00:00'::timestamp without time zone)
  • Filter: ((parent_comment_id IS NULL) AND (fk_topic_id = 1))
  • Buffers: shared hit=1
10. 0.692 2.842 ↑ 6,675.9 150 2

Nested Loop (cost=0.42..7,849.09 rows=1,001,380 width=49) (actual time=0.792..1.421 rows=150 loops=2)

  • Buffers: shared hit=1655
11. 0.150 0.150 ↑ 5.0 200 2

WorkTable Scan on included_childs ch (cost=0.00..20.00 rows=1,000 width=8) (actual time=0.001..0.075 rows=200 loops=2)

12. 0.800 2.000 ↑ 7.0 1 400

Append (cost=0.42..7.76 rows=7 width=49) (actual time=0.004..0.005 rows=1 loops=400)

  • Buffers: shared hit=1655
13. 0.800 0.800 ↑ 6.0 1 400

Index Scan using comments_2019_10_parent_comment_id_idx on comments_2019_10 comments_2019_10_1 (cost=0.42..7.45 rows=6 width=49) (actual time=0.002..0.002 rows=1 loops=400)

  • Index Cond: (parent_comment_id = ch.comment_id)
  • Filter: (created >= '2019-10-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=1255
14. 0.400 0.400 ↓ 0.0 0 400

Index Scan using comments_2019_11_parent_comment_id_idx on comments_2019_11 comments_2019_11_1 (cost=0.15..0.27 rows=1 width=68) (actual time=0.001..0.001 rows=0 loops=400)

  • Index Cond: (parent_comment_id = ch.comment_id)
  • Filter: (created >= '2019-10-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=400
15. 3.901 3.901 ↑ 25,034.8 400 1

CTE Scan on included_childs c (cost=0.00..200,278.00 rows=10,013,900 width=68) (actual time=0.015..3.901 rows=400 loops=1)

  • Buffers: shared hit=1668
16. 16.153 92.865 ↓ 250.0 50,000 1

Hash (cost=1,519.69..1,519.69 rows=200 width=16) (actual time=92.865..92.865 rows=50,000 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2856kB
  • Buffers: shared hit=369
17. 37.521 76.712 ↓ 250.0 50,000 1

HashAggregate (cost=1,515.69..1,517.69 rows=200 width=16) (actual time=58.640..76.712 rows=50,000 loops=1)

  • Group Key: comment_likes_2019_10.fk_comment_id
  • Buffers: shared hit=369
18. 23.220 39.191 ↑ 1.0 50,000 1

Append (cost=0.00..1,264.62 rows=50,213 width=8) (actual time=0.005..39.191 rows=50,000 loops=1)

  • Buffers: shared hit=369
19. 15.964 15.964 ↓ 1.0 50,000 1

Seq Scan on comment_likes_2019_10 (cost=0.00..989.62 rows=49,730 width=8) (actual time=0.004..15.964 rows=50,000 loops=1)

  • Filter: (created >= '2019-10-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=368
20. 0.002 0.007 ↓ 0.0 0 1

Bitmap Heap Scan on comment_likes_2019_11 (cost=7.90..23.93 rows=483 width=8) (actual time=0.007..0.007 rows=0 loops=1)

  • Recheck Cond: (created >= '2019-10-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=1
21. 0.005 0.005 ↓ 0.0 0 1

Bitmap Index Scan on comment_likes_2019_11_created_idx (cost=0.00..7.78 rows=483 width=0) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (created >= '2019-10-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=1
22. 0.159 0.159 ↑ 25,034.8 400 1

CTE Scan on cte (cost=0.00..200,278.00 rows=10,013,900 width=76) (actual time=0.001..0.159 rows=400 loops=1)

23. 0.004 97.862 ↑ 24,812.0 1 1

Hash (cost=227,269.12..227,269.12 rows=24,812 width=86) (actual time=97.862..97.862 rows=1 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 257kB
  • Buffers: shared hit=2040
24. 0.006 97.858 ↑ 24,812.0 1 1

Merge Join (cost=225,322.68..227,269.12 rows=24,812 width=86) (actual time=97.855..97.858 rows=1 loops=1)

  • Merge Cond: (u.user_id = cte_1.from_user_id)
  • Buffers: shared hit=2040
25. 0.009 0.009 ↑ 16,541.7 3 1

Index Scan using pk_users on users u (cost=0.29..1,819.66 rows=49,625 width=86) (actual time=0.006..0.009 rows=3 loops=1)

  • Buffers: shared hit=3
26. 0.007 97.843 ↑ 100.0 2 1

Sort (cost=225,322.39..225,322.89 rows=200 width=4) (actual time=97.842..97.843 rows=2 loops=1)

  • Sort Key: cte_1.from_user_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2037
27. 0.221 97.836 ↑ 100.0 2 1

HashAggregate (cost=225,312.75..225,314.75 rows=200 width=4) (actual time=97.835..97.836 rows=2 loops=1)

  • Group Key: cte_1.from_user_id
  • Buffers: shared hit=2037
28. 97.615 97.615 ↑ 25,034.8 400 1

CTE Scan on cte cte_1 (cost=0.00..200,278.00 rows=10,013,900 width=4) (actual time=92.892..97.615 rows=400 loops=1)

  • Buffers: shared hit=2037
Planning time : 1.706 ms