explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PJ9U

Settings
# exclusive inclusive rows x rows loops node
1. 5.149 3,967.775 ↑ 9,157.4 82 1

Hash Right Join (cost=21,848.84..33,610.24 rows=750,907 width=169) (actual time=3,962.795..3,967.775 rows=82 loops=1)

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

CTE cte

3. 0.062 3,961.874 ↑ 6.1 82 1

Hash Join (cost=250.03..261.40 rows=501 width=76) (actual time=1,881.736..3,961.874 rows=82 loops=1)

  • Hash Cond: (c_1.comment_id = comment_likes_2019_7.fk_comment_id)
  • Buffers: shared hit=2521788, temp read=67074 written=44716
4.          

CTE included_childs

5. 0.048 3,961.508 ↑ 3.8 133 1

Recursive Union (cost=12.76..171.03 rows=501 width=68) (actual time=1,881.437..3,961.508 rows=133 loops=1)

  • Buffers: shared hit=2521772, temp read=67074 written=44716
6. 0.010 1,881.452 ↓ 100.0 100 1

Limit (cost=12.76..12.77 rows=1 width=68) (actual time=1,881.435..1,881.452 rows=100 loops=1)

  • Buffers: shared hit=1260886, temp read=22358 written=22358
7. 102.348 1,881.442 ↓ 100.0 100 1

Sort (cost=12.76..12.77 rows=1 width=68) (actual time=1,881.433..1,881.442 rows=100 loops=1)

  • Sort Key: soi_vw_comments_date_range2.created DESC
  • Sort Method: top-N heapsort Memory: 40kB
  • Buffers: shared hit=1260886, temp read=22358 written=22358
8. 1,779.094 1,779.094 ↓ 804,051.0 804,051 1

Function Scan on soi_vw_comments_date_range2 (cost=0.25..12.75 rows=1 width=68) (actual time=1,490.481..1,779.094 rows=804,051 loops=1)

  • Filter: ((parent_comment_id IS NULL) AND (fk_topic_id = 31974028))
  • Rows Removed by Filter: 920957
  • Buffers: shared hit=1260886, temp read=22358 written=22358
9. 234.630 2,080.008 ↑ 3.1 16 2

Hash Join (cost=0.57..14.82 rows=50 width=68) (actual time=1,038.992..1,040.004 rows=16 loops=2)

  • Hash Cond: (c.parent_comment_id = ch.comment_id)
  • Buffers: shared hit=1260886, temp read=44716 written=22358
10. 1,845.344 1,845.344 ↓ 1,725.0 1,725,008 2

Function Scan on soi_vw_comments_date_range2 c (cost=0.25..10.25 rows=1,000 width=68) (actual time=703.829..922.672 rows=1,725,008 loops=2)

  • Buffers: shared hit=1260886, temp read=44716 written=22358
11. 0.018 0.034 ↓ 6.6 66 2

Hash (cost=0.20..0.20 rows=10 width=8) (actual time=0.017..0.017 rows=66 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
12. 0.016 0.016 ↓ 6.6 66 2

WorkTable Scan on included_childs ch (cost=0.00..0.20 rows=10 width=8) (actual time=0.002..0.008 rows=66 loops=2)

13. 3,961.543 3,961.543 ↑ 3.8 133 1

CTE Scan on included_childs c_1 (cost=0.00..10.02 rows=501 width=68) (actual time=1,881.439..3,961.543 rows=133 loops=1)

  • Buffers: shared hit=2521772, temp read=67074 written=44716
14. 0.025 0.269 ↓ 1.0 203 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
  • Buffers: shared hit=16
15. 0.092 0.244 ↓ 1.0 203 1

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

  • Group Key: comment_likes_2019_7.fk_comment_id
  • Buffers: shared hit=16
16. 0.025 0.152 ↑ 3.9 330 1

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

  • Buffers: shared hit=16
17. 0.019 0.019 ↓ 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.019..0.019 rows=0 loops=1)

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

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

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

  • Index Cond: (created >= '2019-07-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=1
20. 0.024 0.024 ↑ 6.1 82 1

CTE Scan on cte (cost=0.00..10.02 rows=501 width=76) (actual time=0.001..0.024 rows=82 loops=1)

21. 0.265 3,962.602 ↑ 15,643.9 48 1

Hash (cost=468.11..468.11 rows=750,907 width=97) (actual time=3,962.602..3,962.602 rows=48 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 1025kB
  • Buffers: shared hit=2521980, temp read=67074 written=44716
22. 0.052 3,962.337 ↑ 15,643.9 48 1

Nested Loop (cost=11.70..468.11 rows=750,907 width=97) (actual time=3,961.995..3,962.337 rows=48 loops=1)

  • Buffers: shared hit=2521980, temp read=67074 written=44716
23. 0.043 3,961.949 ↑ 4.2 48 1

HashAggregate (cost=11.27..13.27 rows=200 width=4) (actual time=3,961.937..3,961.949 rows=48 loops=1)

  • Group Key: cte_1.from_user_id
  • Buffers: shared hit=2521788, temp read=67074 written=44716
24. 3,961.906 3,961.906 ↑ 6.1 82 1

CTE Scan on cte cte_1 (cost=0.00..10.02 rows=501 width=4) (actual time=1,881.738..3,961.906 rows=82 loops=1)

  • Buffers: shared hit=2521788, temp read=67074 written=44716
25. 0.336 0.336 ↑ 1.0 1 48

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

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