explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LfJY

Settings
# exclusive inclusive rows x rows loops node
1. 0.282 135.380 ↑ 50,036.5 400 1

Hash Left Join (cost=493,757.28..1,000,243.69 rows=20,014,600 width=158) (actual time=132.170..135.380 rows=400 loops=1)

  • Hash Cond: (c.comment_id = comment_likes_2019_10.fk_comment_id)
  • Buffers: shared hit=2553
2.          

CTE included_childs

3. 0.230 2.407 ↑ 50,036.5 400 1

Recursive Union (cost=0.58..490,587.97 rows=20,014,600 width=49) (actual time=0.017..2.407 rows=400 loops=1)

  • Buffers: shared hit=1668
4. 0.049 0.231 ↑ 1.0 100 1

Limit (cost=0.58..22.20 rows=100 width=49) (actual time=0.017..0.231 rows=100 loops=1)

  • Buffers: shared hit=13
5. 0.047 0.182 ↑ 985.0 100 1

Merge Append (cost=0.58..21,297.18 rows=98,496 width=49) (actual time=0.016..0.182 rows=100 loops=1)

  • Sort Key: comments_2019_10.created DESC
  • Buffers: shared hit=13
6. 0.133 0.133 ↑ 985.0 100 1

Index Scan using comments_2019_10_created_idx on comments_2019_10 (cost=0.42..20,262.40 rows=98,495 width=49) (actual time=0.014..0.133 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
7. 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
8. 0.242 1.946 ↑ 13,343.0 150 2

Nested Loop (cost=0.42..9,027.38 rows=2,001,450 width=49) (actual time=0.552..0.973 rows=150 loops=2)

  • Buffers: shared hit=1655
9. 0.104 0.104 ↑ 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.052 rows=200 loops=2)

10. 0.800 1.600 ↑ 7.0 1 400

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

  • Buffers: shared hit=1655
11. 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..8.63 rows=6 width=49) (actual time=0.001..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
12. 0.000 0.000 ↓ 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.000..0.000 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
13. 0.306 38.899 ↑ 50,036.5 400 1

Hash Left Join (cost=1,641.05..454,475.82 rows=20,014,600 width=150) (actual time=35.957..38.899 rows=400 loops=1)

  • Hash Cond: (c.from_user_id = u.user_id)
  • Buffers: shared hit=2184
14. 2.693 2.693 ↑ 50,036.5 400 1

CTE Scan on included_childs c (cost=0.00..400,292.00 rows=20,014,600 width=68) (actual time=0.018..2.693 rows=400 loops=1)

  • Buffers: shared hit=1668
15. 18.098 35.900 ↑ 1.0 50,002 1

Hash (cost=1,016.02..1,016.02 rows=50,002 width=86) (actual time=35.899..35.900 rows=50,002 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3442kB
  • Buffers: shared hit=516
16. 17.802 17.802 ↑ 1.0 50,002 1

Seq Scan on users u (cost=0.00..1,016.02 rows=50,002 width=86) (actual time=0.005..17.802 rows=50,002 loops=1)

  • Buffers: shared hit=516
17. 16.671 96.199 ↓ 250.0 50,000 1

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

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

HashAggregate (cost=1,521.76..1,523.76 rows=200 width=16) (actual time=61.228..79.528 rows=50,000 loops=1)

  • Group Key: comment_likes_2019_10.fk_comment_id
  • Buffers: shared hit=369
19. 23.677 40.117 ↑ 1.0 50,000 1

Append (cost=0.00..1,269.35 rows=50,483 width=8) (actual time=0.009..40.117 rows=50,000 loops=1)

  • Buffers: shared hit=369
20. 16.433 16.433 ↑ 1.0 50,000 1

Seq Scan on comment_likes_2019_10 (cost=0.00..993.00 rows=50,000 width=8) (actual time=0.008..16.433 rows=50,000 loops=1)

  • Filter: (created >= '2019-10-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=368
21. 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
22. 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
Planning time : 1.471 ms