explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Itby

Settings
# exclusive inclusive rows x rows loops node
1. 0.032 886.867 ↑ 721,213.2 11 1

Hash Left Join (cost=1,403,526.21..2,264,592.87 rows=7,933,345 width=170) (actual time=885.156..886.867 rows=11 loops=1)

  • Hash Cond: (cte.comment_id = cl.fk_comment_id)
  • Buffers: shared hit=1289426, temp read=10 written=10
2.          

CTE cte

3. 882.815 882.815 ↑ 1,442,426.4 11 1

CTE Scan on included_childs c (cost=349,813.56..667,147.36 rows=15,866,690 width=68) (actual time=882.464..882.815 rows=11 loops=1)

  • Buffers: shared hit=1289263
4.          

CTE included_childs

5. 0.010 882.807 ↑ 1,442,426.4 11 1

Recursive Union (cost=2.81..349,813.56 rows=15,866,690 width=97) (actual time=882.463..882.807 rows=11 loops=1)

  • Buffers: shared hit=1289263
6. 0.008 882.473 ↑ 1.0 10 1

Limit (cost=2.81..5.03 rows=10 width=97) (actual time=882.460..882.473 rows=10 loops=1)

  • Buffers: shared hit=1289053
7. 0.010 882.465 ↑ 93,384.8 10 1

Merge Append (cost=2.81..207,344.53 rows=933,848 width=97) (actual time=882.457..882.465 rows=10 loops=1)

  • Sort Key: comments_2019_4.created DESC
  • Buffers: shared hit=1289053
8. 357.625 357.625 ↓ 0.0 0 1

Index Scan using comments_2019_4_created_idx on comments_2019_4 (cost=0.42..29,356.55 rows=23 width=76) (actual time=357.625..357.625 rows=0 loops=1)

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

Index Scan using comments_2019_5_created_idx on comments_2019_5 (cost=0.42..23,404.82 rows=23 width=65) (actual time=268.402..268.402 rows=0 loops=1)

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

Index Scan using comments_2019_6_created_idx on comments_2019_6 (cost=0.42..23,983.78 rows=22 width=67) (actual time=256.295..256.295 rows=0 loops=1)

  • Index Cond: (created >= '2019-04-01 00:00:00'::timestamp without time zone)
  • Filter: ((parent_comment_id IS NULL) AND (fk_topic_id = 31974028))
  • Rows Removed by Filter: 430375
  • Buffers: shared hit=407376
11. 0.029 0.029 ↑ 880,927.0 1 1

Index Scan using comments_2019_7_created_idx on comments_2019_7 (cost=0.43..106,641.03 rows=880,927 width=98) (actual time=0.028..0.029 rows=1 loops=1)

  • Index Cond: (created >= '2019-04-01 00:00:00'::timestamp without time zone)
  • Filter: ((parent_comment_id IS NULL) AND (fk_topic_id = 31974028))
  • Buffers: shared hit=4
12. 0.040 0.040 ↑ 52,260.0 1 1

Index Scan using comments_2019_8_created_idx on comments_2019_8 (cost=0.42..5,184.97 rows=52,260 width=87) (actual time=0.040..0.040 rows=1 loops=1)

  • Index Cond: (created >= '2019-04-01 00:00:00'::timestamp without time zone)
  • Filter: ((parent_comment_id IS NULL) AND (fk_topic_id = 31974028))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=4
13. 0.034 0.034 ↑ 147.8 4 1

Index Scan using comments_2019_9_created_idx on comments_2019_9 (cost=0.28..60.48 rows=591 width=89) (actual time=0.032..0.034 rows=4 loops=1)

  • Index Cond: (created >= '2019-04-01 00:00:00'::timestamp without time zone)
  • Filter: ((parent_comment_id IS NULL) AND (fk_topic_id = 31974028))
  • Rows Removed by Filter: 8
  • Buffers: shared hit=3
14. 0.023 0.023 ↓ 6.0 6 1

Index Scan using comments_2019_10_created_idx on comments_2019_10 (cost=0.15..17.91 rows=1 width=68) (actual time=0.018..0.023 rows=6 loops=1)

  • Index Cond: (created >= '2019-04-01 00:00:00'::timestamp without time zone)
  • Filter: ((parent_comment_id IS NULL) AND (fk_topic_id = 31974028))
  • Rows Removed by Filter: 18
  • Buffers: shared hit=2
15. 0.007 0.007 ↓ 0.0 0 1

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

  • Index Cond: (created >= '2019-04-01 00:00:00'::timestamp without time zone)
  • Filter: ((parent_comment_id IS NULL) AND (fk_topic_id = 31974028))
  • Buffers: shared hit=1
16. 0.012 0.324 ↓ 0.0 0 2

Nested Loop (cost=0.42..3,247.47 rows=1,586,668 width=85) (actual time=0.155..0.162 rows=0 loops=2)

  • Buffers: shared hit=210
