- trace_id in (%s)
- %s 에 들어갈 조건들을 쭉 string으로 풀어해치는 로직 추가
ANY 일 때
- trace_id = ANY($1)
- 원래 DB 호출할 때 넘기는 parameter 그대로 사용 가능
IN과 ANY가 동일한 실행계획으로 풀리는 것으로 보이는데 성능도 동일할지 약간의 테스트를 진행해보았습니다.
테스트 테이블
create table minjae_test (col1 bigserial primary key, col2 int, col3 varchar(10));
조회 실행 계획
ANY EXPLAIN
Index Scan using minjae_test_idx on minjae_test (cost=0.14..19.88 rows=50 width=18) (actual time=0.087..0.200 rows=50 loops=1)
Index Cond: ((col3)::text = ANY ('{test1,test2,test3,test4,test5,test6,test7...,test49,test50}'::text[]))
Planning Time: 0.123 ms
Execution Time: 0.222 ms
IN EXPLAIN
Index Scan using minjae_test_idx on minjae_test (cost=0.14..19.88 rows=50 width=18) (actual time=0.087..0.186 rows=50 loops=1)
Index Cond: ((col3)::text = ANY ('{test1,test2,test3,test4,test5,test6,test7...,test49,test50}'::text[]))
Planning Time: 0.137 ms
Execution Time: 0.204 ms
성능 테스트 쿼리
성능 테스트 쿼리
DECLARE
v_ts TIMESTAMP;
rec RECORD;
BEGIN
FOR r IN 1..5 LOOP
v_ts := clock_timestamp();
FOR i IN 1..5 LOOP
for rec in (select * from minjae_test where col3 = any(array['test1','test2','test3','test4']))
loop null;
end loop;
END LOOP;
RAISE INFO 'Run %, Statement ANY: %',
r, (clock_timestamp() - v_ts);
v_ts := clock_timestamp();
FOR i IN 1..5 LOOP
for rec in (select * from minjae_test where col3 in ('test1','test2','test3','test4'))
loop null;
end loop;
END LOOP;
RAISE INFO 'Run %, Statement IN: %',
r, (clock_timestamp() - v_ts);
v_ts := clock_timestamp();
END LOOP;
END$$;
바인딩 수 4
Run 1, Statement ANY: 00:00:00.000312
Run 1, Statement IN : 00:00:00.000221
Run 2, Statement ANY: 00:00:00.000105
Run 2, Statement IN : 00:00:00.000096
Run 3, Statement ANY: 00:00:00.000095
Run 3, Statement IN : 00:00:00.000093
Run 4, Statement ANY: 00:00:00.000092
Run 4, Statement IN : 00:00:00.000093
Run 5, Statement ANY: 00:00:00.000093
Run 5, Statement IN : 00:00:00.000094
바인딩 수 25
Run 1, Statement ANY: 00:00:00.000731
Run 1, Statement IN : 00:00:00.000633
Run 2, Statement ANY: 00:00:00.000469
Run 2, Statement IN : 00:00:00.000461
Run 3, Statement ANY: 00:00:00.000457
Run 3, Statement IN : 00:00:00.000456
Run 4, Statement ANY: 00:00:00.000456
Run 4, Statement IN : 00:00:00.000456
Run 5, Statement ANY: 00:00:00.000454
Run 5, Statement IN : 00:00:00.000465
바인딩 수 50
Run 1, Statement ANY: 00:00:00.001272
Run 1, Statement IN : 00:00:00.001133
Run 2, Statement ANY: 00:00:00.000944
Run 2, Statement IN : 00:00:00.000938
Run 3, Statement ANY: 00:00:00.000957
Run 3, Statement IN : 00:00:00.000962
Run 4, Statement ANY: 00:00:00.000931
Run 4, Statement IN : 00:00:00.000933
Run 5, Statement ANY: 00:00:00.000937
Run 5, Statement IN : 00:00:00.000937
바인딩 수 100
Run 1, Statement ANY: 00:00:00.002292
Run 1, Statement IN : 00:00:00.002171
Run 2, Statement ANY: 00:00:00.001933
Run 2, Statement IN : 00:00:00.002727
Run 3, Statement ANY: 00:00:00.001956
Run 3, Statement IN : 00:00:00.00192
Run 4, Statement ANY: 00:00:00.00192
Run 4, Statement IN : 00:00:00.002159
Run 5, Statement ANY: 00:00:00.00311
Run 5, Statement IN : 00:00:00.00221
테스트를 통해 성능에서 확인되는 차이는 없었습니다.
참고
https://www.postgresql.org/docs/current/functions-comparisons.html#FUNCTIONS-COMPARISONS-ANY-SOME
https://pganalyze.com/blog/5mins-postgres-performance-in-lists-vs-any-operator-bind-parameters
https://blog.jooq.org/sql-in-predicate-with-in-list-or-with-array-which-is-faster/