created at 2024-02-04
Table of contents
서론
본 포스팅에서 테스트할 쿼리는 아래와 같습니다.
select * from chat where room_id = 1 order by created_at asc limit 1000;
이 메소드는 roomId 에 기반하여 최대 1000개의 row 를 createdAt ascending 순서로 가져오는 쿼리입니다. 해당 쿼리는 findAllByRoomId 메소드로 수행이 되는데요. 문제는 이 메소드의 지연시간이 매우 높게 나옵니다 😂. (약 2초 소요…)
그래서 저는 created_at 에 여러가지 인덱싱 알고리즘을 적용해보고, Postgresql 가 제공하는 Explain Analyze 를 통해 지연시간을 측정하면서 성능을 비교할 것입니다.
일단 아무런 인덱싱이 적용되지 않았을 때를 먼저 측정해 볼게요!
No Indexing - 1400ms 소요
1000개 row 가져오면서 order by 로 created_at asc 정렬 시 1400 ms 가 소요되었습니다. 보시면 Seq scan 이 수행되고 있습니다. 이는 인덱싱이 적용되지 않았기 때문에 발생하는 현상입니다.
또는 Postgresql 에서 쿼리 플랜을 짤 때 인덱싱 활용이 의미가 없을 정도로 대다수의 row 를 가져오는 경우에는 Seq scan 을 수행합니다!
위의 그림은 테이블 스키마이며 보시다시피 created_at 에 아무런 인덱싱이 적용되지 않는것을 확인할 수 있죠.
Btree 인덱싱 - 30ms 소요
그리고 이전 select ... order by 를 실행하게 된다면!!!!!
엄청난 빠르기를 확인할 수 있었습니다! 처음 쿼리되면 약 30ms 가 소요되었고, 추가적으로 동일 쿼리를 수행하게 되면 약 0.7ms 가 소요되었습니다. 이유는 Postgresql 은 내부적으로 동일 쿼리에 대해 캐싱을 수행하기 때문입니다.
역시 btree 는 범위쿼리에 매우 효과적이군요! 이러면 궁금증이 하나 생기죠. 만약 hash 나 다른 인덱싱 전략을 구성한다면? 과연 어떤 차이가 있을까요?
BRIN 인덱싱 - 1618ms 소요
BRIN 은 해당 칼럼의 값이 물리적인 저장 순서에 따라 비교적 균일하게 분포되어 있을 때 가장 효과적입니다. 그래서 created_at 처럼 순서대로 삽입되는 칼럼에 적용하면 빠른 인덱싱을 수행할 수 있겠죠?
그러나 데이터가 빈번하게 업데이트되거나 값의 분포가 균일하지 않은 경우에는 적합하지 않다고해요.
order by 에 인덱싱 적용되어 나타나지 않고, sort plan 이 실행되는 걸 확인하였습니다. 이상합니다… 분명 indexing 은 생성되었는데 말이죠.
그래서 찾아보니 order by 에서 인덱싱 지원은 되지 않는다고합니다. 대신 where 절에서는 아래와 같이 인덱싱이 적용된다고 하네요!
따라서 BRIN 인덱싱을 created_at 에 적용한다면 초기 sort 가 일어날 수 밖에 없기때문에 제외해야합니다!
B tree 와 BRIN Order by 지원여부
HASH 인덱싱 - 1864ms 소요
마찬가지로 order by 에 인덱싱 적용되어 나타나지 않고, sort plan 이 실행되는 걸 확인하였습니다. 예상된 결과로 Hash 는 범위쿼리에 전혀 적합하지 않았습니다!
결론은 order by 에서 인덱싱을 활용할 수 있도록 btree 를 적용시킴으로써 쿼리성능을 46배 이상 개선시킬 수 있었습니다!