인덱스 온리 스캔 불가능

인덱스 온리 스캔은 쿼리에서 필요한 모든 데이터가 인덱스에 포함되어 있을 때만 가능하며, 그렇지 않은 경우 실제 테이블 데이터를 읽기 위해 디스크 접근이 필요합니다.

 

SELECT name, age, phone
FROM example_table
WHERE name = 'Alice' AND age = 30;

 

상황 설명

1. 인덱스 온리 스캔 불가: 위 쿼리는 name과 age뿐만 아니라 phone 컬럼도 필요로 합니다. 하지만 인덱스 idx_name_age는 name과 age만 포함하고 있으며 phone은 포함하지 않습니다.
2. 테이블 접근 필요: 쿼리에서 phone 컬럼의 값도 필요하므로, 인덱스를 사용하여 name과 age에 대한 조건을 만족하는 행을 찾은 후, 해당 행의 phone 값을 얻기 위해 실제 테이블 데이터를 읽어야 합니다.

실행 계획

 

EXPLAIN ANALYZE
SELECT name, age, phone
FROM example_table
WHERE name = 'Alice' AND age = 30;

 

실행 계획을 통해 PostgreSQL이 실제로 어떻게 데이터를 가져오는지 확인할 수 있습니다. 일반적으로는 다음과 같은 단계를 포함합니다:

1. 인덱스 스캔: 인덱스 idx_name_age를 사용하여 name = 'Alice'와 age = 30인 행의 포인터(예: rowid)를 찾습니다.
2. 테이블 접근: 인덱스를 통해 얻은 포인터를 사용하여 실제 테이블에서 해당 행을 읽어옵니다. 여기서 name, age, phone 모든 컬럼의 값을 가져옵니다.

성능 영향

• 디스크 접근 증가: 인덱스 온리 스캔을 사용할 수 없는 상황이므로, 디스크에 추가적인 접근이 필요합니다. 이는 디스크 I/O가 증가함을 의미합니다.
• 성능 저하: 디스크 접근은 메모리 접근보다 훨씬 느리기 때문에, 성능이 저하될 수 있습니다.

 

성능 최적화 방안

성능 저하를 방지하기 위해 몇 가지 방안을 고려할 수 있습니다:

 

커버링 인덱스 사용: 쿼리에서 자주 사용하는 모든 컬럼을 포함하는 인덱스를 생성합니다. 예를 들어, name, age, phone 모두를 포함하는 인덱스를 생성할 수 있습니다:

 

CREATE INDEX idx_name_age_phone ON example_table (name, age, phone);

이렇게 하면, 위 쿼리는 인덱스 온리 스캔을 사용할 수 있습니다.

1. 쿼리 최적화: 쿼리에서 불필요한 컬럼을 제외하거나, 필요한 경우 컬럼을 최적화하여 사용하는 방법을 고려합니다.
2. 데이터 모델링: 테이블 구조와 인덱스를 쿼리 패턴에 맞게 설계하여 디스크 I/O를 최소화합니다.

결론

쿼리가 인덱스에 포함되지 않은 컬럼을 요구할 때, PostgreSQL은 해당 컬럼의 값을 가져오기 위해 실제 테이블을 읽어야 합니다. 이는 디스크 접근을 증가시키고 성능 저하를 초래할 수 있습니다. 성능을 최적화하기 위해 커버링 인덱스를 사용하는 것이 효과적입니다.

 

 

4o