데이터베이스 질문과 답변
SELECT item.id FROM item JOIN attribute AS attribute_2 ON item.id = attribute_2.item_id AND attribute_2.metainfo_id = 1 AND attribute_2.value LIKE '%white%' JOIN attribute AS attribute_3 ON item.id = attribute_3.item_id AND attribute_3.metainfo_id = 5 AND attribute_3.value LIKE '%올드%'
안녕하세요. DB 초보 백엔드 개발자입니다.
위 쿼리를 실행하면 2초 정도 걸립니다. 하지만 attribute_2이나 attribute_3의 like연산을 하나라도 빼면 0.1~0.3초안에 처리가 완료됩니다.
어떤 원인때문에 저런현상이 발생하는지 전혀 감이 잡히지 않습니다. ㅜㅜ
알고 계신 고수분이 있으시다면 한번 들여다 보고 답변 부탁드립니다.
* 추가적으로 데이터베이스는 postgresql 14를 사용하고 있습니다!!
1. attribute 의 인덱스 구성이 어찌 되나요?
2. 각각의 건수가 어찌 되나요?
- item 건수
- attribute 전체 건수
- attribute 1 의 hite 조건 전/후 건수
- attribute 5 의 올드 조건 전/후 건수
- 전체 쿼리 결과 건수
- 전체 쿼리 결과 건수 - like 조건 제거 시 건수
1. attribute의 인덱스는 (item_id, metainfo_id), (metainfo_id, value)로 총 2가지가 있습니다.
2. 각 건수
- item 35000
- attribute 70만건
- white 93건
- 올드 130건
- 최종 8건
- like제거시 35000건
입니다!!
attribute 중
- metainfo_id = 1 의 건수
- metainfo_id = 5 의 건수
metainfo_id가 각 35000건 item수 만큼 있습니다!
metainfo가 20개 존재하고 item 35000개
두 테이블의 다대다를 해결해주는 attribute가 70만건 존재합니다!
-- 그러면 item 테이블을 굳이 조인할 필요가 없어 보이네요.
-- 혹시 LIKE 가 문제라면? 다른 함수로 대체 가능합니다.
-- STRPOS(value, 'white') > 0
SELECT item_id
FROM attribute
WHERE (metainfo_id = 1 AND value LIKE '%white%')
OR (metainfo_id = 5 AND value LIKE '%올드%')
GROUP BY item_id
HAVING COUNT(*) = 2
;
SELECT item_id
FROM attribute
WHERE metainfo_id = 1
AND value LIKE '%white%'
INTERSECT
SELECT item_id
FROM attribute
WHERE metainfo_id = 5
AND value LIKE '%올드%'
;
늘 존경하던마농님께 감사의 인사 드립니다. 그리고 마농님 능력에 또 한번 놀라고 갑니다.
또한 이 글과 비슷한 문제를 겪고 계신분들은 아래 참고해주세요.
like 연산자 대신 마농님께서 올려주신 STRPOS(value, 'white') > 0를 사용했을 때 2초 -> 0.14초까지 단축되었습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.