PostgreSQL 8.3에서 추가된 XML 함수들 구경하기
PostgreSQL 8.3에서 추가된 기능들 중에 재미있는 것이 많이 있다. 풀텍스트 검색(full text search)이라던가, XML 함수 같은 것들. VLAAH에서도 PostgreSQL을 사용하고 있고,1 개인적으로도 작년부터 PostgreSQL을 주로 사용하고 있는데,2 8.3 버전이 릴리즈되고 나서 저런 기능들을 써보고 싶었지만 이래저래 계기도 없고 시간도 부족해서 하지 못하다가, 오늘 생각난 김에 간단하게 XML 함수들을 시험해봤다.
해보고 싶었던 것은 XML 함수를 썼을 때 간단히 어떤 작업들을 효율적으로 할 수 있을지에 대해서였다. 바로 생각난 것은 많은 XML 데이터를 쌓아놓고 특정 노드 데이터를 기준으로 정렬이나 검색을 얼마나 빠르게 할 수 있을까 하는 것. 다시 말해 XML의 특정 노드들만 인덱스(index)를 태울 수 있는가였다.
일단 너무 가볍지도 무겁지도 않은 XML 데이터가 필요했는데, IRC #perky 채널에서 조언을 구하니 klutzy 님이 위키미디어에서 공개한 덤프 데이터를 알려주셨다. 너무 큰 것은 부담스러워서 중국어판 위키백과 데이터를 받아서 사용하기로 했다. 압축을 푸니 898MB 정도 되는 XML 파일 하나가 떨어졌다. 나는 페이지 각각의 XML 파일이 나올 줄 알았기 때문에 잠시 당황. 힘겹게 파일을 열어보니 페이지 하나가 <page> 엘리먼트로 표현되는 듯했다. 그래서 무식하게 정규표현식으로 /<page>.*?<\/page>/를 매치한 결과를 테이블에 삽입하기로 했다.
일단 대충 테이블 하나 만들고.3
CREATE TABLE wikipedia_pages ( page xml NOT NULL );
Perl로 테이블에 삽입해주는 스크립트를 작성했다.
#!/usr/bin/env perl
use warnings;
use strict;
use DBI;
my $db = DBI->connect('dbi:Pg:dbname=xmltest', 'postgres', '');
$db->{pg_enable_utf8} = 1;
$db->{pg_server_prepare} = 1;
my $stmt = $db->prepare('INSERT INTO wikipedia_pages (page) VALUES (?)');
my $page;
while(my $line = <>) {
$page .= $line;
if($page =~ m{(<page>.+?</page>)}sg) {
$stmt->execute($1);
$page = '';
}
}
CPAN으로 DBD::Pg 모듈 설치해서 작성하니 간단하게 해결됐다. 스크립트를 실행해보니 약 10분 정도 걸렸다. 카디널리티(cardinality)가 얼마나 될까?
xmltest=# SELECT count(*) FROM wikipedia_pages;
count
--------
445322
(1 row)
40만개 정도. 이 가운데 적어도 한 두개 정도는 무효한(invalid) XML 문서가 들어갔을 것 같은데도, 에러 하나 나지 않은 걸 보니 운이 좋았나보다. 아니면 내가 괜한 겁이 많은 것일지도 모르고; 어쩌면 PostgreSQL의 xml 타입이 생각보다 엄격하지 않은 것일지도 모른다.
어쨌든, 자, 이제 XML 함수를 써볼까? 그 전에 <page> 엘리먼트 구조부터 보자면 다음과 같다.
<page>
<title>Wikipedia:Upload log</title>
<id>1</id>
<restrictions>sysop</restrictions>
<revision>
<id>689183</id>
<timestamp>2005-01-28T14:30:41Z</timestamp>
<contributor>
<username>Shizhao</username>
<id>138</id>
</contributor>
<text xml:space="preserve">'''这个页面现在已经作废，相关信息请参看[[Special:Log/upload]]。'''
Below is a list of the most recent file uploads.
All times shown are server time (UTC).
...생략</text>
</revision>
</page>
어떤 엘리먼트를 가지고 놀아야 재밌을까? 내가 생각한 것은 <username> 내용을 기준으로, 어떤 사람이 중국어 위키백과에 가장 기여를 많이 했고, 기여량이 구체적으로 얼마나 되는지 알아내는 것이었다. 물론 이 덤프 XML은 현재 리비전에 대해서만 데이터가 존재하기 때문에 전혀 정확한 데이터 산출은 아니다. 하지만 내 목적은 어쨌든 XML 함수를 테스트해보고 싶은 것 뿐이라 그 정도로 타협.
XPath로 해당 유저네임을 뽑아내려면 다음과 같은 표현식을 사용하면 된다.
/page/revision/contributor/username/text()
마지막의 text() 함수는 선택된 노드의 텍스트 값을 선택하라는 뜻이다. 저게 없을 경우 Shizhao 대신 <username>Shizhao</username> 노드가 선택되게 된다. XML 관련 함수들 중에 마침 무척 적당한 이름의 xpath() 함수가 있다. 당신이 생각하는 바로 그것. 한번 시험삼아 써보자. 일단 열 개만.
xmltest=# SELECT xpath('/page/revision/contributor/username/text()', page)
xmltest-# FROM wikipedia_pages LIMIT 10;
xpath
---------------
{Shizhao}
{Lorenzarius}
{Sl}
{Synthebot}
{Shizhao}
{1852}
{Sl}
{Jusjih}
{}
{Shizhao}
(10 rows)
앗. 배열로 나온다.4 게다가 아홉번째를 보니 매치되지 않아서 빈 배열을 반환하는 경우도 있다. 쿼리를 조금 바꿔보자.
xmltest=# SELECT (xpath('/page/revision/contributor/username/text()', page))[1]::text
xmltest-# FROM wikipedia_pages
xmltest-# WHERE (xpath('/page/revision/contributor/username/text()', page))[1] IS NOT NULL
xmltest-# LIMIT 10;
xpath
-------------
Shizhao
Lorenzarius
Sl
Synthebot
Shizhao
1852
Sl
Jusjih
Shizhao
VolkovBot
(10 rows)
위 쿼리를 조금 설명하자면, [1]은 예상할 수 있듯 배열 첨자 연산자다. C 등과 달리 1이 첫번째 요소이다. 그리고 expr::t는 expr 값을 t 타입으로 캐스팅하는 표현식이다. 따라서, (xpath('/...', page))[1]::text는 xpath() 함수가 반환한 xml[] 타입 배열의 첫 번째 요소를 꺼내, 그것을 text 타입으로 캐스팅한 값이 된다.
대충 감을 잡았으니, 위에서 말한 기여자 랭킹을 뽑아볼까? 뒤에 있을 반전을 위해 괜히 시각 조건도 포함시켰다. 올해 이후만 대상으로 하도록. (xml 타입을 바로 timestamp로 캐스팅할 수는 없어서 text 타입으로 한번 캐스팅한 것을 다시 timestamp로 변환했다.)
SELECT (xpath('/page/revision/contributor/username/text()', page))[1]::text AS contributor,
count(*) AS page_count
FROM wikipedia_pages
WHERE (xpath('/page/revision/contributor/username/text()', page))[1] IS NOT NULL
AND (xpath('/page/revision/timestamp/text()', page))[1]::text::timestamp > '2008-01-01'
GROUP BY contributor
ORDER BY page_count DESC;
위의 쿼리로 뽑을 수 있다. 기분 나쁜 중복이 존재하지만 무시해주자(ㅋㅋㅋ). 저 쿼리 그냥 실행하면 얼마나 걸릴까? 일단 돌려봤다.
xmltest=# SELECT (xpath('/page/revision/contributor/username/text()', page))[1]::text AS contributor,
xmltest=# count(*) AS page_count
xmltest=# FROM wikipedia_pages
xmltest=# WHERE (xpath('/page/revision/contributor/username/text()', page))[1] IS NOT NULL
xmltest=# AND (xpath('/page/revision/timestamp/text()', page))[1]::text::timestamp > '2008-01-01'
xmltest=# GROUP BY contributor
xmltest=# ORDER BY page_count DESC;
contributor | page_count
-------------------------------------------------------+------------
P-bot | 37553
Alexbot | 17627
SieBot | 6781
FdcnBot | 6609
VolkovBot | 6500
Thijs!bot | 4941
TXiKiBoT | 4056
Vina-iwbot | 3817
Escarbot | 3485
JAnDbot | 3351
計算機 | 2992
Alexsh | 2625
YWong | 2240
PipepBot | 2207
Dingar | 2096
Isnow | 1750
Sz-iwbot | 1688
S19991002 | 1526
长夜无风 | 1523
Puppy8800 | 1475
Kolyma | 1425
Ws227 | 1381
Laikayiu | 1337
AlleborgoBot | 1323
Shizhao | 1320
BOTarate | 1249
1j1z2 | 1169
Bigmorr | 1046
Whhalbert | 1003
Sl | 970
Rei-bot | 967
Stewart | 920
Ricky36 | 918
Iokseng | 902
…
(5285 rows)
약 3분 걸렸다. 왜 이리 오래 걸릴까? 어찌보면 당연하다.
xmltest=# EXPLAIN SELECT (xpath('/page/revision/contributor/username/text()', page))[1]::text AS contributor,
xmltest-# count(*) AS page_count
xmltest-# FROM wikipedia_pages
xmltest-# WHERE (xpath('/page/revision/contributor/username/text()', page))[1] IS NOT NULL
xmltest-# AND (xpath('/page/revision/timestamp/text()', page))[1]::text::timestamp > '2008-01-01'
xmltest-# GROUP BY contributor
xmltest-# ORDER BY page_count DESC;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Sort (cost=52347.32..52347.82 rows=199 width=32)
Sort Key: (count(*))
-> HashAggregate (cost=52336.74..52339.72 rows=199 width=32)
-> Seq Scan on wikipedia_pages (cost=0.00..50121.26 rows=443095 width=32)
Filter: ((xpath('/page/revision/contributor/username/text()'::text, page, '{}'::text[]))[1] IS NOT NULL)
(5 rows)
인덱스를 전혀 타지 않기 때문이다. 인덱스가 없는데 어떻게 타;; 그럼 인덱스를 만들어줘야 한다. 우리가 원하는 인덱스는 pages 컬럼 전체가 아니라, 그 컬럼에 들어있는 <contributor>, <timestamp> 노드의 텍스트 값이다. 이렇게 복잡한 것을 인덱스로 어떻게 만들어;;
…라고 생각할 수 있겠지만, PostgreSQL에서는 아무 표현식(expression)이나 다 인덱스로 만들어버릴 수 있다. 이거 정말 무지막지한 기능이라고 생각한다.5 아무리 복잡한 표현식도 다 인덱스할 수 있다. 함수 반환값, 산술식, 특정 배열 요소 등. 물론 해당 연산이 결정적(deterministic)이어야 한다는 것은 당연하다. random() 같은 것을 인덱스로 만들 수는 없다는 뜻이다. 어차피 비결정적인 연산 결과를 인덱스로 사용할 일도 없긴 하지만.
인덱스를 만들어봤다.
CREATE FUNCTION contributor_from_wikipedia_page(page xml)
RETURNS text AS $$
BEGIN
RETURN (xpath('/page/revision/contributor/username/text()', page))[1]::text;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
CREATE FUNCTION timestamp_from_wikipedia_page(page xml)
RETURNS timestamp AS $$
BEGIN
RETURN (xpath('/page/revision/timestamp/text()', page))[1]::text::timestamp;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
CREATE INDEX wikipedia_pages_idx ON wikipedia_pages
((contributor_from_wikipedia_page(page)), (timestamp_from_wikipedia_page(page)));
VACUUM ANALYZE wikipedia_pages;
잘 만들어진다. ㅋㅋㅋㅋ 앞서 말했듯이 인덱스에 사용되는 표현식은 결정적이어야 한다. 하지만 to_timestamp() 등의 내장 함수는 서버의 시간대(time zone) 설정에 영향을 받기 때문에 비결정적이다. 그래서 따로 함수를 만들어서 썼다(좋은 해결책은 아니다). text를 timestamp로 변환해주는 함수만 만들까 하다가, 다른 표현식도 지저분해서 함께 넣어버렸다. 하는 김에 contributor_from_wikipedia_page() 함수도 같이 만들었다.
PostgreSQL의 쿼리 플래너는 테이블의 통계를 이용해서 실행 계획을 짠다. VACUUM ANALYZE 명령으로 통계 정보도 갱신해주었다. 직접 하지 않아도 주기적으로 스스로 통계 정보를 갱신하지만, 지금 당장 효과를 봐야 해서.
인덱스를 잘 타도록 아까 짠 쿼리도 수정했다.
SELECT contributor_from_wikipedia_page(page) AS contributor,
count(*) AS page_count
FROM wikipedia_pages
WHERE contributor_from_wikipedia_page(page) IS NOT NULL
AND contributor_from_wikipedia_page(page) != ''
AND timestamp_from_wikipedia_page(page) > '2008-01-01'
GROUP BY contributor
ORDER BY page_count DESC;
실행 계획이 어떻게 달라졌는지 살펴보자.
xmltest=# EXPLAIN
xmltest-# SELECT contributor_from_wikipedia_page(page) AS contributor,
xmltest-# count(*) AS page_count
xmltest-# FROM wikipedia_pages
xmltest-# WHERE contributor_from_wikipedia_page(page) IS NOT NULL
xmltest-# AND contributor_from_wikipedia_page(page) != ''
xmltest-# AND timestamp_from_wikipedia_page(page) > '2008-01-01'
xmltest-# GROUP BY contributor
xmltest-# ORDER BY page_count DESC;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=284967.38..284968.36 rows=393 width=32)
Sort Key: (count(*))
-> HashAggregate (cost=284847.28..284950.44 rows=393 width=32)
-> Bitmap Heap Scan on wikipedia_pages (cost=10747.17..283753.67 rows=218722 width=32)
Recheck Cond: (timestamp_from_wikipedia_page(page) > '2008-01-01 00:00:00'::timestamp without time zone)
Filter: ((contributor_from_wikipedia_page(page) IS NOT NULL) AND (contributor_from_wikipedia_page(page) <> ''::text))
-> Bitmap Index Scan on wikipedia_pages_idx (cost=0.00..10692.49 rows=228600 width=0)
Index Cond: (timestamp_from_wikipedia_page(page) > '2008-01-01 00:00:00'::timestamp without time zone)
(8 rows)
단순히 필터로 풀어지던 쿼리가 비트맵 인덱스를 타게 바뀌었다. 시퀀스 스캔을 하던 부분도 비트맵 힙 스캔으로 풀린다. 비용이나 로우 수가 미심쩍지만 대충 괜찮은 것 같아서 돌려봤다.
xmltest=# SELECT contributor_from_wikipedia_page(page) AS contributor,
xmltest-# count(*) AS page_count
xmltest-# FROM wikipedia_pages
xmltest-# WHERE contributor_from_wikipedia_page(page) IS NOT NULL
xmltest-# AND contributor_from_wikipedia_page(page) != ''
xmltest-# AND timestamp_from_wikipedia_page(page) > '2008-01-01'
xmltest-# GROUP BY contributor
xmltest-# ORDER BY page_count DESC;
contributor | page_count
-------------------------------------------------------+------------
P-bot | 37553
Alexbot | 17627
SieBot | 6781
FdcnBot | 6609
VolkovBot | 6500
Thijs!bot | 4941
TXiKiBoT | 4056
Vina-iwbot | 3817
Escarbot | 3485
JAnDbot | 3351
計算機 | 2992
Alexsh | 2625
YWong | 2240
PipepBot | 2207
Dingar | 2096
Isnow | 1750
…
(5285 rows)
얼마나 걸렸을까? 헐. 3분. 아까랑 똑같다. 으악. 예제가 좋지 않았던 것 같다. 어차피 정말 느린 부분은 SELECT 할 때 평가되는 contributor_from_wikipedia_page(page) 연산과 GROUP BY로 묶은 count(*) 집계인 것 같다.
포스팅 처음에서 원하던 극적인 반전은 전혀 없어서 아쉽지만;;6 XML 함수를 가지고 재밌게 논 것 자체만으로 의미가 있었다. 사실 좀더 복잡한 조인과 함께 사용하면 좋은 쿼리 튜닝 사례도 나올 수 있을 것 같다. 다음 포스팅에서는 PostgreSQL의 풀 텍스트 검색 기능이나, 특이한 contrib 기능들 따위에 대해 써볼까 한다. 나는 요즘 PostgreSQL에 푹 빠져있다.
덧. 써놓고보니 엄청 허무한 결론. 그래도 재밌겠지?;; 암튼… 실험 대실패!
-
달개미(Lunant), 야간개발팀라는 이름으로 더 많이 알려진 아마추어 골방 개발팀이다. 원래 선린인터넷고등학교 게임 제작 동아리 1기 멤버들 가운데서도 핵심 멤버들이 졸업하고 놀게 없어서 만들었다;; 나와 shinvee 선배, 후배 이흥섭, 동기 소리미르, 이렇게 넷으로 이루어진 팀. ↩
-
내가 작년부터 지금 다니고 있는 KoreanClick에서 산업기능요원으로 일하기 시작했는데, 입사 시점과 대충 겹친다. 회사에서는 Oracle 데이터베이스를 주요 업무에 사용하고 있다. 인터넷 리서치 업체이고, 따라서 로우데이터 용량이 엄청나기 때문에 국내에서 Oracle 혹사시키기로는 순위권에 들지 않을까 싶다. 아무튼, 따라서 자연스럽게 입사 후부터 데이터베이스에 관심이 생기게 되었다. ↩
-
xml은 타입 이름. 참고로 PostgreSQL은 ORDBMS이다. 원하는 타입, 클래스를 얼마든지 추가할 수 있다. ↩ -
그렇다. PostgreSQL은 당연하다는 듯 배열을 지원한다. 게다가
int[],bigint[]에 한해서는 인덱스까지 가능하다! ↩ -
내가 생각하는 PostgreSQL의 장점은 이런 점들이다. 효율을 염두하면서도 정규화된(normalized) 디자인을 지킬 수 있다. 보통 데이터베이스 쪽에서는 정규화를 하되, 효율을 위해 역정규화(denormalization)를 하라고 가르치지만, PostgreSQL에서는 효율을 위해 역정규화하게 되는 일이 크게 준다. ↩
-
이 포스팅은 실험을 다 끝낸 다음 쓴 것이 아니라, 실험하는 과정에 계속 기록하면서 쓴 것이다. ↩
July 3rd, 2008 at 8:55 PM
홍민희의 알림…
PostgreSQL 8.3에서 추가된 XML 함수들 구경하기…
July 3rd, 2008 at 10:37 PM
이거 읽으면서 좀 흥분했어요; RSS나 애텀 피드를 아예 박아버리는 데 쓸 수도 있겠네요.
July 3rd, 2008 at 11:48 PM
Mycroft Project 같이 특정한 XML 파일을 공유하는 웹 사이트에서 쓰기 아주 좋을 것 같습니다. 3분에서 단축되는 모습도 보고싶어요 ㅋㅋ
July 4th, 2008 at 1:03 AM
흥미롭다. ㅋㅋㅋ 나중에 한번 써먹어보자.
July 4th, 2008 at 1:59 AM
[…] 이전 포스팅에서 PostgreSQL 8.3의 xpath() 함수를 사용했었다. 하지만 요즘에는 Prototype, MooTools의 $$, jQuery의 $ 등 덕분에 CSS 셀렉터에 대부분 더 친숙할 것이다. 나도 그렇고. 그래서 CSS 셀렉터를 사용할 수는 없을까 하고 생각했다. […]
July 5th, 2008 at 6:04 PM
제가 다니는 소만사는 아마 MS SQL Server를 혹사시키는 걸로 순위권에 들지도 모르겠네요.. -_-