postgres利用表连接代替in查询提升查询效率

  • A+
所属分类:数据库
SELECT data.diagnose_record where id in('14320614 '<, ...MORE_ITEM>);
SELECT *
from
  "unnest"(array['14320614 '<, ...MORE_ITEM>])
  left join data.diagnose_record diagnose_record
    on diagnose_record.his_id::text = unnest::text
;

测试时请用更多数据来填充 <, ...MORE_ITEM>

基本没数据的情况下:

left join: [10:40:42] 500 rows retrieved starting from 1 in 410ms (execution: 146ms, fetching: 264ms)
in: [10:41:02] 0 rows retrieved in 308ms (execution: 109ms, fetching: 199ms)

 

30000条数据的情况下:

left join: [10:48:59] 500 rows retrieved starting from 1 in 343ms (execution: 134ms, fetching: 209ms)
in: [10:49:17] 500 rows retrieved starting from 1 in 2s 615ms (execution: 2s 490ms, fetching: 125ms)

 

370000条数据的情况下:

left join: [10:51:11] 500 rows retrieved starting from 1 in 684ms (execution: 579ms, fetching: 105ms)
in: [10:54:07] 0 rows retrieved in 50s 435ms (execution: 50s 318ms, fetching: 117ms)

楚廉

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: