[PostgreSQL]포스트그레 인덱스 생성, 확인, 삭제하기

오늘은 PostgreSQL에서 Index를 생성, 확인, 삭제하는 방법에 대해서

정리를 해보고자 합니다.

포스트그레를 포함한 거의 모든 디비에서 인덱스 기능을 지원합니다.

이 인덱스는 잘 쓰면 좋지만, 잘못쓰면 오히려 성능을 떨어트리는 양날의 칼과 같은 것입니다.

그래서 오늘은 인덱스란 무엇이며 주의점은 무엇인지,

그리고 생성, 확인, 삭제하는 방법에 대해서

정리를 해보고자 합니다.

 

 

 

1. Index란??

우선 인덱스란 무엇인지에 대해서

정리를 해보도록 하겠습니다.

저는 여러 책이나 그런 곳에서 보셨을 것이기 때문에

자세한 내용을 적지는 않도록 하겠습니다.

그냥 간단하게 인덱스를 정리하면 색인하는 것입니다.

책을 읽을 때 색인을 해놓으면 해당 페이지를 쉽게 찾을 수 있는 것입니다.

그것처럼 인덱스를 생성해놓으면 데이터를 찾기는 좋습니다.

빠른 속도로 찾을 수 있습니다.

 

대신, 주의해야 할 점들도 있습니다.

1) CRUD가 많은 컬럼은 불리

Index가 걸린 컬럼에 Insert, Update 등이 자주 일어나게 되면 불리합니다.

이런 CRUD 작업이 있을 때마다 인덱스의 색인을 변경하는 작업이 같이 일어나게 됩니다

그렇기 때문에 이럴 때마다 서버에 약간의 부하는 더 줄 수 있습니다.

그렇기 때문에 자주 변하지 않는 컬럼일 때 해주는 것이 좋습니다.

2) where절에 사용되는 컬럼

제가 위에서 얘기한 것처럼 색인작업을 하는 것입니다.

그렇기 때문에 찾기에는 유리합니다.

그런데 where절에 거의 사용을 안한다?

그러면 굳이 index를 만들 필요가 없는 것입니다.

빠른 select 등을 하기 위해 만드는 것이 index이기 때문에 where에서 사용이 거의 없다면

굳이 쓸 필요가 없습니다.

3) 중복인덱스

중복인덱스를 주의해야 합니다.

동일 컬럼에 인덱스를 여러개 생성할 수 있습니다.

이 때 주의를 해야 합니다.

힌트 등으로 무슨 인덱스를 사용해야 할 지를

강제로 지정해주는 것이 아니면 시스템에서는 자동으로 최적의 인덱스를

지정하여 사용합니다.

이 때 잘못된 인덱스를 탈 수도 있습니다.

그러면 인덱스가 없을 때보다도 더 성능이 안좋을 수 있습니다.

 

 

 

2. 인덱스 생성

이번에는 인덱스를 생성하는 방법에 대해서

정리를 해보도록 하겠습니다.

인덱스를 생성하는 쿼리는 다음과 같습니다.

 

CREATE INDEX [인덱스명] ON [테이블명] ( [컬럼명] )
;

위처럼 쿼리를 작성하여 주시면 되십니다.

인덱스명, 테이블명, 컬럼명 등을 원하시는 값으로 변경하여주시면 되십니다.

 

저는 아래처럼 쿼리를 작성하였습니다.

 

위 쿼리에서 인덱스명, 테이블명, 컬럼명 등을 변경한 것입니다.

제가 운영하는 DB이다보니

이런 정보들은 숨겨놓았습니다.

 

위처럼 쿼리를 작성하고 실행해보았습니다.

결과는 위처럼 나왔습니다.

그냥 인덱스 생성 쿼리가 정상적으로 성공했다는 쿼리입니다.

 

 

 

반응형

 

3. Index 확인

2번에서 인덱스를 생성하였으니

정상적으로 생성이 되었는지 확인이 필요합니다.

인덱스를 확인하는 쿼리는 다음과 같습니다.

 

SELECT * FROM pg_indexes WHERE tablename = '[테이블명]'
;

 

위 쿼리에서는 주의점이 총 2개가 있습니다.

1) 반드시 테이블명을 ''으로 감싸주어야 한다.

2) 대소문자를 구분한다.그러니 대소문자에 주의할 것

 

 

위 쿼리를 보면 테이블명이 조건절입니다.

그렇기 때문에 테이블명을 꼭 ''으로 감싸주어야 합니다.

또한, 대소문자도 구분하오니 대소문자도 신경써야 합니다.

 

 

저는 위처럼 쿼리를 작성하였습니다.

2번에서 인덱스를 생성한 테이블명을 조건에 넣었습니다.

그리고 실행을 하면 결과는 아래처럼 나오게 됩니다.

 

 

위처럼 해당 테이블에 인덱스가 총 3개가 있는 것으로

나와지는 것을 확인할 수 있습니다.

 

인덱스명이나 인덱스 생성 쿼리 등이 나와지는 것을 확인할 수 있습니다.

 

SMALL

 

 

4. 인덱스 삭제

인덱스를 생성하고, 확인하였으니 이제 삭제를 해보도록 하겠습니다.

Index를 삭제하는 경우는 자롬ㅅ 생성하였을 때 빼고는 

잘 없기는 합니다.

그렇지만 그래도 혹시 모르니 삭제하는 방법에 대해서도 알고 있으면 좋을 것 같습니다.

인덱스를 삭제하는 쿼리는 다음과 같습니다.

 

DROP INDEX [인덱스명]
;

위처럼 쿼리를 작성해주시면 되십니다.

2번에서 인덱스를 생성할 때 넣었던 인덱스명

혹은 3번에서 인덱스를 확인했을 때 나온 indexname 을 입력해주시면 되십니다.

그러면 해당 인덱스가 삭제가 되는 것입니다.

 

 

 

저는 위처럼 쿼리를 작성하여씃빈다.

제가 2번에서 만든 인덱스를 삭제하는 쿼리입니다.

 

위처럼 쿼리를 작성하고 실행을 하게 되면

결과는 아래처럼 나오게 됩니다.

 

위처럼 정상적으로 drop index 쿼리가 실행되었다고 나오고 있습니다.

 

이제 3번에서 확인했던 방법으로 인덱스를 다시 확인해보도록 하겠습니다.

아까는 총 3개의 인덱스가 있었습니다.

3번에서의 쿼리를 그대로 실행해보았습니다.

결과는 아래처럼 나와지는 것을 확인하였습니다.

위처럼 인덱스가 총 2개인 것으로 확인이 되었습니다.

즉, 4번에서 drop index를 한 것이 정상적으로 되어 인덱스가 삭제가 된 것을

확인할 수 있습니다.

 

 

 

 

오늘은 포스트그레에서 인덱스를 생성, 확인, 삭제하는 방법에 대해서 

정리를 해보았습니다.

Index는 제가 초반에도 얘기를 했지만 양날의 검과 같습니다.

장점과 단점이 명확하기 때문에

잘 쓰면 좋지만 잘못쓰게 되면 서버의 성능과 퍼포먼스를 낮출 수 있습니다.

그러니 상황에 맞게 생성하고 사용을 잘 해주시면 좋을 것 같습니다.

 

 

 

 

이상으로 PostgreSQL에서 Index 생성, 확인, 삭제하는 방법이였습니다~

 

 

 

 

이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다