explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QJh9 : Optimization for: plan #R5EP

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.033 14.218 ↑ 1.9 51 1

Hash Left Join (cost=1,980.91..1,984.92 rows=97 width=820) (actual time=14.144..14.218 rows=51 loops=1)

  • Hash Cond: ((p.thread_id = fd.thread_id) AND (p.in_thread_no = fd.in_thread_no))
  • Buffers: shared hit=258 read=31
  • I/O Timings: read=13.438
2.          

CTE posts_data

3. 11.215 11.215 ↑ 1.9 51 1

Index Scan using uix_posts_thread_id_in_thread_no on posts p_1 (cost=0.56..339.98 rows=97 width=1,065) (actual time=1.204..11.215 rows=51 loops=1)

  • Index Cond: ((thread_id = 360) AND (in_thread_no >= 2100) AND (in_thread_no <= 2150))
  • Buffers: shared hit=14 read=26
  • I/O Timings: read=10.992
4.          

CTE available_files

5. 0.001 0.139 ↓ 0.0 0 1

Nested Loop (cost=0.85..814.41 rows=1 width=177) (actual time=0.139..0.139 rows=0 loops=1)

  • Buffers: shared hit=153
6. 0.013 0.138 ↓ 0.0 0 1

Nested Loop (cost=0.42..813.83 rows=1 width=32) (actual time=0.138..0.138 rows=0 loops=1)

  • Buffers: shared hit=153
7. 0.023 0.023 ↑ 1.9 51 1

CTE Scan on posts_data pd (cost=0.00..1.94 rows=97 width=12) (actual time=0.000..0.023 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.36 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))
  • Buffers: shared hit=153
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.140 ↓ 0.0 0 1

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

  • Group Key: available_files.thread_id, available_files.in_thread_no
  • Buffers: shared hit=153
12. 0.139 0.139 ↓ 0.0 0 1

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

  • Buffers: shared hit=153
13.          

CTE posts_reply

14. 0.033 12.733 ↑ 4.0 24 1

Nested Loop (cost=2.62..823.29 rows=97 width=24) (actual time=11.127..12.733 rows=24 loops=1)

  • Buffers: shared hit=102 read=29
  • I/O Timings: read=12.276
15. 0.057 10.175 ↑ 3.9 25 1

HashAggregate (cost=2.18..3.15 rows=97 width=8) (actual time=10.164..10.175 rows=25 loops=1)

  • Group Key: posts_data.reply_to
  • Buffers: shared hit=11 read=24
  • I/O Timings: read=9.830
16. 10.118 10.118 ↑ 1.9 51 1

CTE Scan on posts_data (cost=0.00..1.94 rows=97 width=8) (actual time=0.001..10.118 rows=51 loops=1)

  • Buffers: shared hit=11 read=24
  • I/O Timings: read=9.830
17. 2.525 2.525 ↑ 1.0 1 25

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

  • Index Cond: (id = posts_data.reply_to)
  • Buffers: shared hit=91 read=5
  • I/O Timings: read=2.446
18. 0.044 14.043 ↑ 1.9 51 1

Hash Left Join (cost=3.15..6.43 rows=97 width=788) (actual time=13.995..14.043 rows=51 loops=1)

  • Hash Cond: (p.reply_to = r.id)
  • Buffers: shared hit=105 read=31
  • I/O Timings: read=13.438
19. 1.222 1.222 ↑ 1.9 51 1

CTE Scan on posts_data p (cost=0.00..1.94 rows=97 width=772) (actual time=1.209..1.222 rows=51 loops=1)

  • Buffers: shared hit=3 read=2
  • I/O Timings: read=1.162
20. 0.019 12.777 ↑ 4.0 24 1

Hash (cost=1.94..1.94 rows=97 width=24) (actual time=12.777..12.777 rows=24 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=102 read=29
  • I/O Timings: read=12.276
21. 12.758 12.758 ↑ 4.0 24 1

CTE Scan on posts_reply r (cost=0.00..1.94 rows=97 width=24) (actual time=11.129..12.758 rows=24 loops=1)

  • Buffers: shared hit=102 read=29
  • I/O Timings: read=12.276
22. 0.001 0.142 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=153
23. 0.141 0.141 ↓ 0.0 0 1

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

  • Buffers: shared hit=153
Planning time : 0.992 ms
Execution time : 14.392 ms