필독) 특이 SQL 및 PLAN, Access 방식, 지원 Join, Query Rewrite

SELECT DISTINCT ON(FIRSTNAME) FIRSTNAME, LASTNAME FROM MEMBER_TBL ORDER BY FIRSTNAME, LASTNAME DESC;

SELECT MEMBERID, MEMBERNAME, PHONENO FROM MEMBER_TBL ORDER BY MEMBERID LIMIT 4 OFFSET 0; # 0번째 행(=첫 행) 부터 4 개행 가져오기

Oracle 계층 쿼리 ) SELECT A.CONTS_ID,              A.CONTS_NM,              A.UP_CONTS_ID,              A.MENU_ORD,              LEVEL   /* 계층구조에서 단계, 레벨을 나타내주는 함수 */ FROM CLT_MENU A WHERE A.MENU_INCL_YN = 'Y'              AND LEVEL IN (2,4) START WITH A.CONTS_ID = 'voc'   /* 계층구조의 시작조건을 주는 조건절 */ CONNECT BY PRIOR A.CONTS_ID = A.UP_CONTS_ID  /* 계층구조의 상,하위 간의 관계 조건 */ ORDER SIBLINGS BY A.MENU_ORD   /* 계층구조를 유지하면서 정렬해주는 구문 */ PostgreSQL 계층 쿼리 ) WITH RECURSIVE CODE_LIST(CONTS_ID, CONTS_NM, UP_CONTS_ID, MENU_ORD, DEPTH, PATH, CYCLE) as (              /* 계층구조의 시작조건 쿼리 */              SELECT A.CONTS_ID,                            A.CONTS_NM,                              A.UP_CONTS_ID,                            A.MENU_ORD,                            1,                            ARRAY[A.CONTS_ID::text],                            false              FROM CLT_MENU A              WHERE A.CONTS_ID = 'voc'                            AND A.MENU_INCL_YN = 'Y'              UNION ALL              /*하위 데이터를 찾아가기 위한 반복조건 쿼리*/              SELECT A.CONTS_ID,                            A.CONTS_NM,                              A.UP_CONTS_ID,                            A.MENU_ORD,                            B.DEPTH + 1,                            ARRAY_APPEND(B.PATH, A.CONTS_ID::text),                            A.CONTS_ID = any(B.PATH)              FROM CLT_MENU A, CODE_LIST B              WHERE A.UP_CONTS_ID = B.CONTS_ID                            AND A.MENU_INCL_YN = 'Y'                            AND NOT CYCLE ) /*View 쿼리*/ SELECT CONTS_ID,              CONTS_NM,              UP_CONTS_ID,              MENU_ORD,              DEPTH AS A_MENU_LEVEL,              PATH FROM CODE_LIST WHERE DEPTH IN (2,4) ORDER BY PATH - CYCLE은 RECURSIVE를 통한 재귀 쿼리 수행 시 성능 상의 문제를 해결하기 위함 UNION ALL 다음의 반복조건 쿼리가 수행되면 CYCLE이 false이기 때문에 SELECT문이 수행 되고 검색된 자식 node의 ID 값이 배열(ARRAY[A.CONTS_ID::text])에 추가(ARRAY_APPEND(B.PATH, A.CONTS_ID::text)) 됨. - ANY(B.PATH)는 PATH 배열에 자신의 ID값이 있는 지를 검사하여, 이미 찾은 값에 대해서는 더 이상 데이터 검색을 수행하지 않도록 함. - 배열에는 DataType이 int, text인 형태만 담을 수 있으므로 배열에 담을 varchar타입의 컬럼 뒤에 ::text 를 붙여 형태를 변환 해줌.

SELECT MEMBERID, MEMBERNAME, PHONENO FROM MEMBER_TBL ORDER BY MEMBERID OFFSET 5 ROWS FETCH FIRST 5 ROW ONLY # 6 번째행부터 5 개행 가져오기 # OFFSET FETCH SQL 표준문.

