ltree extension PostgreSQL Unconferecnce #43 (2023-08-07) 久々に ltree を使ってみました

自己紹介 ● ぬこ＠横浜 , @nuko_yokohama ● にゃーん ● 趣味でポスグレをやってる者だ ● japan.kanagawa.machida

ltree extension とは

What’s ltree extension? ● 階層ツリーを模擬した構造のデータ型拡張機能 ● PostgreSQL 標準の contrib モジュール – PostgreSQL 7.3(2002 年 ) からサポートされている ● LOAD は不要。データベースに登録して利用。 postgres@ltree=# CREATE EXTENSION ltree ; CREATE EXTENSION postgres@ltree=# \dx List of installed extensions Name | Version | Schema | Description ---------+---------+------------+------------------------------------------------- ltree | 1.2 | public | data type for hierarchical tree-like structures plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (2 rows) postgres@ltree=#

What’s ltree extension? ● ltree は主な DBaaS でも使用可能 – AWS RDS PostgreSQL – AWS Aurora PostgreSQL 互換 – Azure PostgreSQL – Goole Cloud SQL for PostgreSQL

ltree が提供する機能 ● データ型 – ltree, lquery, ltxtquery ● SQL 関数 ● 演算子 ● インデックス – B-tree, GiST GiST(Generalized Search Tree) は 汎用検索ツリーの意味。 様々なインデックスの構築基盤

ltree 型の形式 ● ラベルに使えるのは英字と数字とアンダースコアとハイフン – ハイフンは PostgreSQL 16 以降 ● 残念ながら日本語（マルチバイト文字）は使えない postgres@ltree=# SELECT 'Mammal.Carnivora.Felidae.Homo'::ltree ; ltree ------------------------------- Mammal.Carnivora.Felidae.Homo (1 row) postgres@ltree=# SELECT ' 哺乳綱 . 食肉目 . ネコ科 . ヒト属 '::ltree ; ERROR: ltree syntax error at character 1 LINE 1: SELECT ' 哺乳綱 . 食肉目 . ネコ科 . ヒト属 '::ltree ; ^ postgres@ltree=#

lquery/ltxtquery 型の形式 ● lquery – ltree に一致する正規表現ぽいパターンを記述 ● ltxtquery – ltree に対する全文検索ぽい条件を記述 PostgreSQL 文書には そう書かれているけど 「全文検索っぽい条件」って いまいちピンとこない・・・

ltree を使ってみる

ltree データの格納 ● INSERT 文 ● COPY 文 $ tail -5 ltree.data 15.21.23.30.9.25 1.20.22.26.2.6.11 2.1.12.19.29.28.3.31.28.28.10 14.5.13.19.25.12.32.9.13.16.12 5.24.24.9.32.26.31 $ $ psql -p 16001 ltree -c "\d ltree_test" Null display is "(null)". Table "public.ltree_test" Column | Type | Collation | Nullable | Default --------+-------+-----------+----------+--------- data | ltree | | | $ psql -p 16001 ltree -c "\copy ltree_test FROM ltree.data" Null display is "(null)". COPY 1006 $ ltree のサンプルデータ は contrib/ltree/ltreetest.sql にも入っています

ltree データの検索 ● ltree の比較パターン – ltree と ltree – ltree と lquery – ltree と ltxtquery ltree[] と ltree/lquery/ltxtquery の パターンもあるけど省略

ltree op ltree 演算子 意味 例 = 等号 SELECT '1.2.3'::ltree = '1.2.3'::ltree →true <> 不等号 SELECT '1.2.3'::ltree <> '1.2.3'::ltree →false <, <=, >, >= 大小比較 SELECT '1.25.20'::ltree > '1.3.10'::ltree →false <@, @> 包含 SELECT '10.5.2'::ltree <@ '1.25.10.5.2'::ltree →true || 連結 SELECT '1.2'::ltree || '3.4'::ltree →’1.2.3.4’

ltree op ltree sample(1) ● area を重複排除する（内部で OREDER BY → 大小比較） – ltree の探索は深さ優先 postgres@ltree=# SELECT DISTINCT area FROM ramen_shops ; area ---------------------------- kanagawa.atusgi kanagawa.kawasaki.kawasaki kanagawa.machida kanagawa.sagamihara.midori kanagawa.sagamihara.minami kanagawa.shonan.fujisawa （略） kanagawa.yokohama.isogo （略） kanagawa.yokohama.totsuka kanagawa.yokohama.tsuzuki tokyo.hachiouji tokyo.minato tokyo.ohta tokyo.shinagawa (20 rows)

ltree op lquery 演算子 意味 例 ~ 一致 SELECT '1.2.3.4'::ltree ~ '1.*.4'::lquery →true SELECT '1.2.3.4'::ltree ~ '*.2.*'::lquery →true SELECT '1.2.3.4'::ltree ~ '1.*{1}.4'::lquery →false ltree ~ lquery の検索が ltree 拡張の代表的な使い方

