背景:

开发反馈, 同一个 查询 SQL , 在 测试环境比生产环境快得多

分析

通过 explain (verbose, analyse, costs,timing, buffers) 分析执行的 SQL , 结果如下

生产环境

 Limit  (cost=3384.93..3385.96 rows=10 width=670) (actual time=67.369..67.636 rows=10 loops=1)
   Output: col1, col2,.xxx
   Buffers: shared hit=259 read=5774
   I/O Timings: read=15.947
   ->  Index Scan using idx_test_id on public.test  (cost=3384.93..7345.39 rows=38488 width=670) (actual time=67.365..67.628 rows=10 loops=1)
         Output: col1,col2,xxx
         Order By: (test.vec <=> '[0.231036,-1.41518,-1.09931,-1.12821,0.698702,-0.261864,-1.02543,-0.365026,-0.967448,-0.176928,-0.133368,0.0390539,-0.650554,-1.04057,0.470583,0.258094,-1.13777,
         Buffers: shared hit=259 read=5774
         I/O Timings: read=15.947
 Planning Time: 1.718 ms
 Execution Time: 67.798 ms
(11 rows)

测试环境

 Limit  (cost=3920.97..3922.00 rows=10 width=678) (actual time=10.831..11.199 rows=10 loops=1)
   Output: col1, col2,.xxx
   Buffers: shared hit=261 read=698
   I/O Timings: read=2.252
   ->  Index Scan using idx_test_id on public.test  (cost=3920.97..8507.26 rows=44595 width=678) (actual time=10.826..11.192 rows=10 loops=1)
         Output: col1,col2,xxx
         Order By: (test.vec <=> '[0.231036,-1.41518,-1.09931,-1.12821,0.698702,-0.261864,-1.02543,-0.365026,-0.967448,-0.176928,-0.133368,0.0390539,-0.650554,-1.04057,0.470583,0.258094,-1.13777,
         Buffers: shared hit=261 read=698
         I/O Timings: read=2.252
 Planning Time: 1.648 ms
 Execution Time: 11.312 ms
(11 rows)

总结

生产与测试 shared hit 差不多, 但是 磁盘read 差异较大
生产环境, 读磁盘较多 (Buffers: read=5774) ,所以IO耗时较多
而测试环境 读磁盘block较少 (Buffers: read=698), 所以 IO耗时较少, 总体就比生产环境快些
本质在于数据分布不一样, 测试环境查询的数据大部分在缓存中, 而生产环境大部分查询的数据来自磁盘,所以就慢些