# 테이블 상속. table inheritance # 참조 : https://corekms.tistory.com/entry/table-inheritance%EC%83%81%EC%86%8D a) 부모 테이블 생성 postgres=# CREATE TABLE cities ( postgres(#     name            text, postgres(#     population      float, postgres(#     altitude        int     -- in feet postgres(# ); b) 자식 테이블 생성 postgres=# CREATE TABLE capitals ( postgres(#     state           char(2) postgres(# ) INHERITS (cities); * 부모컬럼포함해서 생성됨 c) insert test insert into cities values('kms',10.23,1000); insert into capitals values('kms',10.23,1000,'01'); select * from cities; name | population | altitude ------+------------+----------  kms  |      10.23 |     1000  kms  |      10.23 |     1000 select * from only cities; name | population | altitude ------+------------+----------  kms  |      10.23 |     1000 d) 설명 - 자식테이블은 부모 컬럼을 포함한다. - 자식에게 데이타를 추가하면 부모에게도 보인다. - 부모에 데이타를 조작하면, 자식에게도 동일하게 영향이 간다. 다만 조회는 only 를 이용하여 해당 테이블의 것만 볼 수 있다. ※ 가독성을 높히기 위해 부모테이블을 대상으로 하는 쿼리를 작성할 때 테이블 후미에 *를 붙여 상속테이블이 포함될 것임을 표시하는 것을 권장합니다. 예시) select * from cities*;

▪ 예측 모드 사용법 explain select * from t2; QUERY PLAN ▪ 실행 모드 사용법 explain (analyze, buffers) select * from t2; QUERY PLAN # 참조 : https://simpledb.tistory.com/75?category=941841 # 참조 : https://simpledb.tistory.com/76?category=941841

 

# Access 방식

Seq Scan 방식 ▪ Seq Scan은 테이블을 Full Scan 하면서 레코드를 읽는 방식이다. ▪ 인덱스가 존재하지 않거나, 인덱스가 존재하더라도 읽어야 할 범위가 넓은 경우에 선택한다.   Index Scan 방식 ▪ Index Scan은 인덱스 Leaf 블록에 저장된 키를 이용해서 테이블 레코드를 액세스하는 방식이다. ▪ 인덱스 키 순서대로 출력된다. ▪ 레코드 정렬 상태에 따라서 테이블 블록 액세스 횟수가 크게 차이 난다.   Bitmap Index Scan 방식 ▪ 테이블 랜덤 액세스 횟수를 줄이기 위해 고안된 방식이다. ▪ Index Scan 방식과 Bitmap Index Scan 방식을 결정하는 기준은 인덱스 칼럼의 Correlation 값이다. ▪ Correlation이란 인덱스 칼럼에 대한 테이블 레코드의 정렬 상태이다. (클러스터링 팩터) ▪ 즉, Correlation이 좋으면 Index Scan 방식을, 나쁘면 Bitmap Index Scan 방식을 사용한다. ▪ Bitmap Index Scan 방식은 액세스할 블록들을 블록 번호 순으로 정렬한 후에 액세스한다. ▪ 이로 인해, 테이블 랜덤 액세스 횟수가 크게 줄어든다. (블록당 1회) ▪ 테이블 블록 번호 순으로 액세스하므로, 인덱스 키 순서대로 출력되지 않는다.   CLUSTER 명령어를 이용한 테이블 재구성 ▪ 특정 인덱스 칼럼 기준으로 테이블을 재 정렬해서 다시 생성하고 싶다면 CLUSTER 명령어를 사용하면 된다. ▪ 다만, 이때도 Vacuum FULL과 동일하게 SELECT와도 락이 호환되지 않는다는 점을 유의해야 한다.   Lossy 모드 ▪ Bitmap Index Scan 방식은 비트맵을 이용해서 처리된다. ▪ 이때, 비트맵 정보는 Backend 프로세스 메모리 내에 저장된다. ▪ 만일 메모리 공간이 부족하면 exact 모드에서 lossy 모드로 전환한다. ▪ lossy 모드는 exact 모드에 비해서 느리다. exact 모드는 비트맵 내의 1개의 비트가 1개의 레코드를 가리킨다. lossy 모드는 비트맵 내의 1개의 비트가 1개의 블록을 가리킨다.   Index Only Scan 방식 ▪ Covering Index를 이용하는 것을 Index Only Scan 방식이라고 한다. ▪ Covering Index란 SELECT 칼럼 및 WHERE 조건을 모두 포함하는 인덱스를 의미한다. ▪ Covering Index의 장점은 테이블 랜덤 액세스를 제거할 수 있다는 것이다. ▪ 단, Vacuum을 수행해야만 Index Only Scan 방식으로 동작한다.   액세스 방식을 제어하는 방법 ▪ SET 절을 이용해서 액세스 방식을 제어할 수 있다

 

# Join 방식

