explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MZbf

Settings
# exclusive inclusive rows x rows loops node
1. 0.048 1,105.511 ↑ 658,910.0 13 1

Hash Left Join (cost=322,024.24..763,015.99 rows=8,565,830 width=169) (actual time=1,029.921..1,105.511 rows=13 loops=1)

  • Hash Cond: (c.comment_id = comment_likes_2019_7.fk_comment_id)
  • Buffers: shared hit=26083 read=57790, temp read=4858 written=18785
2.          

CTE included_childs

3. 0.007 0.201 ↑ 658,910.0 13 1

Recursive Union (cost=33.81..180,921.43 rows=8,565,830 width=98) (actual time=0.053..0.201 rows=13 loops=1)

  • Buffers: shared hit=90
4. 0.008 0.066 ↑ 1.0 10 1

Limit (cost=33.81..33.83 rows=10 width=97) (actual time=0.053..0.066 rows=10 loops=1)

  • Buffers: shared hit=9
5. 0.022 0.058 ↑ 5.6 10 1

Sort (cost=33.81..33.95 rows=56 width=97) (actual time=0.052..0.058 rows=10 loops=1)

  • Sort Key: comments_2019_7.created DESC
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=9
6. 0.003 0.036 ↑ 4.0 14 1

Append (cost=0.43..32.60 rows=56 width=97) (actual time=0.015..0.036 rows=14 loops=1)

  • Buffers: shared hit=9
7. 0.021 0.021 ↑ 4.2 13 1

