PostgreSQL이 사용하는 대표 Scan과 그 Cost

PostgreSQL이 사용하는 대표 Scan과 그 Cost

explain 명령 결과로 보이는 Scan들

# explain select * from pg_tables where tablename = 'pg_class';
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.27..10.43 rows=1 width=260)
   Join Filter: (t.oid = c.reltablespace)
   ->  Nested Loop Left Join  (cost=0.27..9.38 rows=1 width=140)
         Join Filter: (n.oid = c.relnamespace)
         ->  Index Scan using pg_class_relname_nsp_index on pg_class c  (cost=0.27..8.29 rows=1 width=80)
               Index Cond: (relname = 'pg_class'::name)
               Filter: (relkind = ANY ('{r,p}'::"char"[]))
         ->  Seq Scan on pg_namespace n  (cost=0.00..1.04 rows=4 width=68)
   ->  Seq Scan on pg_tablespace t  (cost=0.00..1.02 rows=2 width=68)

explain의 cost

# select name,setting from pg_settings where name like '%cost';
          name           | setting
-------------------------+---------
 cpu_index_tuple_cost    | 0.005
 cpu_operator_cost       | 0.0025
 cpu_tuple_cost          | 0.01
 jit_above_cost          | 100000
 jit_inline_above_cost   | 500000
 jit_optimize_above_cost | 500000
 parallel_setup_cost     | 1000
 parallel_tuple_cost     | 0.1
 random_page_cost        | 4
 seq_page_cost           | 1

Seq Scan 테이블 전체 순차 탐색

-- 먼저 pg_class 테이블의 통계 정보를 수집합니다.
ioseph=# analyze pg_class;
ANALYZE
ioseph=# select relpages from pg_class where relname = 'pg_class';
 relpages
----------
       14
(1개 행)
-- pg_class 테이블은 총 14 페이지입니다.
-- 이제 테이블 전체 순차 탐색을 하도록 인덱스 탐색과 비트맵 탐색을 사용하지 않겠다고 설정합니다.
ioseph=# set enable_indexscan = off;
SET
ioseph=# set enable_bitmapscan = off;
SET
-- 실행 계획을 봅니다.
ioseph=# explain select * from pg_class where relname = 'pg_class';
                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on pg_class  (cost=0.00..19.19 rows=1 width=265)
   Filter: (relname = 'pg_class'::name)
(2개 행)

# select (reltuples * 0.01) + (reltuples * 0.0025) + (relpages * 1) from pg_class where relname = 'pg_class';
 ?column?
----------
  19.1875

Index Scan 인덱스를 이용한 임의 힙 페이지 탐색

-- pageinspect 확장 모듈을 설치합니다.
ioseph=# create extension pageinspect ;
CREATE EXTENSION
-- pg_class 테이블은 어떤 인덱스를 사용하는지 살펴봤습니다.
ioseph=# \d pg_class
                   "pg_catalog.pg_class" 테이블
       필드명        |     형태     | 정렬규칙 | NULL허용 | 초기값
---------------------+--------------+----------+----------+--------
 oid                 | oid          |          | not null |
 relname             | name         |          | not null |
 relnamespace        | oid          |          | not null |
 relfrozenxid        | xid          |          | not null |
......
 relminmxid          | xid          |          | not null |
 relacl              | aclitem[]    |          |          |
 reloptions          | text[]       | C        |          |
 relpartbound        | pg_node_tree | C        |          |
인덱스들:
    "pg_class_oid_index" PRIMARY KEY, btree (oid)
    "pg_class_relname_nsp_index" UNIQUE CONSTRAINT, btree (relname, relnamespace)
    "pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)
-- 릴레이션 이름에 대한 인덱스가 pg_class_relname_nsp_index 인 것을 보고,
-- 이 인덱스의 두번째 페이지에는 어떤 자료가 들어있는지 살펴봅니다.
-- (인덱스의 첫번째 페이지는 테이블의 페이지와 달리 인덱스 그 자체에 대한 정보가 담겨 있어 자료가 없습니다.
ioseph=# select itemoffset, htid,
  convert_from(
    decode(
      replace(substr(data, 1, position(' 00' in data)), ' ' , ''), 
      'hex'),
    'utf8') from bt_page_items('pg_class_relname_nsp_index', 1) limit 10;
 itemoffset |  htid   |           convert_from
------------+---------+-----------------------------------
          1 |         | pg_range
          2 | (10,8)  | _pg_foreign_data_wrappers
          3 | (10,11) | _pg_foreign_servers
          4 | (10,5)  | _pg_foreign_table_columns
          5 | (10,14) | _pg_foreign_tables
          6 | (10,17) | _pg_user_mappings
          7 | (4,25)  | a
          8 | (8,19)  | administrable_role_authorizations
          9 | (8,18)  | applicable_roles
         10 | (8,21)  | attributes
(10개 행)

--이제 인덱스 탐색을 할 수 있도록 설정을 바꿉니다.
ioseph=# set enable_indexscan = on;
SET
ioseph=# explain select * from pg_class where relname = 'pg_class';
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Index Scan using pg_class_relname_nsp_index on pg_class  (cost=0.27..8.29 rows=1 width=265)
   Index Cond: (relname = 'pg_class'::name)

Bitmap Scan 비트맵을 이용한 탐색

ioseph=# explain select relname,relpages from pg_class where relname >= 'pg_a' and relname < 'pg_aa';
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Index Scan using pg_class_relname_nsp_index on pg_class  (cost=0.27..8.29 rows=1 width=68)
   Index Cond: ((relname >= 'pg_a'::name) AND (relname < 'pg_aa'::name))
(2개 행)

-- 윗 쿼리를 비트맵 스캔으로 유도하려면, 임시로 인덱스 스캔을 사용하지 않는다고 설정합니다.
-- 그러면, 비트맵 스캔을 선택하던가 시퀀스 스캔을 선택하겠죠.
ioseph=# set enable_indexscan = off;
SET
ioseph=# explain select relname,relpages from pg_class where relname >= 'pg_a' and relname < 'pg_aa';
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Bitmap Heap Scan on pg_class  (cost=4.28..8.30 rows=1 width=68)
   Recheck Cond: ((relname >= 'pg_a'::name) AND (relname < 'pg_aa'::name))
   ->  Bitmap Index Scan on pg_class_relname_nsp_index  (cost=0.00..4.28 rows=1 width=0)
         Index Cond: ((relname >= 'pg_a'::name) AND (relname < 'pg_aa'::name))
(4개 행) 

마치며