postgresql 쿼리 조회속도 질문드립니다.

Question

쿼리1SELECT a.id, COUNT(b.id) as point_count FROM a JOIN b ON ST_Contains(a.영역geometry, b.지점geometry) WHERE b.연도 &#61; 2024 AND b.분류 IN (분류1, 분류2, ...) GROUP BY a.id;쿼리2WITH filtered_b AS ( SELECT * FROM b WHERE b.연도 &#61; 2024 AND b.분류 IN (분류1, 분류2, ...) ) SELECT a.id, COUNT(filtered_b.id) as point_count FROM a JOIN filtered_b ON ST_Contains(a.영역geometry, filtered_b.지점geometry) GROUP BY a.id;두 쿼리는 같은 결과를 표출하는 쿼리이구요,a테이블은 연도/분류별 지점 위치(point)를 저장하는 테이블이고, b테이블은 영역(polygon)을 저장하는 테이블입니다.그리고 위 쿼리는 영역마다 포함되는 지점의 개수를 출력하는 쿼리입니다.b테이블에서 연도는 무조건 동치연산, 분류는 무조건 in 연산으로 사용됩니다.때문에 b테이블은 btree(연도,분류) 복합인덱스를 가지고있고,폴리곤과 포인트 geometry는 각각 gist인덱스를 가지고 있습니다.그럼 질문드립니다.쿼리2의 경우 서브쿼리에서 btree인덱스로 먼저 필터링을 하고 공간연산을 합니다.그런데 쿼리1의 경우 하나의 쿼리에서 수행을 하는데,그렇다면 join된 모든 튜플을 공간연산을 하고 where절 필터링이 되는건가요?아니면 where절 필터링을 먼저하고 공간연산을 하는걸까요?그리고 결과적으로 쿼리1과 쿼리2중 뭐가 더 빠를지 궁금합니다.그리고 만약에 b테이블을 파티셔닝한다면, in연산을 수행하는 분류 컬럼을 기준으로 파티셔닝하는게 좋을까요?

Clickin · Answer

플랜 보시면 궁금증이 해결되실텐데 실제로는 없는 가상의 테이블인가요?EXPLAIN ANALYZE 해서 실제 실행된 실행계획을 보시면 확실할 듯 합니다.

xml개발자 · Answer

SQL은 항상 '돌려봐야 안다' 입니다.옵티마이저는 내 맘대로 되지 않고, 실행계획도 항상 그대로 실행되지 않습니다.제 예상으로는 쿼리2는 옵티마이저가 내부적으로 WITH절을 해체해서 쿼리1처럼 만들껍니다.with절이 materialize 시킬려면 보통 집계함수를 써야합니다.그래서 예제에 있는 쿼리2는 인라인으로 취급되서 쿼리1과 동일하게 실행될 것으로 보입니다.

톰소여 · Answer

조인을 공간 조인을 했기 때문에 무조건 성능이 좋을 수 없어요.공간의 경계를 bbox로 칼럼을 추가해서 계산하여 업데이트하고 경계 값으로 인덱스 만들고 그걸로 조인이나 검색조건 처리한 후 공간검색이 들어가게 변경해야겠죠.연도 분류 아래에 bbox를 조건으로 잡으세요.

postgresql 쿼리 조회속도 질문드립니다.

3개의 답변

Q&A 목록