Index Scan using comments_2019_7_fk_topic_id_idx on comments_2019_7 (cost=0.43..25.89 rows=54 width=98) (actual time=0.014..0.021 rows=13 loops=1)

  • Index Cond: (fk_topic_id = 31611282)
  • Filter: ((parent_comment_id IS NULL) AND (created >= '2019-07-01 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 5
  • Buffers: shared hit=5
8. 0.009 0.009 ↑ 1.0 1 1

Index Scan using comments_2019_8_fk_topic_id_idx on comments_2019_8 (cost=0.29..2.51 rows=1 width=98) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (fk_topic_id = 31611282)
  • Filter: ((parent_comment_id IS NULL) AND (created >= '2019-07-01 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=3
9. 0.001 0.003 ↓ 0.0 0 1

Bitmap Heap Scan on comments_2019_9 (cost=2.81..3.93 rows=1 width=68) (actual time=0.003..0.003 rows=0 loops=1)

  • Recheck Cond: ((parent_comment_id IS NULL) AND (fk_topic_id = 31611282))
  • Filter: (created >= '2019-07-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=1
10. 0.000 0.002 ↓ 0.0 0 1

BitmapAnd (cost=2.81..2.81 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)

  • Buffers: shared hit=1
11. 0.002 0.002 ↓ 0.0 0 1

Bitmap Index Scan on comments_2019_9_parent_comment_id_idx (cost=0.00..1.28 rows=4 width=0) (actual time=0.001..0.002 rows=0 loops=1)

  • Index Cond: (parent_comment_id IS NULL)
  • Buffers: shared hit=1
12. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on comments_2019_9_fk_topic_id_idx (cost=0.00..1.28 rows=4 width=0) (never executed)

  • Index Cond: (fk_topic_id = 31611282)
13. 0.007 0.128 ↑ 428,291.0 2 2

Nested Loop (cost=0.43..957.10 rows=856,582 width=98) (actual time=0.044..0.064 rows=2 loops=2)

  • Buffers: shared hit=81
14. 0.004 0.004 ↑ 16.7 6 2

WorkTable Scan on included_childs ch (cost=0.00..2.00 rows=100 width=8) (actual time=0.002..0.002 rows=6 loops=2)

15. 0.013 0.117 ↓ 0.0 0 13

Append (cost=0.43..9.41 rows=14 width=98) (actual time=0.008..0.009 rows=0 loops=13)

  • Buffers: shared hit=81
16. 0.078 0.078 ↓ 0.0 0 13

Index Scan using comments_2019_7_parent_comment_id_idx on comments_2019_7 comments_2019_7_1 (cost=0.43..5.90 rows=10 width=98) (actual time=0.006..0.006 rows=0 loops=13)

  • Index Cond: (parent_comment_id = ch.comment_id)
  • Filter: (created >= '2019-07-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=42
17. 0.013 0.013 ↓ 0.0 0 13

Index Scan using comments_2019_8_parent_comment_id_idx on comments_2019_8 comments_2019_8_1 (cost=0.29..3.09 rows=3 width=98) (actual time=0.001..0.001 rows=0 loops=13)

  • Index Cond: (parent_comment_id = ch.comment_id)
  • Filter: (created >= '2019-07-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=26
18. 0.013 0.013 ↓ 0.0 0 13

Index Scan using comments_2019_9_parent_comment_id_idx on comments_2019_9 comments_2019_9_1 (cost=0.15..0.35 rows=1 width=68) (actual time=0.001..0.001 rows=0 loops=13)

  • Index Cond: (parent_comment_id = ch.comment_id)
  • Filter: (created >= '2019-07-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=13
19. 84.687 1,105.203 ↑ 658,910.0 13 1

Hash Left Join (cost=141,023.82..559,053.79 rows=8,565,830 width=161) (actual time=1,029.644..1,105.203 rows=13 loops=1)

  • Hash Cond: (c.from_user_id = u.user_id)
  • Buffers: shared hit=26067 read=57790, temp read=4858 written=18785
20. 0.207 0.207 ↑ 658,910.0 13 1

CTE Scan on included_childs c (cost=0.00..171,316.60 rows=8,565,830 width=68) (actual time=0.054..0.207 rows=13 loops=1)

  • Buffers: shared hit=90
21. 442.873 1,020.309 ↑ 1.0 1,495,489 1

Hash (cost=98,785.14..98,785.14 rows=1,501,814 width=97) (actual time=1,020.309..1,020.309 rows=1,495,489 loops=1)

  • Buckets: 131072 Batches: 32 Memory Usage: 6392kB
  • Buffers: shared hit=25977 read=57790, temp written=18746
22. 577.436 577.436 ↑ 1.0 1,495,489 1

Seq Scan on users u (cost=0.00..98,785.14 rows=1,501,814 width=97) (actual time=0.015..577.436 rows=1,495,489 loops=1)

  • Buffers: shared hit=25977 read=57790
23. 0.025 0.260 ↓ 1.0 203 1

Hash (cost=76.49..76.49 rows=200 width=16) (actual time=0.260..0.260 rows=203 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
  • Buffers: shared hit=16
24. 0.095 0.235 ↓ 1.0 203 1

HashAggregate (cost=72.49..74.49 rows=200 width=16) (actual time=0.212..0.235 rows=203 loops=1)

  • Group Key: comment_likes_2019_7.fk_comment_id
  • Buffers: shared hit=16
25. 0.033 0.140 ↑ 3.9 330 1

Append (cost=0.15..66.02 rows=1,296 width=8) (actual time=0.028..0.140 rows=330 loops=1)

  • Buffers: shared hit=16
26. 0.013 0.013 ↓ 0.0 0 1

Index Scan using comment_likes_2019_7_created_idx on comment_likes_2019_7 (cost=0.15..20.71 rows=483 width=8) (actual time=0.013..0.013 rows=0 loops=1)

  • Index Cond: (created >= '2019-07-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=1
27. 0.087 0.087 ↑ 1.0 330 1

Seq Scan on comment_likes_2019_8 (cost=0.00..18.12 rows=330 width=8) (actual time=0.014..0.087 rows=330 loops=1)

  • Filter: (created >= '2019-07-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=14
28. 0.007 0.007 ↓ 0.0 0 1

Index Scan using comment_likes_2019_9_created_idx on comment_likes_2019_9 (cost=0.15..20.71 rows=483 width=8) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: (created >= '2019-07-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=1
Planning time : 2.772 ms