1. 인덱스(INDEX) 상세 개념 Postgresql 인덱스(INDEX)개념 및 생성, 삭제, 분석, 설계 방법 2. 미사용 인덱스 간단히 말해, 인덱스는 지정 컬럼에 매핑된 정보를 별도로 저장하고 있다. 보통 플랜 확인을 통해 효율적으로 인덱스를 추가하여 쿼리 최적화를 진행하게 된다. 오래되고 변경이 잦은 어플리케이션일수록 미사용 인덱스는 늘어나고, 인덱스가 사용되지 않는 경우를 매번 모니터링하여 삭제하는 것은 힘든 일이다. 하지만 불필요 인덱스는 디비 성능저하 및 vacuum 코스트를 증가시키기에, 최적화된 인덱스 생성만큼 최적화된 인덱스 삭제도 중요하다. ...
1. 발생 해당 에러는 Postgresql에서 Full Text Search를 위해 tsvector 컬럼을 업데이트할 때 발생한다. -- 특정 컬럼을 ts_vector로 변경하여 업데이트 UPDATE TABLE SET tsvec_words = to_tsvector('english',COLUMN); 2. 원인 해당 컬럼 (혹은 다른 컬럼) 에 테이블 row 업데이트/인서트 시 ts_vector를 자동으로 업데이트하는 trigger가 걸려 있기 때문에 업데이트 간 충돌이 생겨 발생한다. 3. 해결 트러거를 삭제 후 데이터 업데이트 후에 트리거를 재설정하면 해결된다. 3-1. 트리거 삭제 drop trigger TABLE_TRGGER on TABLE; 3-2. 트리거 생성 CREATE TRIGGER TABLE_TRIGGER BEFORE INSERT OR UPDATE ON TABLE FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(tsvec_words, 'english',COLUMN); ...
1. 중복 공백 제거 특정 문자열에 대해서 중복 공백 제거를 하고 싶다면 postgresql 정규식을 사용해서 가능하다. (공백 외에 단일 문자에 대한 중복제거도 동일한 방법으로 가능하다.) select regexp_replace(name, ' +', ' ', 'g') from TABLE; -- 'g' 옵션을 제거할 경우 최초 건에 대에서만 변경 2. 중복 단어 제거 컬럼 단위 중복제거는 distinct, group by를 통해 쉽게 가능하지만, 컬럼 내 문자열의 중복 단어 제거의 경우 다음과 같다. (쉼표 기준으로 컬럼을 분리, 중복을 제거한 후 다시 연결) ...
1. BRIN 인덱스란? ▪ Block range index의 약자 ▪ Page 검색에 도움 되는 메타 데이터를 뽑아서 인덱스를 구성 (ex, 특정컬럼의 최대/최솟값) ▪ 특정 컬럼이 물리 주소의 일정한 상관관계를 가지는 매우 큰 테이블을 다루기 위해 설계 (타임시쿼스한 대용량 데이터 조회에 유용) Block range는 테이블 내에서 근접한 물리주소를 가진 page 그룹을 의미한다. 각 Block range 에 대해 일부 요약 정보가 인덱스로 저장된다. 예를 들어 상점의 판매 주문을 저장하는 테이블에는 각 주문이 배치된 날짜 열이 있을 수 있으며 대부분의 경우 이전 주문시점에 맞게 순차적으로 주문정보가 들어갈 것이고, ZIP 코드 열을 저장하는 테이블에는 도시에 대한 모든 코드가 자연스럽게 그룹화되어 있을 것이다. ...
1. GIN 인덱스란? Generalized Inverted Index의 약자이다. 이전 포스트인 full text search에서 사용하는 인덱스의 유형. 기본 구조는 B-tree와 유사하지만, 저장 형태가 다르다. 저장된 요소 자제에 대한 검색이 아닌 인덱스 컬럼의 값을 split 한 token인 lexeme 배열에 대해서 검색을 한다. array_ops, tsvector_ops, jsonb_ops, jsonb_path_ops 등 의 built-in operators를 통해 접근이 가능하다. 2. full text search에서의 적용 2-1. 샘플 테이블 및 데이터 생성 create table ts(doc text, doc_tsv tsvector); insert into ts(doc) values ('Can a sheet slitter slit sheets?'), ('How many sheets could a sheet slitter slit?'), ('I slit a sheet, a sheet I slit.'), ('Upon a slitted sheet I sit.'), ('Whoever slit the sheets is a good sheet slitter.'), ('I am a sheet slitter.'), ('I slit sheets.'), ('I am the sleekest sheet slitter that ever slit sheets.'), ('She slits the sheet she sits on.'); update ts set doc_tsv = to_tsvector(doc); create index on ts using gin(doc_tsv); select doc from ts where doc_tsv @@ to_tsquery('many & slitter'); 2-2. 조회 결과 및 플랜 확인 QUERY PLAN --------------------------------------------------------------------- Bitmap Heap Scan on ts Recheck Cond: (doc_tsv @@ to_tsquery('many & slitter'::text)) -> Bitmap Index Scan on ts_doc_tsv_idx Index Cond: (doc_tsv @@ to_tsquery('many & slitter'::text)) (4 rows) ...