拆分字元串的索引。PostgreSQL 10

我在數據庫中有以下字元串：

• A/B/C/
• A/B/D/
• E/F/C/

這是uri列。

表格如下：

create table meta_info (
   id bigserial primary key,
   uri varchar,
   created timestamp with time zone,
   version varchar,
   binary_length bigserial
   )

在大多數情況下，我會找到目錄並想找到孩子。

例如，

SELECT tt.stem[2+1] as current, tt.stem[2+2]
as child from (SELECT regexp_split_to_array(uri, '/')
                     as stem FROM meta_info
                     where uri like 'A/B%' )
as tt group by tt.stem[2+1], tt.stem[2+2]

… ，其中“2”是要查找的路徑的大小（A/B% - 大小 2，A/B/C% - 大小 3，A/B/D/E% - 大小 4）

例如，

SELECT tt.stem[4+1] as current, tt.stem[4+2]
as child from (SELECT regexp_split_to_array(uri, '/')
                     as stem FROM meta_info
                     where uri like 'A/B/C/D%' )
as tt group by tt.stem[4+1], tt.stem[4+2]

目前我有 400000 行。執行該 sql 查詢需要太多時間。我可以創建索引來提高性能嗎？

以下索引沒有幫助。

CREATE INDEX idx ON meta_info (regexp_split_to_array(uri, '/'));

查詢仍然很慢。尤其是下面的sql很慢：

EXPLAIN ANALYZE
   SELECT DISTINCT((regexp_split_to_array(uri, '/'))[1]) as branch
   FROM meta_info

當我需要獲得唯一的第一級（文件夾）時，我需要執行提到的 sql。

………………. text_pattern_ops 怎麼樣，是的，我知道。我已經在使用它了。但是感謝 split_part(uri, ‘/’,1)。哇，它的工作速度真的更快。我已經用 split_part 替換了所有 regexp_split_to_array。非常感謝！

如果您將不斷地動態解析數據，那麼您可能應該首先將其儲存為已解析的數據。

如果您確實想將其儲存為扁平字元串，則可以採取一些措施來加快速度。

其中 uri 像 ‘A/B/C/D%’

這部分可以加快創建索引

create index on meta_info (uri text_pattern_ops);

這能提高多少取決於“A/B/C/D%”的選擇性。

解釋分析 SELECT DISTINCT((regexp_split_to_array(uri, ‘/’))

$$ 1 $$) 作為來自 meta_info 的分支

如果避免不必要的正則表達式機制和數組機制，這可以加快 2 到 3 倍。

EXPLAIN (ANALYZE, BUFFERS)
SELECT DISTINCT split_part(uri, '/',1) as branch
FROM meta_info;

引用自：https://dba.stackexchange.com/questions/217940