在 PostgreSQL 中使用 ltree 处理层次结构数据

PostgreSQLChina

关注

发布于: 2021 年 03 月 15 日

作者：阿列克谢·瓦西里耶夫（Alexey Vasiliev）

译者：类延良，任职于瀚高基础软件股份有限公司，PostgreSQL 数据库技术爱好者，PostgreSQL ACE、PGCM、10g &11g OCM，OGG 认证专家。

原文地址：https://leopard.in.ua/2013/09/02/postgresql-ltree#.YEhtc2gzaUk

在本文中，我们将学习如何使用 PostgreSQL 的 ltree 模块，该模块允许以分层的树状结构存储数据。

什么是 ltree？

Ltree 是 PostgreSQL 模块。它实现了一种数据类型 ltree，用于表示存储在分层树状结构中的数据的标签。提供了用于搜索标签树的广泛工具。

为什么选择 ltree？

ltree 实现了一个物化路径，对于 INSERT / UPDATE / DELETE 来说非常快，而对于 SELECT 操作则较快
通常，它比使用经常需要重新计算分支的递归 CTE 或递归函数要快
如内置的查询语法和专门用于查询和导航树的运算符
索引！！！

初始数据

首先，您应该在数据库中启用扩展。您可以通过以下命令执行此操作：

CREATE EXTENSION ltree;

复制代码

让我们创建表并向其中添加一些数据：

