일단은 1번에 한표...

당장은 2번이 더 낮은 비용으로 처리된다고 나올지 모르지만 자료량이 달라지면 또 비용 산정도 달라질 것일테고 DBMS가 이해하기 쉬운 가장 집적적인 방법으로 질의문을 만드는 것이 가장 좋은 것은 같아서요.

explain analyze를 해보시지 그랬어요. 설정에 따라서 query plan에서 산정한 가상의 비용과 실 비용이 차이가 나는 경우가 많더군요.

지금 표시된 수행 시간은 의미가 없구요 비용을 가지고 비교를 해야 하는데 역시 쿼리 플렌이 단순한 2번이 비용도 적네요. 466.27. 1번은 2950.82... merge join이 join 방법중에 가장 비용이 많이 드는 방법이니까 역시 2번의 비용이 적은 것으로 나오네요.

1번 쿼리에서 where 절에 b.gif=174 and p.gid=174도 추가시켜보시면 어떨까요?

2번과 3번의 쿼리 플렌이 다른 것이 의외네요.

일단은 1번 쿼리로 2번 플렌이 나오게 하는 것이 목표일 듯 합니다.

첫번째 쿼리

MagicHome=# EXPLAIN ANALYZE SELECT c.no, c.gid, c.name, c.total, c.auth, b.eid, b.name as object_name, p.pid, p.name as page_name

MagicHome-# from
MagicHome-#   view_board_category c left join
MagicHome-#   working.view_boardbox b on c.no=b.default_board and b.gid=c.gid left join
MagicHome-#   working.view_page p on b.eid=any(p.eids) and b.gid=p.gid and c.gid=p.gid
MagicHome-# where c.gid=174;
                                                                          QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=2859.42..2950.82 rows=11 width=74) (actual time=116.762..117.416 rows=6 loops=1)
   Join Filter: ("outer".eid = ANY ("inner".eids))
   ->  Merge Left Join  (cost=2859.42..2885.81 rows=11 width=57) (actual time=116.732..116.924 rows=6 loops=1)
         Merge Cond: ("outer".gid = "inner".gid)
         Join Filter: ("outer"."no" = "inner".opti[1])
         ->  Index Scan using index_tb_board_category_gid on tb_board_category  (cost=0.00..16.29 rows=11 width=25) (actual time=0.042..0.078 rows=6 loops=1)
               Index Cond: (gid = 174)
         ->  Sort  (cost=2859.42..2864.34 rows=1967 width=74) (actual time=115.365..115.942 rows=464 loops=1)
               Sort Key: m.gid
               ->  Hash Join  (cost=49.36..2751.81 rows=1967 width=74) (actual time=11.455..107.407 rows=1902 loops=1)
                     Hash Cond: ("outer".mid = "inner".mid)
                     ->  Seq Scan on tb_object o  (cost=0.00..2668.03 rows=1967 width=74) (actual time=1.195..85.291 rows=1945 loops=1)
                           Filter: ("type" = 3::smallint)
                     ->  Hash  (cost=42.89..42.89 rows=2589 width=8) (actual time=10.213..10.213 rows=0 loops=1)
                           ->  Seq Scan on tb_member m  (cost=0.00..42.89 rows=2589 width=8) (actual time=0.011..5.771 rows=2589 loops=1)
   ->  Index Scan using index_tb_page_gid_pageid on tb_page  (cost=0.00..5.87 rows=1 width=138) (actual time=0.012..0.046 rows=8 loops=6)
         Index Cond: (("outer".gid = tb_page.gid) AND ("outer".gid = tb_page.gid))
 Total runtime: 118.299 ms
(18건 있음)
작업시간: 122.980 ms

두번째 쿼리

MagicHome=# EXPLAIN ANALYZE SELECT c.no, c.gid, c.name, c.total, c.auth, b.eid, b.name as object_name, p.pid, p.name as page_name

