t3, t4 에 숫자에 따옴표로 싸는 실수를 했는데..

안 싸고도 해봤는데 역시나 결과는 마찬가지입니다.

 

버젼은 7.4 입니다.

 

이 문제가 7.4까지 고쳐지지 않았군요 -.-;

참 중요한 문제인거 같습니다..

잘 타던 인덱스가 vacuum 후에 안타거나 하는 경우도 있고 유형도 참 많습니다 =_=;

항상 테이블을 다시 맹글고 데이터를 넣어줘야만 --;

PostgreSQL 쿼리 옵티마이져는 cost 값 우선입니다.

 

윗 explain 결과에서 cost를 잘 살펴보세요.

타당한 처사라고 생각합니다.

인덱스가 없다가 속도가 늦어져서 인덱스를 사용할 요량으로 인덱스를 만들었다면, 당연히 인덱스를 사용하게 될 것입니다. 그것이 explain cost 값이 작다면 말이지요.

 

최소한 몇 백 건의 자료를 가지고 테스트를 해 보셔야할 듯싶습니다.

김상기님의 이야기에는 일리가 있습니다.

하지만 조금 다른 견해를 가지고 있습니다.

 

인덱스가 없다가 자료입력후 생겼을 경우

김상기님의 말대로 비용이 적게 드는 쪽으로작용을 하는게

어쩌면 옵티마이져 입장에선 분명 맞는것도 같은데요..

 

근데  위의 예제를 제시한 것은 FTI 떄문이었습니다.

 

상기님이 RTREE 질문에서 보면 index 생성 시간이 넘 오래 걸린다고 해서

fti 에서 먼저 인덱스를 제거하고 자료를 입력을 했던 것입니다.

역시나 인덱스가 없으니 속도가 빠르더군요

 

모두 입력을 하고 인덱스를 생성하고 쿼리를 줘 봤습니다.

인덱스를 안 타는 것이었습니다.

 

메인 테이블의 래코드수는 103777 개이고

이것으로 파생된 FTI 의 레코드는 2850 만 여개 입니다 (--;);

여기다가 인덱스 까지 거니 게시물 10만건에 대한 자료만 있는데

무려 3.5 기가의 용량이더군요..

 

위의 예제에서 인덱스를 탈 경우의 explain 의 결과에 힌트를 얻어

이렇게 해 보았습니다. (더이상의 자료입력은 하지 않고 단지 쿼리문만 변형했지요)

 

select * from t2 where t like 'b%';

이렇게 하면 인덱스를 안 타던 것이

select * from t2 where (t >= 'b'::text) and (t < 'c'::text);

이렇게 하면 또 인덱스를 탑니다. --;

두개의 쿼리문은 분명 같은 의미를 담고 있으며,

결과물도 같습니다.

 

한가지 궁금한것은

상기님이 언급하신 cost 문제를 어떻게 하면 적게 들게 만들 것인가 인데..

 

혹시 이런 테스트 해보셨는지 모르겠습니다.

똑같은 쿼리를 연속으로 세네번 줘 보고

각각 나타나는 cost 를 비교해보시면

cost 가 일정하지 않고 항상 변하고 있습니다.

(미세하게.. 만일 결과가 많다면 좀 더 차이가 나고요)

이런 상황에서 인덱스를 타는것과 타지않는경우

cost 값의 미세한 변화에 작용한다는것은 좀 맞지 않다고 생각이 듭니다.

 

또 이상한것은

자료 10만건 인덱스 없이 입력한 테이블이

메인테이블과 제목테이블 그리고 FTI 테이블입니다(그외 부수적인것도 있습니다만...)

이 테이블들은 모두가 자료입력완료후 인덱스를 걸어 주었습니다.

근데 희한한게

십만건의 제먹테이블에서

subject like 'xx%'  를 하면 인덱스를 타고 있습니다.

그런데 FTI 테이블은 like 로는 인덱스를 전혀 타질 않고

(xxx >= 'xxx'::text) and (xxx < 'xxy'::text) 이런식으로 하면

인덱스를 또 타고 있습니다.

 

상기님의 마랟로 cost 가 적게 발생하는 쪽으로 optimizer 가 알아서

판단하는건 아닌거 같군요..

 

또 희얀한건

 

n int 가 인덱스 걸려 있습니다.

select * from tb where n > 5555;

뭐 이런식으로 하면 인덱스를 타지 않습니다.

