우선 설치환경은

OS : W2K

PostgreSQL : win용 8.0.3

postgresql.conf 설정내용 : 이건 DSN을 찾아보고 했는데 윈도우용을 설치하고나면

Planner Method Configuration와 Planner Cost Constants 는 전부 #으로 막혀있더군요.

# - Planner Method Configuration -

enable_hashagg = true
enable_hashjoin = true
enable_indexscan = true
enable_mergejoin = true
enable_nestloop = true
enable_seqscan = true
enable_sort = true
enable_tidscan = true

# - Planner Cost Constants -

#effective_cache_size = 1000 # typically 8KB each
random_page_cost = 3 # units are one sequential page fetch cost
#cpu_tuple_cost = 0.01 # (same)
#cpu_index_tuple_cost = 0.001 # (same)
#cpu_operator_cost = 0.0025 # (same)

# - Genetic Query Optimizer -

#geqo = true
#geqo_threshold = 12
#geqo_effort = 5 # range 1-10
#geqo_pool_size = 0 # selects default based on effort
#geqo_generations = 0 # selects default based on effort
#geqo_selection_bias = 2.0 # range 1.5-2.0

-------------------

TABLE의 문자열 필드는 모두 varchar(x)로 설정했습니다.
=========================================================

쿼리는

explain analyze
SELECT C.PRESS_TYPE, B.DYEG_NO, A.ASGN_NO, A.ASGN_SEQ, SHORT_NAME,

A.STYLE_CODE, D.CODE_NAME AS CLOTH_PART_NAME, F.CLOTH_NAME, G.COLOR_NAME,

A.BATH_TOTAL , A.BATH_CURR, A.BATH_STAT, A.SCHD_QTY, A.ASGN_QTY, A.ASGN_WGT,

C.TEST_NO, A.ASGN_NAME, A.SCHD_DATE, A.ODR_NO, A.ODR_SEQ, '' AS STAT
FROM SPBKAGO0 A
LEFT OUTER JOIN SPDDYEGA B ON A.ASGN_NO=B.ASGN_NO AND A.ASGN_SEQ=B.ASGN_SEQ
LEFT OUTER JOIN SPC_LINK C ON A.ODR_NO = C.ODR_NO AND A.ODR_SEQ = C.ODR_SEQ
JOIN SPACODE0 D ON A.CLOTH_PART= D.CODE AND D.CODE_TYPE = 'CLOTH_PART'
JOIN SPACUST0 E ON A.CUST_CODE = E.CUST_CODE
JOIN SPACLTH0 F ON A.CUST_CODE = F.CUST_CODE AND A.CLOTH_CODE = F.CLOTH_CODE
JOIN SPACLOR0 G ON A.CUST_CODE = G.CUST_CODE AND A.COLOR_CODE = G.COLOR_CODE
WHERE A.CUST_CODE LIKE '%'
AND C.TEST_NO IS NOT NULL
AND A.SCHD_DATE BETWEEN '2005-01-11' AND '2005-11-11'
AND B.ASGN_NO IS NULL ORDER BY A.ASGN_NO, A.ASGN_SEQ
이고요

--------------------------------------------------------------

pgAmin3의 SQL에서 실행하면

