좋은 정보 고맙습니다.

성철님이 직접 테스트 해보고 결과를 알려주는 것 밖에는... :)

검증되지 않아서 실무에 써보질 않았습니다.

DSN에 한번 false 해서 상태를 볼까요?

엄... 정보는요... 헛짓하지 말라는 소리 들을 각오하고 급한 맘에 올려본건데요... -.-;;

몇가지 test를 해본 결과는...

몇몇의 경우는 index scan이 seq scan보다 비효율적이어서 더 많은 시간이 걸렸습니다. 그런데 그 차이는 그렇게 크지 않았습니다.

또 다른 경우는 seq scan보다 index scan이 빨랐습니다. 그런데 아주 많이 빨랐습니다. -.-;;

지금까지는 index scan을 우선하도록 한 이 조치가 긍정적인 것 같네요. 그리고 가용한 index가 없을 경우에는 seq scan 언제라도 사용합니다.

대표적으로 문제가 되었던 쿼리의 결과를 첨부합니다.

특정 카테고리 안의 고객 수를 세는 쿼리입니다.

pcrm_up=# EXPLAIN ANALYZE select count(*) from clients where fl_enable='Y' and code_deny_delivery<>'A' and client_id in (select client_id from client_categories where category_id in (10, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22,23,24,25,26,27,28,29,30));
                                                                                                                                                                                                                                           QUERY PLAN                                                                                                                                                                                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=154675.86..154675.86 rows=1 width=0) (actual time=53146.436..53146.437 rows=1 loops=1)
   ->  Hash Join  (cost=118937.20..154636.51 rows=15742 width=0) (actual time=48118.788..53142.077 rows=2206 loops=1)
         Hash Cond: ("outer".client_id = "inner".client_id)
         ->  Seq Scan on clients  (cost=0.00..21545.56 rows=558089 width=4) (actual time=0.039..3782.342 rows=480374 loops=1)
               Filter: ((fl_enable = 'Y'::bpchar) AND (code_deny_delivery <> 'A'::bpchar))
         ->  Hash  (cost=118676.06..118676.06 rows=35253 width=4) (actual time=45263.833..45263.833 rows=0 loops=1)
               ->  Unique  (cost=118432.47..118676.06 rows=35253 width=4) (actual time=45236.254..45241.195 rows=2206 loops=1)
                     ->  Sort  (cost=118432.47..118554.27 rows=48719 width=4) (actual time=45236.250..45237.066 rows=2206 loops=1)
                           Sort Key: client_categories.client_id
                           ->  Seq Scan on client_categories  (cost=0.00..114639.16 rows=48719 width=4) (actual time=0.128..45224.807 rows=2206 loops=1)
                                 Filter: ((category_id = 10) OR (category_id = 12) OR (category_id = 13) OR (category_id = 14) OR (category_id = 15) OR (category_id = 16) OR (category_id = 17) OR (category_id = 18) OR (category_id = 19) OR (category_id = 20) OR (category_id = 21) OR (category_id = 22) OR (category_id = 23) OR (category_id = 24) OR (category_id = 25) OR (category_id = 26) OR (category_id = 27) OR (category_id = 28) OR (category_id = 29) OR (category_id = 30))
 Total runtime: 53147.613 ms
(12 rows)

보시는 것 처럼 seq scan과 hash join으로 처리를 합니다.

이번에는 enable_seqscan을 false로 하고 동일한 질의를 실행합니다.

pcrm_up=# set enable_seqscan=false;
SET
pcrm_up=# EXPLAIN ANALYZE select count(*) from clients where fl_enable='Y' and code_deny_delivery<>'A' and client_id in (select client_id from client_categories where category_id in (10, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22,23,24,25,26,27,28,29,30));
                                                                                                                                                                                                                                                                                                                                                                                  QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                   
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=341746.74..341746.74 rows=1 width=0) (actual time=155.887..155.888 rows=1 loops=1)
   ->  Nested Loop  (cost=177703.21..341707.38 rows=15742 width=0) (actual time=42.527..151.580 rows=2206 loops=1)
         ->  Unique  (cost=177703.21..177946.80 rows=35253 width=4) (actual time=42.445..47.581 rows=2206 loops=1)
               ->  Sort  (cost=177703.21..177825.01 rows=48719 width=4) (actual time=42.443..43.212 rows=2206 loops=1)
                     Sort Key: client_categories.client_id
                     ->  Index Scan using idx_client_categories_cat_id, idx_client_categories_cat_id, idx_client_categories_cat_id, idx_client_categories_cat_id, idx_client_categories_cat_id, idx_client_categories_cat_id, idx_client_categories_cat_id, idx_client_categories_cat_id, idx_client_categories_cat_id, idx_client_categories_cat_id, idx_client_categories_cat_id, idx_client_categories_cat_id, idx_client_categories_cat_id, idx_client_categories_cat_id, idx_client_categories_cat_id, idx_client_categories_cat_id, idx_client_categories_cat_id, idx_client_categories_cat_id, idx_client_categories_cat_id, idx_client_categories_cat_id on client_categories  (cost=0.00..173909.90 rows=48719 width=4) (actual time=0.457..31.574 rows=2206 loops=1)
                           Index Cond: ((category_id = 10) OR (category_id = 12) OR (category_id = 13) OR (category_id = 14) OR (category_id = 15) OR (category_id = 16) OR (category_id = 17) OR (category_id = 18) OR (category_id = 19) OR (category_id = 20) OR (category_id = 21) OR (category_id = 22) OR (category_id = 23) OR (category_id = 24) OR (category_id = 25) OR (category_id = 26) OR (category_id = 27) OR (category_id = 28) OR (category_id = 29) OR (category_id = 30))
         ->  Index Scan using clients_pkey on clients  (cost=0.00..4.63 rows=1 width=4) (actual time=0.036..0.041 rows=1 loops=2206)
               Index Cond: (clients.client_id = "outer".client_id)
               Filter: ((fl_enable = 'Y'::bpchar) AND (code_deny_delivery <> 'A'::bpchar))
 Total runtime: 158.275 ms