결과가 몇개 밖에 없다 하더라도요..

근데 select * from tb where n = 5555;

이런식으로 하면 또 인덱스를 탑니다.

 

text like 'xx%' 가 인덱스를 타지않고

lext >= 'xx'::text and text < 'xy'::text 가

인덱스를 탄다는 것은

<, > 같은 연산자를 사용하더라도 인덱스를 탄다는 것인데

왜 숫자에 저런연산자를 사용하면 인덱스를 타지 않는 것일까요..

 

참으로 이상하며... 난감하네요..

 

p.s : fti 에 대한것인데요..

제가 512 바이트씩 짤라서 넣었다고했는데

미쳐 생각을 못하고 바보같은 생각을 했네요..

text 가 '1111 2222 3333' 형태일때

like '222%' 형태가 찾아지지 않는다는 것을 미쳐

생각을 못했나봐용 (왜 그땐 생각이 안떠올랐는지... --;)

그래서 할수 없이 FTI 방식으로 해야하는데..

대략 10 만건에 fti 가 2850만개라면..

해당 테이블에 대한 파일크기(인덱스 전혀없이)만해도

1.7 기가를 웃돌고 있습니다.

물론 본문 내용이 좀 긴 내용들입니다만..

보통의 경우라 가정하더라도 fti 갯수는 적어도 1000만개는 넘어갈거 같은데요..

검색시 인덱스를 타더라도 난감합니다.

같은 쿼리를 연달아 주면 처음것만 오래걸리고 뒤는 캐시 덕인지

sort mem 덕인지는 모르겠지만 대충의 속도가 나오기는 합니다만..

항상 같은 데이타를 검색하는것도 아닐테고..

검색은 다른테이블 조인 전혀없이

단순하게 쿼리 하나만 넣어서 하는데도 오래 걸리는군요..

(1~2초 상간이 아니라 몇십초입니다. 일단 결과가 나오고 난 후는 1초도 안걸립니다. 또 다른걸 검색하면 또 수십초.. )

 

현재로썬 최선의 방법이겠지만.. fti 는 대용량일경우는 아닌듯 합니다.

상기님꼐 부탁해봅니다.

자료입력시 RTREE에서 속도저하로 포기하셨는데,

또한 tsearch2 구현해보고도 마찬가지고..

아무래도 처음의 생각했던 그 방식을 다시 해보시고

공개좀 해주시면 좋겠습니다.

자료는 인덱스생성에 오래 걸리므로

인덱스를걸지않고 데이타 다 넣고 인덱스를 걸면 될 것입니다.

tsearch2 방식이 아닌

님이 처음에 생각했던 그 방식을 한번만 더 시도해보면어떨런지요

아무래도 text 보다는 int 가 검색이 빠르니깐..

 

지금 대략 난감하네요..

정녕 10만건 이상일때 해결방법이 없는 것인지..

정녕코 자료갯수를 제한해야하여

일정수가 넘으면 테이블로부터 분리해야 할 것인지..

 

참 입력한 데이타는 모두 같은 것이 아니라 예전의 모 게시판 데이타를

여러번 넣어서 10만건을만들었습니다.

 

꼼꼼히는 살펴보질 않았지만,

PostgreSQL 쪽에서 인덱싱 작업으로 오동작을 일으킨 경우는 제 경우는 없었던것 같습니다.

만일 꿈꾸었던 대로 되지 않았다함은 분명 또 다른 이유가 있어서 일터인데.....

 

일단 btree 인덱스와 옵티마이져의 버그인가보지요.

(개인적으로 이 문제는 사람이 생각하는 버그인듯 싶습니다. RDBMS의 핵심 루틴인 인덱스와 옵티마이져 쪽에 이런 치명적인 문제를 안고 있다면, PostgreSQL 개발자들이 가만히 있지 않았겠지요.)

 

같은 자료에 같은 query로 cost 값이 계속틀린다는 것은 있을 수 없습니다. 분명 뭔가가 바뀌었기 때문입니다. cost 계산은 db의 페이징(물리적 저장공간 단위)을 읽어내는 비용을 계산하는 것이거든요. 같은 자료에 같은 쿼리임에도 불구하고, cost 값이 계속해서 틀려진다면, 그 사이 물리적으로 뭔가가 바뀌었기 때문일겝니다. 아니면, pg_stat_ 관련 통계정보를 자동으로 업데이트 하도록 서버를 셋팅해 두어서 옵티마이져가 그 변경된 정보를 참조했기 때문이기도 할터이고.

 

