데이터베이스 질문과 답변
by 오라오라 [SQL Query] postgresql [2023.02.20 14:49:27]
안녕하세요,
postgresql DB 총 550만건의 데이터가있는 테이블이고
where절의 조건으로 줄이면 136만건 가량 됩니다.
월별로 group by해서 중복없이 userid 갯수와 userid 리스트를 뽑으려는데 5초 이상 소요됩니다;;
* 쿼리
select to_char(create,'yyyy-mm', count(distinct userid), array_agg(distinct userid)
from largetable
where keyid = 'AB121212'
and create >= to_date('2022-09','yyyy-mm-dd')::timestamp
and create < to_date('2023-02','yyyy-mm-dd')::timestamp + interval '1month'
group by to_char(create,'yyyy-mm');
* 인덱스(해당 테이블을 여러쿼리에서 활용하고있고 기생성되어있는 인덱스가 많습니다)
인덱스1: id
인덱스2: keyid, create
인덱스3: create
인덱스4: userid, create desc
인덱스5: keyid, userid, date(create) desc
* 기타: largetable 테이블은 현재 파티션 테이블이 아님. (create 컬럼 기준 월별로 range파티션으로 구성해 봤는데도 속도 개선효가가 크지는 않았습니다 T.T)
* 실행계획
GroupAggregate (cost=468472.09..663727.08 rows=1405051 width=72)
Group Key:(to_char(create, 'yyyy-mm'::text))
-> Gather Merge (cost=468472.09..632113.43 rows=140551 width=43)
Workers Planned: 2
-> Sort (cost=467472.06..468935.66 rows=585438 width=43)
Sort Key: (to_char(create, 'yyyy-mm'::text))
-> Parallel Seq Scan on log (cost=0.00..398524.57 rows=585438 width=43)
Filter((keyid)::text = 'AB121212') AND (create >= (to_date('2022-09'::text,'yyyy-mm-dd'::text))::timestamp without time zone) AND (create < ((to_date('2023-02'::text, 'yyyy-mm-dd'::text))::timestamp without time zone + '1 mon'::interval)))
실행계획을 보면 인덱스를 안타고 seq scan하고 있는데
select절의 count(distinct userid), array_agg(distinct userid)를 제거하면
HashAggregate + Index Only Scan 으로 인덱스2: keyid, create 를 타고 922ms가량 소요되는데
to_char(create,'yyyy-mm', count(distinct userid), array_agg(distinct userid) 처럼 필요한 select 절을 다 주면
인덱스도 안타고 속도도 많이 느립니다.
count(distinc), array_agg(distinct)하는 부분과 가능하다면 1초 가까이 걸리는 group by 부분에 대한 개선도
필요할것 같은데 조언 부탁드립니다. 감사합니다
-- 2단계 GROUP BY 방안 --
SELECT ym
, COUNT(*) cnt
, ARRAY_AGG(userid) user_list
FROM (SELECT DISTINCT
TO_CHAR(create, 'yyyy-mm') ym
, userid
FROM largetable
WHERE keyid = 'AB121212'
AND create >= TO_DATE('2022-09', 'yyyy-mm')::TIMESTAMP
AND create < TO_DATE('2023-02', 'yyyy-mm')::TIMESTAMP + INTERVAL '1 MONTH'
) a
GROUP BY ym
;
-- User 테이블을 이용하는 방안 --
SELECT TO_CHAR(a.dt, 'yyyy-mm') ym
, COUNT(*) cnt
, ARRAY_AGG(b.userid) user_list
FROM generate_series('2022-09-01', '2023-02-01', INTERVAL '1 MONTH') a(dt)
, user_table b
WHERE EXISTS (SELECT 1
FROM largetable
WHERE keyid = 'AB121212'
AND userid = b.userid
AND create >= a.dt
AND create < a.dt + INTERVAL '1 MONTH'
)
GROUP BY a.dt
;
마농님 바쁘실텐데 정말 감사합니다!
2단계 Group by 방안으로 기존보다 1.5초 정도 속도 개선 되었습니다!
만약 여기서 더 빠르게 하고 싶다면 별도의 통계 테이블(월별집계 테이블 만들고, 월별집계 테이블의 데이터와 그 이후부터 현재까지 데이터를
UNION ALL하는식)을 두는것 말고도 방법이 또 있을까요?
) 버튼을 클릭하여 작성 하시면 됩니다.