是否可以在类型为文本数组的列上创建索引。尝试使用GIN索引,但查询似乎没有使用这些索引。
-- Example:
CREATE TABLE users (
name VARCHAR(100),
groups TEXT[],
);
-- Query:
SELECT name FROM users WHERE ANY(groups) = 'Engineering';
还有,最好的方法是如何高效地对groups
列执行GROUP BY
操作以便可以获得groups
和计数。
是否可以在类型为文本数组的列上创建索引。尝试使用GIN索引,但查询似乎没有使用这些索引。
-- Example:
CREATE TABLE users (
name VARCHAR(100),
groups TEXT[],
);
-- Query:
SELECT name FROM users WHERE ANY(groups) = 'Engineering';
还有,最好的方法是如何高效地对groups
列执行GROUP BY
操作以便可以获得groups
和计数。
可以使用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()可能会有所帮助。
为什么不对数据进行归一化处理?这将解决所有问题,包括您尚未遇到的许多问题。
我认为处理这个问题的最佳方式是规范化您的模型。以下内容可能会包含错误,因为我没有尝试过,但是思路应该很清晰:
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 来查找特定组的成员。
https://pgpedia.info/a/array_to_tsvector.html
所以,也许最有效的方法是创建tsvector并在它们上使用gin索引。