postgres에서 index가 생성되어 있는데, index scan이 아니라 seq scan을 하는지 궁금합니다.

조회수 8364회

일단 문제를 알게된 배경은 select를 할때 분명히 해당 select 문을 고려하여 인덱스를 생성해 놓았고, 개발 db(로컬 db)에서는 index scan이 의도대로 잘 이루어져 방심하고있다가.

production db에서는 seq scan을 하고있어 쿼리 실행시간(execution time)가 3배 정도 더 들고있습니다...

쿼리는 이렇습니다.

SELECT * FROM "friends" WHERE "friends"."user_id" = 1234;

인덱스는 이렇게 생성되어 있습니다.

CREATE UNIQUE INDEX index_friends_on_key_id_and_user_id ON friends USING btree (key_id, user_id);

EXPLAIN 결과는 이렇고요...

Seq Scan on friends  (cost=0.00..97182.54 rows=75 width=1) (actual time=248.244..370.122 rows=64 loops=1)
  Filter: (user_id = 1234)
  Rows Removed by Filter: 3289314
Planning time: 0.044 ms
Execution time: 370.145 ms
Index Scan using index_friends_on_key_id_and_user_id on friends  (cost=0.43..117476.69 rows=75 width=1) (actual time=1.280..80.742 rows=64 loops=1)
  Index Cond: (user_id = 1234)
Planning time: 0.039 ms
Execution time: 80.791 ms

강제로라도 index를 타보려고 SET enable_seqscan = OFF; 을 수행하니까 바로 index scan을 하더라고요.

위에서 보이는 것처럼 실행시간이 3배정도 차이나는데

왜 postgres의 optimizer는 SET enable_seqscan = OFF;을 해줘야만 index scan을 할까요.

또 특이한 현상은 로컬 db에서는 SET enable_seqscan = OFF;을 하지 않아도 의도한대로 index scan을 합니다. production db에서 seq scan을 하고있습니다.

로컬 db는 PostgreSQL 9.4.4 on x86_64-apple-darwin14.3.0, compiled by Apple LLVM version 6.1.0 (clang-602.0.53) (based on LLVM 3.6.0svn), 64-bit

aws rds에 올라가있는 production db는 PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit 입니다.

얕은 저의 지식으로는 무언가 설정 차이 일꺼같은데... 어떻게 접근을 해봐야할까요?

  • (•́ ✖ •̀)
    알 수 없는 사용자

2 답변

  • local DB와 production DB의 데이터 건수에 따라 optimizer가 index scan을 할지 sequential scan을 할지 선택하게 됩니다. SELECT가 전체 row수의 5-10% 이상을 가져오는 경우 sequential scan이 훨씬 더 빠르다고 합니다. (index scan은 인덱스 블럭을 읽어온 후 데이터 블럭을 읽어야하기 때문에 더 느릴 수 있습니다.) 아마 production DB에서 select한 row수가 전체 row수의 5% 이상이라고 optimizer가 판단한 것 같습니다. ANALYZE table을 실행하여 통계 정보를 업데이트한 후 execution plan을 확인해보면 다른 결과가 나올 수도 있습니다.

    그리고, 한 가지 궁금한 점이 있는데 예로 드신 쿼리의 where문이 user_id = 1234니까 index를 (key_id, user_id)가 아니라 (user_id, key_id)에 거는 것이 더 낫지 않나요?

    • (•́ ✖ •̀)
      알 수 없는 사용자
    • (•́ ✖ •̀)
      알 수 없는 사용자
    • 이확영님 답변 정말 감사드립니다. 일단 답변주신것에 추가 질문 먼저 드리겠습니다. 일단 제가 위에서 제시한 쿼리의 결과는 전체 row수의 5%에 미치지 않습니다. 300만 row중에서 70개의 row를 가져오는 쿼리입니다. 그리고 production db랑 최대한 똑같은 상황으로 로컬에서도 시험해보기위해서 production db를 덤프하여 로컬 db에 restore 한 뒤 시험해본 내용이므로 데이터양 차이에 의한 것은 아닌것으로 판단하고있습니다. 그리고 말씀해주신대로 `ANALYZE friends;`를 수행 한뒤 해봐도 똑같이 seq scan을 하는 증상이 있습니다... ㅠㅠ 알 수 없는 사용자 2016.3.26 13:20
    • 마지막 문단에 질문주신 내용에 대한 답변을 하겠습니다. 위 인덱스의 첫번째 목적은 사실 unique를 위한 것이였습니다... 부가적인 목적으로 조회시 빠르게 가져오려는 목적이 있는건데... 말씀해주신대로 (key_id, user_id)가 아니라 (user_id, key_id) 이렇게 걸었을때 어떠한 차이가 있는건가요? 알 수 없는 사용자 2016.3.26 13:22
    • 300만 row 중 최종 결과는 70개이지만 가져와서 조건에 맞는지 검사해야할 데이터는 더 많아서 그런 것 같습니다. local DB와 동일한 데이터인데 execution plan이 다른 것은 이해가 안 가네요 ^^ OS나 메모리 등의 실행환경도 영향을 주는 것 같습니다. 알 수 없는 사용자 2016.3.26 22:29
  • multicolumn B-Tree index의 경우 인덱스 컬럼에 나열된 순서대로 traverse하기 때문에 (즉, key_id, user_id의 순) key_id에 조건이 없으면 인덱스를 타지 않을 수도 있습니다. 인덱스를 (user_id, key_id)로 변경하시면 항상 index scan을 하기 때문에 성능에 큰 향상이 있을 것입니다.

    결합 인덱스와 관련해서 아래 글을 참고하세요.

    http://databaser.net/moniwiki/wiki.php/%EA%B2%B0%ED%95%A9%EC%9D%B8%EB%8D%B1%EC%8A%A4%EC%9D%98%EC%82%AC%EC%9A%A9

    • (•́ ✖ •̀)
      알 수 없는 사용자

답변을 하려면 로그인이 필요합니다.

프로그래머스 커뮤니티는 개발자들을 위한 Q&A 서비스입니다. 로그인해야 답변을 작성하실 수 있습니다.

(ಠ_ಠ)
(ಠ‿ಠ)