겪은 상황

• 크롤링한 가게 데이터들을 추가하고 나서 인덱스를 작업하지 않았다. (4만건 정도)

• 추가로 distange_range라는 값을 추가로 주어야했다.

  ex.) 가게는 250m/500m/750m 단위로 검색할 수 있게 해둔 상황. sorting을 위해
  현재 위치와 100m 떨어져있으면 "distance_range": "250",
  현재 위치와 450m 떨어져있으면 "distance_range": "500"
  이런 식으로 값을 추가로 보내야 했다.

• 쿼리를 수정하니 원래 1~2초 단위로 실행되던 api가 15초 이상이 걸렸다.

해결방법

1. 서비스 단에서 계산해서 반환하기

• 쿼리에서 distance를 계산하지 않고, 서비스 단에서 계산하는 것으로 수정했다.

return stores.stream()
            .map(store -> {
                int distance = store.getDistance();
                int distanceRange = distance <= 250 ? 250 
                              : distance <= 500 ? 500 
                              : distance <= 750 ? 750 
                              : 0;

-> 속도에 큰 변화가 없었다.(여전히 10초 대)

2. 시간 측정 추가하기

• 근본적으로 어디 부분에서 시간이 많이 소요되는지를 알아야했다.

long totalStart = System.currentTimeMillis();

long start = System.currentTimeMillis();
List<String> storeIds = findStoreService.메서드명1(변수...);
System.out.println("메서드명1: " + (System.currentTimeMillis() - start) + "ms");
   
start = System.currentTimeMillis();
List<StoreDistanceDTO> storeDetails = findStoreService.메서드명2(변수...);
System.out.println("메서드명2: " + (System.currentTimeMillis() - start) + "ms");

System.out.println("Total time: " + (System.currentTimeMillis() - totalStart) + "ms");

이런 식으로 실제 시간을 측정하는 로그를 추가했다.
이 방법으로 서비스 단에서 객체 변환이 아닌 DB 조회가 오래 걸리는 것임을 확인했다. (객체 변환에서는 2ms 정도밖에 소요되지 않았다.)

3. 쿼리 개선

3-1) CTE 절에서 조건 추가하여 전부 스캔하지 않도록 변경

• CTE 절에서 모든 건수를 조회하지 않도록 조건을 추가했다.

-- 원래 사용하던 CTE절
WITH menu_counts AS (
    SELECT store_id, COUNT(*) AS cnt
    FROM menu
    WHERE represent = 'Y'  -- 전체 menu 테이블 스캔!
    GROUP BY store_id
)

-- 개선한 CTE 절
WITH menu_counts AS (
    SELECT store_id, COUNT(*) AS cnt
    FROM menu
    WHERE represent = 'Y'
      AND store_id IN (N개)  -- 해당하는 N개만 조회
    GROUP BY store_id
)

3-2) 불필요한 서브쿼리를 직접 JOIN으로 수정

• store 테이블을 2번 조회 (메인 쿼리 + 서브쿼리) 하던 것을 직접 JOIN으로 바꾸어 한번만 조회하도록 수정했다.

LEFT JOIN
    (SELECT st.store_id, f.type1, f.food_type_id
     FROM store st
     JOIN food_type f ON st.category = f.type2
     WHERE st.store_id IN (N개)  -- store 테이블 또 조회
    ) f ON s.store_id = f.store_id


LEFT JOIN food_type f ON s.category = f.type2  -- 직접 JOIN

-> 2초대로 줄었다.

4. 인덱스 추가

• 가게 테이블의 가게 PK
• 메뉴 테이블의 가게 FK
• 방송 테이블의 가게 FK
• 음식 타입 테이블의 타입 ID
• ... 등 JOIN 이나 WHERE 에서 자주 쓰일 컬럼에 인덱스를 추가하였다.
• 인덱스가 없으면 TABLE FULL SCAN이 일어나 데이터 조회에 많은 시간이 소요될 수 있다.

  가게를 조회할 때 마다, 메뉴/방송/가게/음식 타입 테이블을 전부 조회한다.

-- 인덱스가 있는지 확인하고 없으면 만든다.
CREATE INDEX IF NOT EXISTS idx_store_id ON store(store_id);

-- 인덱스가 있는지만 확인한다.
SELECT indexname, indexdef 
FROM pg_indexes 
WHERE tablename = 'store';

-> 실행시간이 30ms 대로 줄었다.

5. 쿼리 개선2

다른 메서드에서도 병목현상으로 3초 정도로 실행되고 있어서 쿼리를 개선했다.

5-1) 현재 상황

• 가게 영업시간 테이블에 저장된 값이 시간만 들어가 있거나 정기 휴무<이런 식으로 텍스트가 들어가 있는경우도 있었다. (매일 달라지는 시간을 어떻게 관리하는게 좋을지 모르겠어서 이렇게 시작했으나 더 좋은 방법이 있다면 알려주실 분...)

• where 절에서 영업시간이 텍스트인 행들을 먼저 필터링 했어도 PostgreSQL 옵티마이저가 실행 순서를 바꿔서 hh24:mi 부분에서 에러가 났었던 적이 있었다.

5-2) 문제점1. 3개의 중첩된 EXISTS = 테이블을 3번 스캔

• 그래서 텍스트인 행들을 필터링 하겠다고 테이블들을 반복해서 접근 하고 있었다.