PostgreSQL에서 지원하는 조인 방법 ▪ Nested Loop 조인 ▪ Sort Merge 조인 ▪ 해시 조인 (Hybrid 해시 조인 지원)

Nested Loop Join

NL 조인 시에 Materialize가 발생하면 인덱스 생성을 고려해야 한다.

▪ NL 조인 시에 연결 고리에 인덱스가 없으면 Materialize 오퍼레이션이 발생할 수 있다.

▪ 이는 보완책이지 해결책이 아니다. 따라서 인덱스 생성을 고려해야 한다

 

Hash Join

▪ 해시 조인은 해시 함수를 이용한다. 해시 함수(h)는 다음과 같은 특성을 갖는다.

1. X=Y이면 반드시 h(X)=h(Y) 이다.

2. h(X)≠h(Y)이면 반드시 X≠Y 이다.

3. X≠Y이면 h(X)≠h(Y)인 것이 가장 이상적이다.

4. X≠Y이면 h(X)=h(Y) 일 수도 있다. 이것을 해시 충돌이라고 한다.

 

In-Memory 해시 조인

▪ In-Memory 해시 조인은 해시 Build 작업을 work_mem 공간 내에서 모두 처리할 수 있을 때 사용하는 방식이다.

 

In-Memory로 처리할 수 없을 때 사용되는 해시 조인 방식들

▪ 해시 Build 작업을 work_mem 공간 내에서 모두 처리할 수 없을 때 사용하는 방식은 크게 3가지이다.

Outer 조인 개요

▪ Outer 조인은 조인 성공 여부와 무관하게 기준 집합의 레코드를 모두 출력하는 방식이다.

▪ PostgreSQL은 ANSI-SQL 형태의 Outer 조인 문법만을 지원한다.

▪ Outer 조인은 결과 건수에 영향을 미치므로 사용시에 주의해야 한다.

▪ NL Outer 조인은 기준 집합을 항상 먼저 Driving 한다.

▪ Hash Outer 조인은 SWAP INPUT 기능을 제공한다

 

Hash Right Join, Hash Left Join의 의미

 

 

▪ Explain 결과의 Hash Right Join은 기준 테이블이 RIGHT, 즉 Build 테이블이라는 사실을 알려준다.

▪ Explain 결과의 Hash Left Join은 기준 테이블이 LEFT, 즉 Probe 테이블이라는 사실을 알려준다.

 

 

 

# Query Rewrite

▪ 사용자가 작성한 쿼리를 Optimizer가 더 좋은 실행계획을 수립할 수 있는 형태로 변경하는 것을 Query Rewrite라고 한다.

1) 서브 쿼리 Collapse : 서브 쿼리를 Main 쿼리에 병합하는 기법 (Sub query unnest)

2) View Merging : 뷰 또는 인라인 뷰를 풀어헤쳐 테이블 간의 조인으로 변경하는 방법

3) JPPD(조인 조건 push down) : View Merging이 실패한 경우, 조인 조건을 뷰 내부로 밀어 넣는 방법

 

View Merging

▪ 뷰를 풀어헤쳐서 테이블 간의 조인으로 변경함으로써 , 다양한 조인 순서와 조인 방법을 선택할 수 있다. 이때 View는 2개로 구분된다.

▪ Simple View는 항상 View Merging에 성공한다.

▪ Complex View는 항상 View Merging에 실패한다.

1) Simple View: Group by , Distinct와 같은 Aggregate를 사용하지 않는 뷰

2) Complex View: Group by , Distinct와 같은 Aggregate를 사용하는 뷰

PostgreSQL은 Simple View Merging을 지원한다.

 

JPPD (Join Predicate Push Down)

2가지 방법을 이용해서 JPPD를 동작하게 할 수 있다.

① 조인 조건을 상수화 한다.

② LATERAL View를 사용한다. - 측면 테이블의 조건 입력

▪ 이는 JPPD를 직접 구현할 수 있는 매우 획기적인 튜닝 방법이다.

▪ LATERAL View는 LEFT JOIN도 지원한다.

▪ LEFT JOIN시에는 ON TRUE 키워드를 이용한다

 

# 힌트의 필요성과 PG_HINT_PLAN

힌트의 필요성과 PG_HINT_PLAN

 

▪ 특정 인덱스를 지정하거나, 조인 순서를 지정하거나, 여러 개의 테이블 간의 조인 방법을 각

조인마다 다르게 선택하는 것을 사용자가 지정할 수 있다면, 이는 매우 강력한 무기일 것이다.

