explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aQGC

Settings
# exclusive inclusive rows x rows loops node
1. 0.034 11.482 ↑ 3.5 51 1
→

Hash Left Join (cost=3,620.95..3,628.30 rows=178 width=820) (actual time=11.412..11.482 rows=51 loops=1)

  • Hash Cond: ((p.thread_id = fd.thread_id) AND (p.in_thread_no = fd.in_thread_no))
2.          

CTE posts_data
3. 10.510 10.510 ↑ 3.5 51 1
→

Index Scan using uix_posts_thread_id_in_thread_no on posts p_1 (cost=0.56..623.63 rows=178 width=1,065) (actual time=0.020..10.510 rows=51 loops=1)

  • Index Cond: ((thread_id = 360) AND (in_thread_no >= 1,100) AND (in_thread_no <= 1,150))
4.          

CTE available_files
5. 0.000 0.134 ↓ 0.0 0 1
→

Nested Loop (cost=0.85..1,480.68 rows=1 width=177) (actual time=0.134..0.134 rows=0 loops=1)

6. 0.016 0.134 ↓ 0.0 0 1
→

Nested Loop (cost=0.42..1,480.10 rows=1 width=32) (actual time=0.134..0.134 rows=0 loops=1)

7. 0.016 0.016 ↑ 3.5 51 1
→

CTE Scan on posts_data pd (cost=0.00..3.56 rows=178 width=12) (actual time=0.000..0.016 rows=51 loops=1)

8. 0.102 0.102 ↓ 0.0 0 51
→

Index Scan using ix_file_post_thread_id_in_thread_no on file_post fp (cost=0.42..8.29 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=51)

  • Index Cond: ((thread_id = pd.thread_id) AND (in_thread_no = pd.in_thread_no))
9. 0.000 0.000 ↓ 0.0 0
→

Index Scan using pk_file_id on files f (cost=0.42..0.57 rows=1 width=153) (never executed)

  • Index Cond: (id = fp.file_id)
10.          

CTE files_data
11. 0.001 0.136 ↓ 0.0 0 1
→

HashAggregate (cost=0.03..0.05 rows=1 width=48) (actual time=0.136..0.136 rows=0 loops=1)

  • Group Key: available_files.thread_id, available_files.in_thread_no
12. 0.135 0.135 ↓ 0.0 0 1
→

CTE Scan on available_files (cost=0.00..0.02 rows=1 width=1,492) (actual time=0.135..0.135 rows=0 loops=1)

13.          

CTE posts_reply
14. 0.019 11.207 ↑ 12.7 14 1
→

Nested Loop (cost=4.44..1,510.78 rows=178 width=24) (actual time=10.647..11.207 rows=14 loops=1)

15. 0.045 10.633 ↑ 11.9 15 1
→

HashAggregate (cost=4.00..5.79 rows=178 width=8) (actual time=10.626..10.633 rows=15 loops=1)

  • Group Key: posts_data.reply_to
16. 10.588 10.588 ↑ 3.5 51 1
→

CTE Scan on posts_data (cost=0.00..3.56 rows=178 width=8) (actual time=0.000..10.588 rows=51 loops=1)

17. 0.555 0.555 ↑ 1.0 1 15
→

Index Scan using pk_post_id on posts (cost=0.44..8.46 rows=1 width=20) (actual time=0.037..0.037 rows=1 loops=15)

  • Index Cond: (id = posts_data.reply_to)
18. 0.038 11.311 ↑ 3.5 51 1
→

Hash Left Join (cost=5.79..11.79 rows=178 width=788) (actual time=11.267..11.311 rows=51 loops=1)

  • Hash Cond: (p.reply_to = r.id)
19. 0.038 0.038 ↑ 3.5 51 1
→

CTE Scan on posts_data p (cost=0.00..3.56 rows=178 width=772) (actual time=0.025..0.038 rows=51 loops=1)

20. 0.013 11.235 ↑ 12.7 14 1
→

Hash (cost=3.56..3.56 rows=178 width=24) (actual time=11.234..11.235 rows=14 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
21. 11.222 11.222 ↑ 12.7 14 1
→

CTE Scan on posts_reply r (cost=0.00..3.56 rows=178 width=24) (actual time=10.648..11.222 rows=14 loops=1)

22. 0.001 0.137 ↓ 0.0 0 1
→

Hash (cost=0.02..0.02 rows=1 width=48) (actual time=0.137..0.137 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
23. 0.136 0.136 ↓ 0.0 0 1
→

CTE Scan on files_data fd (cost=0.00..0.02 rows=1 width=48) (actual time=0.136..0.136 rows=0 loops=1)