PostgreSQL에는 pageinspect라는 extension이 있습니다. 해당 extension을 사용하면, 테이블이나 인덱스의 실제 페이지(블록) 정보를 들여다 볼수 있습니다.
SQL STARTER 도서와 StartUP Tuning 강의에서 제공하는 실습 데이터베이스에는 tr_ord(주문)라는 테이블이 존재합니다. 해당 테이블의 인덱스를 먼저 살펴보면 다음과 같습니다.
-- [SQL-1]
SELECT t1.*
FROM pg_indexes t1
WHERE t1.tablename = 'tr_ord';
schemaname|tablename|indexname |tablespace|indexdef |
----------+---------+-----------+----------+----------------------------------------------------------------------+
startdbpg |tr_ord |tr_ord_pk | |CREATE UNIQUE INDEX tr_ord_pk ON startdbpg.tr_ord USING btree (ord_no)|
startdbpg |tr_ord |tr_ord_fk01| |CREATE INDEX tr_ord_fk01 ON startdbpg.tr_ord USING btree (mbr_id) |
startdbpg |tr_ord |tr_ord_fk02| |CREATE INDEX tr_ord_fk02 ON startdbpg.tr_ord USING btree (shop_id) |
startdbpg |tr_ord |tr_ord_x01 | |CREATE INDEX tr_ord_x01 ON startdbpg.tr_ord USING btree (ord_dtm) |
SQL
복사
pk 인덱스인 tr_ord_pk 인덱스 구조를 pageinspect를 사용해 들여다 보려고 합니다. tr_ord_pk는 ord_no 컬럼으로 구성되어 있습니다. ord_no의 최소값과 최대값을 먼저 살펴보면 다음과 같습니다. tr_ord_pk의 리프 페이지(블록)에는 1부터 730327까지의 순번이 저장되어 있다는 사실을 알 수 있습니다.
-- [SQL-2]
select min(ord_no) min_ord_no ,max(ord_no) max_ord_no from tr_ord;
min_ord_no|max_ord_no|
----------+----------+
1| 730327|
SQL
복사
pk_ord_no의 인덱스 정보를 살펴보면 다음과 같습니다.
-- [SQL-3]
SELECT * FROM public.bt_metap('tr_ord_pk'::text);
magic |version|root|level|fastroot|fastlevel|last_cleanup_num_delpages|last_cleanup_num_tuples|allequalimage|
------+-------+----+-----+--------+---------+-------------------------+-----------------------+-------------+
340322| 4| 412| 2| 412| 2| 0| -1.0|true |
SQL
복사
위 결과의 중요한 내용은 다음과 같습니다.
•
root: 인덱스의 루트 페이지 번호
•
level: 인덱스 높이(루트에서 리프까지 몇 단계인지를 표현, 루트→브랜치→리프)
위 결과에서 인덱스의 루트 페이지 번호가 412라는 것을 알 수 있습니다. 해당 번호를 bt_page_items에 대입해 루트 페이지를 살펴볼 수 있습니다. 아래와 같습니다.
-- [SQL-4]
-- root 가 412
SELECT t1.*
FROM bt_page_items(get_raw_page('tr_ord_pk', 412)) t1;
itemoffset|ctid |itemlen|nulls|vars |data |dead|htid|tids|
----------+--------+-------+-----+-----+-----------------------+----+----+----+
1|(3,0) | 8|false|false| | | |NULL|
2|(411,1) | 16|false|false|77 97 01 00 00 00 00 00| | |NULL|
3|(698,1) | 16|false|false|ed 2e 03 00 00 00 00 00| | |NULL|
4|(984,1) | 16|false|false|63 c6 04 00 00 00 00 00| | |NULL|
5|(1270,1)| 16|false|false|d9 5d 06 00 00 00 00 00| | |NULL|
6|(1556,1)| 16|false|false|4f f5 07 00 00 00 00 00| | |NULL|
7|(1842,1)| 16|false|false|c5 8c 09 00 00 00 00 00| | |NULL|
SQL
복사
위 결과 항목별 중요 내용을 정리하면 아래와 같습니다.
•
itemoffset: 페이지내 슬롯번호 정도로 생각하면 됩니다.
•
ctid(block, offset): block은 자식 페이지 블록번호, offset은 자식페이지 내 슬롯번호
•
itemlen: 해당 슬롯의 바이트 길이
•
nulls: 키 필드에 null 포함 여부
•
vars: 가변길이 필드 포함 여부
•
data: 인덱스 키값: 리틀엔디안 hex값 형태로 저장되어 있음
위 결과 중에 data 부분이 키 값인데 hex 값 형태로 저장되어 있습니다. hex 값을 우리가 알기 쉬운 십진수 형태로 변경하려면 다음과 같습니다.(ord_no는 숫자형이기 때문에, 숫자로 변환해야 합니다. 만약에 인덱스 키 값이 문자형이라면 다른 방식을 사용해야 합니다.)
•
data = 77 97 01 00 00 00 00 00
◦
공백을 제거합니다. : 7797010000000000
◦
두자리씩 끊어서 뒤집습니다.: 0000000000019777
◦
앞쪽에 x를 붙입니다: x0000000000019777
◦
64비트 비트열로 변환후에 다시 bigint로 변환합니다: 104311
좀 복잡하지만, 위와 같이 변환을 하면 hex 형태로 저장된 인덱스 키 값을 숫자로 변환할 수 있습니다. 위 내용을 SQL로 구현해서 data의 실제 키 값을 같이 출력해보면 다음과 같습니다.
-- [SQL-5]
-- root 가 412
SELECT t1.*
,('x' || substring(replace(data::text,' ','') FROM 15 FOR 2) ||
substring(replace(data::text,' ','') FROM 13 FOR 2) ||
substring(replace(data::text,' ','') FROM 11 FOR 2) ||
substring(replace(data::text,' ','') FROM 9 FOR 2) ||
substring(replace(data::text,' ','') FROM 7 FOR 2) ||
substring(replace(data::text,' ','') FROM 5 FOR 2) ||
substring(replace(data::text,' ','') FROM 3 FOR 2) ||
substring(replace(data::text,' ','') FROM 1 FOR 2))::bit(64)::bigint val
FROM bt_page_items(get_raw_page('tr_ord_pk', 412)) t1;
itemoffset|ctid |itemlen|nulls|vars |data |dead|htid|tids|val |
----------+--------+-------+-----+-----+-----------------------+----+----+----+------+
1|(3,0) | 8|false|false| | | |NULL| 0|
2|(411,1) | 16|false|false|77 97 01 00 00 00 00 00| | |NULL|104311|
3|(698,1) | 16|false|false|ed 2e 03 00 00 00 00 00| | |NULL|208621|
4|(984,1) | 16|false|false|63 c6 04 00 00 00 00 00| | |NULL|312931|
5|(1270,1)| 16|false|false|d9 5d 06 00 00 00 00 00| | |NULL|417241|
6|(1556,1)| 16|false|false|4f f5 07 00 00 00 00 00| | |NULL|521551|
7|(1842,1)| 16|false|false|c5 8c 09 00 00 00 00 00| | |NULL|625861|
SQL
복사
위 결과를 통해, 루트의 브랜치를 분기하는 기준 키 값(data)이 실제 어떤 값이 저장되어 있는지 알 수 있습니다. 이제, 각 브랜치 페이지에 저장된 값의 범위를 찾아보려고 합니다. 루트의 첫 번째 슬록에 ctid는 (3,0)입니다. 3번 블록에 첫번째 브랜치가 저장되어 있다는 뜻입니다. 3번 블록을 조회하면서, 해당 블록이 가진 인덱스 키 값의 최대값과 최소값을 같이 조회해봅니다. 다음과 같습니다.
-- [SQL-6]
-- 첫번째 브랜치(citd=3,0)
SELECT MIN(t2.val) OVER() min_val
,MAX(t2.val) OVER() max_val
,t2.*
FROM (
SELECT t1.*
,('x' || substring(replace(data::text,' ','') FROM 15 FOR 2) ||
substring(replace(data::text,' ','') FROM 13 FOR 2) ||
substring(replace(data::text,' ','') FROM 11 FOR 2) ||
substring(replace(data::text,' ','') FROM 9 FOR 2) ||
substring(replace(data::text,' ','') FROM 7 FOR 2) ||
substring(replace(data::text,' ','') FROM 5 FOR 2) ||
substring(replace(data::text,' ','') FROM 3 FOR 2) ||
substring(replace(data::text,' ','') FROM 1 FOR 2))::bit(64)::bigint val
FROM bt_page_items(get_raw_page('tr_ord_pk', 3)) t1
) t2
ORDER BY t2.val ASC;
min_val|max_val|itemoffset|ctid |itemlen|nulls|vars |data |dead|htid|tids|val |
-------+-------+----------+-------+-------+-----+-----+-----------------------+----+----+----+-----+
0| 104311| 2|(1,0) | 8|false|false| | | |NULL| 0|
0| 104311| 3|(2,1) | 16|false|false|6f 01 00 00 00 00 00 00| | |NULL| 367|
0| 104311| 4|(4,1) | 16|false|false|dd 02 00 00 00 00 00 00| | |NULL| 733|
...생략...
SQL
복사
위 결과를 통해 3번 블록인 첫번째 브랜치에는 0부터 104311까지가 저장된 것을 알 수 있습니다. 가장 작은 값이 들어간 리프 블록은 ctid가 (1,0)입니다. 위 SQL에, get_raw_page에 대한 값을 1로 변경해 실행보면 다음과 같습니다. ord_no 값이 1부터 차례대로 입력되어 있는 것을 알 수 있습니다.
-- [SQL-7]
-- 첫번째 리프(citd=1,0)
SELECT MIN(t2.val) OVER() min_val
,MAX(t2.val) OVER() max_val
,t2.*
FROM (
SELECT t1.*
,('x' || substring(replace(data::text,' ','') FROM 15 FOR 2) ||
substring(replace(data::text,' ','') FROM 13 FOR 2) ||
substring(replace(data::text,' ','') FROM 11 FOR 2) ||
substring(replace(data::text,' ','') FROM 9 FOR 2) ||
substring(replace(data::text,' ','') FROM 7 FOR 2) ||
substring(replace(data::text,' ','') FROM 5 FOR 2) ||
substring(replace(data::text,' ','') FROM 3 FOR 2) ||
substring(replace(data::text,' ','') FROM 1 FOR 2))::bit(64)::bigint val
FROM bt_page_items(get_raw_page('tr_ord_pk', 1)) t1
) t2
ORDER BY t2.val ASC;
min_val|max_val|itemoffset|ctid |itemlen|nulls|vars |data |dead |htid |tids|val|
-------+-------+----------+------+-------+-----+-----+-----------------------+-----+------+----+---+
1| 367| 2|(0,1) | 16|false|false|01 00 00 00 00 00 00 00|false|(0,1) |NULL| 1|
1| 367| 3|(0,2) | 16|false|false|02 00 00 00 00 00 00 00|false|(0,2) |NULL| 2|
1| 367| 4|(0,3) | 16|false|false|03 00 00 00 00 00 00 00|false|(0,3) |NULL| 3|
...생략...
SQL
복사
마지막으로, [SQL-5] 루프 블록 조회 결과의 브랜치 블록 번호(ctid) 별로 [SQL-6]을 실행해, 루트 블록의 엔트리별 브랜치 블록의 최대, 최소값을 정리해보면 다음과 같습니다.
위 결과를 통해, 알 수 있는 사실 하나는, 인덱스 엔트리별로 가진 인덱스 키 값이, 이전 브랜치(또는 리프)의 최대값이라는 사실입니다. 이 부분은 인덱스 탐색을 이해는데 있어서 중요한 포인트중 하나입니다.
오늘 살펴볼 내용은 여기까지입니다. pageinspect를 사용해 인덱스 구조를 탐색해보기 바랍니다.