▪ ORACLE은 Hint를 이용해서 이러한 작업을 수행할 수 있다.

▪ 다행히 PG_HINT_PLAN 이란 기능이 제공된다.

▪ 이는 Hint 가 아니라 Plan Tree 자체를 변경하는 기법이다.

▪ 따라서 Hint와 달리 Optimizer는 PG_HINT_PLAN을 무시할 수 없다.

▪ 즉, 매우 강력하면서도 위험한 무기인 셈이다.

 

PG_HINT_PLAN 설치

 

PG_HINT_PLAN이 제공하는 Hint들

 

 

 

 

# BRIN Index

BRIN

▪ BRIN은 PostgreSQL 9.5부터 제공되는 인덱스 유형이다.

▪ BRIN은 블록 내의 MIN/MAX 값을 이용해서 블록 단위로 인덱싱한다.

▪ 이로 인해 인덱스 크기는 매우 작아진다는 장점이 있다. 이러한 장점으로 인해, 디스크 공간이 부족한 경우에는 BRIN을 선택하기도 한다.

▪ BRIN과 EXADATA의 Storage 인덱스는 매우 흡사하다

 

BRIN의 특성

▪ BRIN의 크기는 매우 작다. 예를 들어, 1Gb 테이블인 경우에 BRIN의 크기는 6블록이다. (1MB 단위로 1개의 Min/Max 값을 저장하기 때문이다)

▪ 1건을 읽어도 128 블록 (1 MB)를 읽는다.

▪ BRIN은 Index Only Scan 방식보다는 느리다. 왜냐면 반드시 테이블을 액세스하기 때문이다.

▪ BRIN 생성 후에 입력된 레코드들은 Vacuum을 수행해야만 BRIN에 적용된다.

▪ BRIN은 Row Level 락을 지원한다.

 

Partial Index

▪ PostgreSQL은 인덱스 생성 시에 WHERE 조건을 제공한다.

▪ PostgreSQL은 NULL 값도 인덱스에 포함된다.

create index t10_flag_idx on t10(flag) where flag='N’;

 

 

# Parallel Processing (병렬 처리)

Parallel Processing

▪ PostgreSQL 9.6 부터 병렬 처리를 지원한다.

▪ 병렬 처리는 Parallel Scan, Parallel Group by, Parallel Join을 지원한다.

▪ ORACLE과 같은 생산자-소비자 모델은 아니다.

Worker 프로세스 개수 산정 방식

▪ Worker 프로세스의 개수는 min_parallel_relation_size 파라미터값을 기준으로 산정한다.

▪ 기준 크기의 3배 단위로 1개씩 증가하며, 최대 7개이다.

Parallel Index Scan

▪ PostgreSQL 10부터는 Parallel Index Scan 기능을 제공한다.

▪ Parallel Index Scan 기능은 Index Full Scan 뿐만 아니라 Index Range Scan 시에도 동작한다.

1) 조건절 범위에 해당하는 "Start 리프 블록"과 "End 리프 블록"의 위치를 계산한다.

2) 처리 범위가 인덱스 병렬 처리를 할 정도로 큰지 확인한다. (아래 표 참조)

3) 만일 크다면, 크기에 따라서 Worker 개수를 설정한 후에, "Start" ~ "End" 범위를 나눠서 처리한다.

4) 만일 작다면, 싱글 프로세스로 처리한다.

 

 

 

# PostgreSQL 파티션

PostgreSQL 파티션

 

▪ 상속 (Inherits)을 이용해서 파티션을 구현함

▪ 인덱스 정보는 상속되지 않음

▪ Trigger를 이용해서 파티션 Pruning을 처리함

▪ 파티션 생성 시에 CHECK 조건을 이용함

▪ Range, List 파티션을 제공함

▪ Global Index는 제공하지 않음

▪ 입력 조건에 맞는 파티션만 액세스하는 것을 ‘파티션 Pruning’이라고 한다.

▪ 이를 위해서는 constraint_exclusion 파라미터를 ‘on’ 또는 ‘partition’으로 설정해야 한다.

(기본 설정값: partition)

▪ CHECK 조건을 입력하면 정상적으로 파티션 Pruning이 수행된다.

 

서브 파티션을 이용한 튜닝 방안

▪ 1차 파티션을 통해 IO 범위를 줄인 후에도 여전히 IO 량이 많은 경우에는 2차 파티션을 고려한다.