CREATE TABLE comments (user_id integer, description text, path ltree);
INSERT INTO comments (user_id, description, path) VALUES ( 1, md5(random()::text), '0001');
INSERT INTO comments (user_id, description, path) VALUES ( 2, md5(random()::text), '0001.0001.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 2, md5(random()::text), '0001.0001.0001.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 1, md5(random()::text), '0001.0001.0001.0002');
INSERT INTO comments (user_id, description, path) VALUES ( 5, md5(random()::text), '0001.0001.0001.0003');
INSERT INTO comments (user_id, description, path) VALUES ( 6, md5(random()::text), '0001.0002');
INSERT INTO comments (user_id, description, path) VALUES ( 6, md5(random()::text), '0001.0002.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 6, md5(random()::text), '0001.0003');
INSERT INTO comments (user_id, description, path) VALUES ( 8, md5(random()::text), '0001.0003.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 9, md5(random()::text), '0001.0003.0002');
INSERT INTO comments (user_id, description, path) VALUES ( 11, md5(random()::text), '0001.0003.0002.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 2, md5(random()::text), '0001.0003.0002.0002');
INSERT INTO comments (user_id, description, path) VALUES ( 5, md5(random()::text), '0001.0003.0002.0003');
INSERT INTO comments (user_id, description, path) VALUES ( 7, md5(random()::text), '0001.0003.0002.0002.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 20, md5(random()::text), '0001.0003.0002.0002.0002');
INSERT INTO comments (user_id, description, path) VALUES ( 31, md5(random()::text), '0001.0003.0002.0002.0003');
INSERT INTO comments (user_id, description, path) VALUES ( 22, md5(random()::text), '0001.0003.0002.0002.0004');
INSERT INTO comments (user_id, description, path) VALUES ( 34, md5(random()::text), '0001.0003.0002.0002.0005');
INSERT INTO comments (user_id, description, path) VALUES ( 22, md5(random()::text), '0001.0003.0002.0002.0006');

复制代码

另外，我们应该添加一些索引：

CREATE INDEX path_gist_comments_idx ON comments USING GIST(path);CREATE INDEX path_comments_idx ON comments USING btree(path);

复制代码

正如您看到的那样，我建立 comments 表时带有 path 字段，该字段包含该表的 tree 全部路径。如您所见，对于树分隔符，我使用 4 个数字和点。

让我们在 commenets 表中找到 path 以‘0001.0003’的记录：

$ SELECT user_id, path FROM comments WHERE path <@ '0001.0003';
 user_id |           path
---------+--------------------------
       6 | 0001.0003
       8 | 0001.0003.0001
       9 | 0001.0003.0002
      11 | 0001.0003.0002.0001
       2 | 0001.0003.0002.0002
       5 | 0001.0003.0002.0003
       7 | 0001.0003.0002.0002.0001
      20 | 0001.0003.0002.0002.0002
      31 | 0001.0003.0002.0002.0003
      22 | 0001.0003.0002.0002.0004
      34 | 0001.0003.0002.0002.0005
      22 | 0001.0003.0002.0002.0006
(12 rows)

复制代码

让我们通过 EXPLAIN 命令检查这个 SQL：

$ EXPLAIN ANALYZE SELECT user_id, path FROM comments WHERE path <@ '0001.0003';
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Seq Scan on comments  (cost=0.00..1.24 rows=2 width=38) (actual time=0.013..0.017 rows=12 loops=1)
   Filter: (path <@ '0001.0003'::ltree)
   Rows Removed by Filter: 7
 Total runtime: 0.038 ms
(4 rows)

复制代码

让我们禁用 seq scan 进行测试：

$ SET enable_seqscan=false;
SET
$ EXPLAIN ANALYZE SELECT user_id, path FROM comments WHERE path <@ '0001.0003';
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Index Scan using path_gist_comments_idx on comments  (cost=0.00..8.29 rows=2 width=38) (actual time=0.023..0.034 rows=12 loops=1)
   Index Cond: (path <@ '0001.0003'::ltree)
 Total runtime: 0.076 ms
(3 rows)

复制代码

现在 SQL 慢了，但是能看到 SQL 是怎么使用 index 的。

第一个 SQL 语句使用了 sequence scan，因为在表中没有太多的数据。

我们可以将 select “path <@ ‘0001.0003’” 换种实现方法：

$ SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*';
user_id |           path
---------+--------------------------
       6 | 0001.0003
       8 | 0001.0003.0001
       9 | 0001.0003.0002
      11 | 0001.0003.0002.0001
       2 | 0001.0003.0002.0002
       5 | 0001.0003.0002.0003
       7 | 0001.0003.0002.0002.0001
      20 | 0001.0003.0002.0002.0002
      31 | 0001.0003.0002.0002.0003
      22 | 0001.0003.0002.0002.0004
      34 | 0001.0003.0002.0002.0005
      22 | 0001.0003.0002.0002.0006
(12 rows)

复制代码

你不应该忘记数据的顺序，如下的例子：

$ INSERT INTO comments (user_id, description, path) VALUES ( 9, md5(random()::text), '0001.0003.0001.0001');
$ INSERT INTO comments (user_id, description, path) VALUES ( 9, md5(random()::text), '0001.0003.0001.0002');
$ INSERT INTO comments (user_id, description, path) VALUES ( 9, md5(random()::text), '0001.0003.0001.0003');
$ SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*';
user_id |           path
---------+--------------------------
       6 | 0001.0003
       8 | 0001.0003.0001
       9 | 0001.0003.0002
      11 | 0001.0003.0002.0001
       2 | 0001.0003.0002.0002
       5 | 0001.0003.0002.0003
       7 | 0001.0003.0002.0002.0001
      20 | 0001.0003.0002.0002.0002
      31 | 0001.0003.0002.0002.0003
      22 | 0001.0003.0002.0002.0004
      34 | 0001.0003.0002.0002.0005
      22 | 0001.0003.0002.0002.0006
       9 | 0001.0003.0001.0001
       9 | 0001.0003.0001.0002
       9 | 0001.0003.0001.0003
(15 rows)

复制代码

现在进行排序：

$ SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*' ORDER by path;
 user_id |           path
---------+--------------------------
       6 | 0001.0003
       8 | 0001.0003.0001
       9 | 0001.0003.0001.0001
       9 | 0001.0003.0001.0002
       9 | 0001.0003.0001.0003
       9 | 0001.0003.0002
      11 | 0001.0003.0002.0001
       2 | 0001.0003.0002.0002
       7 | 0001.0003.0002.0002.0001
      20 | 0001.0003.0002.0002.0002
      31 | 0001.0003.0002.0002.0003
      22 | 0001.0003.0002.0002.0004
      34 | 0001.0003.0002.0002.0005
      22 | 0001.0003.0002.0002.0006
       5 | 0001.0003.0002.0003
(15 rows)

复制代码

可以在 lquery 的非星号标签的末尾添加几个修饰符，以使其比完全匹配更匹配：

“ @”-不区分大小写匹配，例如 a @匹配 A

“ *”-匹配任何带有该前缀的标签，例如 foo *匹配 foobar

“％”-匹配以下划线开头的单词

$ SELECT user_id, path FROM comments WHERE path ~ '0001.*{1,2}.0001|0002.*' ORDER by path;
 user_id |           path
---------+--------------------------
       2 | 0001.0001.0001
       2 | 0001.0001.0001.0001
       1 | 0001.0001.0001.0002
       5 | 0001.0001.0001.0003
       6 | 0001.0002.0001
       8 | 0001.0003.0001
       9 | 0001.0003.0001.0001
       9 | 0001.0003.0001.0002
       9 | 0001.0003.0001.0003
       9 | 0001.0003.0002
      11 | 0001.0003.0002.0001
       2 | 0001.0003.0002.0002
       7 | 0001.0003.0002.0002.0001
      20 | 0001.0003.0002.0002.0002
      31 | 0001.0003.0002.0002.0003
      22 | 0001.0003.0002.0002.0004
      34 | 0001.0003.0002.0002.0005
      22 | 0001.0003.0002.0002.0006
       5 | 0001.0003.0002.0003
(19 rows)

复制代码

我们来为 parent ‘0001.0003’找到所有直接的 childrens，见下：

$ SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*{1}' ORDER by path;
 user_id |      path
---------+----------------
       8 | 0001.0003.0001
       9 | 0001.0003.0002
(2 rows)

复制代码

为 parent ‘0001.0003’找到所有的 childrens，见下：

$ SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*' ORDER by path;
 user_id |           path
---------+--------------------------
       6 | 0001.0003
       8 | 0001.0003.0001
       9 | 0001.0003.0001.0001
       9 | 0001.0003.0001.0002
       9 | 0001.0003.0001.0003
       9 | 0001.0003.0002
      11 | 0001.0003.0002.0001
       2 | 0001.0003.0002.0002
       7 | 0001.0003.0002.0002.0001
      20 | 0001.0003.0002.0002.0002
      31 | 0001.0003.0002.0002.0003
      22 | 0001.0003.0002.0002.0004
      34 | 0001.0003.0002.0002.0005
      22 | 0001.0003.0002.0002.0006
       5 | 0001.0003.0002.0003
(15 rows)

复制代码

为 children ‘0001.0003.0002.0002.0005’找到 parent：

$ SELECT user_id, path FROM comments WHERE path = subpath('0001.0003.0002.0002.0005', 0, -1) ORDER by path;
 user_id |        path
---------+---------------------
       2 | 0001.0003.0002.0002
(1 row)

复制代码

如果你的路径不是唯一的，你会得到多条记录。

概述

可以看出，使用 ltree 的物化路径非常简单。在本文中，我没有列出 ltree 的所有可能用法。它不被视为全文搜索问题 ltxtquery。但是您可以在 PostgreSQL 官方文档(http://www.postgresql.org/docs/current/static/ltree.html)中找到它。

了解更多 PostgreSQL 热点资讯、新闻动态、精彩活动，请访问中国 PostgreSQL 官方网站：www.postgresqlchina.com

解决更多 PostgreSQL 相关知识、技术、工作问题，请访问中国 PostgreSQL 官方问答社区：www.pgfans.cn

下载更多 PostgreSQL 相关资料、工具、插件问题，请访问中国 PostgreSQL 官方下载网站：www.postgreshub.cn

发布于: 2021 年 03 月 15 日阅读数: 11

原文链接:【http://xie.infoq.cn/article/d2746eacfe92ba43820ce719e】。

PostgreSQLChina

关注

开源是一种商业模式适合于中国 2020.10.31 加入

官方公众号：开源软件联盟PostgreSQL分会官方网站：postgresqlchina.com 官方交流社区：pgfans.cn 官方资源社区：postgreshub.cn

发布

暂无评论

创作场景

在 PostgreSQL 中使用 ltree 处理层次结构数据

什么是 ltree？

为什么选择 ltree？

初始数据

概述

PostgreSQLChina

评论