explain.depesz.com

PostgreSQL's explain analyze made readable

Result: R5EP

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.061 19.163 ↑ 3.7 51 1

Hash Left Join (cost=3,878.86..3,886.75 rows=191 width=820) (actual time=19.087..19.163 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.766 10.766 ↑ 3.7 51 1

Index Scan using uix_posts_thread_id_in_thread_no on posts p_1 (cost=0.56..668.64 rows=191 width=1,065) (actual time=1.848..10.766 rows=51 loops=1)

  • Index Cond: ((thread_id = 360) AND (in_thread_no >= 1000) AND (in_thread_no <= 1050))
4.          

CTE available_files

5. 0.016 4.305 ↓ 5.0 5 1

Nested Loop (cost=0.85..1,582.82 rows=1 width=177) (actual time=1.580..4.305 rows=5 loops=1)

6. 0.051 3.029 ↓ 5.0 5 1

Nested Loop (cost=0.42..1,582.25 rows=1 width=32) (actual time=1.012..3.029 rows=5 loops=1)

7. 0.020 0.020 ↑ 3.7 51 1

CTE Scan on posts_data pd (cost=0.00..3.82 rows=191 width=12) (actual time=0.000..0.020 rows=51 loops=1)

8. 2.958 2.958 ↓ 0.0 0 51

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

  • Index Cond: ((thread_id = pd.thread_id) AND (in_thread_no = pd.in_thread_no))
9. 1.260 1.260 ↑ 1.0 1 5

Index Scan using pk_file_id on files f (cost=0.42..0.57 rows=1 width=153) (actual time=0.252..0.252 rows=1 loops=5)

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

CTE files_data

11. 1.324 5.645 ↓ 5.0 5 1

HashAggregate (cost=0.03..0.05 rows=1 width=48) (actual time=5.635..5.645 rows=5 loops=1)

  • Group Key: available_files.thread_id, available_files.in_thread_no
12. 4.321 4.321 ↓ 5.0 5 1

CTE Scan on available_files (cost=0.00..0.02 rows=1 width=1,492) (actual time=1.583..4.321 rows=5 loops=1)

13.          

CTE posts_reply

14. 0.021 11.483 ↑ 8.7 22 1

Nested Loop (cost=4.74..1,621.11 rows=191 width=24) (actual time=10.189..11.483 rows=22 loops=1)

15. 0.049 9.070 ↑ 8.3 23 1

HashAggregate (cost=4.30..6.21 rows=191 width=8) (actual time=9.060..9.070 rows=23 loops=1)

  • Group Key: posts_data.reply_to
16. 9.021 9.021 ↑ 3.7 51 1

CTE Scan on posts_data (cost=0.00..3.82 rows=191 width=8) (actual time=0.001..9.021 rows=51 loops=1)

17. 2.392 2.392 ↑ 1.0 1 23

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

  • Index Cond: (id = posts_data.reply_to)
18. 0.057 13.442 ↑ 3.7 51 1

Hash Left Join (cost=6.21..12.65 rows=191 width=788) (actual time=13.392..13.442 rows=51 loops=1)

  • Hash Cond: (p.reply_to = r.id)
19. 1.865 1.865 ↑ 3.7 51 1

CTE Scan on posts_data p (cost=0.00..3.82 rows=191 width=772) (actual time=1.851..1.865 rows=51 loops=1)

20. 0.016 11.520 ↑ 8.7 22 1

Hash (cost=3.82..3.82 rows=191 width=24) (actual time=11.520..11.520 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
21. 11.504 11.504 ↑ 8.7 22 1

CTE Scan on posts_reply r (cost=0.00..3.82 rows=191 width=24) (actual time=10.192..11.504 rows=22 loops=1)

22. 0.006 5.660 ↓ 5.0 5 1

Hash (cost=0.02..0.02 rows=1 width=48) (actual time=5.660..5.660 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
23. 5.654 5.654 ↓ 5.0 5 1

CTE Scan on files_data fd (cost=0.00..0.02 rows=1 width=48) (actual time=5.638..5.654 rows=5 loops=1)

Planning time : 13.350 ms
Execution time : 19.462 ms