▪ PostgreSQL은 상속에 의한 파티션 생성 방식이므로, 이론적으로는 n차 파티션도 가능하다.

▪ 따라서 업무 속성에 따른 서브 파티션 생성을 통한 IO 튜닝이 가능하다.

 

파티션 Partial 인덱스를 이용한 튜닝 방안

▪ PostgreSQL은 파티션 Partial 인덱스를 제공한다.

▪ 즉, 파티션 별로 인덱스 생성 여부를 결정할 수도 있고, 파티션 마다 다른 인덱스 (예, B*Tree, Covering Index, BRIN 등)를 생성할 수도 있다.

▪ 참고로, 오라클은 12c 버전부터 파티션 Partial 인덱스 기능을 제공한다.

 

파티션 입력 성능 향상을 위한 Tip

▪ RULE 보다는 Trigger를 이용한다. 어떤 경우에도 Trigger가 빠르다.

▪ 자주 입력되는 파티션을 Trigger의 윗 부분에 위치하는 것이 좋다. 그래야 IF THEN ELSE의 부하를 줄일 수 있다.

▪ 가능하면, 파티션을 지정해서 입력하는 것이 좋다.

 

PostgreSQL 파티션 Pruning 시의 주의 사항

▪ 예상과 달리, 파티션 Pruning이 동작하지 않는 경우가 존재한다.

▪ 이점은 쿼리 작성 시에 매우 주의해야할 점이다.

▪ IN 절 내에 Subquery를 사용할 경우에는 파티션 Pruning이 동작하지 않는다.

▪ IN 절 사용시 파티션 Pruning을 유도하기 위해서는 실제 CHECK 조건을 입력해야 한다.

▪ NL 조인으로 수행되면 추가 조건 없이도 파티션 Pruning이 수행된다.

▪ Hash 조인으로 수행되면 추가 조건을 반드시 입력해야 한다.

▪ 따라서, 쿼리 작성 후에 Explain 결과 확인 및 추가 조건을 입력할 필요가 있다.

▪ Hash 조인으로 수행될 때를 고려해서, 상수 조건을 추가하는 것을 습관화 하는 것이 좋다.

▪ PostgreSQL 10부터 오라클과 유사한 파티션 생성 문법을 제공한다.

▪ 이때, 트리거는 생성할 필요가 없다. (이점은 관리측면의 편의성을 제공한다)

▪ 단, 파티션 Pruning은 기존과 같은 한계가 존재한다. (즉, 해시 조인 시에는 상수 조건 추가 필요)

 

파티션 성능 테스트(INSERT)

▪ 9.6 까지는 파티션이 1개인 경우에도 일반 테이블보다 입력 속도가 3배 이상 느리다. 그 이유는 매 건마다 Trigger 함수를 호출하는 부하 때문이다.

▪ CPU 성능에 좋을수록 이 차이는 감소한다.

▪ 파티션 개수가 많아질 수록 입력 성능은 저하된다.

▪ 이 문제는 PG10 버전에서도 여전히 발생한다.

▪ 상속을 이용한 파티션은 Trigger 조정을 통해서 입력 성능을 향상 시킬 수 있다.

▪ 하지만, Native 파티션 (PG 10 버전)은 입력 파티션 위치에 따른 성능 차이가 거의 없다.

 

 

 

# PostgreSQL – Grafana 연동

설치하기 전 git, make, go 패키지 설치

# yum install git

# yum install make

# yum install go

 

git에서 postgres_exporter 가져옴

# git clone https://github.com/prometheus-community/postgres_exporter.git

 

폴더로 이동 후 빌드

# cd postgres_exporter

# make build

 

데이터 소스 환경 변수 설정 및 실행

# export DATA_SOURCE_NAME=postgresql://어드민계정:어드민비밀번호@RDS_ENDPOINT:5432/접속DB?sslmode=disable

# . /postgres_exporter

Prometheus에 pgexporter Job 등록

# vi prometheus.yml

Prometheus 서버에 pgexporter가 등록되었는지 확인

http://prometheus서버ip:9090/targets

 

Prometheus 그래프 확인

http://prometheus서버ip:9090/graph

 

Grafana에서 대시보드에 그래프 출력

 

Grafana에 PostgreSQL 직접 등록

 

 

 

 

# PostgreSQL slow query – Cloudwatch 연동

RDS의 데이터베이스 옵션에서 Cloudwatch Logs로 보낼 수 있도록 설정.