[DA/튜닝] PostgreSQL 튜닝 관련 Parameter

PostgreSQL (v.10.15) 파라미터

# 쿼리 플랜 방법 설정 변수

    - enable_bitmapscan (Default : on)

쿼리 플래너의 bitmap-scan plan types 사용을 활성화 또는 비활성화한다. 

    - enable_gathermerge (Default : on)

쿼리 플래너의 gather merge plan types 사용을 활성화 또는 비활성화한다.

    - enable_hashagg (Default : on)

쿼리 플래너의 hashed aggregation plan types 사용을 활성화 또는 비활성화한다.

    - enable_hashjoin (Default : on)

쿼리 플래너의 hash-join plan types 사용을 활성화 또는 비활성화한다.  

    - enable_indexscan (Default : on)

쿼리 플래너의 index-scan plan types 사용을 활성화 또는 비활성화한다.  

    - enable_indexonlyscan (Default : on)

쿼리 플래너의 index-only-scan plan types 사용을 활성화 또는 비활성화한다.

    - enable_material (Default : on)

해당 파라미터를 off로 할 시, materialization을 완전히 억제하는 것은 어렵지만 이 변수를 해제하면 정확도가 요구되는 경우 외에는 플래너의 materialize 노드 삽입이 방지된다. 

    - enable_mergejoin (Default : on)

쿼리 플래너의 merge-join plan types 사용을 활성화 또는 비활성화한다. 

    - enable_nestloop (Default : on)

해당 파라미터를 off로 할 시, nested-loop joins의 우선순위를 하위에 두고 실행계획을 세운다.

    - enable_seqscan (Default : on)

해당 파라미터를 off로 할 시, sequential scans의 우선순위를 하위에 두고 실행계획을 세운다.

    - enable_sort (Default : on)

해당 파라미터를 off로 할 시, explicit sorts의 우선순위를 하위에 두고 실행계획을 세운다.   

    - enable_tidscan (Default : on)

쿼리 플래너의 TID scan plan types 사용을 활성화 또는 비활성화한다. 

# 쿼리 플랜 비용 설정 변수

    - seq_page_cost (Default : 1.0)

플래너가 예상한, 디스크에서 page를 fetch 할 때 sequential fetch의 비용을 설정한다. 기본값일 때, 동일한 이름의 테이블스페이스 매개 변수 설정에 의해 특수한 테이블스페이스의 테이블과 인덱스를 오버라이드할 수 있다.

    - random_page_cost (Default : 4.0)

플래너가 예상한, 디스크에서 page를 fetch 할 때 random fetch의 비용을 설정한다. 기본값일 때, 동일한 이름의 테이블스페이스 매개 변수 설정에 의해 특수한 테이블스페이스의 테이블과 인덱스를 오버라이드할 수 있다.

이 값을 seq_page_cost에 비례하여 줄이면 시스템이 인덱스 스캔 쪽으로 치우치게 된다. 이 값을 늘리면 인덱스 스캔이 좀 더 비싸진다. 양쪽 값을 함께 늘리거나 줄여서 CPU 비용에 비례하여 디스크 I/O 비용의 중요도를 변경할 수 있다. 

 + Tip

random_page_cost를 seq_page_cost 미만으로 설정하는 것이 시스템에서 허용되더라도 실제로는 그렇게 하는 것이 합리적이지 않다. 단, 데이터베이스 전체가 RAM에 캐치되는 경우에는 시퀀스 밖 페이지를 손대는 것에 대한 패널티가 없으므로 동일하게 설정하는 것은 괜찮다. 또한 과도하게 캐시되는 데이터베이스에서 RAM에 이미 있는 페이지를 가져오는 비용이 일반적인 상태의 것보다 훨씬 적으므로 사용자는 CPU 매개 변수에 비례하여 양쪽 값을 줄여야 한다.

    - cpu_tuple_cost (Default : 0.01)

쿼리 실행 중 각 row를 읽을 때의 cost를 설정하는 파라미터

    - cpu_index_tuple_cost (Default : 0.005)

인덱스 스캔 도중 각 인덱스의 row를 읽을 때의 cost를 설정하는 파라미터

    - cpu_operator_cost (Default : 0.0025)

쿼리 실행 중 실행된 각 연산자 또는 함수의 처리 비용을 설정하는 파라미터

    - effective_cache_size (Default : 4GB)

단일 쿼리에 사용할 수 있는 디스크 캐시의 효율적인 크기에 대한 플래너의 가정을 설정한다. 이것은 인덱스를 사용하는 비용 추정에 반영된다. 값이 클수록 인덱스 스캔이 사용될 가능성이 높다. 값이 작을수록 순차 스캔이 사용될 가능성이 높다. 이 매개 변수를 설정하는 경우 PostgreSQL의 공유 버퍼와, PostgreSQL 데이터 파일에 사용되는 커널의 디스크 캐시 부분을 모두 고려해야 한다. 또한 사용 가능한 공간을 공유해야 하므로 서로 다른 테이블에 대해 예상되는 동시 쿼리 수도 고려해야 한다. 이 매개 변수는 PostgreSQL에 의해 할당된 공유 메모리 크기에는 효과가 없으며, 커널 디스크 캐시도 예약하지 않는다. 추정용으로만 사용된다. 또한 시스템은 디스크 캐시에 쿼리 간 데이터가 잔류할 것이라고 가정하지 않는다. 

# 제네틱 쿼리 옵티마이저 설정 변수

    - geqo (Default : on)

제네틱 쿼리 옵티마이저를 사용할 것인지 하지 않을 것인지 설정하는 파라미터

    - geqo_threshold (Default : 12)

