Mastering Postgresql 13 chapter 6 을 간단히 정리한 글입니다.

Spotting problems

Inspecting estimates

• 실제와 다를 수 있기 때문에 Analyze 로 해야함
• 과소평가되는 cross-column correlation 를 주의할 필요가 있다

Inspecting buffer usage

• 램에 캐싱된 데이터를 가져오는 것이 디스크에서 블록사이즈로 가져오는 것 보다 100배 빠름

Fixing high buffer usage

• CLUSTER 사용
  - 특정 인덱스에 맞게 테이블 row 를 rewrite
  - 테이블 락을 사용하기 때문에 OLTP 에서는 어려움

Understanding and fixing joins

Getting joins right

SELECT avg(aid), avg(bid)
FROM a
LEFT JOIN b
ON (aid = bid AND bid = 2);
	   avg         |       avg 
-------------------+--------------------  
2.0000000000000000 | 2.0000000000000000

• outer join 은 생각한 대로 동작하지 않음

Processing outer joins

• inner join 재정렬하면 속도를 상당히 개선할 수 있다

(A leftjoin B on (Pab)) innerjoin C on (Pac) = (A innerjoin C on (Pac)) leftjoin B on (Pab)

Understanding the join_collapse_limit variable

• join_collapse_limit 는 쿼리 계획 중에 단일 조인 트리로 축소할 수 있는 관계의 최대 수를 설정하는 구성 매개 변수
• postgresql 은 쿼리에 사용되는 테이블들이 조인되는 가능한 모든 조인쿼리 셋을 생성
• 최적화기는 이중 가장 효율적인 것을 선택
• 하지만 여러 테이블이 포함된 복잡한 쿼리에서는 조인트리가 굉장히 많다
• 이 중 설정 된 limit 을 넘어갈 경우 heuristic search 에 의해 가장 효율적인 것을 찾는다.

Enabling and disabling optimizer settings

• 기본적으로는 optimizer 가 효율적인것을 판단하지만, end user 가 옵션을 원하는 대로 설정 가능
• 하지만 모든 옵션을 꺼버릴 경우 실제로 끄는게 아닐 수도 있음
  - ex) hash join, merge join, nested loops 를 모두 종료해도 기본적인 join 을 사용
• 옵션 종류

# - Planner Method Configuration -

#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_indexonlyscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_parallel_append = on
#enable_seqscan = on
#enable_sort = on
#enable_incrementalsort = on
#enable_tidscan = on
#enable_partitionwise_join = off
#enable_partitionwise_aggregate = off
#enable_parallel_hash = on
#enable_partition_pruning = on

Understanding genetic query optimization

Genetic Query Optimization (GEQO)

• GEQO 로 쿼리 최적하하기 이전에 join_collapse_limit 등을 이용해서 최적화 경험을 쌓길

Partitioning data

• 부모 테이블에서 상속받아 나눌 수 있다
  

CREATE TABLE t_data (id serial, t date, payload text);
CREATE TABLE t_data_2016 () INHERITS (t_data);
CREATE TABLE t_data_2015 () INHERITS (t_data);
CREATE TABLE t_data_2014 () INHERITS (t_data);
CREATE TABLE t_data_2013 (special text) INHERITS (t_data);

Applying table constraints

• 필터를 걸 경우 모든 파티션에 걸게 된다.

EXPLAIN SELECT * FROM t_data WHERE t = '2016-01-04';
					QUERY PLAN
---------------------------------------------------------------------------- 
Append (cost=0.00..95.24 rows=23 width=40)
	-> Seq Scan on t_data t_data_1 (cost=0.00..0.00 rows=1 width=40)
		Filter: (t = '2016-01-04'::date)
    -> Seq Scan on t_data_2016 t_data_2 (cost=0.00..25.00 rows=6 width=40)
	    Filter: (t = '2016-01-04'::date)
    -> Seq Scan on t_data_2015 t_data_3 (cost=0.00..25.00 rows=6 width=40)
	    Filter: (t = '2016-01-04'::date)
    -> Seq Scan on t_data_2014 t_data_4 (cost=0.00..25.00 rows=6 width=40)
	    Filter: (t = '2016-01-04'::date)

• Partitioning data 예제처럼, 연도별로 테이블을 나눈다 하더라도 postgres 는 그것을 알 방법이 없음
• 이럴 때 constraint exclusion 을 적용

ALTER TABLE t_data_2013
	ADD CHECK (t < '2014-01-01');
ALTER TABLE t_data_2014
	ADD CHECK (t >= '2014-01-01' AND t < '2015-01-01');