ltree op lquery sample(1) ● area が横浜市の店舗を検索（継承） postgres@ltree=# SELECT * FROM ramen_shops WHERE area ~ 'kanagawa.yokohama.*'; area | category | name ---------------------------+----------+---------------------- kanagawa.yokohama.nishi | 家系 | 吉◯家 kanagawa.yokohama.isogo | 家系 | 杉◯家 kanagawa.yokohama.kounan | 家系 | 環 X 家 kanagawa.yokohama.minami | 家系 | 田◯家 kanagawa.yokohama.minami | 家系 | 壱◯家 弘明寺店 kanagawa.yokohama.totsuka | 家系 | 町田◯店 東戸塚店 kanagawa.yokohama.tsuzuki | 家系 | 町田◯店 横浜池辺町店 kanagawa.yokohama.tsuzuki | 家系 | 町田◯店 仲町台店 kanagawa.yokohama.naka | 二郎系 | ラーメン◯郎 関内店 kanagawa.yokohama.midori | 二郎系 | ラーメン◯郎 中山店 (10 rows)

ltree op lquery sample(2) ● area が神奈川県の任意の市の南区の店舗を検索 postgres@ltree=# SELECT * FROM ramen_shops WHERE area ~ 'kanagawa.*.minami'; area | category | name ----------------------------+----------+---------------------------------------- kanagawa.yokohama.minami | 家系 | 田◯家 kanagawa.yokohama.minami | 家系 | 壱◯家 弘明寺店 kanagawa.sagamihara.minami | 家系 | 家系ラーメン クッ◯ら kanagawa.sagamihara.minami | 家系 | 横浜家系ラーメン 町田家 小田急相模原店 kanagawa.sagamihara.minami | 二郎系 | ラーメン◯郎 相模大野店 (5 rows) 横浜市にも相模原市にも 「南区」があります

ltree op ltxtquery 演算子 意味 例 @ 一致 SELECT 'japan.kanagawa.machida'::ltree @ 'machida & japan & !tokyo'::ltxtquery; →true SELECT 'japan.tokyo.machida'::ltree @ 'machida & japan & !tokyo'::ltxtquery; →false

ltree op ltxtquery sample(1) ● area の経路に「 midori 」かつ「 kanagawa 」を含む postgres@ltree=# SELECT * FROM ramen_shops WHERE area @ 'midori & kanagawa'; area | category | name ----------------------------+----------+-------------------- kanagawa.sagamihara.midori | 家系 | 町田◯店 城山店 kanagawa.yokohama.midori | 二郎系 | ラーメン◯郎 中山店 (2 rows) ● area の経路に「 machida 」かつ NOT 「 tokyo 」を含む postgres@ltree=# SELECT * FROM ramen_shops WHERE area @ '!tokyo & machida'; area | category | name ------------------+----------+-------------- kanagawa.machida | 家系 | 町田◯店 本店 (1 row) 横浜市にも相模原市にも 「緑区」があります

ltree データの検索 ● ltree 関数 (1) 関数名 内容 例 subltree 部分経路取得 subltree('japan.kanagawa.yokohama.naka',1, 3) → 'kanagawa.yokohama' subpath 部分経路取得 subpath('japan.kanagawa.yokohama.naka',1, 3) → 'kanagawa.yokohama.naka' nlevel ラベル数取得 nlevel('japan.kanagawa.yokohama.naka') → 4

ltree データの検索 ● ltree 関数 (2) 関数名 内容 例 index 出現位置取得 index('japan.kanagawa.yokohama.naka', 'yokohama.naka') → 2 index('japan.kanagawa.yokohama.naka', 'yokohama.aoba') → -1 ※ 見つからない場合 lca 最長接頭辞 lca('japan.kanagawa.yokohama.naka', 'japan.kanagawa.mahida') → japan.kanagawa

ltree データに対するインデックス設定 インデックス種別 データ型 演算子 演算子クラス B-Tree(btree) ltree =, <, <=, >, >= － GiST(gist) ltree =, <, <=, >, >=, @>, <@, @, ~, ? gist_ltree_ops(siglen= 整数 ) GiST(gist) ltree[] <@, @>, @, ~, ? gist__ltree_ops(siglen= 整数 ) siglen の設定による インデックスサイズの変化や 検索時間への影響は今回未調査

GiST インデックスの作成 ● ltree 列にインデックス GiST インデックスを設定 postgres@ltree=# \d test Table "public.test" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- id | integer | | | label | ltree | | | data | text | | | Indexes: "test_label_gist" gist (label) postgres@ltree=# \timing Timing is on. postgres@ltree=# CREATE INDEX test_label_gist ON test USING gist (label gist_ltree_ops); CREATE INDEX Time: 245080.481 ms (04:05.080) postgres@ltree=# レコード数は 1000 万件

