explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xEuS

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 827.204 ↑ 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=825.634..827.204 rows=11 loops=1)

  • Hash Cond: (cte.comment_id = cl.fk_comment_id)
2.          

CTE cte

3. 824.151 824.151 ↑ 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=823.976..824.151 rows=11 loops=1)

4.          

CTE included_childs

5. 0.006 824.146 ↑ 1,442,426.4 11 1

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

6. 0.006 823.984 ↑ 1.0 10 1

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

7. 0.010 823.978 ↑ 93,384.8 10 1

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

  • Sort Key: comments_2019_4.created DESC
8. 322.755 322.755 ↓ 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=322.755..322.755 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
9. 268.033 268.033 ↓ 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.033..268.033 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
10. 233.087 233.087 ↓ 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=233.087..233.087 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
11. 0.024 0.024 ↑ 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.024..0.024 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))
12. 0.029 0.029 ↑ 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.029..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))
  • Rows Removed by Filter: 1
13. 0.018 0.018 ↑ 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.016..0.018 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
14. 0.015 0.015 ↓ 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.010..0.015 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
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))
16. 0.009 0.156 ↓ 0.0 0 2

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

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.011 0.143 ↓ 0.0 0 11

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

19. 0.022 0.022 ↓ 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.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)
20. 0.022 0.022 ↓ 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.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)
21. 0.022 0.022 ↓ 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.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)
22. 0.022 0.022 ↓ 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.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)
23. 0.011 0.011 ↓ 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.001..0.001 rows=0 loops=11)

  • Index Cond: (parent_comment_id = ch.comment_id)
  • Filter: (created >= '2019-04-01 00:00:00'::timestamp without time zone)
24. 0.011 0.011 ↓ 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.001..0.001 rows=0 loops=11)

  • Index Cond: (parent_comment_id = ch.comment_id)
  • Filter: (created >= '2019-04-01 00:00:00'::timestamp without time zone)
25. 0.011 0.011 ↓ 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.001..0.001 rows=0 loops=11)

  • Index Cond: (parent_comment_id = ch.comment_id)
  • Filter: (created >= '2019-04-01 00:00:00'::timestamp without time zone)
26. 0.011 0.011 ↓ 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.001..0.001 rows=0 loops=11)

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

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

  • Hash Cond: (cte.from_user_id = u.user_id)
28. 0.003 0.003 ↑ 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.001..0.003 rows=11 loops=1)

29. 0.169 824.479 ↑ 75,243.3 10 1

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

  • Buckets: 131072 Batches: 16 Memory Usage: 1024kB
30. 0.010 824.310 ↑ 75,243.3 10 1

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

31. 0.014 824.170 ↑ 20.0 10 1

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

  • Group Key: cte_1.from_user_id
32. 824.156 824.156 ↑ 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=823.977..824.156 rows=11 loops=1)

33. 0.130 0.130 ↑ 1.0 1 10

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

  • Index Cond: (user_id = cte_1.from_user_id)
34. 0.002 0.133 ↑ 100.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
35. 0.008 0.131 ↑ 100.0 2 1

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

  • Group Key: cl.fk_comment_id
36. 0.013 0.123 ↑ 10,201.5 4 1

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

37. 0.008 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.003 0.003 ↑ 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.001..0.003 rows=11 loops=1)

39. 0.011 0.099 ↓ 0.0 0 11

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

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)
41. 0.000 0.000 ↓ 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.000..0.000 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)
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)
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)
44. 0.022 0.022 ↓ 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.002..0.002 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)
45. 0.011 0.011 ↓ 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.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)
46. 0.011 0.011 ↓ 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.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)
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)
Planning time : 4.532 ms