ALTER TABLE t_data_2015
	ADD CHECK (t >= '2015-01-01' AND t < '2016-01-01');
ALTER TABLE t_data_2016
	ADD CHECK (t >= '2016-01-01' AND t < '2017-01-01');

Postgresql 의 constraints 는 MySQL 보다 더 엄격히 관리된다

EXPLAIN SELECT * FROM t_data WHERE t = '2016-01-04';
							QUERY PLAN 
---------------------------------------------------------------------------- 
Append (cost=0.00..25.04 rows=7 width=40)
	-> Seq Scan on t_data t_data_1 (cost=0.00..0.00 rows=1 width=40)
		Filter: (t = '2016-01-04'::date)
	-> Seq Scan on t_data_2016 t_data_2 (cost=0.00..25.00 rows=6 width=40)
		Filter: (t = '2016-01-04'::date)

Modifying inherited structures

• 부모 테이블에 변경을 가하면, 자식 테이블들에도 변경이 전파됨
• Index 는 별개
  - 부모에 인덱스를 추가해도 자식 테이블에는 반영이 안된다

Moving tables in and out of partitioned structures

• create 시 LIKE 키워드를 사용하면 해당 table 과 같은 layout 으로 테이블 생성 가능
  - index, constraints 및 default 설정들도 다 같이 생성한다.

Cleaning up data

• 부모 table 을 cascade 로 삭제 시 child 테이블들도 삭제된다

Understanding PostgreSQL 13.x partitioning

• 기본 table 에서 partition 했을 경우 update 하더라도 알아서 range 에 맞는 partition 으로 바뀐다.
• partition 을 할 경우 index 도 자동으로 생김
• default 키워드를 이용해 range 에 속하지 않는 데이터들을 분류할 수 있다

Adjusting parameters for good query performance

• group by 에서 hash function 으로 그루핑 시 entry 가 너무 많으면 메모리가 부족하여 다른 전략을 취함
• work_mem 의 사이즈를 늘려줌으로써 해소 가능

Speeding up sorting

• work_mem 은 grouping 의 속도만 향상시키는 것은 아님
• work_mem default 는 4MB
• 정렬 메카니즘 종류
  - external merge Disk
  - quicksort Memory
  - top-N heapsort Memory - limit 을 했을 경우
• postgres 13 에 추가된 알고리즘
  - incremental Sort: 이미 정렬된 변수로 정렬
• work_mem 을 높게 잡을 경우 OLTP 환경에서는 OOM 이 날 수 있다고들 한다
  - 하지만 애초에 OLTP 시스템이 OOM 이 나게 된 경위부터 확인하는 것이 맞음
• globally 하게 설정할 수 있지만, operation 당 적용이 기본

Speeding up administrative tasks

• CREATE INDEX 같은 경우 maintenance_work_mem 를 사용
  - index 생성 시 속도를 더욱 향상시킴

DROP INDEX idx_id;
DROP INDEX
SET maintenance_work_mem TO '1 MB'; 
SET
\timing
Timing is on.
CREATE INDEX idx_id ON t_test (id);
CREATE INDEX
Time:  104.268 ms
------------------------------------
SET maintenance_work_mem TO '1 GB'; 
SET
CREATE INDEX idx_id2 ON t_test (id); CREATE INDEX;
Time:  46.774 ms

• postgresql 11 부터 btree 인덱스를 병렬 생성 가능

SHOW max_parallel_maintenance_workers;
max_parallel_maintenance_workers
----------------------------------
2

• vacuum / alter table 작업에도 사용됨

Making use of parallel queries

• 9.6 부터 병렬 쿼리 지원
• 11 부터 더 많은 기능 추가 (parellel index creation)
• 기본적으로 max_parallel_workers_per_gather (2), min_parallel_table_scan_size (8MB)

What is PostgreSQL able to do in parallel?

• Parallel sequential scans
• Parallel index scans (btrees only)
• Parallel bitmap heap scans
• Parallel joins (all types of joins)
• Parallel btree creation (CREATE INDEX)
• Parallel aggregation
• Parallel append
• VACUUM
• CREATE INDEX

Parallelism in practice

• 병렬처리 시 내부 프로세스 소통이 굉장히 비싼 작업
  - single-process 모드보다 느릴 수 있다.

#parallel_tuple_cost = 0.1

프로세스끼리 튜플을 넘길 때 계산에서 0.1 포인트씩 추가

Introducing JIT compilation

Configuring JIT

• 컴파일 타임에 추가되어야함

  --with-llvm build with LLVM based JIT support