MagicHome-# from
MagicHome-#   (select * from view_board_category where gid=174) c left join
MagicHome-#   (select * from working.view_boardbox where gid=174) b on c.no=b.default_board and b.gid=c.gid left join
MagicHome-#   (select * from working.view_page where gid=174) p on b.eid=any(p.eids) and b.gid=p.gid and c.gid=p.gid
MagicHome-# where c.gid=174;
                                                                         QUERY PLAN                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.00..466.27 rows=1 width=74) (actual time=0.422..2.342 rows=6 loops=1)
   Join Filter: ("outer".eid = ANY ("inner".eids))
   ->  Nested Loop Left Join  (cost=0.00..460.36 rows=1 width=57) (actual time=0.405..1.856 rows=6 loops=1)
         Join Filter: (("outer"."no" = "inner".opti[1]) AND ("inner".gid = "outer".gid))
         ->  Index Scan using index_tb_board_category_gid on tb_board_category  (cost=0.00..4.03 rows=1 width=25) (actual time=0.040..0.067 rows=6 loops=1)
               Index Cond: ((gid = 174) AND (gid = 174))
         ->  Nested Loop  (cost=0.00..456.30 rows=2 width=74) (actual time=0.039..0.272 rows=6 loops=6)
               ->  Index Scan using index_tb_member_gid_uid on tb_member m  (cost=0.00..9.11 rows=2 width=8) (actual time=0.014..0.018 rows=1 loops=6)
                     Index Cond: (gid = 174)
               ->  Index Scan using index_tb_object_mid on tb_object o  (cost=0.00..223.52 rows=6 width=74) (actual time=0.015..0.216 rows=6 loops=6)
                     Index Cond: ("outer".mid = o.mid)
                     Filter: ("type" = 3::smallint)
   ->  Index Scan using index_tb_page_gid_pageid on tb_page  (cost=0.00..5.87 rows=1 width=138) (actual time=0.010..0.045 rows=8 loops=6)
         Index Cond: (("outer".gid = tb_page.gid) AND ("outer".gid = tb_page.gid))
         Filter: (gid = 174)
 Total runtime: 2.657 ms
(16건 있음)
작업시간: 8.074 ms

첫번째 쿼리에 where gid=? 추가

MagicHome=# EXPLAIN ANALYZE SELECT c.no, c.gid, c.name, c.total, c.auth, b.eid, b.name as object_name, p.pid, p.name as page_name

MagicHome-# from
MagicHome-#   view_board_category c left join
MagicHome-#   working.view_boardbox b on c.no=b.default_board and b.gid=c.gid and b.gid=174 left join
MagicHome-#   working.view_page p on b.eid=any(p.eids) and b.gid=p.gid and c.gid=p.gid and p.gid=174
MagicHome-# where c.gid=174;
                                                                          QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Left Join  (cost=456.30..535.50 rows=11 width=74) (actual time=0.486..0.839 rows=6 loops=1)
   Merge Cond: ("outer".gid = "inner".gid)
   Join Filter: ("outer".eid = ANY ("inner".eids))
   ->  Merge Left Join  (cost=456.30..472.66 rows=11 width=57) (actual time=0.441..0.618 rows=6 loops=1)
         Merge Cond: ("outer".gid = "inner".gid)
         Join Filter: ("outer"."no" = "inner".opti[1])
         ->  Index Scan using index_tb_board_category_gid on tb_board_category  (cost=0.00..16.29 rows=11 width=25) (actual time=0.043..0.070 rows=6 loops=1)
               Index Cond: (gid = 174)
         ->  Materialize  (cost=456.30..456.32 rows=2 width=74) (actual time=0.065..0.400 rows=31 loops=1)
               ->  Nested Loop  (cost=0.00..456.30 rows=2 width=74) (actual time=0.059..0.338 rows=6 loops=1)
                     ->  Index Scan using index_tb_member_gid_uid on tb_member m  (cost=0.00..9.11 rows=2 width=8) (actual time=0.020..0.024 rows=1 loops=1)
                           Index Cond: (gid = 174)
                     ->  Index Scan using index_tb_object_mid on tb_object o  (cost=0.00..223.52 rows=6 width=74) (actual time=0.026..0.271 rows=6 loops=1)
                           Index Cond: ("outer".mid = o.mid)
                           Filter: ("type" = 3::smallint)
   ->  Index Scan using index_tb_page_gid_pageid on tb_page  (cost=0.00..62.74 rows=16 width=138) (actual time=0.020..0.095 rows=17 loops=1)
         Index Cond: (gid = 174)
 Total runtime: 1.148 ms
(18건 있음)
작업시간: 5.625 ms

이렇습니다~~

두번째 쿼리로 하면 특정 상황에서 엄청나게 느려지는걸 발견 했습니다 -.-;

그래서 세번째 쿼리로 바꿨습니다 플랜은 복잡해도 성능도 좋고.. 좋네요 ㅎㅎ

성철님 감사합니다 :)

