오옷.. 저랑 같은 현상을 호소하시는 분이 드뎌 -.-;;

해결책 발견하시면 꼭 좀 알려주세요 ㅠ_ㅠ;

Primary key가 정보도 알려주시지요.

multi column 인지, 자료형은 어떻게 되는지.

 

일단 vacuum 뒤에 인덱스를 타지 않는다는 사실을 알았으니, 어떤 인덱스가 그런 것인지를 다른 시스템, 다른 환경에서도 같은 증상인지를 살펴봐야겠네요.

 

근데, 제가 기억하기로는 그런 7.3.x 대에서는 그런 버그가 보고된 적이 없거든요. (치명적인 버그인지라, 흔한 증상이라면, 벌써 알려졌을터인데...)

아래 제가 호소했던 내용에는..

따로 멀티컬럼 인덱스를 추가 했었는데

int 형 2개와 문자열 1개 짜리 컬럼이었습니다

vacuum 하고 나면 정렬이 입력된 순으로만 무조건 되버리는 -.-;;

원래는 btree 의 기본 동작처럼

(int, int, char) 세가지 컬럼중에 첫번째 컬럼을 검색하면 나머지 두개의 컬럼이 자동으로 정렬되는 그걸 원했고 vacuum 전에는 원하던 대로 잘 동작했었습니다 -.-;

 

테이블 생성 스크립터는

Create table tb_code (
    com_cd      char(8) not null,
    code_cd     char(8) not null,
    code_name   varchar(20),
    primary key (com_cd,code_cd)
    );

 

입니다.

 

제가 사용하는 테이블이 30여개 정도를 사용중인데요..

이것과 다른 한개 총 2개가 계속해서 동일한 문제가 반복되어집니다.

 

고수님들의 조언을 부탁드립니다.

감사합니다. ^-^

 

 

참고: 다른 시스템에서도 동일한 문제가 발생됩니다.

       

저도 무진장 궁금하거든요.

 

과연 이 문제가 PostgreSQL 문제인지, 사용자측 오류인지가.

 

일단 데이터가 몇개 정도 쌓이면 저런 사태가 발생하는지 테스트를 한번 해보셔야할 것같습니다.

 

지금까지 이야기로 알아낸 것이,

"멀티 칼럼이고, 그 가운데, varchar, char 자료형이 primary key로 잡혀있는 경우, vacuumdb를 사용하면 인덱스가 깨어진다"

이거지요? 아니면, 인덱스가 깨어지지는 않는데, 쿼리 옵티마이져가 인덱스를 사용하지 않는다? 이건가요?

 

제 경험으로는 정수형 칼럼에 대한 멀티 칼럼 인덱스에는 문제가 없는 것 같은데.. 이것도 아닌가요?

 

지금까지의 test결과를 알려 드립니다.

 

위에 말씀 드린 테이블은 모두 기초 코드 관리용이라 data 건수가

그리 많지 않습니다.

 

현재 건수가 20개 정도 밖에 없는 상태 입니다.

그래서 15000건을 임의 생성하여 vacuumdb해본 결과

아무 이상없이 index를 사용하고 있습니다.

 

그런데 문제가 또한가지 있습니다.

 

20건일때 index를 타고 있는 상황에서 data를 15000건을 입력하고

vacuumdb해보면 index를 잘탑니다.

 

그렇지만 20건일때 index를 타지 않고(물론 index있음) 있는 상황에서

data를 15000건을 입력하고 vacuumdb해보면 index를 타지 않습니다.

 

data의 건수와 상관없이 index를 타게 할수 있는 방법이 없는지요?

고수님들의 조언을 기다립니다. ^-^

 

 

참고:혹 건수가 그것밖에 없는데 index를 타지 않는것이 어떠냐 하실분이 계실지 모르겠는데요... 다른 table과 조인해서 사용하기 때문에

속도의 차이가 많이 납니다.

오늘 시간이 나서 한번 살펴보았는데,

제가 내린 결론은 PostgreSQL의 query optimizer 구현방식 때문인듯 싶습니다.

 

이곳 DSN 자료 구조에서 살펴본 결과,

어떤 자료에서 단일 row를 primary key로 인덱스를 사용해서 찾아내는데, 드는 비용이 약 6이랍니다.

 

