explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VR6F

Settings
# exclusive inclusive rows x rows loops node
1. 0.044 1,070.799 ↑ 658,910.0 13 1

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

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

CTE included_childs

3. 0.007 0.165 ↑ 658,910.0 13 1

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

  • Buffers: shared hit=90
4. 0.006 0.054 ↑ 1.0 10 1

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

  • Buffers: shared hit=9
5. 0.011 0.048 ↑ 5.6 10 1

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

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

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

  • Buffers: shared hit=9
7. 0.023 0.023 ↑ 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.016..0.023 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.008..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.002..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.009 0.104 ↑ 428,291.0 2 2

Nested Loop (cost=0.43..957.10 rows=856,582 width=98) (actual time=0.033..0.052 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.001..0.002 rows=6 loops=2)

15. 0.000 0.091 ↓ 0.0 0 13

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

  • Buffers: shared hit=81
16. 0.065 0.065 ↓ 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.005..0.005 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. 76.004 1,070.510 ↑ 658,910.0 13 1

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

  • Hash Cond: (c.from_user_id = u.user_id)
  • Buffers: shared hit=26323 read=57534, temp read=4858 written=18785
20. 0.176 0.176 ↑ 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.047..0.176 rows=13 loops=1)

  • Buffers: shared hit=90
21. 431.750 994.330 ↑ 1.0 1,495,489 1

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

  • Buckets: 131072 Batches: 32 Memory Usage: 6392kB
  • Buffers: shared hit=26233 read=57534, temp written=18746
22. 562.580 562.580 ↑ 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.011..562.580 rows=1,495,489 loops=1)

  • Buffers: shared hit=26233 read=57534
23. 0.041 0.245 ↓ 1.0 203 1

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

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

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

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

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

  • Buffers: shared hit=16
26. 0.011 0.011 ↓ 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.011..0.011 rows=0 loops=1)

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

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

  • Filter: (created >= '2019-07-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=14
28. 0.005 0.005 ↓ 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.004..0.005 rows=0 loops=1)

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