죄송한데요. 첫 쿼리의 where 조건에 p.gid=174 and b.gid=174를 추가 시켜서 결과를 알려주실 수 있나요? 궁금해서요. 부탁합니다.

첫째 쿼리는 tb_object와 tb_member를 join하는데 대부분의 시간이 소비되네요. 위의 조건을 추가하면 1번 쿼리에서도 lested join을 해서 빨라질 것 같은 생각이 듭니다.

안그래도 답글 보고 첫 쿼리에 p.gid=174 and b.gid=174를 붙여 보았는데 그 쿼리는 결과도 나오고 쿼리 플랜도 두번째 쿼리와 비슷(?)했는데 결과가 원하는 결과가 아니었습니다..

left 조인이기 때문에 p.gid, b.gid 가 null 일 수 있어서 174인 놈만 가져오라고 하면 쿼리 결과가 원하는 놈이 아니어서요~

MagicHome=# EXPLAIN ANALYZE SELECT
MagicHome-#   c.no, c.gid, c.name, c.total, c.auth, b.eid, b.name as object_name, p.pid, p.name as page_name
MagicHome-# from
MagicHome-#   view_board_category c left join
MagicHome-#   working.view_boardbox b on c.no=b.default_board and b.gid=c.gid left join
MagicHome-#   working.view_page p on b.eid=any(p.eids) and b.gid=p.gid and c.gid=p.gid
MagicHome-# where c.gid=174 and p.gid=174 and b.gid=174;
                                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..526.37 rows=8 width=74) (actual time=0.797..0.973 rows=1 loops=1)
   Join Filter: ("outer".eid = ANY ("inner".eids))
   ->  Nested Loop  (cost=0.00..463.07 rows=1 width=57) (actual time=0.126..0.550 rows=3 loops=1)
         ->  Nested Loop  (cost=0.00..456.30 rows=2 width=74) (actual time=0.088..0.401 rows=6 loops=1)
               ->  Index Scan using index_tb_member_gid_uid on tb_member m  (cost=0.00..9.11 rows=2 width=8) (actual time=0.041..0.046 rows=1 loops=1)
                     Index Cond: (gid = 174)
               ->  Index Scan using index_tb_object_mid on tb_object o  (cost=0.00..223.52 rows=6 width=74) (actual time=0.029..0.304 rows=6 loops=1)
                     Index Cond: ("outer".mid = o.mid)
                     Filter: ("type" = 3::smallint)
         ->  Index Scan using tb_board_category_pkey on tb_board_category  (cost=0.00..3.37 rows=1 width=25) (actual time=0.013..0.015 rows=0 loops=6)
               Index Cond: (tb_board_category."no" = "outer".opti[1])
               Filter: (gid = 174)
   ->  Index Scan using index_tb_page_gid_pageid on tb_page  (cost=0.00..62.74 rows=16 width=138) (actual time=0.016..0.087 rows=17 loops=3)
         Index Cond: (gid = 174)
 Total runtime: 1.253 ms
(15건 있음)

작업시간: 6.223 ms

저런... 그렇군요.

그런데 inline view에 gid=174 조건을 주는것과 주 쿼리의 where 절에 gid=174를 주는 것이 다른가요?

그러니까 세번째 쿼리를

SELECT c.no, c.gid, c.name, c.total, c.auth, b.eid, b.name as object_name, p.pid, p.name as page_name

from

(select * from view_board_category) c left join

(select * from working.view_boardbox) b on c.no=b.default_board and b.gid=c.gid left join

(select * from working.view_page) p on b.eid=any(p.eids) and b.gid=p.gid and c.gid=p.gid

where c.gid=174 and b.gid=174 and p.gid=174

이렇게 바꿔도 되는 것 아닌가요? 그렇다면 첫번째 쿼리에 and b.gid=174 and p.gid=174를 붙이는 것과 같아 보이는데....

아닌가요? 극적 극적... 뻘쭘...

outer join 의 조건을 줄 때와 마지막에 where로 검색할 때와 검색 결과가 다릅니다~

left 조인을 하게 되면 필수가 아닌 테이블의 값이 null로 나올 때도 있는데 맨 마지막에 where로 검색하게 되면 null인 값에 비교를 하게 되서 outer가 아닌 그냥 join을 할 때와 같은 효과가 나니까여

전에 이걸로 고생을 좀;;

음... 정확히는 이해 못했지만 막연히 그럴 것 같은 생각이 드네요.

앞으로 주의해야겠습니다.

좋은 정보 감사합니다.