explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Zvh4

Settings
# exclusive inclusive rows x rows loops node
1. 6.525 6.525 ↑ 1,757,003.2 200 1

CTE Scan on included_childs (cost=9,062,290.97..16,090,303.97 rows=351,400,650 width=176) (actual time=0.092..6.525 rows=200 loops=1)

2.          

CTE included_childs

3. 0.390 6.082 ↑ 1,757,003.2 200 1

Recursive Union (cost=36.79..9,062,290.97 rows=351,400,650 width=182) (actual time=0.090..6.082 rows=200 loops=1)

4. 0.120 1.965 ↑ 1.0 100 1

Limit (cost=36.79..397.96 rows=100 width=182) (actual time=0.089..1.965 rows=100 loops=1)

5. 0.501 1.845 ↑ 10,225.8 100 1

Nested Loop Left Join (cost=36.79..3,693,277.49 rows=1,022,577 width=182) (actual time=0.087..1.845 rows=100 loops=1)

  • Join Filter: (comments_2019_2.comment_id = comment_likes.fk_comment_id)
  • Rows Removed by Join Filter: 300
6. 0.298 1.144 ↑ 10,225.8 100 1

Nested Loop Left Join (cost=1.29..640,692.86 rows=1,022,577 width=174) (actual time=0.068..1.144 rows=100 loops=1)

7. 0.120 0.546 ↑ 10,225.8 100 1

Merge Append (cost=1.01..338,811.91 rows=1,022,577 width=55) (actual time=0.061..0.546 rows=100 loops=1)

  • Sort Key: comments_2019_2.created DESC
8. 0.009 0.009 ↓ 0.0 0 1

Index Scan using comments_2019_2_created_idx on comments_2019_2 (cost=0.17..10.29 rows=1 width=60) (actual time=0.008..0.009 rows=0 loops=1)

  • Index Cond: ((created >= ((date_trunc('month'::text, now()) - ('0 month'::cstring)::interval))::timestamp without time
  • Filter: ((parent_comment_id IS NULL) AND (fk_topic_id = 1))
9. 0.004 0.004 ↓ 0.0 0 1

Index Scan using comments_2019_3_created_idx on comments_2019_3 (cost=0.17..10.20 rows=1 width=55) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: ((created >= ((date_trunc('month'::text, now()) - ('0 month'::cstring)::interval))::timestamp without time
  • Filter: ((parent_comment_id IS NULL) AND (fk_topic_id = 1))
10. 0.408 0.408 ↑ 10,225.7 100 1

Index Scan using comments_2019_4_created_idx on comments_2019_4 (cost=0.46..323,442.46 rows=1,022,574 width=55) (actual time=0.043..0.408 rows=100 loops=1)

  • Index Cond: ((created >= ((date_trunc('month'::text, now()) - ('0 month'::cstring)::interval))::timestamp without time
  • Filter: ((parent_comment_id IS NULL) AND (fk_topic_id = 1))
  • Rows Removed by Filter: 622
11. 0.005 0.005 ↓ 0.0 0 1

Index Scan using comments_2019_5_created_idx on comments_2019_5 (cost=0.17..10.29 rows=1 width=60) (actual time=0.004..0.005 rows=0 loops=1)

  • Index Cond: ((created >= ((date_trunc('month'::text, now()) - ('0 month'::cstring)::interval))::timestamp without time
  • Filter: ((parent_comment_id IS NULL) AND (fk_topic_id = 1))
12. 0.300 0.300 ↑ 1.0 1 100

Index Scan using pk_users on users (cost=0.28..0.30 rows=1 width=123) (actual time=0.003..0.003 rows=1 loops=100)

  • Index Cond: (comments_2019_2.from_user_id = user_id)
13. 0.187 0.200 ↑ 66.7 3 100

Materialize (cost=35.50..40.50 rows=200 width=12) (actual time=0.001..0.002 rows=3 loops=100)

14. 0.007 0.013 ↑ 66.7 3 1

HashAggregate (cost=35.50..37.50 rows=200 width=12) (actual time=0.010..0.013 rows=3 loops=1)

  • Group Key: comment_likes.fk_comment_id
15. 0.006 0.006 ↑ 566.7 3 1

Seq Scan on comment_likes (cost=0.00..27.00 rows=1,700 width=4) (actual time=0.004..0.006 rows=3 loops=1)

16. 0.224 3.727 ↑ 351,400.5 100 1

Hash Left Join (cost=166.29..203,388.00 rows=35,140,055 width=182) (actual time=2.230..3.727 rows=100 loops=1)

  • Hash Cond: (comments_2019_4_1.comment_id = comment_likes_1.fk_comment_id)
17. 0.224 3.486 ↑ 351,400.5 100 1

Hash Left Join (cost=124.29..109,148.69 rows=35,140,055 width=174) (actual time=2.207..3.486 rows=100 loops=1)

  • Hash Cond: (comments_2019_4_1.from_user_id = users_1.user_id)
18. 0.204 1.084 ↑ 351,400.5 100 1

Nested Loop (cost=0.15..16,538.50 rows=35,140,055 width=55) (actual time=0.020..1.084 rows=100 loops=1)

19. 0.013 0.013 ↑ 58.8 17 1

WorkTable Scan on included_childs ch (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.001..0.013 rows=17 loops=1)

20. 0.187 0.867 ↑ 3.5 6 17

Append (cost=0.15..16.31 rows=21 width=55) (actual time=0.014..0.051 rows=6 loops=17)

  • Subplans Removed: 3
21. 0.680 0.680 ↑ 3.0 6 17

Index Scan using comments_2019_4_parent_comment_id_idx on comments_2019_4 comments_2019_4_1 (cost=0.43..5.21 rows=18 width=55) (actual time=0.012..0.040 rows=6 loops=17)

  • Index Cond: (parent_comment_id = ch.comment_id)
  • Filter: ((created <= ((now() + '00:05:00'::interval))::timestamp without time zone) AND (created >= ((date_trunc('month'::text, now()) - ('0 month'::cstring)::interval))::timestamp without time zone))
22. 1.014 2.178 ↑ 1.0 1,606 1

Hash (cost=104.06..104.06 rows=1,606 width=123) (actual time=2.178..2.178 rows=1,606 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 216kB
23. 1.164 1.164 ↑ 1.0 1,606 1

Seq Scan on users users_1 (cost=0.00..104.06 rows=1,606 width=123) (actual time=0.005..1.164 rows=1,606 loops=1)

24. 0.003 0.017 ↑ 66.7 3 1

Hash (cost=39.50..39.50 rows=200 width=12) (actual time=0.017..0.017 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.009 0.014 ↑ 66.7 3 1

HashAggregate (cost=35.50..37.50 rows=200 width=12) (actual time=0.012..0.014 rows=3 loops=1)

  • Group Key: comment_likes_1.fk_comment_id
26. 0.005 0.005 ↑ 566.7 3 1

Seq Scan on comment_likes comment_likes_1 (cost=0.00..27.00 rows=1,700 width=4) (actual time=0.004..0.005 rows=3 loops=1)