explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BsEP

Settings
# exclusive inclusive rows x rows loops node
1. 7.112 11.922 ↑ 522,307.0 82 1

Hash Right Join (cost=5,673,314.89..10,322,645.45 rows=42,829,175 width=169) (actual time=5.800..11.922 rows=82 loops=1)

  • Hash Cond: (cte.from_user_id = u.user_id)
  • Buffers: shared hit=1285, temp read=26 written=26
2.          

CTE cte

3. 0.115 2.784 ↑ 1,044,614.0 82 1

Hash Join (cost=1,781,640.93..3,724,425.84 rows=85,658,350 width=76) (actual time=0.687..2.784 rows=82 loops=1)

  • Hash Cond: (c.comment_id = comment_likes_2019_7.fk_comment_id)
  • Buffers: shared hit=1093
4.          

CTE included_childs

5. 0.048 2.086 ↑ 644,047.7 133 1

Recursive Union (cost=0.89..1,781,561.93 rows=85,658,350 width=98) (actual time=0.148..2.086 rows=133 loops=1)

  • Buffers: shared hit=1077
6. 0.018 1.590 ↑ 1.0 100 1

Limit (cost=0.89..14.28 rows=100 width=98) (actual time=0.146..1.590 rows=100 loops=1)

  • Buffers: shared hit=246
7. 0.032 1.572 ↑ 8,866.0 100 1

Merge Append (cost=0.89..118,748.74 rows=886,605 width=98) (actual time=0.144..1.572 rows=100 loops=1)

  • Sort Key: comments_2019_7.created DESC
  • Buffers: shared hit=246
8. 0.060 0.060 ↑ 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.060..0.060 rows=1 loops=1)

  • Index Cond: (created >= '2019-07-01 00:00:00'::timestamp without time zone)
  • Filter: ((parent_comment_id IS NULL) AND (fk_topic_id = 31974028))
  • Buffers: shared hit=4
9. 1.467 1.467 ↑ 56.8 100 1

Index Scan using comments_2019_8_created_idx on comments_2019_8 (cost=0.29..630.57 rows=5,677 width=98) (actual time=0.069..1.467 rows=100 loops=1)

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

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

  • Index Cond: (created >= '2019-07-01 00:00:00'::timestamp without time zone)
  • Filter: ((parent_comment_id IS NULL) AND (fk_topic_id = 31974028))
  • Buffers: shared hit=1
11. 0.033 0.448 ↑ 535,364.1 16 2

Nested Loop (cost=0.43..6,838.07 rows=8,565,825 width=98) (actual time=0.105..0.224 rows=16 loops=2)

  • Buffers: shared hit=831
12. 0.016 0.016 ↑ 15.2 66 2

WorkTable Scan on included_childs ch (cost=0.00..20.00 rows=1,000 width=8) (actual time=0.001..0.008 rows=66 loops=2)

13. 0.133 0.399 ↓ 0.0 0 133

Append (cost=0.43..6.68 rows=14 width=98) (actual time=0.003..0.003 rows=0 loops=133)

  • Buffers: shared hit=831
14. 0.133 0.133 ↓ 0.0 0 133

Index Scan using comments_2019_7_parent_comment_id_idx on comments_2019_7 comments_2019_7_1 (cost=0.43..5.58 rows=10 width=98) (actual time=0.001..0.001 rows=0 loops=133)

  • Index Cond: (parent_comment_id = ch.comment_id)
  • Filter: (created >= '2019-07-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=399
15. 0.133 0.133 ↓ 0.0 0 133

Index Scan using comments_2019_8_parent_comment_id_idx on comments_2019_8 comments_2019_8_1 (cost=0.29..0.79 rows=3 width=98) (actual time=0.001..0.001 rows=0 loops=133)

  • Index Cond: (parent_comment_id = ch.comment_id)
  • Filter: (created >= '2019-07-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=299
16. 0.000 0.000 ↓ 0.0 0 133

Index Scan using comments_2019_9_parent_comment_id_idx on comments_2019_9 comments_2019_9_1 (cost=0.15..0.24 rows=1 width=68) (actual time=0.000..0.000 rows=0 loops=133)

  • Index Cond: (parent_comment_id = ch.comment_id)
  • Filter: (created >= '2019-07-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=133
17. 2.162 2.162 ↑ 644,047.7 133 1

CTE Scan on included_childs c (cost=0.00..1,713,167.00 rows=85,658,350 width=68) (actual time=0.149..2.162 rows=133 loops=1)

  • Buffers: shared hit=1077
18. 0.048 0.507 ↓ 1.0 203 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
  • Buffers: shared hit=16
19. 0.148 0.459 ↓ 1.0 203 1

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

  • Group Key: comment_likes_2019_7.fk_comment_id
  • Buffers: shared hit=16
20. 0.033 0.311 ↑ 3.9 330 1

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

  • Buffers: shared hit=16
21. 0.010 0.010 ↓ 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.010..0.010 rows=0 loops=1)

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

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

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

  • Index Cond: (created >= '2019-07-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=1
24. 0.022 0.022 ↑ 1,044,614.0 82 1

CTE Scan on cte (cost=0.00..1,713,167.00 rows=85,658,350 width=76) (actual time=0.001..0.022 rows=82 loops=1)

25. 0.303 4.788 ↑ 15,643.9 48 1

Hash (cost=1,927,769.71..1,927,769.71 rows=750,907 width=97) (actual time=4.787..4.788 rows=48 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 1025kB
  • Buffers: shared hit=1285
26. 0.044 4.485 ↑ 15,643.9 48 1

Nested Loop (cost=1,927,313.30..1,927,769.71 rows=750,907 width=97) (actual time=2.959..4.485 rows=48 loops=1)

  • Buffers: shared hit=1285
27. 0.054 2.905 ↑ 4.2 48 1

HashAggregate (cost=1,927,312.88..1,927,314.88 rows=200 width=4) (actual time=2.893..2.905 rows=48 loops=1)

  • Group Key: cte_1.from_user_id
  • Buffers: shared hit=1093
28. 2.851 2.851 ↑ 1,044,614.0 82 1

CTE Scan on cte cte_1 (cost=0.00..1,713,167.00 rows=85,658,350 width=4) (actual time=0.691..2.851 rows=82 loops=1)

  • Buffers: shared hit=1093
29. 1.536 1.536 ↑ 1.0 1 48

Index Scan using pk_users on users u (cost=0.43..2.64 rows=1 width=97) (actual time=0.032..0.032 rows=1 loops=48)

  • Index Cond: (user_id = cte_1.from_user_id)
  • Buffers: shared hit=192
Planning time : 5.098 ms