SELECT DISTINCT bh.store_id
FROM business_hours bh
WHERE ...
  AND EXISTS (
      SELECT 1 FROM business_hours bh2  -- 1번째 스캔
      WHERE bh2.store_id = bh.store_id
      AND bh2.business_type = 'O0001'
      ...
  )
  AND EXISTS (
      SELECT 1 FROM business_hours bh3, bh4  -- 2번째, 3번째 스캔
      WHERE bh3.store_id = bh.store_id
      AND bh4.store_id = bh.store_id
      ...
  )

5-3) 문제점2. 동적 컬럼명 (${currentDay}) - 인덱스 활용 불가

• 요일별로 동적인 컬럼명을 사용하고 있었는데 이 부분에서 인덱스 활용이 불가했다.

WHERE bh2.${currentDay} IS NOT NULL  -- currentDay = 'mon', 'tue' 등

5-4) 문제점3. LIKE 패턴 검색 - 인덱스 활용 불가

WHERE bh2.${currentDay}::text NOT LIKE '%휴무%'

• %휴무%는 앞뒤에 와일드카드가 있어서 인덱스가 있어도 전체 스캔이 필요하다.

5-5) 해결책1. CTE로 한번에 조회하여 해결

WITH valid_stores AS (
    -- 폐업하지 않은 store만 먼저 필터링
),
dinner_hours AS (
    SELECT 
        store_id,
        MAX(CASE WHEN business_type = 'O0001' THEN ${currentDay} END) as open_time,
        MAX(CASE WHEN business_type = 'C0001' THEN ${currentDay} END) as close_time
    FROM business_hours
    WHERE store_id IN (SELECT store_id FROM valid_stores)
      AND business_type IN ('O0001', 'C0001')
      AND ${currentDay} IS NOT NULL
      AND ${currentDay} NOT IN ('휴무', '정기휴무', '')
    GROUP BY store_id  -- 핵심: 한 번에 집계!
)

• CTE절을 사용하여 1번의 조회로 모든 테이블을 가져오도록 수정했다.
• GROUP BY로 각 store의 open_time, close_time을 메모리에서 집계하고 있다.

5-6) 해결책2. CTE절 내에서 CASE 문으로 데이터 피봇

MAX(CASE WHEN business_type = 'O0001' THEN ${currentDay} END) as open_time,
MAX(CASE WHEN business_type = 'C0001' THEN ${currentDay} END) as close_time

## 개선 전:
-- O0001 찾기 위해 테이블 스캔
SELECT ... WHERE business_type = 'O0001'
-- C0001 찾기 위해 또 테이블 스캔  
SELECT ... WHERE business_type = 'C0001'

### 개선 후:
-- 한 번에 가져와서 메모리에서 분리
CASE WHEN business_type = 'O0001' THEN ... 
CASE WHEN business_type = 'C0001' THEN ...

5-7) 해결책3. CTE절 내에서 휴무 조건을 한번에 필터링

## 개선 전 :
EXISTS (
    SELECT 1 FROM business_hours bh2
    WHERE ... AND bh2.${currentDay} NOT LIKE '%휴무%'
)
-- 각 store마다 반복 검사!

## 개선 후 :
WHERE business_type IN ('O0001', 'C0001')
  AND ${currentDay} IS NOT NULL
  AND ${currentDay} NOT IN ('휴무', '정기휴무', '')
  AND ${currentDay}::text NOT LIKE '%휴무%'
-- 한 번만 검사!

6. 쿼리 개선3

6-1) 문제점 1. 인덱스 사용이 불가한ST_DistanceSphere

WHERE ST_DistanceSphere(location, ST_GeomFromText(...)) <= 750

• GIST 인덱스는 공간 범위 검색에 최적화되어 있다.
• 그러나 ST_DistanceSphere는 함수 실행 결과를 비교한다.
• 그러므로 PostgreSQL은 함수 실행 결과를 비교하려면 전체 스캔을 하게된다.

6-2) 해결책 1. ST_DWithin으로 공간 인덱스 직접 활용

WHERE ST_DWithin(
    s.location::geography,
    ST_SetSRID(ST_MakePoint(#{currentLong}, #{currentLat}), 4326)::geography,
    750
)

6-3) GIST 인덱스의 작동 원리

GIST (Generalized Search Tree):

• 범위 기반 또는 위치 기반 검색에 최적화된 트리 구조이다.
• 데이터의 일부만 빠르게 필터링하여 검색 시간을 단축하는 트리 구조를 구축할 수 있다.
• 공간 데이터를 직사각형으로 묶어 그룹화하여 데이터를 구성한다. 이를 MBR(Minimum Bounding Region) (최소 경계 영역)이라고 한다.
• 직사각형의 좌표를 알기 때문에 검색하려는 지점이 특정 그룹 내에 있는지 쉽게 확인할 수 있다.

6-4) geography 타입 -> geometry 타입

• geometry는 평면 좌표계 계산용
• geography는 지구 표면(위/경도) 기반 계산용으로, 보통 위도/경도 데이터를 다룰 때 더 적합하다.

## 원래 (geometry):
sqlST_DistanceSphere(location, ST_GeomFromText(...))
-- geometry 타입, 거리는 "도" 단위 → 미터로 변환 필요

## 개선 (geography):
sqlST_DWithin(s.location::geography, ...::geography, 750)
-- geography 타입, 거리가 바로 "미터" 단위

-- geometry: 1도 ≈ 111km (위도에 따라 다름)
WHERE ST_Distance(location, point) <= 0.0067  -- 750m를 도로 변환?

-- geography: 직관적!
WHERE ST_DWithin(location, point, 750)  -- 750미터

---

출처

ServBay에서 PostGIS 사용하기: PostgreSQL에 공간 기능 추가
[Postgresql] Gist 인덱스란?