23. PostgreSQL - planner의 cost 계산

PostgreSQL의 query 옵티마이저는 cost를 기반으로 plan을 세움.

rule기반은 제공하지 않음.

 

cost는 costsize.c에 정의된 function에 의해 계산되며

executor에 의해 실행되는 모든 operation에 해당하는 cost function을 제공함.

 

PostgreSQL의 3종류의 cost

start-up : 첫 tuple을 fetch하기 전에 소모되는 cost

      예를들어, table의 첫 tuple에 access하기 위해 index page를 읽는 비용

run : 모든 tuple을 fetch하는 비용

total : start-up cost + run cost

explain select * from test;
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on test  (cost=0.00..145.00 rows=10000 width=4)

 

위의 예시

start-up cost = 0.00

total coast = 145.00

 

cost 계산을 위한 테스트 데이터 생성

CREATE TABLE tbl (id int PRIMARY KEY, data int);
CREATE INDEX tbl_data_idx ON tbl (data);
INSERT INTO tbl SELECT generate_series(1,10000),generate_series(1,10000);
ANALYZE;

 \d+ tbl
                                           Table "public.tbl"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 id     | integer |           | not null |         | plain   |             |              | 
 data   | integer |           |          |         | plain   |             |              | 
Indexes:
    "tbl_pkey" PRIMARY KEY, btree (id)
    "tbl_data_idx" btree (data)
Access method: heap

 

 

sequential scan run cost 계산

run cost = cpu run cost + disk run cost

              =  (cpu_tuple_cost + cpu_operator_cost) x pg_class.reltuples + seq_page_cost x pg_class.relpages

select name,setting,boot_val,category from pg_settings where name in ('cpu_tuple_cost','cpu_operator_cost','seq_page_cost');
       name        | setting | boot_val |               category                
-------------------+---------+----------+---------------------------------------
 cpu_operator_cost | 0.0025  | 0.0025   | Query Tuning / Planner Cost Constants
 cpu_tuple_cost    | 0.01    | 0.01     | Query Tuning / Planner Cost Constants
 seq_page_cost     | 1       | 1        | Query Tuning / Planner Cost Constants


SELECT relpages, reltuples FROM pg_class WHERE relname = 'tbl';
 relpages | reltuples 
----------+-----------
       45 |     10000


#### plan tree에서 filter의 의미는 table의 전체 tuple을 읽은 뒤 data를 필터링하는 것을 의미함.
EXPLAIN SELECT * FROM tbl WHERE id <= 8000;
                       QUERY PLAN                       
--------------------------------------------------------
 Seq Scan on tbl  (cost=0.00..170.00 rows=8000 width=8)
   Filter: (id <= 8000)
(2 rows)

 

그외 다양한 cost 계산 방식은 아래 링크를 참고

https://www.interdb.jp/pg/pgsql03/02.html

3.2. Cost Estimation in Single-Table Query :: Hironobu SUZUKI @ InterDB

 

selectivity

query predicates의 selectivity는 histogram_bounds or MCV(Most Common Value)를 사용해 계산함.

system catalog인 pg_stats에서 확인할 수 있음.

 

MCV 확인

psql -f country.sql postgres

\d+ countries
                                        Table "public.countries"
  Column   | Type | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
-----------+------+-----------+----------+---------+----------+-------------+--------------+-------------
 continent | text |           |          |         | extended |             |              | 
 country   | text |           |          |         | extended |             |              | 
Indexes:
    "idx_continent" btree (continent)
Access method: heap

#### 선택도 계산
SELECT continent, count(*) AS "number of countries",
(count(*)/(SELECT count(*) FROM countries)::real) AS "number of countries / all countries"
FROM countries 
GROUP BY continent ORDER BY "number of countries" DESC;

   continent   | number of countries | number of countries / all countries 
