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

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)