對于較大資料量的表,如果在索引字段上面有小結果集join,用nestloop join是比較好的方法。
但是nestloop帶來的一個問題就是離散io,這個是無法回避的問題,特别是硬體io能力不行的情況下,性能會比較糟糕。
有什麼優化方法呢?
postgresql提供了一個指令,可以修改實體存儲的順序,減少離散io就靠它了。
建立兩張表
産生一些離散primary key資料
分析表
清除緩存,并重新開機
第一次調用,耗費大量的離散io,執行時間18.490毫秒(我這台機器是ssd,iops能力算好的,差的機器時間更長)
第二次,緩存命中5.4毫秒
根據索引字段調整表的實體順序,降低離散io。
清除緩存,重新開機資料庫
第一次調用,降低到了5.4毫秒
第二次調用,3.6毫秒
通過cluster, 将表的實體順序和索引對齊,是以如果查詢的值是連續的,在使用嵌套循環時可以大幅減少離散io,取得非常好查詢優化的效果。
如果查詢的值是跳躍的,那麼這種方法就沒有效果啦,不過好在postgresql有bitmap index scan,在讀取heap tuple前,會對ctid排序,按排序後的ctid取heap tuple,也可以起到減少離散io的作用。