Contrib Extensions – 인덱스 및 검색
Contrib Extension에서 인덱스와 검색기능을 보완하는 기능을 제공하는 Extension을 모아서 정리해봤습니다.
- bloom
- dict_xsyn
- dict_int
bloom
bloom 필터를 기반으로 하는 인덱스 액세스 방법을 제공합니다. bloom 필터란 구성요소가 집합의 구성원인지 여부를 테스트하는데 사용되는 공간 효율적인 데이터 구조입니다. 이 유형의 인덱스는 테이블에 많은 속성이 있고, 쿼리가 속성의 임의 조합을 테스트할 때 가장 유용합니다. btree 인덱스가 bloom보다 빠르지만, 단일 bloom 인덱스만 필요한 쿼리를 지원하려면 많은 btree가 필요할 수 있습니다.
bloom 인덱스는 with 절에서 다음 매개변수를 허용합니다.
| 매개변수 | 설명 |
|---|---|
| length | 각 색인 항목의 길이(bit단위). 16의 가장 가까운 배수로 반올림. default 80, max 4096 |
| col1 ~ col32 | 각 인덱스 컬럼에 대해 생성된 비트 수. 각 매개변수의 이름은 해당 매개변수가 제어하는 인덱스 열의 번호. default 2bit, max 4095. 실제로 사용되지 않는 인덱스 컬럼의 매개변수는 무시. |
또한 bloom 인덱스에 대한 연산자 클래스는 인덱싱된 데이터 유형에 대한 해시함수와, 검색을 위한 등호 연산자만 필요합니다.
예시
간단하게 bloom인덱스를 생성하는 예시와 임의의 테이블로 bloom 인덱스를 사용하기 좋은 상황의 예시입니다.’
- bloom 인덱스 생성 예시 구문
CREATE INDEX bloomidx ON tbloom USING bloom (i1,i2,i3)
WITH (length=80, col1=2, col2=2, col3=4);
–index는 80 bit의 길이로 생성되며 속성 i1 및 i2는 2bit, i3은 4bit에 매핑됩니다.
–default 값은 생략 가능합니다.
- bloom 인덱스 사용 예시 및 btree 인덱스 보다 성능이 더 나오는 예시
예시 테이블 생성
CREATE TABLE tbloom AS
SELECT
(random() * 1000000)::int as i1,
(random() * 1000000)::int as i2,
(random() * 1000000)::int as i3,
(random() * 1000000)::int as i4,
(random() * 1000000)::int as i5,
(random() * 1000000)::int as i6
FROM
generate_series(1,10000000);
SELECT 10000000
테이블 생성 후 쿼리 실행 계획
EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
QUERY PLAN
Seq Scan on tbloom (cost=0.00..2137.14 rows=3 width=24) (actual time=16.971..16.971 rows=0 loops=1)
Filter: ((i2 = 898732) AND (i5 = 123451))
Rows Removed by Filter: 100000
Planning Time: 0.346 ms
Execution Time: 16.988 ms
(5 rows)
btree 인덱스 생성 후 index 크기와 실행계획
CREATE INDEX btreeidx ON tbloom (i1, i2, i3, i4, i5, i6);
CREATE INDEXSELECT pg_size_pretty(pg_relation_size(‘btreeidx’));
pg_size_pretty
3976 kB
(1 row)EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
QUERY PLAN
Seq Scan on tbloom (cost=0.00..2137.00 rows=2 width=24) (actual time=12.805..12.805 rows=0 loops=1)
Filter: ((i2 = 898732) AND (i5 = 123451))
Rows Removed by Filter: 100000
Planning Time: 0.138 ms
Execution Time: 12.817 ms
(5 rows)
bloom 인덱스 생성 후 index 크기와 실행계획
CREATE INDEX bloomidx ON tbloom USING bloom (i1, i2, i3, i4, i5, i6);
CREATE INDEXSELECT pg_size_pretty(pg_relation_size(‘bloomidx’));
pg_size_pretty
1584 kB
(1 row)EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
QUERY PLAN
Bitmap Heap Scan on tbloom (cost=1792.00..1799.69 rows=2 width=24) (actual time=0.388..0.388 rows=0 loops=1)
Recheck Cond: ((i2 = 898732) AND (i5 = 123451))
Rows Removed by Index Recheck: 29
Heap Blocks: exact=28
-> Bitmap Index Scan on bloomidx (cost=0.00..1792.00 rows=2 width=0) (actual time=0.356..0.356 rows=29 loops=1)
Index Cond: ((i2 = 898732) AND (i5 = 123451))
Planning Time: 0.099 ms
Execution Time: 0.408 ms
(8 rows)
추가로, btree의 성능을 높이려면 아래와 같이 각 컬럼에 대해 인덱스를 별도로 생성해야 합니다.
CREATE INDEX btreeidx1 ON tbloom (i1);
CREATE INDEX
CREATE INDEX btreeidx2 ON tbloom (i2);
CREATE INDEX
CREATE INDEX btreeidx3 ON tbloom (i3);
CREATE INDEX
…
CREATE INDEX btreeidx6 ON tbloom (i6);
CREATE INDEX
EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;QUERY PLAN
Bitmap Heap Scan on tbloom (cost=24.34..32.03 rows=2 width=24) (actual time=0.028..0.029 rows=0 loops=1)
Recheck Cond: ((i5 = 123451) AND (i2 = 898732))
-> BitmapAnd (cost=24.34..24.34 rows=2 width=0) (actual time=0.027..0.027 rows=0 loops=1)
-> Bitmap Index Scan on btreeidx5 (cost=0.00..12.04 rows=500 width=0) (actual time=0.026..0.026 rows=0 loops=1)
Index Cond: (i5 = 123451)
-> Bitmap Index Scan on btreeidx2 (cost=0.00..12.04 rows=500 width=0) (never executed)
Index Cond: (i2 = 898732)
Planning Time: 0.491 ms
Execution Time: 0.055 ms
(9 rows)
이렇게 구성 시 성능은 확실하게 나오지만, 인덱스 사이즈가 너무 커져서(각 컬럼별 인덱스마다 2MB) bloom을 사용할 경우보다 8배의 공간을 차지하게 됩니다.
참고 사이트
- 공식 홈페이지 : https://www.postgresql.org/docs/current/bloom.html
- 공식 git hub contrib 참고 : sample sql https://github.com/postgres/postgres/tree/master/contrib/bloom/sql sample sql 결과 https://github.com/postgres/postgres/tree/master/contrib/bloom/expected
dict_xsyn
전체 텍스트 검색을 위한 추가 사전 템플릿의 예입니다. 이 사전 유형은 단어를 동의어 그룹으로 대체하므로 동의어를 사용하여 단어를 검색할 수 있습니다.
dictionary에는 다음과 같은 옵션을 사용합니다.
| 옵션 | 설명 |
|---|---|
| matchorig | 원래 단어가 사전에서 허용되는지 여부를 제어. default true |
| matchsynonyms | 동의어가 사전에서 허용되는지 여부를 제어. default true |
| keeporig | 원래 단어가 사전의 출력에 포함되는지 여부를 제어. default true |
| keepsynonyms | 동의어가 사전의 출력에 포함되는지 여부를 제어. default true |
| rules | 동의어 목록이 포함된 파일의 기본 이름. 이 파일은 $SHAREDIR/tsearch_data 위치에 저장($SHAREDIR은 /usr/pgsql-14/share경로처럼 PostgreSQL 공유 데이터 디렉토리.). 이름은 .rules로 끝나야 하고 rules 내용은 word syn1 syn2 syn3 와 같이 작성됩니다. ex)supernova sn sne 1987a |
예시
다음은 dict_xsyn 구문의 예시와 각 옵션을 활용했을 경우 결과의 예시입니다.
ALTER TEXT SEARCH DICTIONARY xsyn (RULES=’xsyn_sample’, KEEPORIG=true, MATCHORIG=true, KEEPSYNONYMS=true, MATCHSYNONYMS=false);
–이미 작성된 xsyn_sample의 supernova단어에 대한 동의어들이 같이 조회됩니다.
SELECT ts_lexize(‘xsyn’, ‘supernova’);ts_lexize
{supernova,sn,sne,1987a}
(1 row)–matchsynonyms옵션이 false라 조회되지 않습니다.
SELECT ts_lexize(‘xsyn’, ‘sn’);ts_lexize
(1 row)
–전혀다른 단어라 당연히 조회되지 않습니다.
SELECT ts_lexize(‘xsyn’, ‘grb’);ts_lexize
(1 row)
–동의어만 조회됩니다.
ALTER TEXT SEARCH DICTIONARY xsyn (RULES=’xsyn_sample’, KEEPORIG=false, MATCHORIG=true, KEEPSYNONYMS=true, MATCHSYNONYMS=false);SELECT ts_lexize(‘xsyn’, ‘supernova’);
ts_lexize
{sn,sne,1987a}
(1 row)–동의어 입력시 같이 조회됩니다.
ALTER TEXT SEARCH DICTIONARY xsyn (RULES=’xsyn_sample’, KEEPORIG=true, MATCHORIG=true, KEEPSYNONYMS=true, MATCHSYNONYMS=true);SELECT ts_lexize(‘xsyn’, ‘supernova’);
ts_lexize
{supernova,sn,sne,1987a}
(1 row)SELECT ts_lexize(‘xsyn’, ‘sn’);
ts_lexize
{supernova,sn,sne,1987a}
(1 row)
참고 사이트
- 공식 홈페이지 : https://www.postgresql.org/docs/current/dict-xsyn.html
- 공식 git hub contrib 참고 : sample sql https://github.com/postgres/postgres/tree/master/contrib/dict_xsyn/sql sample sql 결과 https://github.com/postgres/postgres/tree/master/contrib/dict_xsyn/expected
dict_int
dict_int는 전체 텍스트 검색을 위한 add-on 사전 템플릿의 예시입니다. 이 사전이 만들어진 동기는 정수(signed 및 unsigned)의 인덱싱을 제어하여 검색 성능에 큰 영향을 미치는 고유한 단어 수의 과도한 증가를 방지하면서 이러한 숫자가 인덱싱되도록 하는 것입니다.
이 사전은 세가지 옵션을 허용합니다.
- maxlen 매개변수는 정수 단어에 허용되는 최대 자릿수를 지정합니다. default는 6입니다.
- rejectlong 매개변수는 길이가 긴 정수를 자르거나 무시할지 여부를 지정합니다. default 값은 false이며, 이 경우 사전은 정수의 첫번째 maxlen 숫자를 반환합니다. true일 경우 길이가 초과된 정수를 중지 단어로 처리하므로 index가 생성되지 않습니다.
- absval 매개변수는 정수 단어에서 선행 “+” 또는 “-” 기호를 제거해야하는지 여부를 지정합니다. default는 false이며, true인 경우 maxlen이 적용되기 전에 기호가 제거됩니다.
예시
각 옵션별로 활용하는 예시입니다.
ALTER TEXT SEARCH DICTIONARY intdict (MAXLEN = 6.7);
–‘+’기호 포함 최대자릿수 6.7자리라 6자리인 +40865까지 출력됨을 확인
select ts_lexize(‘intdict’, ‘+40865854’);ts_lexize
{+40865}
(1 row)ALTER TEXT SEARCH DICTIONARY intdict (ABSVAL = true);
— MAXLEN 6.7 + ABSVAL true 옵션으로, 기호를 제거하고 408658까지 출력됨을 확인
select ts_lexize(‘intdict’, ‘-40865854’);ts_lexize
{408658}
(1 row)ALTER TEXT SEARCH DICTIONARY intdict (REJECTLONG = 1);
— MAXLEN 6.7 + ABSVAL 처리를 해도 길이초과로 데이터가 출력되지 않음
select ts_lexize(‘intdict’, ‘-40865854’);ts_lexize
{}
(1 row)— MAXLEN 6.7 + ABSVAL 처리시 길이가 맞아서 데이터 출력됨
select ts_lexize(‘intdict’, ‘-408658’);ts_lexize
{408658}
(1 row)
참고 사이트
- 공식 홈페이지 : https://www.postgresql.org/docs/current/dict-int.html
- 공식 git hub contrib 참고 : sample sql https://github.com/postgres/postgres/tree/master/contrib/dict_int/sql sample sql 결과 https://github.com/postgres/postgres/tree/master/contrib/dict_int/expected
지금까지 ‘PostgreSQL의 Contrib Extensions – 인덱스 및 검색’에 관해 알아보았습니다
‘PostgreSQL의 pgcryto’를 바로 이어서 확인해보세요!
