explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Kt0e

Settings
# exclusive inclusive rows x rows loops node
1. 0.042 420.113 ↑ 144,876.0 42 1

Hash Left Join (cost=1,965,690.92..2,643,261.34 rows=6,084,790 width=317) (actual time=418.163..420.113 rows=42 loops=1)

  • Hash Cond: (cte.comment_id = cl.fk_comment_id)
  • Functions: 81
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 6.616 ms, Inlining 6.158 ms, Optimization 253.803 ms, Emission 156.555 ms, Total 423.133 ms"Execution Time: 426.916 ms
2.          

CTE cte

3. 417.368 417.368 ↑ 289,751.9 42 1

CTE Scan on included_childs c (cost=250,280.76..493,672.36 rows=12,169,580 width=68) (actual time=416.979..417.368 rows=42 loops=1)

4.          

CTE included_childs

5. 0.031 417.336 ↑ 289,751.9 42 1

Recursive Union (cost=0.73..250,280.76 rows=12,169,580 width=82) (actual time=416.978..417.336 rows=42 loops=1)

6. 416.963 417.029 ↑ 1.0 20 1

Limit (cost=0.73..3.04 rows=20 width=82) (actual time=416.976..417.029 rows=20 loops=1)

7. 0.011 0.066 ↑ 21,551.0 20 1

Append (cost=0.73..49,798.03 rows=431,020 width=82) (actual time=0.022..0.066 rows=20 loops=1)

8. 0.013 0.013 ↓ 0.0 0 1

Index Scan using comments_2020_9_created_idx on comments_2020_9 (cost=0.15..17.91 rows=1 width=68) (actual time=0.013..0.013 rows=0 loops=1)

  • Index Cond: (created >= '2020-07-01 00:00:00'::timestamp without time zone)
  • Filter: ((parent_comment_id IS NULL) AND (fk_topic_id = 1))
9. 0.009 0.009 ↑ 1.0 1 1

Index Scan using comments_2020_8_created_idx on comments_2020_8 (cost=0.15..17.91 rows=1 width=68) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: (created >= '2020-07-01 00:00:00'::timestamp without time zone)
  • Filter: ((parent_comment_id IS NULL) AND (fk_topic_id = 1))
10. 0.033 0.033 ↑ 22,685.2 19 1

Index Scan using comments_2020_7_created_idx on comments_2020_7 (cost=0.43..47,607.11 rows=431,018 width=82) (actual time=0.014..0.033 rows=19 loops=1)

  • Index Cond: (created >= '2020-07-01 00:00:00'::timestamp without time zone)
  • Filter: ((parent_comment_id IS NULL) AND (fk_topic_id = 1))
  • Rows Removed by Filter: 91
11. 0.052 0.276 ↑ 110,632.4 11 2

Nested Loop (cost=0.43..688.61 rows=1,216,956 width=82) (actual time=0.063..0.138 rows=11 loops=2)

12. 0.014 0.014 ↑ 9.5 21 2

WorkTable Scan on included_childs ch (cost=0.00..4.00 rows=200 width=8) (actual time=0.001..0.007 rows=21 loops=2)

13. 0.042 0.210 ↑ 7.0 1 42

Append (cost=0.43..3.35 rows=7 width=82) (actual time=0.004..0.005 rows=1 loops=42)

14. 0.084 0.084 ↑ 5.0 1 42

Index Scan using comments_2020_7_parent_comment_id_idx on comments_2020_7 comments_2020_7_1 (cost=0.43..2.73 rows=5 width=82) (actual time=0.001..0.002 rows=1 loops=42)

  • Index Cond: (parent_comment_id = ch.comment_id)
  • Filter: (created >= '2020-07-01 00:00:00'::timestamp without time zone)
15. 0.042 0.042 ↓ 0.0 0 42

Index Scan using comments_2020_8_parent_comment_id_idx on comments_2020_8 comments_2020_8_1 (cost=0.15..0.30 rows=1 width=68) (actual time=0.001..0.001 rows=0 loops=42)

  • Index Cond: (parent_comment_id = ch.comment_id)
  • Filter: (created >= '2020-07-01 00:00:00'::timestamp without time zone)
16. 0.042 0.042 ↓ 0.0 0 42