like 연산에서 인덱스 타지 않는 문제는 로케일하고 관련이 있을지 모릅니다.

DB 문자셋 셋팅이 문제가 있으면, like 연산이 인덱스를 타지 않게 되거든요.

 

-----

현재 이곳 DSN 각 섹션별 자료가 많은 곳은 2만건 정도 평균 검색 속도가 0.2초 정도입니다.

이론상으로는 이 모든 섹션이 한 곳에 모여서 통합 검색을 한다면, 약 7만건 자료에 대한 검색을 하는 꼴이 되는데, 예상 속도는 0.5초 미만일것입니다.

 

물론 검색어가 '가 & 아' 같은 무지막지한 검색 결과를 만들어내는 놈이 아닌 일반적인 검색이라면 말이지요.

 

개인적인 판단으로는 10만건 정도의 자료 정도는 현재 fti 알고리즘으로 충분히 감당해 낼 수 있으리라고 봅니다. 제가 이곳 DSN 설계할 때, 50만건 자료로 테스팅을 했거든요.

 

부디 좋은 결과가 있으시길. :)

 

fti 자료를 줄이는 방법을 빼 먹었군요.

 

fti 자료를 만들때는 정책이 필요합니다.

참고로 DSN 정책을 말씀드리면,

1. 영문자는 무조건 소문자로 바꾼다
2. 단어 구분문자를 정한다. (이부분이 아주 중요합니다. pg_class 이놈을 pg, class 두개의 단어로 볼 것인지, pg_class 하나의 단어로 볼 것인지를 결정하는 것이지요. 하나의 단어로 본다면, class 검색어로는 pg_class 만 들어간 문서는 찾아지지 않습니다)
3. 단어의 최소 글자수를 정한다. 이곳은 2글자 이상입니다. '가 나 다 라 마 바 사 아' 이런 본문이 있다면, 이곳에서는 어떠한 방법으로 이 문서는 찾을 수 없습니다. 즉 최소 글자수보다 작은 단어들에 대해서는 무시한다는 정책이 필요합니다.
4. 단어의 최대 글자수를 정한다.(이곳은 최대 50글자입니다) 설령 본문에 아주 긴 단어(앞의 단어 구분문자로 분리된 한 단어)가 있다고 하더라고 그 단어는 정책에 따라 최대 글자수로 잘라서 fti 자료로 사용한다는 것입니다. (그래야, fti 자료를 최대한 줄일 수 있으니까요, 어차피 그 단어 그대로 검색어로 사용될 가능성은 거의 희박하기 때문입니다)
5. 중복 단어의 대한 처리. '우리 집에는 우리 강아지도 있지요' 이런 식의 문장에서 두번 나타난 '우리'를 어떻게 처리할 것인가?도 고민하셔야합니다. 이곳에서는 한 본문에 대한 fti 자료는 단어별로 유니크합니다. (자료 줄이는 방법 - 이 방법에는 그 단어의 빈도수를 계산해 내질 못합니다. 참고하세요.)
6. 검색 제외 단어를 정한다. 이곳에서는 mysql 섹션에서는 mysql 단어가 제외 단어입니다. (본문의 90%가 이 단어를 포함하고 있어서 - 검색어로서의 의미가 없는 단어라고 판단되어었기 때문입니다)

여기까지입니다.

 

생각보다 복잡하지요?

fti 쪽은 지금까지 해 온 것 보다 훨씬 더 복잡합니다. 아직 남았는 것이,

그 찾은 본문을 어떻게 보여줄 것인지,

무엇을 먼저 보여줄 것인지,

찾지 못했다면, 어떻게 할 것인지,

검색 통계는 어떻게 처리할 것인지...

 

이 모든 문제를 함께 풀어야 참한 놈이 나옵니다.

 

그런데, 이 모든 것들을 아주 깔끔하게 처리한 놈이 있는데, 그놈이 바로 아래에서 언급한 tsearch2 놈입니다.

 

오늘까지의 제 개인적인 결론은 tsearch2 알고리즘과 이곳 DSN 검색루틴 알고리즘을 합쳐야, 현재로써는 한국어 관련 검색이 비교적 깔끔하겠다는 생각이 들었습니다. 어떻게 합치면 될까?를 고민하고 있지요.