PostgreSQL Any 연산자

 

개발팀에서 IN 구문이 아닌 ANY 구문 사용을 요청을 하였습니다

코드 레벨에서 IN 을 사용하면 코드가 무너지는( ? ) 원인이 있었습니다

 

--개발팀 코멘트

IN 일때

• 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/