---------------+---------------------+-------------------------------------
 Africa        |                  53 |                 0.27461139896373055
 Europe        |                  47 |                 0.24352331606217617
 Asia          |                  44 |                 0.22797927461139897
 North America |                  23 |                 0.11917098445595854
 Oceania       |                  14 |                 0.07253886010362694
 South America |                  12 |                 0.06217616580310881

#### pg_stat에 저장된 선택도 확인
SELECT most_common_vals, most_common_freqs FROM pg_stats
WHERE tablename = 'countries' AND attname='continent';
-[ RECORD 1 ]-----+---------------------------------------------------------------------
most_common_vals  | {Africa,Europe,Asia,"North America",Oceania,"South America"}
most_common_freqs | {0.2746114,0.24352331,0.22797927,0.119170986,0.07253886,0.062176164}

 

histogram_bounds 확인

column의 값을 대략 동일한 모집단의 그룹으로 나누는 값의 목록

histogram 100개의 bucket으로 나눠짐.

 

 

MCV를 사용할 수 없는 경우(integer or double precision data type 등을 사용 시)

histogram_bounds 값을 이용해 cost를 계산.

 

SELECT histogram_bounds FROM pg_stats WHERE tablename = 'tbl' AND attname = 'data';

 histogram_bounds                                                                                                                                                                                                                                                 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {1,100,200,300,400,500,600,700,800,900,1000,1100,1200,1300,1400,1500,1600,1700,1800,1900,2000,2100,2200,2300,2400,2500,2600,2700,2800,2900,3000,3100,3200,3300,3400,3500,3600,3700,3800,3900,4000,4100,4200,4300,4400,4500,4600,4700,4800,4900,5000,5100,5200,5300,5400,5500,5600,5700,5800,5900,6000,6100,6200,6300,6400,6500,6600,6700,6800,6900,7000,7100,7200,7300,7400,7500,7600,7700,7800,7900,8000,8100,8200,8300,8400,8500,8600,8700,8800,8900,9000,9100,9200,9300,9400,9500,9600,9700,9800,9900,10000}
(1 row)

 

 

index correlation

index correlation(인덱스 상관관계)는 physical row 순서와 column 값의 logical 순서간 통계적 상관관계를 뜻함.

즉,  index scan cost를 계산할 때 table의 index 순서와 물리적인 tuple 순서의 불일치로 인해 발생하는 random access의 영향을 반영하는 통계적 상관 관계를 의미

index correlation의 범위는 -1 ~ +1임.

 

자세한 내용은 링크 참고

 

explain command 사용  시 주의 사항

EXPLAIN SELECT id, data FROM tbl WHERE data <= 240;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Index Scan using tbl_data_idx on tbl  (cost=0.29..7.69 rows=240 width=8)
   Index Cond: (data <= 240)
(2 rows)

위에서 index condition으로  Index Cond: (data <= 240) 확인 가능

access predicate라고 말하며 index scan의 시작과 끝을 표현함.

 

EXPLAIN command 사용 시 access predicate와 index filter predicate를 구분하지 않음.

따라서 EXPLAIN 결과를 분석할 때 index conditions와 예측되는 rows의 값을 함께 고려해야함

 

블로그 내용 참고

 

random_page_cost 변경 필요성

PostgreSQL의 random_page_cost 값이 seq_page_cost값보다 4배 느리게 설정되어 있음.

이 default 값은 HDD를 쓴다는 가정하에 설정된 값임.

 

따라서 SSD를 쓰고 있다면 random_page_cost = 1로 설정하는 것이 좋음.

random_page_cost를 default 값으로 사용할 경우의 문제점은  블로그를 참고

 

select name,setting,boot_val,category 
from pg_settings where name in ('random_page_cost','seq_page_cost');

       name       | setting | boot_val |               category                
------------------+---------+----------+---------------------------------------
 random_page_cost | 1.1     | 4        | Query Tuning / Planner Cost Constants
 seq_page_cost    | 1       | 1        | Query Tuning / Planner Cost Constants
(2 rows)