안녕하세요. 최근에 postgreSQL 을 알게 되어서, 공부하고 있는
중입니다.
이전에 는 mssql 을 사용했었거든요. 근데, 게시판 문서를 이리저리
살펴보다 보니 mssql 은 대충 설정해도 속도가 나오는데, postgresSQL
은 속도가 잘 안나온다는 내용이 있었습니다. 저도 비슷한 문제가
있어서 도움을 부탁드립니다.
테스트를 위해 mssql 에서 사용하던 자료를 모두 postgreSQL 로
복사했습니다. 자료구조와 데이타 인덱스를 모두 동일하게 작성하고
세개의 테이블에 inner join, outer join 이 있는 쿼리를 실행시켰는데,
mssql 은 10초 정도, postgreSQL 은 한참을 기다려도 답이 나오지
않습니다. 자세한 설명을 위해 인덱스와 자료크기 및 쿼리, 쿼리플랜을
같이 적습니다.
테이블 medshe1
- 칼럼 76개
- 레코드수 231,803 줄
- pk : pk_medshe1 : cChungnum char(10), cIlyun char(5)
- index : medshe1_jinyymm : cJinyymm char(6)
테이블 medshe4
- 칼럼 41개
- 레코드 수 2,421,845 개
- pk : pk_medshe4 : cChungnum char(10), cIlyun char(5), snLine
integer
- index : medshe4_code : cGubun, cCode
medshe4_cost : dCost
medshe4_hang : cHang, cMok
medshe4_jh : cJH
테이블 medrlt3
- 칼럼 13개
- 레코드수 49331 개
- index : medrlt3_chungnum : cChungnum, cIlyun, snLine
medrlt3_jubsunum : cJubsunum
medrlt3_simsa : cSimsa, cJubsunum, cMukem, cIlyun,
snLine
쿼리
Select *
From Medshe1 m1,
medshe4 m4
Left outer join medrlt3 t3 on m4.cChungnum=t3.cChungnum and
m4.cIlyun=t3.cIlyun and
m4.snLine=t3.snLine
Where m1.cJinyymm='200502' and m1.cChungnum=m4.cChungnum and
m1.cIlyun=m4.cIlyun
limit 10
쿼리플랜
Limit (cost=0.00..2009.16 rows=10 width=1459)
-> Merge Join (cost=0.00..5267626.67 rows=26218
width=1459)
Merge Cond: (("outer".cchungnum = "inner".cchungnum) AND
("outer".cilyun = "inner".cilyun))
-> Merge Left Join (cost=0.00..4621681.45 rows=2421845
width=708)
Merge Cond: (("outer".cchungnum = "inner".cchungnum)
AND ("outer".cilyun = "inner".cilyun) AND ("outer".snline =
"inner".snline))
-> Index Scan using pk_medshe4 on medshe4 m4
(cost=0.00..4598227.84 rows=2421845 width=601)
-> Index Scan using medrlt3_chungnum on medrlt3
t3 (cost=0.00..4876.19 rows=49331 width=107)
-> Index Scan using pk_medshe1 on medshe1 m1
(cost=0.00..84575.49 rows=3499 width=751)
Filter: (cjinyymm = '200502'::bpchar)
* limit 는 붙였을때나 없을때나 플랜상의 limit (cost .....) 부분
말고는 똑같습니다.
시스템
- cpu p4 2.4Ghz / memory 1Gbyte
- DB 파일이 있는 하드디스크 19Gbyte 에 남은공간 8Gbyte
* 성능 테스트를 위해 mssql 과 같은 드라이브에 db 파일을
두었습니다.
postgresSQL.conf 중 기본에서 설정바꾼 부분
....
# - Memory -
shared_buffers = 15000 # min 16, at least
max_connections*2, 8KB each
work_mem = 524288 # min 64, size in KB
maintenance_work_mem = 131072 # min 1024, size in KB
max_stack_depth = 2048 # min 100, size in KB
.....
# - Planner Method Configuration -
enable_hashagg = false
enable_hashjoin = false
enable_indexscan = true
enable_mergejoin = true
enable_nestloop = true
enable_seqscan = true
enable_sort = false
enable_tidscan = false
.....
이상입니다.
쿼리 플랜으로 볼때는 결과가 금방 나와야 할것 같은데, 우찌해서
답이 안나오는지 모르겠습니다.
쿼리를 실행시키면, cpu 나 메모리는 별로 안쓰는데, 하드디스크를
무지하게 읽습니다.
결과집합이 큰 쿼리일 경우 레코드셋을 가져오는데만도 많은 시간이
걸리는 것 같습니다. 특정 쿼리에서 limit 1 을 끝에 붙이면 금방
끝나는데, 없을 경우에는 한참이 걸립니다.
참고로 저는 mssql 도 오래 썼고, 오라클도 2년 정도 사용했습니다.
이렇게 헤멘적이 없거든요 ^^;;
인덱스는 쿼리에 맞게 걸었다고 생각이 드는데, 뭐가 문제인지
모르겠습니다.
누가 저의 무식을 좀 지적해 주세요...ㅠㅠ;;
도움 부탁드립니다. 감사합니다.
|