Q＆A
커버링 인덱스 질문 드립니다
답변 감사합니다! 쿼리도 같이 보여드리겠습니다 장소에는 방명록을 남길 수 있고 방명록은 해당 장소에서 찍은 사진과 기타 정보들이 있습니다. 방명록은 삭제, 비공개가 가능합니다 - 장소(Spot) : 방명록(Post) = 1 : N - 방명록 : 사진(Photo) = 1 : 1 페이징 쿼리는 다음과 같은 형태입니다 select p.spot_id, p.id, p.user_id, ph.photo_url, p.is_private from post p join photo ph on ph.id = p.photo_id where p.spot_id = 1 and p.deleted_at is null and (p.is_private = false or p.user_id = 1) order by p.id desc limit 0, 10; 다음 쿼리는 특정 장소별로 최신 방명록 미리보기 n개를 조회하는 쿼리입니다 select s.id, spot_id, photo_url from spot s join lateral ( select p.id, spot_id, photo.photo_url from post p join photo on p.photo_id = photo.id where p.spot_id = s.id and p.is_private = false and p.deleted_at is null order by spot_id DESC, p.id DESC limit 5) as recent_post where s.id in (1, 2, 3, 7, 9); 더미 데이터(장소 10개, 장소별 방명록 1000개)를 넣고 테스트 해본 결과 일부입니다 1. post_search_idx(spot_id desc, id desc, is_private, deleted_at, photo_id) explain id | select_type | table | type | key | ref | rows | filtered | Extra 1 | PRIMARY | s | index | PRIMARY | null | 10 |50 | Using where; Using index; Rematerialize ( ) 1 | PRIMARY | | ALL | null | 5 | 100 | null 2 | DEPENDENT DERIVED | p | ref | post_search_idx | photospot.s.id | 10000 | 5 | Using where; Using index 2 | DEPENDENT DERIVED | photo | eq_ref | PRIMARY | photospot.p.photo _id | 1 |100 | null explain analyze -> Nested loop inner join (cost=1206 rows=25) (actual time=0.268..1.01 rows=25 loops=1) -> Invalidate materialized tables (row from s) (cost=1.25 rows=5) (actual time=0.0292..0.039 rows=5 loops=1) -> Filter: ( s.id in (1,2,3,7,9)) (cost=1.25 rows=5) (actual time=0.0285..0.0378 rows=5 loops=1) -> Covering index scan on s using PRIMARY (cost=1.25 rows=10) (actual time=0.0269..0.033 rows=10 loops=1) -> Table scan on recent_post (cost=240..242 rows=5) (actual time=0.191..0.192 rows=5 loops=5) -> Materialize (invalidate on row from s) (cost=239..239 rows=5) (actual time=0.19..0.19 rows=5 loops=5) -> Limit: 5 row(s) (cost=239 rows=5) (actual time=0.0809..0.182 rows=5 loops=5) -> Nested loop inner join (cost=239 rows=500) (actual time=0.0806..0.181 rows=5 loops=5) -> Filter: (( p.is _private = false) and (p.deleted_at is null) and ( p.photo _id is not null)) (cost=63.6 rows=500) (actual time=0.0659..0.147 rows=5 loops=5) -> Covering index lookup on p using post_search_idx (spot_id= s.id ) (cost=63.6 rows=10000) (actual time=0.06..0.121 rows=140 loops=5) -> Single-row index lookup on photo using PRIMARY (id= p.photo _id) (cost=0.25 rows=1) (actual time=0.00652..0.00655 rows=1 loops=25) 2. post_search_idx(spot_id desc, id desc, photo_id) explain id | select_type | table | type | key | ref | rows | filtered | Extra 1 | PRIMARY | s | index | PRIMARY | null |10 |50 |Using where; Using index; Rematerialize ( ) 1 | PRIMARY | | ALL | null | null | 5 |100 |null 2 | DEPENDENT DERIVED | p | index | FKqng73nkpy18qx7h7k6wq87ygx | null | 5 |1 | Using where; Backward index scan 2 | DEPENDENT DERIVED| photo | null | eq_ref | PRIMARY | photospot.p.photo _id | 1 | 100 | null explain analyze -> Nested loop inner join (cost=1024 rows=0.25) (actual time=16.2..51 rows=25 loops=1) -> Invalidate materialized tables (row from s) (cost=1.25 rows=5) (actual time=0.0244..0.0368 rows=5 loops=1) -> Filter: ( s.id in (1,2,3,7,9)) (cost=1.25 rows=5) (actual time=0.0241..0.0359 rows=5 loops=1) -> Covering index scan on s using PRIMARY (cost=1.25 rows=10) (actual time=0.0224..0.0289 rows=10 loops=1) -> Table scan on recent_post (cost=15.6..15.6 rows=0.05) (actual time=10.2..10.2 rows=5 loops=5) -> Materialize (invalidate on row from s) (cost=13.1..13.1 rows=0.05) (actual time=10.2..10.2 rows=5 loops=5) -> Limit: 5 row(s) (cost=13.1 rows=0.05) (actual time=10..10.2 rows=5 loops=5) -> Nested loop inner join (cost=13.1 rows=0.05) (actual time=10..10.2 rows=5 loops=5) -> Filter: (( p.is _private = false) and ( p.spot _id = s.id ) and (p.deleted_at is null) and ( p.photo _id is not null)) (cost=0.975 rows=0.05) (actual time=10..10.2 rows=5 loops=5) -> Index scan on p using FKqng73nkpy18qx7h7k6wq87ygx (reverse) (cost=0.975 rows=5) (actual time=0.0147..9.43 rows=5727 loops=5) -> Single-row index lookup on photo using PRIMARY (id= p.photo _id) (cost=0.252 rows=1) (actual time=0.00392..0.00395 rows=1 loops=25) 조회 성능 자체는 인덱스에 2개 컬럼(is_private, deleted_at)을 포함하면 좋은 것 같은데 선택도도 떨어지고 where 조건에 비교 조건으로 사용되는 컬럼들을 인덱스에 추가하면 성능적인 이점보다 단점이 커질 수 있다고 하신 부분 때문에 고민이 되어서 질문 드렸습니다..!
- いいね数
- 1
- コメント数
- 2
- 閲覧数
- 389