"Sort (cost=4185.58..4199.20 rows=5448 width=194) (actual time=750.000..750.000 rows=450 loops=1)"
" Sort Key: a.asgn_no, a.asgn_seq"
" -> Merge Join (cost=3551.31..3847.49 rows=5448 width=194) (actual time=641.000..750.000 rows=450 loops=1)"
" Merge Cond: ("outer".odr_no = "inner".odr_no)"
" Join Filter: ("inner".odr_seq = "outer".odr_seq)"
" -> Index Scan using spc_link_odr_no on spc_link c (cost=0.00..232.92 rows=5384 width=42) (actual time=0.000..46.000 rows=5384 loops=1)"
" Filter: (test_no IS NOT NULL)"
" -> Sort (cost=3551.31..3551.79 rows=191 width=171) (actual time=641.000..657.000 rows=11581 loops=1)"
" Sort Key: a.odr_no"
" -> Hash Join (cost=3016.31..3544.08 rows=191 width=171) (actual time=578.000..641.000 rows=858 loops=1)"
" Hash Cond: (("outer".cust_code = "inner".cust_code) AND ("outer".cloth_code = "inner".cloth_code))"
" -> Merge Join (cost=2961.39..3312.54 rows=3657 width=165) (actual time=562.000..625.000 rows=858 loops=1)"
" Merge Cond: (("outer".cust_code = "inner".cust_code) AND ("outer".color_code = "inner".color_code))"
" -> Index Scan using spaclor0_pkey on spaclor0 g (cost=0.00..267.61 rows=3833 width=32) (actual time=0.000..31.000 rows=3829 loops=1)"
" -> Sort (cost=2961.39..2970.78 rows=3756 width=149) (actual time=562.000..562.000 rows=858 loops=1)"
" Sort Key: a.cust_code, a.color_code"
" -> Hash Join (cost=2256.99..2738.38 rows=3756 width=149) (actual time=422.000..547.000 rows=858 loops=1)"
" Hash Cond: (("outer".cloth_part)::text = ("inner".code)::text)"
" -> Merge Left Join (cost=2250.35..2473.32 rows=9816 width=145) (actual time=422.000..532.000 rows=858 loops=1)"
" Merge Cond: (("outer".asgn_no = "inner".asgn_no) AND ("outer".asgn_seq = "inner".asgn_seq))"
" Filter: ("inner".asgn_no IS NULL)"
" -> Sort (cost=1400.64..1425.18 rows=9816 width=129) (actual time=312.000..312.000 rows=9817 loops=1)"
" Sort Key: a.asgn_no, a.asgn_seq"
" -> Seq Scan on spbkago0 a (cost=0.00..749.80 rows=9816 width=129) (actual time=0.000..155.000 rows=9817 loops=1)"
" Filter: ((cust_code ~~ '%'::text) AND ((schd_date)::text >= '2005-01-11'::text) AND ((schd_date)::text <= '2005-11-11'::text))"
" -> Sort (cost=849.71..872.11 rows=8959 width=35) (actual time=110.000..126.000 rows=8959 loops=1)"
" Sort Key: b.asgn_no, b.asgn_seq"
" -> Seq Scan on spddyega b (cost=0.00..261.59 rows=8959 width=35) (actual time=0.000..32.000 rows=8959 loops=1)"
" -> Hash (cost=6.61..6.61 rows=10 width=16) (actual time=0.000..0.000 rows=0 loops=1)"
" -> Seq Scan on spacode0 d (cost=0.00..6.61 rows=10 width=16) (actual time=0.000..0.000 rows=8 loops=1)"
" Filter: ((code_type)::text = 'CLOTH_PART'::text)"
" -> Hash (cost=49.71..49.71 rows=1040 width=54) (actual time=16.000..16.000 rows=0 loops=1)"
" -> Hash Join (cost=5.71..49.71 rows=1040 width=54) (actual time=0.000..16.000 rows=1040 loops=1)"
" Hash Cond: ("outer".cust_code = "inner".cust_code)"
" -> Seq Scan on spaclth0 f (cost=0.00..28.40 rows=1040 width=37) (actual time=0.000..0.000 rows=1040 loops=1)"
" -> Hash (cost=5.37..5.37 rows=137 width=17) (actual time=0.000..0.000 rows=0 loops=1)"
" -> Seq Scan on spacust0 e (cost=0.00..5.37 rows=137 width=17) (actual time=0.000..0.000 rows=137 loops=1)"
"Total runtime: 750.000 ms"

이렇게 나옵니다.

Planner Method Configuration 와 Planner Cost Constants의 random_page_cost = 3 가 #으로

막혀일을때는

"Total runtime: 813.000 ms"
이정도가 나옵니다.

근데 실제환경에서는 20~30초 정도 걸린다는데 빨리 쿼리하는 방법이 없을까요?

좀 질문이 심한거 같은데 좀 부탁드립니다.