그런데, 자료가 적을 경우에는, (얼마일지는 모르겠습니다. 아무튼 수십건) 그 비용이 1-3정도 밖에 들지 않습니다. 인덱스를 사용하지 않는 것이.

 

그래서 optimizer가 인덱스를 사용하지 않나봅니다.

 

이것은 그 인덱스로 사용될 column의 자료형과는 무관한 듯싶습니다.

 

optimizer가 판단한 결과, 조인에서도 인덱스를 사용하지 않는 것이 비용이 적게 든다고 판단되었기 때문에 그런 것같습니다.

 

참고로 작업한 결과를 알려드리겠습니다.

dsn=> select a.criteria,a.subcrit 
from menus a, menus b 
where a.upper = 95 and a.menuid = b.menuid;
 criteria |  subcrit
----------+-----------
 unisql   | links
 unisql   | columns
 unisql   | files
 unisql   | news
 unisql   | tutorials
 unisql   | devel
 unisql   | qna
(7 rows)
Time: 3.35 ms

dsn=> explain select a.criteria,a.subcrit 
from menus a, menus b 
where a.upper = 95 and a.menuid = b.menuid;
                             QUERY PLAN
--------------------------------------------------------------------
 Hash Join  (cost=5.47..11.28 rows=6 width=25)
   Hash Cond: ("outer".menuid = "inner".menuid)
   ->  Seq Scan on menus b  (cost=0.00..5.16 rows=116 width=4)
   ->  Hash  (cost=5.45..5.45 rows=6 width=21)
         ->  Seq Scan on menus a  (cost=0.00..5.45 rows=6 width=21)
               Filter: (upper = 95)
(6 rows)
Time: 1.98 ms

 

여기서 보듯이 인덱스를 사용하지 않는 것이 인덱스를 사용할 때보다 더 적게 들거든요.

 

vacuum full analyze 작업을 주기적으로 해주시면, query optimizer가 판단해서 비용이 적게 드는 쪽으로 인덱스를 사용하든지 안하든지 하겠지요.

 

요기까지 알아냈습니다.

 

일단 원하는 최종작업에서 인덱스 때문에 속도가 많이 늦어진 것인지 아니면, 그외 다른 요인 때문인지를 한번 살펴보아야겠네요.

 

일단 DSN이 전체 약 800MB 정도의 자료임에도 불구하고 저 저용량 자료에 인덱스타지 않는 문제 때문에 속도가 떨어진다는 것을 이곳 페이지를 보셔서 아시겠지만 말이 안되거든요.

 

코딩해보셔서 아시겠지만, 저런 코드 테이블들은 얼마나 많이 조인되고 참조 됩니까. :)

헉! 그렇다면...

seq scan 이 일어날때는 당연히 BTREE 방식의 인덱스를 타지 않게되고..

BTREE 인덱스 특유의 멀티컬럼 정렬이 일어나지 않게 된다는 거네요? -.-;

으아.. 대략 난감한 상황입니다..

자동으로 인덱스때문에 정렬되서 order 를 안해줘도 되던걸 -_-; 해줘야 하는 상황이...

윗 문제라면, 데이타가 비교적 유동적이지 않다면, 일단 인덱스를 하나 만들고, cluster 명령으로 데이터 자체를 물리적으로 그 인덱스 기준으로 다시 정리해서 사용하면 되지 않을까요?

 

윗 tb_code 같은 테이블이라면, 충분히 가능한 일인것같습니다.

문제는 데이타가 20건일때 index를 타지 않습니다. 그러다

data가 대량으로 입력된후(약 15000건)  vacuumdb 하면 건수가

많으니 index를 타야 되지 않습니까?

그런데 index를 안탄다는 겁니다!

 

건수가 적을경우 index를 타지 않는 것까지는 이해가 되지만

건수가 늘어난 후에도 계속 타지 않아 문제 입니다.

 

^-^

그건 저도 아직 모르겠습니다. :)

제가 일하고 있는 회사에서도 저런 형태의 자료구조가 있는데, 자료가 많아지면 인덱스를 사용하게 되더군요.

 

혹 inline view를 사용한 inner join이 아닌지요?

 

아무튼 지금까지 제가 내린 잠정적 결론은 PostgreSQL 자체의 결함이라기 보다는 query optimizer의 특성이 아닐까 생각해봅니다.

 

혹 이문제를 잘 푸셨다면 꼭 알려주셨으면 고맙겠습니다.