(11 rows)

엄청난 속도 향상이... -.-;;

clients table에는 약 50만명이 등록되어 있고 client_categories table에는 170만건이 등록되어 있네요.

물론 vacuum analyze는 한 상황입니다.

헛 제가 사용하고 있습니다 -.-;;

단 특정 페이지에서만요

게시물 리스팅 할 부분 있으면 그 대만 set enable_seqscan=false 했다가 리스팅 끝나면 다시 true 로 설정해 주고 있습니다.

그렇군요.

저도 목록을 가지고 올 때에만 했으면 좋겠는데 프로그램을 DBMS에 독립적으로 돌아가도록 짜고 있어서 가능하면 특정 DB의 특성에 맞는 부분은 코드에서 빼려고 하고 있습니다.

몇가지 더 test를 해봤는데요. 엉뚱한 index를 만들지 않았다면 enable_seqscan을 false로 해도 큰 문제는 없겠더군요. 이렇게 설정을 해도 억지로 관계도 없는 index를 아무거나 가져다가 쓰는 바보 같은 짓은 안하네요.

그리고 좀전에 다시 해보았는데 random_page_cost을 기본값인 4에서 3으로 낮추니 아까는 안타던 index를 타기 시작하네요. 제가 아까 설정을 바꾸고 reload를 안했었는지 2까지 낮춰도 seq scan만 하던놈이...

일단 random_page_cost를 3 이하로 낮춰서 DB 성능을 지켜보다가 그래도 안되면 postgresql.conf의 enable_seqscan을 false로 해야 할 것 같습니다.

참고로 좀 전에 test했던 것 붙여 봅니다.

먼저 clients table에서 탈퇴하지 않은 고객 수를 새어 봤습니다. 대부분의 고객이 탈퇴를 하지 않았기 때문에 만약 인덱스를 탄다면 seq scan 보다 더 많은 비용이 들겁니다.

pcrm_up=# explain analyze select count(*) from clients where fl_enable='Y';
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=21513.69..21513.69 rows=1 width=0) (actual time=3989.184..3989.184 rows=1 loops=1)
   ->  Seq Scan on clients  (cost=0.00..20118.46 rows=558089 width=0) (actual time=0.023..3077.294 rows=480387 loops=1)
         Filter: (fl_enable = 'Y'::bpchar)
 Total runtime: 3989.498 ms
(4 rows)

예상과 같이 seq scan으로 처리하네요.

이번에는 enable_seqscan을 false로 하고 동일한 질의를 처리합니다.

pcrm_up=# set enable_seqscan=false;
SET
pcrm_up=# explain analyze select count(*) from clients where fl_enable='Y';
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=100021513.69..100021513.69 rows=1 width=0) (actual time=3583.332..3583.333 rows=1 loops=1)
   ->  Seq Scan on clients  (cost=100000000.00..100020118.46 rows=558089 width=0) (actual time=0.023..2678.638 rows=480387 loops=1)
         Filter: (fl_enable = 'Y'::bpchar)
 Total runtime: 3583.583 ms
(4 rows)

앞에서 말씀 드린 것 처럼 enable_seqscan이 false라고 해도 아무 인덱스나 집어다가 억지로 타지는 않네요.

억지로 인덱스를 타도록 유도해 보겠습니다.

pcrm_up=# explain analyze select count(*) from clients where client_id > 0 and fl_enable='Y';
                                                                  QUERY PLAN                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1153179.01..1153179.01 rows=1 width=0) (actual time=8075.142..8075.143 rows=1 loops=1)
   ->  Index Scan using clients_pkey on clients  (cost=0.00..1151783.92 rows=558033 width=0) (actual time=0.679..7157.519 rows=480387 loops=1)
         Index Cond: (client_id > 0)
         Filter: (fl_enable = 'Y'::bpchar)
 Total runtime: 8075.420 ms
(5 rows)

예상과 같이 seq scan보다 몇배 더 걸려서 처리를 합니다.