在PostgreSQL中索引字符串数组列类型

Question

是否可以在类型为文本数组的列上创建索引。尝试使用GIN索引，但查询似乎没有使用这些索引。

-- Example:
CREATE TABLE users (
   name VARCHAR(100),
   groups TEXT[],
);

-- Query: 
SELECT name FROM users WHERE ANY(groups) = 'Engineering';

还有，最好的方法是如何高效地对groups列执行GROUP BY操作以便可以获得groups和计数。

- Anoop

---

什么阻止了你的正常化？ - Konrad Garus

实际表格有多个多值列。数据库相对规范化，除了这些列，我想避免过多的连接，因为它会减慢性能。我使用的“用户”表只是一个例子。 - Anoop

Answer 1

可以使用Gin索引：

CREATE TABLE users (
 name VARCHAR(100),
 groups text[]
);

CREATE INDEX idx_users ON users USING GIN(groups);

-- disable sequential scan in this test:
SET enable_seqscan TO off;

EXPLAIN ANALYZE
SELECT name FROM users WHERE  groups @> (ARRAY['Engineering']);

结果：

"Bitmap Heap Scan on users  (cost=4.26..8.27 rows=1 width=218) (actual time=0.021..0.021 rows=0 loops=1)"
"  Recheck Cond: (groups @> '{Engineering}'::text[])"
"  ->  Bitmap Index Scan on idx_users  (cost=0.00..4.26 rows=1 width=0) (actual time=0.016..0.016 rows=0 loops=1)"
"        Index Cond: (groups @> '{Engineering}'::text[])"
"Total runtime: 0.074 ms"

在数组上使用聚合函数将是另一个问题。函数unnest()可能会有所帮助。

为什么不对数据进行归一化处理？这将解决所有问题，包括您尚未遇到的许多问题。

- Frank Heikens

---

我认为在查询中没有使用ARRAY ['Engineering']，因此GIN索引从未被使用。关于规范化，实际表格和用例与我所提到的不同。实际表格确实具有多个列，这些列是使用字符串数组表示的多值列。我试图避免多个连接，因为每个表格的预期记录数是以百万计的数量级。 - Anoop

我认为操作符@>起了决定性作用，它看起来像是ANY()无法使用索引。 - Frank Heikens

特别是当您预期使用大型中间表和/或结果表时，应尽可能使数据易于DBMS访问。规范化使其能够在多种情况下使用引用而不是值进行操作。 - Simon Richter

使用 explain 函数，我发现在 operator @> 和 any() 中没有区别，两者都给出相同的结果： explain select count(*) from kwetsbaar.kwo where bijztype @> (array['WON'])::varchar[]; 最终聚合（cost=505166.56..505166.57 rows=1 width=8） -> 聚集（cost=505166.14..505166.55 rows=4 width=8） 预计工作线程：4 -> 部分聚合（cost=504166.14..504166.15 rows=1 width=8） -> 并行 Seq 扫描（cost=0.00..500662.60 rows=1401414 width=0） 过滤条件：(bijztype @> '{WON}'::character varying[]) 带有 GIN 索引，800万行。 - Jan

Answer 2

我认为处理这个问题的最佳方式是规范化您的模型。以下内容可能会包含错误，因为我没有尝试过，但是思路应该很清晰：

CREATE TABLE users (id INTEGER PRIMARY KEY, name VARCHAR(100) UNIQUE);
CREATE TABLE groups (id INTEGER PRIMARY KEY, name VARCHAR(100) UNIQUE);
CREATE TABLE user_group (
    user INTEGER NOT NULL REFERENCES users,
    group INTEGER NOT NULL REFERENCES groups);
CREATE UNIQUE INDEX user_group_unique ON user_group (user, group);

SELECT users.name
    FROM user_group
    INNER JOIN users ON user_group.user = users.id
    INNER JOIN groups ON user_group.group = groups.id
    WHERE groups.name = 'Engineering';

得到的执行计划应该已经相当有效了；你仍然可以通过对 user_group(group) 建立索引进行优化，这样可以使用 index_scan 而不是 sequential_scan 来查找特定组的成员。

- Simon Richter

---

我想避免规范化这个特定的用例。这就是为什么我被迫使用字符串数组数据类型的原因。 - Anoop

Answer 3

现在 PostgreSQL 9.6 版本中有 array_to_tsvector() 函数。

https://pgpedia.info/a/array_to_tsvector.html

所以，也许最有效的方法是创建tsvector并在它们上使用gin索引。

- Kvetoslav