参考： B-Tree/GiST の作成時間とサイズ ● ltree 列 1000 万件から B-Tree/GiST インデックスを作成 GiST B-Tree 0 50 100 150 200 250 300 245.08 36.17 インデックス作成時間 作成時間（秒） GiST B-Tree 0 100 200 300 400 500 600 700 800 900 823.6 474.9 インデックスサイズ インデックスサイズ（ MiB ） サイズは 1.7 倍 作成時間は 6.7 倍！

GiST インデックスの効果 ● 1000 万件の ltree データ（ラベル数 =3 ）の検索 – GiST インデックスなし postgres@ltree=# EXPLAIN ANALYZE SELECT * FROM test WHERE label ~ '1.3.*'; QUERY PLAN --------------------------------------------------------------------------------------------- ---------------- Seq Scan on test (cost=0.00..248457.21 rows=1000 width=66) (actual time=0.207..1601.684 rows=1000 loops=1) Filter: (label ~ '1.3.*'::lquery) Rows Removed by Filter: 9999000 Planning Time: 0.092 ms Execution Time: 1602.386 ms (5 rows)

GiST インデックスの効果 – GiST インデックスあり postgres@ltree=# EXPLAIN ANALYZE SELECT * FROM test WHERE label ~ '1.3.*'; QUERY PLAN --------------------------------------------------------------------------------------------- ---------------------------------- Bitmap Heap Scan on test (cost=52.17..3780.29 rows=1000 width=66) (actual time=0.394..2.066 rows=1000 loops=1) Recheck Cond: (label ~ '1.3.*'::lquery) Heap Blocks: exact=993 -> Bitmap Index Scan on test_label_gist (cost=0.00..51.92 rows=1000 width=0) (actual time=0.260..0.261 rows=1000 loops=1) Index Cond: (label ~ '1.3.*'::lquery) Planning Time: 0.093 ms Execution Time: 2.530 ms (7 rows) – 1602ms → 2.5ms に改善 こうかは ばつぐんだ！

ltree の用途

ltree の用途 ● 階層的に表現できる集合 – 住所 – 図書分類（日本十進分類法等）、生物分類など ├japan │ ├tokyo │ │ 　 　 ├ minato │ │ 　 　 ├ musashino │ │ 　 　 ├ machida │ ├kanagawa │ │ 　 　 ├ yokohama │ │ │ ├naka │ │ │ │ ├kotobuki │ │ │ ├midori │ │ ├machida ├400 自然科学 │ 　 　 ├ 410 数学 │ 　 　 ├ 420 物理学 │ 　 　 ├ 430 化学 │ 　 　 ├ 440 天文学 │ 　 　 ├ 450 地球科学 │ 　 　 └ 460 生物科学 │ 　 　 　 　 　 ├ 470 植物学 │ 　 　 　 　 　 └ 480 動物学 ├490 医学 Wikipedia 「日本十進分類法」から 引用しています。

ltree の用途 ● GiST インデックスの設定が前提になると 更新が頻繁に発生するテーブルには使うのは考えもの – 前述のとおり GiST インデックス更新オーバヘッドは大 – ほとんど更新のない、マスタデータに使うほうが良さげ

PostgreSQL 16 での改善点

ラベルにハイフンを許容 ● PostgreSQL 15 postgres@ltree=# SELECT 'japan.kanagawa.yokohama.naka-ku'::ltree; ERROR: ltree syntax error at character 29 LINE 1: SELECT 'japan.kanagawa.yokohama.naka-ku'::ltree; ^ postgres@ltree=# SELECT 'japan.kanagawa.yokohama.naka-ku'::ltree; ltree --------------------------------- japan.kanagawa.yokohama.naka-ku (1 row) ● PostgreSQL 16 beta1

ラベル最大長（ 256→1000 ） ● PostgreSQL 15 postgres@ltree=# SELECT '0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789AB CDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF012345678 9ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF'::ltree; ERROR: label string is too long LINE 1: SELECT '0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123... ^ DETAIL: Label length is 256, must be at most 255, at character 257. postgres@ltree=# SELECT '0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789AB CDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF012345678 9ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF'::ltree; （略） 0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABC DEF0123456789ABCDEF0123456789AB CDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF012345678 9ABCDEF0123456789ABCDEF012345678 9ABCDEF (1 row) ● PostgreSQL 16 beta1

まとめ

ltree まとめ ● PostgreSQL 標準の拡張機能（ PostgreSQL 7.3 ～） ● 主要 DBaaS でも利用可能 ● データ型、演算子、 SQL 関数、インデックスを提供 ● 包含関係検索等の高度な検索機能 ● B-Tree/GiST インデックス対応 ● 階層構造管理用 ● PostgreSQL 16 でも機能改善あり 階層構造を意識した 検索を行うときに 便利そうな拡張機能

おしまい