Upgrade to Pro — share decks privately, control downloads, hide ads and more …

ltree extension

ltree extension

A brief introduction to ltree extensions.

nuko_yokohama

August 07, 2023
Tweet

More Decks by nuko_yokohama

Other Decks in Technology

Transcript

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

    View full-size slide

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

    View full-size slide

  3. ltree extension とは

    View full-size slide

  4. 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=#

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  7. 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=#

    View full-size slide

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

    View full-size slide

  9. ltree を使ってみる

    View full-size slide

  10. 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
    にも入っています

    View full-size slide

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

    View full-size slide

  12. 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’

    View full-size slide

  13. 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)

    View full-size slide

  14. 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 拡張の代表的な使い方

    View full-size slide

  15. 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)

    View full-size slide

  16. 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)
    横浜市にも相模原市にも
    「南区」があります

    View full-size slide

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

    View full-size slide

  18. 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)
    横浜市にも相模原市にも
    「緑区」があります

    View full-size slide

  19. 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

    View full-size slide

  20. 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

    View full-size slide

  21. ltree データに対するインデックス設定
    インデックス種別 データ型 演算子 演算子クラス
    B-Tree(btree) ltree =,
    <, <=, >, >=

    GiST(gist) ltree =,
    <, <=, >, >=,
    @>, <@, @,
    ~, ?
    gist_ltree_ops(siglen= 整数 )
    GiST(gist) ltree[] <@, @>, @,
    ~, ?
    gist__ltree_ops(siglen= 整数 )
    siglen の設定による
    インデックスサイズの変化や
    検索時間への影響は今回未調査

    View full-size slide

  22. 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 万件

    View full-size slide

  23. 参考: 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 倍!

    View full-size slide

  24. 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)

    View full-size slide

  25. 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 に改善
    こうかは
    ばつぐんだ!

    View full-size slide

  26. ltree の用途

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  29. PostgreSQL 16 での改善点

    View full-size slide

  30. ラベルにハイフンを許容
    ● 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

    View full-size slide

  31. ラベル最大長( 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

    View full-size slide

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

    View full-size slide

  33. おしまい

    View full-size slide