자료구조에 특별한 문제가 없어보입니다.

문제는 반드시

where 절에

'user_key 는 누구다' 는 식의 조건이 붙으면

의도된 대로 잘 작동 할 것 같네요.

select u.nick, f.pk from test_user u, test_friend f

where

f.user_key = 1

and u.pk = f.friend_key

의도했던 대로 인덱스를 쓸 것이고 비용도 적을 것 같네요.

문제는 전체 회원의 전체 친구 목록을 출력해야하는 그런 무지막지한 요구를 한다면, db 쿼리하는 사람이 "못해, 배째" 하면서 딱 잘라야죠. :)

의미없는 출력입니다.

더 이상의 다른 묘책이 없는 것입니다.

그래도 뽑아라고 한다면,

응용프로그램에서 일단 전체 회원 번호를 뽑고 하나씩 윗 쿼리를 사용해서 쭉 합치는 것이 full scan 보다는 비용이 적을 것같습니다. db입장에서 보면요.

감사합니다.

먼저 말씀 주신 것처럼, 사용자 PK 에 대한, 조건은 추가를 하는데요,

제 질문이 조금 성급했던거 같습니다.

하지만,

그래도 INDEX 를 타지 않습니다.

아래는 조건을 주었을때, Query 와 PLAN 입니다.

EXPLAIN ANALYZE
SELECT
 TEST_USER.NICK,
 TEST_FRIEND.PK
FROM
 TEST_USER,
 TEST_FRIEND
WHERE
 FRIEND_KEY = TEST_USER.PK AND
 TEST_FRIEND.USER_KEY = 1

QUERY PLAN                 
------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=2.49..2.67 rows=6 width=13) (actual time=0.250..0.359 rows=6 loops=1)
   Merge Cond: ("outer"."?column3?" = "inner".friend_key)
   ->  Sort  (cost=1.27..1.29 rows=10 width=13) (actual time=0.141..0.165 rows=8 loops=1)
         Sort Key: (test_user.pk)::numeric
         ->  Seq Scan on test_user  (cost=0.00..1.10 rows=10 width=13) (actual time=0.016..0.058 rows=10 loops=1)
   ->  Sort  (cost=1.23..1.24 rows=6 width=14) (actual time=0.082..0.102 rows=6 loops=1)
         Sort Key: test_friend.friend_key
         ->  Seq Scan on test_friend  (cost=0.00..1.15 rows=6 width=14) (actual time=0.013..0.042 rows=6 loops=1)
               Filter: (user_key = 1::numeric)
 Total runtime: 0.578 ms
(10 rows)

그래서 다른 방법을 써보았습니다.

EXPLAIN ANALYZE
SELECT
 TEST_USER.NICK,
 FRIEND.PK
FROM
 ( SELECT PK, USER_KEY, FRIEND_KEY FROM TEST_FRIEND WHERE USER_KEY = 1 ) AS FRIEND
 JOIN TEST_USER ON ( FRIEND.FRIEND_KEY = TEST_USER.PK )

                                                    QUERY PLAN                 
------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=2.49..2.67 rows=6 width=13) (actual time=0.227..0.336 rows=6 loops=1)
   Merge Cond: ("outer".friend_key = "inner"."?column3?")
   ->  Sort  (cost=1.23..1.24 rows=6 width=14) (actual time=0.085..0.107 rows=6 loops=1)
         Sort Key: test_friend.friend_key
         ->  Seq Scan on test_friend  (cost=0.00..1.15 rows=6 width=14) (actual time=0.014..0.047 rows=6 loops=1)
               Filter: (user_key = 1::numeric)
   ->  Sort  (cost=1.27..1.29 rows=10 width=13) (actual time=0.119..0.144 rows=8 loops=1)
         Sort Key: (test_user.pk)::numeric
         ->  Seq Scan on test_user  (cost=0.00..1.10 rows=10 width=13) (actual time=0.008..0.052 rows=10 loops=1)
 Total runtime: 0.418 ms
(10 rows)

하지만, 크게 달라지는 것이 없었습니다.

Sequence Scan 을 하지 않고, Index Scan 을 하여, 속도를 개선해 보고 싶은데요,

어떤 방법이 있을까요?

친절한 답변에 다시 한번 감사드립니다.

장마철 비피해 없으시길 바랍니다.

형이 틀려서 인덱스를 타지 못하는 것 아닌가요

