postgresql partial index

PostgreSQL의 부분 인덱스 (Partial Index)는 테이블의 특정 부분에만 인덱스를 적용하는 기능.

전체 테이블에 인덱스를 생성하는 것이 아니라, 주어진 WHERE 조건을 만족하는 행들에만 인덱스 생성가능.

postgresql의 경우 null 데이타도 인덱스에 포함이 되기 때문에 필요이상의 용량과 성능에 영향을 주게된다. (Oracle은 null은 포함되지 않는다)

신규 컬럼을 추가할때 not null, null 에 대한 고민을 조금은 풀어줄만한 기능으로 보여진다.

 

장점

1. 공간 효율성 : 부분 인덱스는 전체 테이블에 인덱스를 생성하는 것보다 디스크 공간을 덜 사용한다.
2. 성능 향상 : WHERE 절의 조건이 인덱스에 매핑되는 경우, 해당 인덱스는 쿼리 성능 향상에 크게 기여할 수 있다.
3. 유지비용 감소 : 인덱스가 작으면, 데이터베이스에서 데이터를 추가, 수정, 삭제할 때 인덱스를 유지하는 데 필요한 작업도 줄어든다.
   
   

예제

예를 들어, asset 테이블에서 delete_flag 컬럼이 true인 행은 거의 수행되지 않는다고 가정한다.

이런 경우, delete_flag가 false인 행들만을 대상으로 인덱스를 생성하면 쿼리 성능을 향상시킬 수 있다.

CREATE INDEX idx_asset_not_deleted ON asset(updated_date) WHERE delete_flag = false;

 

위의 인덱스는 delete_flag가 false인 행들의 updated_date 컬럼에만 적용됩니다.

사용 시 고려사항

1. 쿼리 최적화: PostgreSQL 쿼리 플래너는 부분 인덱스를 사용할 수 있도록 쿼리가 작성되어야 한다. 즉, 쿼리의 WHERE 절이 인덱스의 조건과 일치해야 한다.
2. 데이터 분포: 부분 인덱스는 특정 조건을 만족하는 행들이 일정 부분을 차지할 때 가장 효과적이다.
   데이터의 분포와 패턴을 잘 알아야 효율적인 부분 인덱스를 설계할 수 있다.

 

요약하면, 부분 인덱스는 PostgreSQL에서 특정 조건을 만족하는 행들에 대해서만 인덱스를 생성하는 기능이다.

이를 통해 디스크 공간, 인덱스 유지비용을 절약하고 쿼리 성능을 향상시킬 수 있다.

 

 

[부분 인덱스와 같이 사용 될만한 쿼리 기능]

NULLS FIRST 및 NULLS LAST

PostgreSQL에서 ORDER BY 절에서 사용할 수 있는 옵션

이 옵션들을 사용하면 NULL 값의 정렬 순서를 지정할 수 있다.

기본적으로 PostgreSQL의 B-tree 인덱스에서 오름차순(ASC) 정렬 시 NULL 값은 마지막에 위치하며, 내림차순(DESC) 정렬 시 NULL 값은 처음에 위치한다.

그러나 때로는 이러한 기본 동작과 다른 방식으로 NULL 값을 정렬해야 할 경우가 있다.

이때 NULLS FIRST 및 NULLS LAST 옵션을 사용하여 원하는 정렬 순서를 지정할 수 있다.

 

예제

1. NULLS FIRST를 사용한 오름차순 정렬이 쿼리는 column_name을 오름차순으로 정렬하되, NULL 값은 최상단에 위치시킨다.

select * from asset ORDER BY updated_user_id ASC NULLS FIRST;

2. NULLS LAST를 사용한 오름차순 정렬

select * from asset ORDER BY updated_user_id ASC NULLS LAST;

2. NULLS FIRST를 사용한 내림차순 정렬

select * from asset ORDER BY updated_user_id DESC NULLS FIRST;

 

 

주의사항

• NULLS FIRST 및 NULLS LAST 옵션은 ORDER BY 절에서만 사용할 수 있다.
• 인덱스와 관련하여, 특정 정렬 순서와 NULL 처리 방식을 자주 사용한다면, 해당 정렬 순서와 옵션을 고려하여 인덱스를 생성하는 것이 쿼리 성능에 도움이 될 수 있다.