17. 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)

18. 0.022 0.308 ↓ 0.0 0 11

Append (cost=0.42..32.11 rows=34 width=85) (actual time=0.028..0.028 rows=0 loops=11)

  • Buffers: shared hit=210
19. 0.044 0.044 ↓ 0.0 0 11

Index Scan using comments_2019_4_parent_comment_id_idx on comments_2019_4 comments_2019_4_1 (cost=0.42..8.09 rows=6 width=76) (actual time=0.004..0.004 rows=0 loops=11)

  • Index Cond: (parent_comment_id = ch.comment_id)
  • Filter: (created >= '2019-04-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=33
20. 0.033 0.033 ↓ 0.0 0 11

Index Scan using comments_2019_5_parent_comment_id_idx on comments_2019_5 comments_2019_5_1 (cost=0.42..6.97 rows=5 width=65) (actual time=0.003..0.003 rows=0 loops=11)

  • Index Cond: (parent_comment_id = ch.comment_id)
  • Filter: (created >= '2019-04-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=33
21. 0.033 0.033 ↓ 0.0 0 11

Index Scan using comments_2019_6_parent_comment_id_idx on comments_2019_6 comments_2019_6_1 (cost=0.42..6.95 rows=5 width=67) (actual time=0.003..0.003 rows=0 loops=11)

  • Index Cond: (parent_comment_id = ch.comment_id)
  • Filter: (created >= '2019-04-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=33
22. 0.033 0.033 ↓ 0.0 0 11

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.003..0.003 rows=0 loops=11)

  • Index Cond: (parent_comment_id = ch.comment_id)
  • Filter: (created >= '2019-04-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=33
23. 0.055 0.055 ↓ 0.0 0 11

Index Scan using comments_2019_8_parent_comment_id_idx on comments_2019_8 comments_2019_8_1 (cost=0.42..2.62 rows=4 width=87) (actual time=0.005..0.005 rows=0 loops=11)

  • Index Cond: (parent_comment_id = ch.comment_id)
  • Filter: (created >= '2019-04-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=33
24. 0.033 0.033 ↓ 0.0 0 11

Index Scan using comments_2019_9_parent_comment_id_idx on comments_2019_9 comments_2019_9_1 (cost=0.28..0.71 rows=2 width=89) (actual time=0.003..0.003 rows=0 loops=11)

  • Index Cond: (parent_comment_id = ch.comment_id)
  • Filter: (created >= '2019-04-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=23
25. 0.022 0.022 ↓ 0.0 0 11

Index Scan using comments_2019_10_parent_comment_id_idx on comments_2019_10 comments_2019_10_1 (cost=0.15..0.36 rows=1 width=68) (actual time=0.002..0.002 rows=0 loops=11)

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

Index Scan using comments_2019_11_parent_comment_id_idx on comments_2019_11 comments_2019_11_1 (cost=0.15..0.35 rows=1 width=68) (actual time=0.003..0.003 rows=0 loops=11)

  • Index Cond: (parent_comment_id = ch.comment_id)
  • Filter: (created >= '2019-04-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=11
27. 3.374 886.592 ↑ 721,213.2 11 1

Hash Right Join (cost=378,619.77..1,218,420.11 rows=7,933,345 width=162) (actual time=884.886..886.592 rows=11 loops=1)

  • Hash Cond: (cte.from_user_id = u.user_id)
  • Buffers: shared hit=1289303, temp read=10 written=10
28. 0.002 0.002 ↑ 1,442,426.4 11 1

CTE Scan on cte (cost=0.00..317,333.80 rows=15,866,690 width=68) (actual time=0.000..0.002 rows=11 loops=1)

29. 0.138 883.216 ↑ 75,243.3 10 1

Hash (cost=357,457.36..357,457.36 rows=752,433 width=98) (actual time=883.216..883.216 rows=10 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 1024kB
  • Buffers: shared hit=1289303
30. 0.013 883.078 ↑ 75,243.3 10 1

Nested Loop (cost=357,000.95..357,457.36 rows=752,433 width=98) (actual time=882.884..883.078 rows=10 loops=1)

  • Buffers: shared hit=1289303
31. 0.015 882.835 ↑ 20.0 10 1

HashAggregate (cost=357,000.52..357,002.52 rows=200 width=4) (actual time=882.831..882.835 rows=10 loops=1)

  • Group Key: cte_1.from_user_id
  • Buffers: shared hit=1289263
32. 882.820 882.820 ↑ 1,442,426.4 11 1

CTE Scan on cte cte_1 (cost=0.00..317,333.80 rows=15,866,690 width=4) (actual time=882.466..882.820 rows=11 loops=1)

  • Buffers: shared hit=1289263
33. 0.230 0.230 ↑ 1.0 1 10

Index Scan using pk_users on users u (cost=0.43..2.64 rows=1 width=98) (actual time=0.023..0.023 rows=1 loops=10)

  • Index Cond: (user_id = cte_1.from_user_id)
  • Buffers: shared hit=40
34. 0.007 0.243 ↑ 100.0 2 1

Hash (cost=357,756.58..357,756.58 rows=200 width=16) (actual time=0.243..0.243 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=123
35. 0.017 0.236 ↑ 100.0 2 1

HashAggregate (cost=357,752.58..357,754.58 rows=200 width=16) (actual time=0.234..0.236 rows=2 loops=1)

  • Group Key: cl.fk_comment_id
  • Buffers: shared hit=123
36. 0.010 0.219 ↑ 10,201.5 4 1

Nested Loop (cost=357,000.68..357,548.55 rows=40,806 width=8) (actual time=0.133..0.219 rows=4 loops=1)

  • Buffers: shared hit=123
37. 0.009 0.011 ↑ 18.2 11 1

HashAggregate (cost=357,000.52..357,002.52 rows=200 width=8) (actual time=0.008..0.011 rows=11 loops=1)

  • Group Key: cte_2.comment_id
38. 0.002 0.002 ↑ 1,442,426.4 11 1

CTE Scan on cte cte_2 (cost=0.00..317,333.80 rows=15,866,690 width=8) (actual time=0.000..0.002 rows=11 loops=1)

39. 0.022 0.198 ↓ 0.0 0 11

Append (cost=0.15..2.58 rows=15 width=8) (actual time=0.015..0.018 rows=0 loops=11)

  • Buffers: shared hit=123
40. 0.011 0.011 ↓ 0.0 0 11

Index Scan using comment_likes_2019_4_fk_comment_id_fk_user_id_idx on comment_likes_2019_4 cl (cost=0.15..0.29 rows=2 width=8) (actual time=0.001..0.001 rows=0 loops=11)

  • Index Cond: (fk_comment_id = cte_2.comment_id)
  • Filter: (created >= '2019-04-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=11
41. 0.011 0.011 ↓ 0.0 0 11

Index Scan using comment_likes_2019_5_fk_comment_id_fk_user_id_idx on comment_likes_2019_5 cl_1 (cost=0.15..0.29 rows=2 width=8) (actual time=0.001..0.001 rows=0 loops=11)

  • Index Cond: (fk_comment_id = cte_2.comment_id)
  • Filter: (created >= '2019-04-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=11
42. 0.011 0.011 ↓ 0.0 0 11

Index Scan using comment_likes_2019_6_fk_comment_id_fk_user_id_idx on comment_likes_2019_6 cl_2 (cost=0.15..0.29 rows=2 width=8) (actual time=0.001..0.001 rows=0 loops=11)

  • Index Cond: (fk_comment_id = cte_2.comment_id)
  • Filter: (created >= '2019-04-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=11
43. 0.011 0.011 ↓ 0.0 0 11

Index Scan using comment_likes_2019_7_fk_comment_id_fk_user_id_idx on comment_likes_2019_7 cl_3 (cost=0.15..0.29 rows=2 width=8) (actual time=0.001..0.001 rows=0 loops=11)

  • Index Cond: (fk_comment_id = cte_2.comment_id)
  • Filter: (created >= '2019-04-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=11
44. 0.055 0.055 ↓ 0.0 0 11

Index Scan using comment_likes_2019_8_fk_comment_id_fk_user_id_idx on comment_likes_2019_8 cl_4 (cost=0.42..0.46 rows=2 width=8) (actual time=0.005..0.005 rows=0 loops=11)

  • Index Cond: (fk_comment_id = cte_2.comment_id)
  • Filter: (created >= '2019-04-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=33
45. 0.033 0.033 ↓ 0.0 0 11

Index Scan using comment_likes_2019_9_fk_comment_id_fk_user_id_idx on comment_likes_2019_9 cl_5 (cost=0.27..0.29 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=11)

  • Index Cond: (fk_comment_id = cte_2.comment_id)
  • Filter: (created >= '2019-04-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=23
46. 0.033 0.033 ↓ 0.0 0 11

Index Scan using comment_likes_2019_10_fk_comment_id_fk_user_id_idx on comment_likes_2019_10 cl_6 (cost=0.15..0.29 rows=2 width=8) (actual time=0.003..0.003 rows=0 loops=11)

  • Index Cond: (fk_comment_id = cte_2.comment_id)
  • Filter: (created >= '2019-04-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=12
47. 0.011 0.011 ↓ 0.0 0 11

Index Scan using comment_likes_2019_11_fk_comment_id_fk_user_id_idx on comment_likes_2019_11 cl_7 (cost=0.15..0.29 rows=2 width=8) (actual time=0.001..0.001 rows=0 loops=11)

  • Index Cond: (fk_comment_id = cte_2.comment_id)
  • Filter: (created >= '2019-04-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=11
Planning time : 11.303 ms