커버링 인덱스 질문 드립니다

code-tree님, 안녕하세요. 수강 감사드려요.

우선, 이 질문에는 몇가지 상황에 따른 가정이 필요한데요.

1. 원하시는 쿼리가 단순 SELECT COUNT(*) 쿼리라면

   1. 커버링 인덱스로 처리되면 좋은데,

   2. 문제는 2개 컬럼이외에도 쿼리에서 사용중인 모든 컬럼이 인덱스에 추가되어야만 커버링 인덱스 효과를 얻을 수 있습니다.

2. SELECT * 쿼리라면, 인덱스에 해당 컬럼을 추가해도, 커버링 인덱스 실행 계획은 사용할수 없습니다.

   1. 다만, deleted_at is null and is_privte=false 조건에 부합되지 않는 레코드가 많다면, 커버링이 아니어도 인덱스에 2개 컬럼을 넣어주면 성능을 개선하실 수 있어요.

   2. 그렇지 않다면, 굳이 인덱스에 2개 컬럼을 추가할 필요는 없어 보입니다.

   
   사용하시는 쿼리 전체를 보여주시면, 더 자세히 답변 드릴 수 있을 것 같아요.

    

   혹시 위 답변이 이해되지 않거나 이상하다면, 쿼리를 좀 같이 질문에 추가해주세요.

감사합니다.

답변 감사합니다! 쿼리도 같이 보여드리겠습니다

장소에는 방명록을 남길 수 있고 방명록은 해당 장소에서 찍은 사진과 기타 정보들이 있습니다. 방명록은 삭제, 비공개가 가능합니다

- 장소(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 (<derived2>)
1 | PRIMARY | <derived2> | 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 (<derived2>)
1 | PRIMARY | <derived2> | 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 조건에 비교 조건으로 사용되는 컬럼들을 인덱스에 추가하면 성능적인 이점보다 단점이 커질 수 있다고 하신 부분 때문에 고민이 되어서 질문 드렸습니다..!