제네틱 쿼리 옵티마이저를 사용 시, FROM 절에 적어도 몇 개의 테이블로 쿼리 플랜을 생성하게 할 지 설정하는 파라미터 (FULL OUTER JOIN 은 오직 1개만 인식??)

    - geqo_effort (Default : 5) [ 1 ~ 10 ] 

쿼리 플랜을 생성하는데 사용되는 시간과 플랜의 퀄리티를 제어하는 파라미터. 높을수록 시간을 많이 사용하지만, 좋은 플랜을 설계

    - geqo_pool_size (Default : 0) [ 최소 2, 보통 100 ~ 1000 ]

제네틱 쿼리 옵티마이저의 pool 크기를 설정하는 파라미터. 

0으로 설정 시, geqo_effort 값과 들어오는 쿼리에 따라 읽어야할 테이블 수에 따라 동적으로 변함.

    - geqo_generations (Default : 0) [ 최소 1, 보통 geqo_pool_size와 동일하게 함. ] 

알고리즘 반복 숫자이자, GEQO에서 사용되는 생성 수를 설정하는 파라미터.

0으로 설정되면 geqo_pool_size 값에 따름.

    - geqo_selection_bias (Default : 2.00) [ 1.50 ~ 2.00 ]

GEQO에 의해 사용되는 선택 편향 관련 작업을 제어한다. 선택 편향이란 자연 선택 기준값이 된다.

    - geqo_seed (Default : 0) [ 0 ~ 1 ]

조인 순서 검색 공간의 랜덤 경로를 선택하기 위해 GEQO에 의한 난수 발생기의 기본값을 제어한다.
값을 변경하면 탐색할 조인 경로 집합이 바뀌고 결과적으로 발견된 최상의 경로가 좋을 수도 있고 나쁠 수도 있다.

 

# 기타 쿼리 플랜 설정 변수

    - default_statistics_target (Default : 100)

ALTER TABLE SET STATISTICS를 통해 설정된 칼럼 특정 타겟 없이, 테이블 칼럼에 대한 기본 통계 타겟을 설정한다. 큰 값을 설정하면 ANALYZE를 수행하는 데 필요한 시간이 늘어나지만 플래너 평가 수준을 높일 수 있다. 

    - constraint_exclusion (Default : partition) [ on | off | partition ]

쿼리 최적화를 위해 쿼리 플래너의 테이블 제약 조건을 제어한다. constraint_exclusion의 허용 값은 on(모든 테이블에 대해 제약 조건 검사), off(제약 조건 검사 안함) 및 partition(상속 자식 테이블 및 UNION ALL 하위 쿼리에 대해서만 제약 조건 검사)이다. 

    - cursor_tuple_fraction (Default : 0.1) [ 0.1 ~ 1.0 ]

검색할 커서 행의 분할에 대한 플래너의 추정치를 설정한다.  값이 작을수록 플래너가 커서에 대해 “fast start” 플랜을 사용하도록 유도된다. 즉, 부분 범위 처리를 적극적으로 사용. 값이 클수록 커서가 정확히 일반 쿼리처럼 플랜되어 총 예상 시간만 고려된다. 즉, 부분 범위 처리를 사용하지 않음.

    - from_collapse_limit (Default : 8)

결과로 나온 FROM 목록에 이 숫자만큼의 항목이 없을 경우 플래너가 서브 쿼리를 메인 쿼리에 merge하려고 한다. 값이 작을수록 플래닝 시간은 짧아지지만 좋지 못한 플랜이 나올 수 있다. 이 값을 geqo_threshold 이상으로 설정하면 GEQO 플래너의 사용이 트리거되어 결과적으로 최적화되지 않은 플랜이 된다. 

    - join_collapse_limit (Default : 8)

결과 목록이 이 항목에 미치지 못할 경우 플래너는 명시적 JOIN 구문(FULL JOIN 제외)을 FROM 항목으로 재작성한다. 값이 작을수록 플래닝 시간은 짧아지지만 하위 쿼리 플랜이 나올 수 있다.

기본적으로 이 변수는 from_collapse_limit와 동일하게 설정되며, 1로 설정하면 명시적 JOIN의 재정렬이 방지된다. 쿼리 플래너가 항상 최적의 조인 순서를 선택하는 것은 아니므로 고급 사용자는 이 변수를 임시로 1로 선택한 다음, 원하는 조인 순서를 명시적으로 지정할 수 있다. 이 값을 geqo_threshold 이상으로 설정하면 GEQO 플래너의 사용이 트리거되어 결과적으로 최적화되지 않은 플랜이 된다. 

    - work_mem (Default : 4096)

정렬이 언제 메모리 내부에서 수행되고, 언제 디스크로 스왑되어 수행되는지를 결정하는 파라미터.

※ 주의 : 각 클라이언트가 각 정렬 노드에서 설정 값 만큼의 메모리를 사용할 수 있음 따라서 아래와 같은 방법으로 설정할 것을 권고.

work_mem = Total RAM / max_connections / (4 ~ 16)

    - cursor_tuple_fraction (Default : 0.1)커서를 이용한 질의가 리턴할 행의 수에 대한 기대치를 낮출 수 있게 해주는 파라미터. 1.0으로 설정 시, 커서는 일반적인 쿼리와 동일하게 동작한다. 0.1로 설정 시, 쿼리 옵티마이저는 전체 행의 10%만이 요청될 것이라는 가정하에 신속하게 결과를 리턴하는 쿼리 계획이 수립(부분 범위 처리와 비슷하게 동작) 되도록 한다.