Index Scan using comments_2020_9_parent_comment_id_idx on comments_2020_9 comments_2020_9_1 (cost=0.15..0.29 rows=1 width=68) (actual time=0.001..0.001 rows=0 loops=42)

  • Index Cond: (parent_comment_id = ch.comment_id)
  • Filter: (created >= '2020-07-01 00:00:00'::timestamp without time zone)
17. 2.190 419.924 ↑ 144,876.0 42 1

Hash Right Join (cost=1,196,551.05..1,857,810.44 rows=6,084,790 width=309) (actual time=418.006..419.924 rows=42 loops=1)

  • Hash Cond: (cte.from_user_id = u.user_id)
18. 0.014 0.014 ↑ 289,751.9 42 1

CTE Scan on cte (cost=0.00..243,391.60 rows=12,169,580 width=68) (actual time=0.001..0.014 rows=42 loops=1)

19. 0.154 417.720 ↑ 65,650.0 12 1

Hash (cost=1,160,545.55..1,160,545.55 rows=787,800 width=245) (actual time=417.720..417.720 rows=12 loops=1)

  • Buckets: 16,384 Batches: 128 Memory Usage: 128kB
20. 0.021 417.566 ↑ 65,650.0 12 1

Nested Loop Left Join (cost=273,815.98..1,160,545.55 rows=787,800 width=245) (actual time=417.455..417.566 rows=12 loops=1)

21. 0.017 417.509 ↑ 65,650.0 12 1

Nested Loop (cost=273,815.98..274,270.55 rows=787,800 width=99) (actual time=417.439..417.509 rows=12 loops=1)

22. 0.035 417.432 ↑ 16.7 12 1

HashAggregate (cost=273,815.55..273,817.55 rows=200 width=4) (actual time=417.426..417.432 rows=12 loops=1)

  • Group Key: cte_1.from_user_id
23. 417.397 417.397 ↑ 289,751.9 42 1

CTE Scan on cte cte_1 (cost=0.00..243,391.60 rows=12,169,580 width=4) (actual time=416.980..417.397 rows=42 loops=1)

24. 0.060 0.060 ↑ 1.0 1 12

Index Scan using pk_users on users u (cost=0.43..2.63 rows=1 width=99) (actual time=0.005..0.005 rows=1 loops=12)

  • Index Cond: (user_id = cte_1.from_user_id)
25. 0.012 0.036 ↓ 0.0 0 12

Limit (cost=0.00..1.10 rows=1 width=166) (actual time=0.003..0.003 rows=0 loops=12)

26. 0.024 0.024 ↓ 0.0 0 12

Seq Scan on user_images u1_1 (cost=0.00..1.10 rows=1 width=166) (actual time=0.002..0.002 rows=0 loops=12)

  • Filter: ((u.user_id = user_id) AND (picture_type = 1))
  • Rows Removed by Filter: 5
27. 0.004 0.147 ↑ 50.0 4 1

Hash (cost=275,465.01..275,465.01 rows=200 width=16) (actual time=0.147..0.147 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
28. 0.009 0.143 ↑ 50.0 4 1

HashAggregate (cost=275,461.01..275,463.01 rows=200 width=16) (actual time=0.141..0.143 rows=4 loops=1)

  • Group Key: cl.fk_comment_id
29. 0.045 0.134 ↑ 61,812.8 5 1

Nested Loop (cost=273,815.97..273,915.69 rows=309,064 width=8) (actual time=0.054..0.134 rows=5 loops=1)

30. 0.034 0.047 ↑ 4.8 42 1

HashAggregate (cost=273,815.55..273,817.55 rows=200 width=8) (actual time=0.033..0.047 rows=42 loops=1)

  • Group Key: cte_2.comment_id
31. 0.013 0.013 ↑ 289,751.9 42 1

CTE Scan on cte cte_2 (cost=0.00..243,391.60 rows=12,169,580 width=8) (actual time=0.001..0.013 rows=42 loops=1)

32. 0.042 0.042 ↓ 0.0 0 42

Index Scan using comment_likes_2020_7_fk_comment_id_fk_user_id_idx on comment_likes_2020_7 cl (cost=0.42..0.47 rows=2 width=8) (actual time=0.001..0.001 rows=0 loops=42)

  • Index Cond: (fk_comment_id = cte_2.comment_id)
  • Filter: ((created >= '2020-07-01 00:00:00'::timestamp without time zone) AND (created < '2020-08-01 00:00:00'::timestamp without time zone))