PostgreSQL의 pg_tre 및 pg_re2 확장을 pg_trgm과 비교하며 정규 표현식 검색 성능과 퍼지 매칭 기능을 살펴봅니다.
사실대로 말하자면 이것들이 아주 새로운 것은 아닙니다(몇 달쯤 되었죠). 하지만 이제야 이를 조사할 시간이 났습니다.
그럼 무엇이 어떤지 살펴보겠습니다. 우선 테스트 데이터가 필요합니다. 다행히 explain.depesz.com DB가 있습니다…
모든 플랜을 다음 구조의 별도 테이블로 추출했습니다:
=$ \d all_plans Table "public.all_plans" Column | Type | Collation | Nullable | Default --------+------+-----------+----------+--------- id | text | | not null | plan | text | | | Indexes: "all_plans_pkey" PRIMARY KEY, btree (id)
총 160만 행이 있으며, 평균 길이는 22kB, 최대 길이(플랜)는 약 9.5MB이고, 모든 플랜의 총길이는 약 33GB입니다.
첫 번째 테스트 사례는 단어 “sususu”입니다. 이 문자열은 흔하지 않아서 선택했습니다(포함하는 플랜은 93개뿐입니다).
아무 마법도 쓰지 않은 원래 Pg:
=$ explain (analyze on, buffers on, costs off) select count(*) from all_plans where plan ~ 'sususu'; QUERY PLAN ─────────────────────────────────────────────────────────────────────────────────────────────────────── Finalize Aggregate (actual time=41431.824..41442.352 rows=1.00 loops=1) Buffers: shared hit=2007647 read=941087 -> Gather (actual time=41367.015..41442.322 rows=3.00 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=2007647 read=941087 -> Partial Aggregate (actual time=41391.421..41391.422 rows=1.00 loops=3) Buffers: shared hit=2007647 read=941087 -> Parallel Seq Scan on all_plans (actual time=1591.041..41391.268 rows=31.00 loops=3) Filter: (plan ~ 'sususu'::text) Rows Removed by Filter: 541276 Buffers: shared hit=2007647 read=941087 Planning Time: 0.305 ms Execution Time: 41442.639 ms (14 rows)
좋습니다. 약 40초가 걸렸고, 3방향 병렬 시퀀셜 스캔이었습니다.
좋네요. 다행히 pg_trgm을 사용해 최적화할 수 있다는 것을 알고 있습니다:
1667 MB (1 row)
시간이 좀 걸렸습니다….
하지만 이제는:
Aggregate (actual time=1615.599..1615.600 rows=1.00 loops=1) Buffers: shared hit=6038 read=29131 -> Bitmap Heap Scan on all_plans (actual time=38.302..1615.518 rows=93.00 loops=1) Recheck Cond: (plan ~ '(su){3}'::text) Rows Removed by Index Recheck: 2879 Heap Blocks: exact=2884 Buffers: shared hit=6038 read=29131 -> Bitmap Index Scan on trgm_idx (actual time=1.829..1.830 rows=2972.00 loops=1) Index Cond: (plan ~ '(su){3}'::text) Index Searches: 1 Buffers: shared hit=9 read=21 Planning: Buffers: shared read=1 Planning Time: 0.365 ms Execution Time: 1615.620 ms (15 rows)
좋습니다. 1.6초가 걸렸고, 인덱스를 사용했습니다.
이제 _새로운 것들_을 살펴보겠습니다.
첫 번째는 pg_tre이며, 최초의(적어도 제게는) 발표는 여기에 있습니다.
설치는 꽤 간단합니다:
=$ git clone --recurse-submodules https://codeberg.org/gregburd/pg_tre.git =$ cd pg_tre =$ make =$ sudo make install
그런 다음 DB에서:
=$ CREATE EXTENSION pg_tre; CREATE EXTENSION =$ CREATE INDEX plan_tre ON all_plans_tre USING tre (plan); NOTICE: pg_tre: collected 1701834577 trigram entries from 1623920 heap tuples NOTICE: pg_tre: built 755269 posting trees NOTICE: pg_tre: built range tier with 1407 ranges across 47 pages NOTICE: pg_tre: build complete, indexed 1623920 heap tuples into 755269 trigrams CREATE INDEX Time: 26127621.037 ms (07:15:27.621)
맙소사. 7시간입니다. 크기는 어떨까요?
=$ select pg_size_pretty( pg_relation_size('plan_tre'::regclass)); pg_size_pretty ──────────────── 21 GB (1 row)
테이블 이름을 바꾼 것을 눈치챘을지도 모르겠습니다. _all_plans_와 동일하지만, 모든 접근법을 동시에 시험할 수 있도록 복사본을 만들었습니다.
그럼 새 인덱스가 빠른지 보겠습니다…
=$ explain (analyze on, buffers on, costs off) SELECT * FROM all_plans_tre WHERE plan %~~ tre_pattern('su{3}', 0); QUERY PLAN ──────────────────────────────────────────────────────────────────────────────────── Bitmap Heap Scan on all_plans_tre (actual time=2.182..2292.506 rows=85.00 loops=1) Recheck Cond: (plan %~~ 'su{3}'::tre_pattern) Rows Removed by Index Recheck: 357 Heap Blocks: exact=431 Buffers: shared hit=5967 -> Bitmap Index Scan on plan_tre (actual time=0.132..0.132 rows=442.00 loops=1) Index Cond: (plan %~~ 'su{3}'::tre_pattern) Index Searches: 0 Buffers: shared hit=7 Planning: Buffers: shared hit=14 Planning Time: 0.135 ms Execution Time: 2292.584 ms (13 rows)
시퀀셜 스캔보다는 빠르지만 트라이그램만큼 빠르지는 않습니다. 하지만 문서에는 이렇게 나와 있습니다:
pg_tre가 답이 아닌 경우
정확한 부분 문자열 / LIKE: pg_trgm은 충분히 검증되었으며 모든 PG 설치에 포함됩니다. 이를 사용하세요.
제 검색은 분명 매우 단순했습니다. 그러니 좀 더 “재미있는” 것을 시도해 보겠습니다:
=$ explain (analyze, buffers, costs off) select * from all_plans where plan ~ '(?<=e.)aa[bc]c[b-d]'; QUERY PLAN ───────────────────────────────────────────────────────────────────────────────────── Bitmap Heap Scan on all_plans (actual time=124.424..39356.809 rows=40.00 loops=1) Recheck Cond: (plan ~ '(?<=e.)aa[bc]c[b-d]'::text) Rows Removed by Index Recheck: 5137 Heap Blocks: exact=5005 Buffers: shared hit=19212 read=103648 -> Bitmap Index Scan on trgm_idx (actual time=7.965..7.966 rows=5177.00 loops=1) Index Cond: (plan ~ '(?<=e.)aa[bc]c[b-d]'::text) Index Searches: 1 Buffers: shared hit=177 Planning: Buffers: shared hit=1 Planning Time: 1.789 ms Execution Time: 39356.895 ms (13 rows)
하지만 이 정규 표현식은 tre에서 사용할 수 없습니다:
=$ explain (analyze, buffers, costs off) select * from all_plans_tre where plan %~~ tre_pattern('(?<=e.)aa[bc]c[b-d]'); ERROR: pg_tre: invalid regex pattern:
흥미롭게도 메시지가 _:_로 끝나지만 그 뒤에는 아무것도 없어서, 메시지에 오류가 있는 듯합니다.
그렇다면 어디에 유용할까요?
내장 퍼지 매칭을 지원하는 듯합니다.
예를 들면:
=$ select word from word_stats where word %~~ tre_pattern('postgresql', 1); word ──────────────────────────────────── autopostgresqlbackup metadatapostgresqlserver rpostgresql inpostgresql postgresl postgresml postgresql Postgresql PostgresqlDatabaseServices postgresqlmatches postgrestls postgresxl usrpostgresplanif InternalPostgresqlDatabaseServices libpostgresql VRTSpostgresql (16 rows)
끝의 숫자는 Levenshtein 거리와 관련이 있습니다. 따라서 꽤 멋진 일을 할 수 있습니다. 단어 목록에 이를 사용하고, 그다음 일반 트라이그램으로 추출하면 아주 좋을 것 같습니다:
=$ EXPLAIN ( analyze, buffers, costs off) WITH re AS ( SELECT string_agg( word, '|' ) AS ex FROM word_stats WHERE word %~~ tre_pattern( 'postgresql', 1 ) ) SELECT * FROM all_plans WHERE plan ~ ( SELECT re.ex FROM re ); QUERY PLAN ──────────────────────────────────────────────────────────────────────────────────────────────────────────── Bitmap Heap Scan on all_plans (actual time=684.096..9993.531 rows=846.00 loops=1) Recheck Cond: (plan ~ (InitPlan 1).col1) Rows Removed by Index Recheck: 12951 Heap Blocks: exact=12946 Buffers: shared hit=28794 read=94765 InitPlan 1 -> Finalize Aggregate (actual time=638.129..638.264 rows=1.00 loops=1) Buffers: shared read=14464 -> Gather (actual time=629.474..638.250 rows=3.00 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared read=14464 -> Partial Aggregate (actual time=619.772..619.773 rows=1.00 loops=3) Buffers: shared read=14464 -> Parallel Seq Scan on word_stats (actual time=309.658..619.756 rows=5.33 loops=3) Filter: (word %~~ 'postgresql@1'::tre_pattern) Rows Removed by Filter: 800091 Buffers: shared read=14464 -> Bitmap Index Scan on trgm_idx (actual time=680.737..680.737 rows=13797.00 loops=1) Index Cond: (plan ~ (InitPlan 1).col1) Index Searches: 1 Buffers: shared hit=693 read=15219 Planning: Buffers: shared hit=1 Planning Time: 2.671 ms Execution Time: 9994.121 ms (26 rows)
여기서는 단어 목록을 검색해 _postgresql_과 어느 정도 비슷한 단어를 찾고, 그중 무엇이든 매칭하는 정규 표현식을 만들었습니다(안전한 방식은 아니지만, 단지 테스트입니다). 그런 뒤 검색에 트라이그램 인덱스를 사용했습니다. 나쁘지 않습니다.
어쨌든 일부 용도는 있겠지만, 현재는 더 많은 작업이 필요한 몇몇 거친 부분이 있다고 생각합니다. 그래도 분명 흥미로워 보입니다…
그럼 pg_re2를 살펴보겠습니다.
설치는 아주 간단합니다:
=$ sudo apt-get install libre2-dev =$ sudo pgxnclient install re2
그러면 다음을 할 수 있습니다:
=$ create extension re2; CREATE EXTENSION
좋습니다. 이제 시험해 볼 수 있습니다:
=$ explain (analyze on, buffers on, costs off) SELECT * FROM all_plans_re2 WHERE plan @~ '(su){3}'; QUERY PLAN ─────────────────────────────────────────────────────────────────────────────────────────────── Gather (actual time=247.527..23113.592 rows=93.00 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=2007641 read=941087 -> Parallel Seq Scan on all_plans_re2 (actual time=1067.656..23075.232 rows=31.00 loops=3) Filter: (plan @~ '(su){3}'::text) Rows Removed by Filter: 541276 Buffers: shared hit=2007641 read=941087 Planning Time: 0.290 ms Execution Time: 23113.893 ms (10 rows)
뭐라고요?! 23초라고요? 인덱스 없이 스캔하고, 내장 Pg의 일반 ~ 연산자를 사용하면 40초가 조금 넘게 걸렸습니다?! 몇 번 시험해 보았고 결과를 확인했습니다.
그럼 사용자 지정 re2 인덱스를 추가해 보겠습니다:
=$ CREATE INDEX re2idx ON all_plans_re2 USING gin (plan gin_re2_ops); CREATE INDEX Time: 898682.472 ms (14:58.682) =$ select pg_size_pretty( pg_relation_size('re2idx'::regclass)); pg_size_pretty ──────────────── 2927 MB (1 row)
즉, 인덱스 생성은 트라이그램 인덱스보다 짧은 시간이 걸렸고 크기는 트라이그램 인덱스의 거의 2배입니다. 그렇다면 동작은 어떨까요?
=$ explain (analyze on, buffers on, costs off) SELECT * FROM all_plans_re2 WHERE plan @~ '(su){3}'; QUERY PLAN ───────────────────────────────────────────────────────────────────────────────────── Bitmap Heap Scan on all_plans_re2 (actual time=21.121..958.796 rows=93.00 loops=1) Recheck Cond: (plan @~ '(su){3}'::text) Rows Removed by Index Recheck: 2879 Heap Blocks: exact=2884 Buffers: shared hit=6033 read=29142 -> Bitmap Index Scan on re2idx (actual time=19.322..19.323 rows=2972.00 loops=1) Index Cond: (plan @~ '(su){3}'::text) Index Searches: 1 Buffers: shared hit=10 read=21 Planning: Buffers: shared read=1 Planning Time: 0.368 ms Execution Time: 958.857 ms (13 rows)
아주 멋집니다. 트라이그램보다 확실히 빠릅니다. 더 복잡한 정규 표현식은 어떨까요?
=$ explain (analyze on, buffers on, costs off) SELECT * FROM all_plans_re2 WHERE plan @~ '(^|^.|[^e].)aa[bc]c[b-d]'; QUERY PLAN ────────────────────────────────────────────────────────────────────────────────────── Bitmap Heap Scan on all_plans_re2 (actual time=24.061..2267.620 rows=376.00 loops=1) Recheck Cond: (plan @~ '(^|^.|[^e].)aa[bc]c[b-d]'::text) Rows Removed by Index Recheck: 4801 Heap Blocks: exact=5005 Buffers: shared hit=11775 read=111084 -> Bitmap Index Scan on re2idx (actual time=11.902..11.903 rows=5177.00 loops=1) Index Cond: (plan @~ '(^|^.|[^e].)aa[bc]c[b-d]'::text) Index Searches: 1 Buffers: shared hit=72 read=104 Planning: Buffers: shared read=1 Planning Time: 0.449 ms Execution Time: 2267.799 ms (13 rows)
이 정규 표현식은 기능적으로 _plan ~ ‘(?<=e.)aa[bc]c[b-d]'_와 같아야 하며, 실제로 같은 수의 행을 반환합니다. 시간은 약 50%입니다!
왜 _(?<=…_를 사용할 수 없을까요? re2 라이브러리의 제한 사항입니다. 빠르게 만들기 위해 특정 기능들이 제거되었습니다.
종합하면 PostgreSQL의 정규 표현식과 관련해 흥미로운 작업들이 있습니다. 그중 일부는 이미 훌륭한 결과를 제공합니다. 일부는 밝은 미래에 대한 약속에 더 가깝습니다. 하지만 여전히 흥미로워 보입니다.