FRIEND_KEY     : numeric 

TEST_USER.PK : int

numeric 을 int로 바꾸어 보시면 어떨가요

실수가 아니고 큰수가 필요하다면 bigint 사용을 고려해 보시구요..

데이터가 적기 때문에 옵티마이저가 풀스캔이 더 낫다라고 판단할 수 있습니다.

인덱스를 탈 때의 쿼리플랜을 알아보시려면

set enable_seqscan=false;

하시고 쿼리를 해보시면 인덱스를 강제로 타게 할 수 있습니다.

인덱스를 강제로 탈 때의 쿼리플랜을 보시면 Index cond 라는게 없으면 해당 인덱스를 탈 수 없지만 그 인덱스를 이용해서 각 row를 가져오는 것 입니다. pgsql FAQ에 보시면 인덱스를 왜 안타는지에 대한 답변으로 이럴 경우 디스크 random seek가 일어나므로 오히려 성능에 안좋게 됩니다.

강제로 인덱스를 타게 했을때의 쿼리플랜도 한번 보여주시면 좋겠네요~

그리고 where으로 검색조건을 줬을때와 안줬을때의 쿼리플랜이 확 바뀔 수 있습니다. 예를 들어 뷰를 만들때 쓰는 쿼리는 쿼리플랜이 엉망이지만 그 뷰를 사용할때 조건들이 들어가면 쿼리플랜이 말끔해 지는 경우도 많습니다.

두분의 친절한 답변에 정말 감사 드립니다.

1. casting 을 해도, index 를 타지 않습니다.

2. set enable_seqscan=false 를 해도 query plan 은 위와 같이 나옵니다.

음...

답변 주셨는데, 제가 미숙한것인지, 잘 이해를 못 하는것인지 또 다른 질문을 어찌 드려야 할지 모르겠습니다.

2번 경우에 대하여, query 와 plan 을 다시 한번 남겨봅니다.

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

set enable_seqscan=false;
SET

EXPLAIN ANALYZE
SELECT
  TEST_USER.NICK,
  FRIEND_INFO.USER_KEY,
  FRIEND_INFO.FRIEND_KEY
FROM
  ( SELECT USER_KEY, FRIEND_KEY FROM TEST_FRIEND WHERE USER_KEY = 1 ) AS FRIEND_INFO
  JOIN TEST_USER ON ( FRIEND_INFO.FRIEND_KEY = TEST_USER.PK );

                                                              QUERY PLAN       
---------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=100000004.42..100000004.60 rows=6 width=29) (actual time=0.276..0.386 rows=6 loops=1)
   Merge Cond: ("outer".friend_key = "inner"."?column3?")
   ->  Sort  (cost=3.16..3.17 rows=6 width=20) (actual time=0.121..0.143 rows=6 loops=1)
         Sort Key: test_friend.friend_key
         ->  Index Scan using idx_user_key on test_friend  (cost=0.00..3.08 rows=6 width=20) (actual time=0.025..0.053 rows=6 loops=1)
               Index Cond: (user_key = 1::numeric)
   ->  Sort  (cost=100000001.27..100000001.29 rows=10 width=13) (actual time=0.125..0.151 rows=8 loops=1)
         Sort Key: (test_user.pk)::numeric
         ->  Seq Scan on test_user  (cost=100000000.00..100000001.10 rows=10 width=13) (actual time=0.021..0.066 rows=10 loops=1)
 Total runtime: 15.341 ms
(10 rows)

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

다시 한번 두분의 답변에 감사드리고요, 저도 계속 방법을 찾아보고 해결이 되면, 바로 또 글 올리도록 하겠습니다.

아무쪼록, 다른분들도 뭔가 다른 해법이 있다면 답글 부탁드립니다.

감사합니다.

FRIEND_KEY 는 index안거셧는데,.....

잘못본건가?...

아, 죄송합니다.

질문에는 CREATE INDEX 가 빠져있는데요,

실제로는 FRIEND_KEY 에 INDEX 가 걸려 있습니다.

QUERY PLAN 은 FRIEND_KEY 에 INDEX 가 걸려 있는 것이고요,

죄송합니다.

1. casting 을 해도, index 를 타지 않습니다.

자료형이 일치하지 않는것이 문제 아닌가요

어떻게 casting을 하신건지요

형이 다른 두 인덱스간 비교를 하는데 casting 한다고 바로 비교가 될것 갈지 않은데요