Contrib Extension 에서 기본 제공되는 내장 연산자를 보완하는 ‘연산자’를 제공하는 Extension을 모아서 정리해봤습니다.
- intarray
- pg_trgm
- btree_gist
- btree_gin
Intarray
null이 없는 정수 배열을 조작하는데 유용한 여러 함수와 연산자를 제공하기에, 연산자 파트에서 소개하게 되었습니다. 일부 연산자를 사용하는 index 검색도 지원 됩니다. 제공된 배열에 null 요소가 포함되어 있으면 모든 작업에서 오류가 발생합니다. 2차원 이상의 배열을 허용하긴 하나, 데이터 저장순서에서 선형(1차원) 배열인 것처럼 처리됩니다.
제공 함수
함수 목록
| 함수 | 설명 |
| icount ( integer[] ) returns integer | 배열의 요소 수를 반환합니다. |
| sort ( integer[], dir text ) returns integer[] | 배열을 오름차순 또는 내림차순으로 정렬합니다. dir은 asc , desc중 하나여야합니다. |
| sort ( integer[] ) returns integer[] sort_asc ( integer[] ) returns integer[] | 오름차순으로 정렬합니다. |
| sort_desc ( integer[] ) returns integer[] | 내림차순으로 정렬합니다. |
| uniq ( integer[] ) returns integer[] | 인접한 중복을 제거합니다. 모든 중복을 제거하기 위해 sort와 함께 사용됩니다. |
| idx ( integer[], item integer ) returns integer | item과 일치하는 첫 번째 배열 요소의 인덱스를 반환 하거나 일치하지 않으면 0을 반환합니다. |
| subarray ( integer[], start integer, len integer ) returns integer[] | start 위치에서 시작하는 배열 부분을 len 요소와 함께 추출합니다. |
| subarray ( integer[], start integer ) returns integer[] | start 위치에서 시작하는 배열 부분을 추출합니다. |
| intset ( integer ) returns integer[] | 단일 요소 배열을 만듭니다. |
제공 연산자
- 연산자 목록 &&, @>및 @@연산자에 대한 인덱스 지원 과 일반 배열 동등성을 제공합니다.
| 연산자 | 설명 |
| integer[] && integer[] → boolean | 배열이 겹치는지 확인합니다. (공통 요소가 최소 하나 이상 있는지 여부) |
| integer[] @> integer[] → boolean | 왼쪽 배열에 오른쪽 배열이 포함되어 있는지 확인합니다. |
| integer[] <@ integer[] → boolean | 왼쪽 배열이 오른쪽 배열에 포함되어 있는지 확인합니다. |
| #integer[] → integer | 배열의 요소 수를 반환합니다. |
| integer[] # integer → integer | 오른쪽 인수와 일치하는 첫 번째 배열 요소의 인덱스를 반환하거나, 일치하지 않으면 0을 반환합니다(idx 함수와 동일합니다.). |
| integer[] + integer → integer[] | 배열의 끝에 요소를 추가합니다. |
| integer[] + integer[] → integer[] | 배열을 연결합니다. |
| integer[] – integer → integer[] | 배열에서 올바른 인수와 일치하는 항목을 제거합니다. |
| integer[] – integer[] → integer[] | 왼쪽 배열에서 오른쪽 배열의 요소를 제거합니다. |
| integer[] | integer → integer[] | 인수의 합집합을 계산합니다. |
| integer[] | integer[] → integer[] | 인수의 합집합을 계산합니다. |
| integer[] & integer[] → integer[] | 인수의 교집합을 계산합니다. |
| integer[] @@ query_int → boolean | 배열이 쿼리를 만족하는지 확인합니다. |
| query_int ~~ integer[] → boolean | 배열이 쿼리를 만족하는지 확인합니다. |
예시
intarray에서 제공하는 함수와 연산자를 활용하는 예시입니다.
SELECT intset(1234);
intset
{1234}
(1 row)SELECT icount(‘{1234234,234234}’);
icount
2(1 row)
SELECT sort(‘{1234234,-30,234234}’);
sort
{-30,234234,1234234}
(1 row)SELECT sort(‘{1234234,-30,234234}’,’asc’);
sort
{-30,234234,1234234}
(1 row)SELECT sort(‘{1234234,-30,234234}’,’desc’);
sort
{1234234,234234,-30}
(1 row)SELECT sort_asc(‘{1234234,-30,234234}’);
sort_asc
{-30,234234,1234234}
(1 row)SELECT sort_desc(‘{1234234,-30,234234}’);
sort_desc
{1234234,234234,-30}
(1 row)SELECT uniq(‘{1234234,-30,-30,234234,-30}’);
uniq
{1234234,-30,234234,-30}
(1 row)SELECT uniq(sort_asc(‘{1234234,-30,-30,234234,-30}’));
uniq
{-30,234234,1234234}
(1 row)SELECT idx(‘{1234234,-30,-30,234234,-30}’,-30);
idx
2
(1 row)SELECT subarray(‘{1234234,-30,-30,234234,-30}’,2,3);
subarray
{-30,-30,234234}
(1 row)SELECT subarray(‘{1234234,-30,-30,234234,-30}’,-1,1);
subarray
{-30}
(1 row)SELECT subarray(‘{1234234,-30,-30,234234,-30}’,0,-1);
subarray
{1234234,-30,-30,234234}
(1 row)SELECT #'{1234234,234234}’::int[];
?column?
2(1 row)
SELECT ‘{123,623,445}’::int[] + 1245;
?column?
{123,623,445,1245}
(1 row)SELECT ‘{123,623,445}’::int[] + 445;
?column?
{123,623,445,445}
(1 row)SELECT ‘{123,623,445}’::int[] + ‘{1245,87,445}’;
?column?
{123,623,445,1245,87,445}
(1 row)SELECT ‘{123,623,445}’::int[] – 623;
?column?
{123,445}
(1 row)SELECT ‘{123,623,445}’::int[] – ‘{1623,623}’;
?column?
{123,445}
(1 row)SELECT ‘{123,623,445}’::int[] | 623;
?column?
{123,445,623}
(1 row)SELECT ‘{123,623,445}’::int[] | 1623;
?column?
{123,445,623,1623}
(1 row)SELECT ‘{123,623,445}’::int[] | ‘{1623,623}’;
?column?
{123,445,623,1623}
(1 row)SELECT ‘{123,623,445}’::int[] & ‘{1623,623}’;
?column?
{623}
(1 row)SELECT ‘{-1,3,1}’::int[] & ‘{1,2}’;
?column?
{1}
(1 row)SELECT ‘{1}’::int[] & ‘{2}’::int[];
?column?
{}
(1 row)SELECT array_dims(‘{1}’::int[] & ‘{2}’::int[]);
array_dims
(1 row)
SELECT (‘{1}’::int[] & ‘{2}’::int[]) = ‘{}’::int[];
?column?
t
(1 row)SELECT (‘{}’::int[] & ‘{}’::int[]) = ‘{}’::int[];
?column?
t
(1 row)–test query_int
SELECT ‘1’::query_int;query_int
1
(1 row)SELECT ‘ 1’::query_int;
query_int
1
(1 row)SELECT ‘1 ‘::query_int;
query_int
1
(1 row)SELECT ‘ 1 ‘::query_int;
query_int
1
(1 row)SELECT ‘ ! 1 ‘::query_int;
query_int
!1
(1 row)SELECT ‘!1’::query_int;
query_int
!1
(1 row)SELECT ‘1|2’::query_int;
query_int
1 | 2
(1 row)SELECT ‘1|!2’::query_int;
query_int
1 | !2
(1 row)SELECT ‘!1|2’::query_int;
query_int
!1 | 2
(1 row)SELECT ‘!1|!2’::query_int;
query_int
!1 | !2
(1 row)SELECT ‘!(!1|!2)’::query_int;
query_int
!( !1 | !2 )
(1 row)SELECT ‘!(!1|2)’::query_int;
query_int
!( !1 | 2 )
(1 row)SELECT ‘!(1|!2)’::query_int;
query_int
!( 1 | !2 )
(1 row)SELECT ‘!(1|2)’::query_int;
query_int
!( 1 | 2 )
(1 row)SELECT ‘1&2’::query_int;
query_int
1 & 2
(1 row)SELECT ‘!1&2’::query_int;
query_int
!1 & 2
(1 row)SELECT ‘1&!2’::query_int;
query_int
1 & !2
(1 row)SELECT ‘!1&!2’::query_int;
query_int
!1 & !2
(1 row)SELECT ‘(1&2)’::query_int;
query_int
1 & 2
(1 row)SELECT ‘1&(2)’::query_int;
query_int
1 & 2
(1 row)SELECT ‘!(1)&2’::query_int;
query_int
!1 & 2
(1 row)SELECT ‘!(1&2)’::query_int;
query_int
!( 1 & 2 )
(1 row)SELECT ‘1|2&3’::query_int;
query_int
1 | 2 & 3
(1 row)SELECT ‘1|(2&3)’::query_int;
query_int
1 | 2 & 3
(1 row)SELECT ‘(1|2)&3’::query_int;
query_int
( 1 | 2 ) & 3
(1 row)SELECT ‘1|2&!3’::query_int;
query_int
1 | 2 & !3
(1 row)SELECT ‘1|!2&3’::query_int;
query_int
1 | !2 & 3
(1 row)SELECT ‘!1|2&3’::query_int;
query_int
!1 | 2 & 3
(1 row)SELECT ‘!1|(2&3)’::query_int;
query_int
!1 | 2 & 3
(1 row)SELECT ‘!(1|2)&3’::query_int;
query_int
!( 1 | 2 ) & 3
(1 row)SELECT ‘(!1|2)&3’::query_int;
query_int
( !1 | 2 ) & 3
(1 row)SELECT ‘1|(2|(4|(5|6)))’::query_int;
query_int
1 | ( 2 | ( 4 | ( 5 | 6 ) ) )
(1 row)SELECT ‘1|2|4|5|6’::query_int;
query_int
( ( ( 1 | 2 ) | 4 ) | 5 ) | 6
(1 row)SELECT ‘1&(2&(4&(5&6)))’::query_int;
query_int
1 & 2 & 4 & 5 & 6
(1 row)SELECT ‘1&2&4&5&6’::query_int;
query_int
1 & 2 & 4 & 5 & 6
(1 row)SELECT ‘1&(2&(4&(5|6)))’::query_int;
query_int
1 & 2 & 4 & ( 5 | 6 )
(1 row)SELECT ‘1&(2&(4&(5|!6)))’::query_int;
query_int
1 & 2 & 4 & ( 5 | !6 )
(1 row)
참고사이트
- 공식 홈페이지 : https://www.postgresql.org/docs/current/intarray.html
- 공식 git hub contrib 참고 : sample sql https://github.com/postgres/postgres/tree/master/contrib/intarray/sql sample sql 결과 https://github.com/postgres/postgres/tree/master/contrib/intarray/expected
pg_trgm이란?
- PostgreSQL contrib extension ****
- pg_trgm은 문자열에 대한 검색을 빠르게 해주는 인덱스 연산자 클래스 뿐 아니라 trigram matching을 기반으로 영숫자 텍스트의 유사성을 결정하기 위한 함수 및 연산자를 제공합니다. 마찬가지로 연산자도 같이 제공하기 때문에 연산자 파트에서 소개하게 되었습니다.
pg_trgm 설치
- contrib extension이기 때문에 postgresql-contrib 가 설치 되면 아래 커맨드로 설치할 수 있습니다.
postgres=# CREATE EXTENSION pg_trgm;
CREATE EXTENSION
trigram(트라이그램)이란?
트라이그램은 문자열에서 가져온 세 개의 연속 문자 그룹입니다.
함수 및 연산자
제공 함수
함수 목록
| 함수 | 설명 |
|---|---|
| similarity( text, text) → real | 두 인수가 얼마나 유사한지를 나타내는 숫자를 반환합니다. 결과의 범위는 0(두 문자열이 완전히 다름을 나타냄)에서 1(두 문자열이 동일함을 나타냄)입니다. |
| show_trgm( text) → text[] | 주어진 문자열에 있는 모든 trigram의 배열을 반환합니다. (디버깅을 제외하고는 거의 유용하지 않습니다.) |
| word_similarity( text, text) → real | 첫 번째 문자열의 trigram 집합과 두 번째 문자열의 정렬된 trigram 집합 사이 가장 큰 유사성을 나타내는 숫자를 반환합니다. |
| strict_word_similarity( text, text) → real | word_similarity와 비슷하지만 더 엄격한 임계값으로, 유사성보다는 일치에 가까운 숫자를 반환합니다. |
| show_limit() → real | %연산자가 사용하는 현재 유사성 임계값을 반환합니다. 서로 철자가 틀리기에 충분히 유사한 것으로 간주되도록 두 단어 사이의 최소 유사성을 설정합니다.(더 이상 사용되지 않고 postgreql.conf에 pg_trgm.similarity_threshold 변수로 대체되었습니다.) |
| set_limit( real) → real | %연산자가 사용하는 현재 유사성 임계값을 설정합니다 . 임계값은 0과 1 사이여야 합니다(기본값은 0.3). 전달된 동일한 값을 반환합니다.(더 이상 사용되지 않고 postgreql.conf에 pg_trgm.similarity_threshold 변수로 대체되었습니다.) |
제공 연산자
연산자 목록
| 연산자 | 설명 |
| text % text →boolean | 인수의 유사성이 pg_trgm.similarity_threshold 매개변수에 의해 설정된 값보다 큰 경우 true를 반환 합니다. |
| text <% text →boolean | 첫 번째 인수(기준 문자열)의 뒷부분의 문자들과 두 번째 인수(검색어) 사이에 유사도가 pg_trgm.word_similarity_threshold 매개변수에 의해 설정된 값보다 큰 경우 true를 반환 합니다. |
| text %> text →boolean | 첫 번째 인수(기준 문자열)의 앞부분의 문자들과 두 번째 인수(검색어) 사이에 유사도가 pg_trgm.word_similarity_threshold 매개변수에 의해 설정된 값보다 큰 경우 true를 반환 합니다. |
| text <<% text →boolean | 첫 번째 인수(기준 문자열)의 뒷부분의 문자들과 두 번째 인수(검색어) 사이에 유사도가 pg_trgm.strict_word_similarity_threshold 매개변수에 의해 설정된 값보다 큰 경우 true를 반환 합니다. |
| text %>> text →boolean | 첫 번째 인수(기준 문자열)의 앞부분의 문자들과 두 번째 인수(검색어) 사이에 유사도가 pg_trgm.strict_word_similarity_threshold 매개변수에 의해 설정된 값보다 큰 경우 true를 반환 합니다. |
| text <-> text → real | 인수 사이의 ” 거리 ” , 즉 1에서 similarity()값을 뺀 값을 반환합니다. |
| text <<-> text → real | 인수의 뒷 부분을 기준으로 문자열 사이의 ” 거리 ” , 즉 1에서 word_similarity()값을 뺀 값을 반환합니다. |
| text <->> text → real | 인수의 앞 부분을 기준으로 문자열 사이의 ” 거리 ” , 즉 1에서 word_similarity()값을 뺀 값을 반환합니다. |
| text <<<-> text → real | 인수의 뒷 부분을 기준으로 문자열 사이의 ” 거리 ” , 즉 1에서 strict_word_similarity()값을 뺀 값을 반환합니다. |
| text <->>> text → real | 인수의 앞 부분을 기준으로 문자열 사이의 ” 거리 ” , 즉 1에서 strict_word_similarity()값을 뺀 값을 반환합니다. |
인덱스 지원
pg_trgm은 문자열의 유사성 검색을 위해 GIN과 GiST인덱스 연산자 클래스를 제공합니다. 위에서 설명한 유사성 연산자들을 지원하고 LIKE, ILIKE,~,*,= 쿼링에 대한 트라이그램 기반 인덱스 검색을 추가로 지원합니다.
예시
pg_trgm을 설치하고 like 검색 시 소요 시간의 차이를 보여주는 예시입니다.
1.랜덤 데이터를 가진 테이블을 생성
create table test_trgm(col varchar(30);
insert into test_trgm
select substr(‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ’, trunc(random() * 62)::integer + 1, trunc(random() * 10)::integer)
FROM generate_series(1, 100000) series;select * from test_trgm;
col
MNO
BCDEF
wxyzABQRSTUVW
PQRSTUVW
Q
.
.
.
.
2. 임의의 3글자이상 문자를 like로 검색하는 것을 explain analyze을 통해 확인
postgres=# explain analyze select * from test_trgm where col like ‘%abcd%’;
QUERY PLAN
Seq Scan on test_trgm (cost=0.00..1706.00 rows=881 width=4) (actual time=0.031..38.355 rows=991 loops=1)
Filter: ((col)::text ~~ ‘%abcd%’::text)
Rows Removed by Filter: 99009
Planning Time: 0.110 ms
Execution Time: 38.542 ms
(5 rows)
–sequencial scan 및 실행시간확인
3. gin index 생성 후 똑같은 구문 실행계획을 확인합니다. like 활용 시 성능이 개선됨을 확인합니다.
postgres=# create index trgm_idx on test_trgm using gin(col gin_trgm_ops);
CREATE INDEX
postgres=# explain analyze select * from test_trgm where col like ‘%abcd%’;QUERY PLAN
Bitmap Heap Scan on test_trgm (cost=26.83..497.22 rows=881 width=4) (actual time=1.115..4.054 rows=991 loops=1)
Recheck Cond: ((col)::text‘%abcd%’::text) Rows Removed by Index Recheck: 3348 Heap Blocks: exact=455 -> Bitmap Index Scan on trgm_idx (cost=0.00..26.61 rows=881 width=0) (actual time=1.012..1.013 rows=4339 loops=1) Index Cond: ((col)::text‘%abcd%’::text)
Planning Time: 0.238 ms
Execution Time: 4.251 ms
(8 rows)
–index scan 및 실행시간 확인
참고사이트
- 공식 홈페이지 : https://www.postgresql.org/docs/current/pgtrgm.html
- 공식 git hub contrib 참고 : sample sql https://github.com/postgres/postgres/tree/master/contrib/pg_trgm/sql sample sql 결과 https://github.com/postgres/postgres/tree/master/contrib/pg_trgm/expected
btree_gist
btree_gist는 데이터 유형 int2, int4, int8, float4, float8, numeric, timestamp with time zone, timestamp without time zone, time with time zone, time without time zone, date, interval, oid, money, char, varchar, text, bytea, bit, varbit, macaddr, macaddr8, inet, cidr, uuid 그리고 모든 enum 유형에 대해 b-tree에 해당하는 동작을 구현하는 GiST 인덱스 연산자 클래스를 제공합니다.
일반적으로 이러한 연산자 클래스는 동등한 표준 b-tree 인덱스 내에서 활용가능하며, 표준 b-tree 코드의 한가지 주요 기능인 고유성을 적용하는 기능이 부족합니다. 그러나 b-tree 인덱스에서 사용할 수 없는 몇가지 다른 기능을 제공합니다. 또한 이 연산자 클래스는 여러 컬럼의 GiST인덱스가 필요할 때 유용합니다.
일반적인 b-tree 검색 연산자 외에도 <>(“not equals”)에 대한 인덱스 지원도 제공합니다. 또한 int2, int4, int8, float4, float8, timestamp with time zone, timestamp without time zone, time without time zone, date, interval, oid, money 데이터 유형에 대해 거리 연산자가 제공됩니다.
예시
다음은 btree_gist를 적용하는 예시입니다.
CREATE TABLE test (a int4);
— 인덱스 생성
CREATE INDEX testidx ON test USING GIST (a);
— query
SELECT * FROM test WHERE a < 10; — 가까운 이웃검색: “42”에 가장 가까운 항목 10개 찾기 SELECT *, a <-> 42 AS dist FROM test ORDER BY a <-> 42 LIMIT 10;
추가로 제외 제약 조건을 같이 활용한 예시입니다.
CREATE TABLE zoo (
cage INTEGER,
animal TEXT,
EXCLUDE USING GIST (cage WITH =, animal WITH <>)
);INSERT INTO zoo VALUES(123, ‘zebra’);
INSERT 0 1
INSERT INTO zoo VALUES(123, ‘zebra’);
INSERT 0 1
INSERT INTO zoo VALUES(123, ‘lion’);
ERROR: conflicting key value violates exclusion constraint “zoo_cage_animal_excl”
DETAIL: Key (cage, animal)=(123, lion) conflicts with existing key (cage, animal)=(123, zebra).
–같은 123 케이지에 zebra가 들어가있고 zebra만 insert가능한데,
–lion을 추가로 insert하려해서 에러메시지 출력
=> INSERT INTO zoo VALUES(124, ‘lion’);
INSERT 0 1
–124 케이지로 lion을 insert시 에러 없이 insert확인
참고사이트
- 공식 홈페이지 : https://www.postgresql.org/docs/current/btree-gist.html
- 공식 git hub contrib 참고 : sample sql https://github.com/postgres/postgres/tree/master/contrib/btree_gist/sql sample sql 결과 https://github.com/postgres/postgres/tree/master/contrib/btree_gist/expected
btree_gin
btree_gin은 데이터유형 int2, int4, int8, float4, float8, timestamp with time zone, timestamp without time zone, time with time zone, time without time zone, date, interval, oid, money, “char”, varchar, text, bytea, bit, varbit, macaddr, macaddr8, inet, cidr, uuid, name, bool, bpchar 그리고 모든 enum 유형에 대해 b-tree에 해당되는 동작을 구현하는 샘플 GIN 연산자 클래스를 제공합니다.
일반적으로 이러한 연산자 클래스는 동등한 표준 b-tree 인덱스 내에서 활용가능하며, 표준 b-tree 코드의 한가지 주요 기능인 고유성을 적용하는 기능이 부족합니다. 그러나 GIN 연산자 클래스를 개발하는데 유용합니다. 그리고 GIN 인덱싱 가능 컬럼과 btree 인덱싱 가능 컬럼을 모두 테스트하는 쿼리의 경우 이 연산자 클래스 중 하나를 사용하는 다중 컬럼 GIN 인덱스를 만드는것이 두개의 별도 인덱스를 만드는 것보다 효율적입니다.
예시
다음은 인덱스 생성시 GIN 을 적용하는 예시입니다.
–bool 데이터타입 가진 테이블 생성
CREATE TABLE test_bool (i boolean);–실행계획 확인
EXPLAIN (COSTS OFF) SELECT * FROM test_bool WHERE i<true ORDER BY i;QUERY PLAN
Sort
Sort Key: i
-> Seq Scan on test_bool
Filter: (i < true)
(4 rows)— 인덱스 생성시 GIN 연산자 사용
CREATE INDEX idx_bool ON test_bool USING gin (i);— 실행계획 확인
EXPLAIN (COSTS OFF) SELECT * FROM test_bool WHERE i<true ORDER BY i;QUERY PLAN
Sort
Sort Key: i
-> Bitmap Heap Scan on test_bool
Recheck Cond: (i < true) -> Bitmap Index Scan on idx_bool
Index Cond: (i < true)
(6 rows)
참고사이트
- 공식 홈페이지 : https://www.postgresql.org/docs/current/btree-gin.html
- 공식 git hub contrib 참고 : sample sql https://github.com/postgres/postgres/tree/master/contrib/btree_gin/sql sample sql 결과 https://github.com/postgres/postgres/tree/master/contrib/btree_gin/expected
지금까지 ‘PostgreSQL Contrib Extensions – 연산자’에 관해 알아보았습니다
‘PostgreSQL의 Contrib Extensions – 데이터 타입’를 바로 이어서 확인해보세요!
