728x90
프로젝트의 DB에 데이터를 가져와서 활용할 수 있는 아이디어를 한번 찾아보자.
총정리
-- 전체 데이터 중 5개만 추출.
select * from "Bid" limit 5;
-- 전체 로우 카운트 개수 세기.
select count(*) from "Bid";
-- 컬럼이름 가져오기.
select column_name from information_schema.columns where table_name = 'Bid'
-- 전체 데이터 조회.
explain analyze select * from "Bid";
-- 특정 가격대 bid 조회.
explain analyze select * from "Bid" b where b."bidPrice" > 370000 and b."bidPrice" < 40000;
-- 래플 id로 래플 정보 조회.
explain analyze select * from "Bid" b where "bidId" =1124888;
-- 가격대순으로 내림차순 정렬하기.
explain analyze select * from "Bid" b order by "bidPrice" desc;
-- 가격대에 인덱싱하기.
create index idx_bidPrice on "Bid"("bidPrice");
-- 가격대순으로 오름차순 정렬하기.
explain analyze select * from "Bid" b order by "bidPrice" asc;
-- 가격 인덱스 제거.
drop index idx_bidPrice;
------------------------------------
-- 비딩 사이즈
-- 비딩 상품 사이즈 조회.
explain analyze select "bidSize" from "Bid" b;
select distinct "bidSize" from "Bid" b;
-- bidsize 인덱스 생성/제거.
create index idx_bidsize on "Bid"("bidSize");
drop index idx_bidsize;
-- 특정 bidsize 조회.
explain analyze select * from "Bid" b where "bidSize" = 275;
-- 특정 구간 사이즈 조회.
explain analyze select * from "Bid" b where "bidSize" > 275 and "bidSize" < 290;
------------------------------------
-- 비딩이 참여한 래플의 최종 가격 추출 예.
-- 비딩-래플 2단
select r."closedPrice" from "Bid" b
left join "Raffle" r on b."raffleId" = r."raffleId" limit 5;
explain analyze select r."closedPrice" from "Bid" b
left join "Raffle" r on b."raffleId" = r."raffleId";
-- 비딩 테이블의 raffleId 인덱싱 적용.
create index idx_raffle on "Bid"("raffleId");
-- 비딩 테이블의 raffleId 인덱싱 적용.
create index idx_raffle2 on "Raffle"("raffleId");
--두 테이블의 인덱스 가져오기/삭제.
select * from pg_indexes where tablename = 'Bid';
drop index idx_raffle;
select * from pg_indexes where tablename = 'Raffle';
drop index idx_raffle2;
------------------------------------
-- 전체 비딩의 상품의 컬러 유형 추출.
-- 비딩-래플-상품 3단계로 정보 가져오기.
select distinct p."productColor" from "Bid" b
left join "Raffle" r on b."raffleId" = r."raffleId"
left join "Products" p on r."productId" =p."productId";
explain analyze select p."productColor" from "Bid" b
left join "Raffle" r on b."raffleId" = r."raffleId"
left join "Products" p on r."productId" =p."productId";
create index idx_raffle on "Bid"("raffleId");
create index idx_product on "Raffle"("productId");
drop index idx_product;
과정
# Total number of data
select count(*) from "Bid";
count |
-------+
1219145|Bid는 경매 입찰 정보가 담긴 테이블이다. 총 1,219,145건의 비딩 정보가 담겨있다.
-- get information Schema
select column_name from information_schema.columns where table_name = 'Bid'
column_name |
---------------+
createdAt |
updatedAt |
deletedAt |
bidId |
bidSize |
bidPrice |
bidQuantity |
raffleId |
usersId |
productsize |
releaseprice |
productname |
releasedate |
productcolor |
productcategory|
productimage |
productmodel |컬럼 정보들을 추출. 이중 몇몇은 관계 테이블의 정보여서 함께 딸려오는 것으로 보인다.
단순 정보 조회
explain analyze select * from "Bid";
QUERY PLAN |
----------------------------------------------------------------------------------------------------------------+
Seq Scan on "Bid" (cost=0.00..23603.86 rows=1219886 width=764) (actual time=0.006..80.456 rows=1219145 loops=1)|
Planning Time: 0.041 ms |
Execution Time: 125.143 ms |래플 전체 데이터를 조회할 경우, 125ms가 소요된다. 스캔 방식은 Sequent Scan이 적용되었다.
-- search in specific price range
explain analyze select * from "Bid" b where b."bidPrice" > 300000 and b."bidPrice" < 350000;
QUERY PLAN |
-----------------------------------------------------------------------------------------------------------------+
Seq Scan on "Bid" b (cost=0.00..29703.29 rows=176453 width=764) (actual time=0.010..113.173 rows=174301 loops=1)|
Filter: (("bidPrice" > 300000) AND ("bidPrice" < 350000)) |
Rows Removed by Filter: 1044844 |
Planning Time: 0.058 ms |
Execution Time: 120.102 ms |30만원 이상 35만원 이하인 bidPrice 조건을 추가했다. 버려진 로우가 거의 100만개에 달았다. Seq Scan으로 인해 어쩔 수 없다.
-- 래플 id로 래플 정보 조회.
explain analyze select * from "Bid" b where "bidId" =1124888;
QUERY PLAN |
------------------------------------------------------------------------------------------------------------------------------------------+
Index Scan using "PK_88960f301c458b51987cd93dbb9" on "Bid" b (cost=0.43..8.45 rows=1 width=764) (actual time=0.015..0.016 rows=1 loops=1)|
Index Cond: ("bidId" = 1124888) |
Planning Time: 0.066 ms |
Execution Time: 0.047 ms |bid의 pk로 검색. pk로 검색할 경우 Index Scan 방식으로 빠르게 검색할 수 있다. 왜냐하면 DB내부에서는 PK 자체가 인덱스로 작동하기 때문이다.
비딩 가격 다루기
-- 가격대순으로 내림차순하기
explain analyze select * from "Bid" b order by "bidPrice";
QUERY PLAN |
-------------------------------------------------------------------------------------------------------------------------------------+
Gather Merge (cost=409648.40..528256.75 rows=1016572 width=764) (actual time=473.713..959.932 rows=1219145 loops=1) |
Workers Planned: 2 |
Workers Launched: 2 |
-> Sort (cost=408648.38..409919.09 rows=508286 width=764) (actual time=461.427..595.377 rows=406382 loops=3) |
Sort Key: "bidPrice" |
Sort Method: external merge Disk: 26504kB |
Worker 0: Sort Method: external merge Disk: 26504kB |
Worker 1: Sort Method: external merge Disk: 16336kB |
-> Parallel Seq Scan on "Bid" b (cost=0.00..16487.86 rows=508286 width=764) (actual time=0.010..76.216 rows=406382 loops=3)|
Planning Time: 0.065 ms |
Execution Time: 1043.395 ms |약 1초가 걸린다. 2개의 워커가 seq scan한 결과를 취합하여 sort를 한다. 인덱싱을 하면 얼마나 빨라지는지 확인해보자.
-- 가격대에 인덱싱하기.
create index idx_bidPrice on "Bid"("bidPrice");-- 가격대순으로 내림차순 정렬하기.
explain analyze select * from "Bid" b order by "bidPrice" desc;
QUERY PLAN |
-------------------------------------------------------------------------------------------------------------------------------------------------+
Index Scan Backward using idx_bidprice on "Bid" b (cost=0.43..72064.36 rows=1219145 width=764) (actual time=0.015..626.739 rows=1219145 loops=1)|
Planning Time: 0.067 ms |
Execution Time: 675.516 ms |병렬 Seq Scan으로 진행한 인덱싱 적용 이전은 1043ms가 소요되었는데, 인덱싱 적용 이후 675ms로 더 빨라졌다.
-- 가격대순으로 오름차순 정렬하기.
explain analyze select * from "Bid" b order by "bidPrice" asc;
QUERY PLAN |
----------------------------------------------------------------------------------------------------------------------------------------+
Index Scan using idx_bidprice on "Bid" b (cost=0.43..72064.36 rows=1219145 width=764) (actual time=0.017..614.167 rows=1219145 loops=1)|
Planning Time: 0.064 ms |
Execution Time: 670.846 ms |오름차순도 자동으로 인덱스 스캔이 적용된다.
다만 비딩가격은 중복도가 높은, 즉 Cardinality가 낮은 컬럼이므로 Index scan의 효과가 떨어진다는 점이 한계인듯 하다.
-- 가격 인덱스 제거.
drop index idx_bidPrice;다 썼으니 지워주자.
비딩한 상품의 사이즈 정보 다루기
-- 비딩 상품 사이즈 조
explain analyze select "bidSize" from "Bid" b;
QUERY PLAN |
-----------------------------------------------------------------------------------------------------------------+
Seq Scan on "Bid" b (cost=0.00..23596.45 rows=1219145 width=4) (actual time=0.007..130.912 rows=1219145 loops=1)|
Planning Time: 0.038 ms |
Execution Time: 175.935 ms |-- bidsize 인덱스 생성.
create index idx_bidsize on "Bid"("bidSize");-- 비딩 상품 사이즈 조회.
explain analyze select "bidSize" from "Bid" b;
QUERY PLAN |
-----------------------------------------------------------------------------------------------------------------------------------------+
Index Only Scan using idx_bidsize on "Bid" b (cost=0.43..22439.60 rows=1219145 width=4) (actual time=0.017..81.787 rows=1219145 loops=1)|
Heap Fetches: 0 |
Planning Time: 0.052 ms |
Execution Time: 125.898 ms |175ms에서 125ms로 감소했다. DB 내부에서만 테스트한 것이라 어떤 의미를 가진지 잘 모르겠다.
조건문도 비교해보자
-- 인덱싱 이전
-- 특정 bidsize 조회.
explain analyze select * from "Bid" b where "bidSize" = 275
QUERY PLAN |
---------------------------------------------------------------------------------------------------------------------------+
Gather (cost=1000.00..20693.11 rows=19384 width=764) (actual time=0.237..104.807 rows=18967 loops=1) |
Workers Planned: 2 |
Workers Launched: 2 |
-> Parallel Seq Scan on "Bid" b (cost=0.00..17754.71 rows=8077 width=764) (actual time=0.022..92.692 rows=6322 loops=3)|
Filter: ("bidSize" = 275) |
Rows Removed by Filter: 400059 |
Planning Time: 0.071 ms |
Execution Time: 105.989 ms |병렬 seq scan으로 bid 사이즈 정보들을 스캔했다. 찾다가 불필요해서 버린 로우는 400059개로 비용 낭비가 있음을 알 수 있다.
-- 특정 bidsize 조회.
explain analyze select * from "Bid" b where "bidSize" = 275
QUERY PLAN |
----------------------------------------------------------------------------------------------------------------------------+
Bitmap Heap Scan on "Bid" b (cost=218.65..12243.19 rows=19384 width=764) (actual time=2.443..12.452 rows=18967 loops=1) |
Recheck Cond: ("bidSize" = 275) |
Heap Blocks: exact=9255 |
-> Bitmap Index Scan on idx_bidsize (cost=0.00..213.81 rows=19384 width=0) (actual time=1.257..1.258 rows=18967 loops=1)|
Index Cond: ("bidSize" = 275) |
Planning Time: 0.192 ms |
Execution Time: 13.201 ms |105ms에서 13ms로 1/10 수준으로 감소했다. 놀랍다.
-- 인덱싱 적용 전
-- 특정 구간 사이즈 조회.
explain analyze select * from "Bid" b where "bidSize" > 275 and "bidSize" < 290;
QUERY PLAN |
-----------------------------------------------------------------------------------------------------------------+
Seq Scan on "Bid" b (cost=0.00..29692.17 rows=262035 width=764) (actual time=0.008..108.688 rows=262466 loops=1)|
Filter: (("bidSize" > 275) AND ("bidSize" < 290)) |
Rows Removed by Filter: 956679 |
Planning Time: 0.448 ms |
Execution Time: 119.789 ms |-- bidsize 인덱스 생성
create index idx_bidsize on "Bid"("bidSize");
-- 다시 시도
explain analyze select * from "Bid" b where "bidSize" > 275 and "bidSize" < 290;
QUERY PLAN |
-------------------------------------------------------------------------------------------------------------------------------+
Bitmap Heap Scan on "Bid" b (cost=3582.29..18917.81 rows=262035 width=764) (actual time=10.469..58.552 rows=262466 loops=1) |
Recheck Cond: (("bidSize" > 275) AND ("bidSize" < 290)) |
Heap Blocks: exact=11404 |
-> Bitmap Index Scan on idx_bidsize (cost=0.00..3516.78 rows=262035 width=0) (actual time=8.889..8.890 rows=262466 loops=1)|
Index Cond: (("bidSize" > 275) AND ("bidSize" < 290)) |
Planning Time: 0.181 ms |
Execution Time: 68.273 ms 이번엔 Bitmap Heap Scan이 적용되었다. 속도는 119에서 68ms로 절반 조금 안되게 감소했다.
비딩이 소속된 래플의 정보 다루기
-- 비딩이 참여한 래플의 최종 가격 추출 예.
-- 비딩-래플 2단
select r."closedPrice" from "Bid" b
left join "Raffle" r on b."raffleId" = r."raffleId" limit 5;
closedPrice|
-----------+
1467159|
1746312|
1710627|
1877988|
89154|우리가 가진 비딩 테이블로는 래플의 마감 가격을 알 수 없으니 left join으로 래플 테이블의 정보를 참조하여 래플의 마감 가격을 알아냈다.
explain analyze select r."closedPrice" from "Bid" b
left join "Raffle" r on b."raffleId" = r."raffleId";
QUERY PLAN |
------------------------------------------------------------------------------------------------------------------------+
Hash Left Join (cost=360.49..27158.43 rows=1219145 width=4) (actual time=2.500..361.984 rows=1219145 loops=1) |
Hash Cond: (b."raffleId" = r."raffleId") |
-> Seq Scan on "Bid" b (cost=0.00..23596.45 rows=1219145 width=4) (actual time=0.007..87.576 rows=1219145 loops=1) |
-> Hash (cost=223.55..223.55 rows=10955 width=8) (actual time=2.469..2.471 rows=11001 loops=1) |
Buckets: 16384 Batches: 1 Memory Usage: 558kB |
-> Seq Scan on "Raffle" r (cost=0.00..223.55 rows=10955 width=8) (actual time=0.005..1.350 rows=11001 loops=1)|
Planning Time: 0.130 ms |
Execution Time: 407.305 ms |쿼리플랜을 보니 Raffle 테이블을 참조할 때는 seq scan으로 진행한 것을 알 수 있다. 407ms면 빠른 편은 아니다. 여기에도 인덱싱을 적용할 수 있지 않을까?
현재 raffleId가 비딩 테이블과 래플 테이블 두 곳에 있는데, 래플 테이블에서는 raffleId가 인덱싱으로 적용될 것이니, 그럼 비딩 테이블의 raffleId을 인덱싱해주면 더 빠르지 않을까?
-- 비딩 테이블의 raffleId 인덱싱 적용.
create index idx_raffle on "Bid"("raffleId");explain analyze select r."closedPrice" from "Bid" b
left join "Raffle" r on b."raffleId" = r."raffleId";
QUERY PLAN |
------------------------------------------------------------------------------------------------------------------------+
Hash Left Join (cost=360.49..27158.43 rows=1219145 width=4) (actual time=2.470..350.331 rows=1219145 loops=1) |
Hash Cond: (b."raffleId" = r."raffleId") |
-> Seq Scan on "Bid" b (cost=0.00..23596.45 rows=1219145 width=4) (actual time=0.006..80.354 rows=1219145 loops=1) |
-> Hash (cost=223.55..223.55 rows=10955 width=8) (actual time=2.440..2.442 rows=11001 loops=1) |
Buckets: 16384 Batches: 1 Memory Usage: 558kB |
-> Seq Scan on "Raffle" r (cost=0.00..223.55 rows=10955 width=8) (actual time=0.005..1.308 rows=11001 loops=1)|
Planning Time: 0.159 ms |
Execution Time: 394.722 ms |indexing이 적용되지 않았다. 조인 테이블의 외래키는 이런 식으로 인덱싱을 적용하는 것이 아닌가?
찾아보니 PostgreSQL은 자동으로 pk와 unique contstraints에 인덱스를 생성한다고 한다. 그러나 참조하는 반대쪽 테이블의 외래키는 적용되지 않는다고 한다.
https://stackoverflow.com/questions/970562/postgres-and-indexes-on-foreign-keys-and-primary-keys
-- 비딩 테이블의 raffleId 인덱싱 적용.
create index idx_raffle2 on "Raffle"("raffleId");QUERY PLAN |
-----------------------------------------------------------------------------------------------------------------------------------------------+
Hash Left Join (cost=361.95..26130.62 rows=1219145 width=4) (actual time=3.099..385.445 rows=1219145 loops=1) |
Hash Cond: (b."raffleId" = r."raffleId") |
-> Index Only Scan using idx_raffle on "Bid" b (cost=0.43..22567.60 rows=1219145 width=4) (actual time=0.006..134.383 rows=1219145 loops=1)|
Heap Fetches: 0 |
-> Hash (cost=224.01..224.01 rows=11001 width=8) (actual time=3.068..3.070 rows=11001 loops=1) |
Buckets: 16384 Batches: 1 Memory Usage: 558kB |
-> Seq Scan on "Raffle" r (cost=0.00..224.01 rows=11001 width=8) (actual time=0.007..1.725 rows=11001 loops=1) |
Planning Time: 0.412 ms |
Execution Time: 445.978 ms |Seq에서 Index Only Scan으로 적용되었다. 하지만 응답 속도는 오히려 394에서 445ms로 증가했다.
--두 테이블의 인덱스 가져오기/삭제.
select * from pg_indexes where tablename = 'Bid';
drop index idx_raffle;
select * from pg_indexes where tablename = 'Raffle';
drop index idx_raffle2;인덱스들을 지워주었다.
비딩과 관련된 상품 컬러 정보 다루기
-- 전체 비딩의 상품의 컬러 유형 추출.
-- 비딩-래플-상품 3단계로 정보 가져오기.
select distinct p."productColor" from "Bid" b
left join "Raffle" r on b."raffleId" = r."raffleId"
left join "Products" p on r."productId" =p."productId";
productColor|
------------+
Orange |
Indigo |
Red |
Green |
Yellow |
Pink |
Teal |
Mauv |
Crimson |
Blue |
Goldenrod |
Purple |
Turquoise |
Maroon |
Violet |
Fuscia |
Khaki |
Puce |
Aquamarine |explain analyze select distinct p."productColor" from "Bid" b
left join "Raffle" r on b."raffleId" = r."raffleId"
left join "Products" p on r."productId" =p."productId";
쿼리 총 소요시간은 932ms로 높은 편이다. 세세하게 들어가면 Seq Scan으로 진행된 것을 알 수 있다.
create index idx_raffle on "Bid"("raffleId");
create index idx_product on "Raffle"("productId");explain analyze select distinct p."productColor" from "Bid" b
left join "Raffle" r on b."raffleId" = r."raffleId"
left join "Products" p on r."productId" =p."productId";
기존 쿼리 시간이 932ms에서 688ms로 감소했다.
728x90
'Research > Database' 카테고리의 다른 글
| 데이터베이스 정규화(Normalization) (0) | 2023.03.23 |
|---|---|
| postgres_row count 성능 개선 방법 (0) | 2023.03.13 |
| Postgres_쿼리 실행 계획 분석 (0) | 2023.03.07 |
| db_Redis 클라우드 사용하는 방법 (0) | 2023.03.01 |
| nestjs_cache-manager로 redis 사용하기 (0) | 2023.02.27 |
댓글