PostgreSQL可以索引数组列吗？

9 浏览
0 Comments

PostgreSQL可以索引数组列吗？

我在文档中找不到对这个问题的确切答案。如果一列是数组类型，那么所有输入的值都会被单独索引吗？\n我创建了一个简单的表，其中有一个int[]列，并在上面放了一个唯一索引。我注意到我无法添加相同的int数组，这让我相信索引是数组项的组合，而不是每个项的索引。\n

INSERT INTO "Test"."Test" VALUES ('{10, 15, 20}');
INSERT INTO "Test"."Test" VALUES ('{10, 20, 30}');
SELECT * FROM "Test"."Test" WHERE 20 = ANY ("Column1");

\n索引会帮助这个查询吗？

0

3 答案

0 Comments

可以在PostgreSQL 9.2.1上对数组的每个元素进行索引。例如：

CREATE TABLE test (foo int[]);
INSERT INTO test VALUES ('{1,2,3}');
INSERT INTO test VALUES ('{4,5,6}');
CREATE INDEX test_index on test ((foo[1]));
SET enable_seqscan TO off;
EXPLAIN ANALYZE SELECT * from test WHERE foo[1]=1;
                                                QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Index Scan using test_index on test  (cost=0.00..8.27 rows=1 width=32) (actual   time=0.070..0.071 rows=1 loops=1)
   Index Cond: (foo[1] = 1)
 Total runtime: 0.112 ms
(3 rows)

这在至少Postgres 9.2.1上有效。请注意，您需要为每个数组索引构建一个单独的索引，在我的示例中，我只索引了第一个元素。

不要忘记 - 对于希望使用ANY()运算符的可变长度数组，这种方法是不可行的。

这实际上并不是非常有用。如果您有固定数量的数组元素，您应该使用每个元素的单独列（和普通的btree索引），而不是为每个数组元素构建更昂贵的表达式索引。单独列的存储成本也更低，没有数组开销。

0
0 Comments

Can PostgreSQL index array columns?

在Stack Overflow的一个评论中，一个用户提出了一个问题：

我没有找到当前的答案有效。在数组类型的列上使用GIN索引并不能提高ANY()操作符的性能。真的没有解决办法吗？

一个回答告诉你使用数组操作符，在Postgres 11中仍然正确。PostgreSQL的官方文档中提到：

... PostgreSQL的标准发行版包含了针对数组的GIN操作类，支持使用以下操作符进行索引查询：

<@
@>
=
&&

在标准发行版中，内置的GIN索引操作类的完整列表可以在这里找到。

在PostgreSQL中，索引与操作符绑定在一起（这些操作符是为特定类型实现的），而不是只与数据类型、函数或其他任何东西绑定在一起。这是Postgres最初的Berkeley设计的遗产，现在很难改变。而且这通常工作得很好。这里有一个关于这个问题的pgsql-bugs的讨论帖子，Tom Lane在其中发表了评论。

一些PostGis函数（如ST_DWithin()）似乎违反了这个原则，但事实并非如此。这些函数在内部被重写为使用相应的操作符。

索引表达式必须位于操作符的左侧。对于大多数操作符（包括上面提到的所有操作符），如果将索引表达式放在右侧，查询规划器可以通过翻转操作数来实现这一点，前提是定义了一个COMMUTATOR。当与各种操作符结合使用时，ANY构造不是一个操作符本身。当使用constant = ANY (array_expression)时，只有支持数组元素上的=操作符的索引才能符合要求，我们需要一个与= ANY()对应的commutator。GIN索引无法满足这个要求。

目前Postgres还无法从中推导出一个可用于GIN索引的表达式。首先，constant = ANY (array_expression)与array_expression @> ARRAY[constant]并不完全等价。如果涉及到任何NULL元素，数组操作符会返回错误，而ANY构造可以处理任意一侧的NULL。而且对于数据类型不匹配，结果也是不同的。

与此相关的回答：

- [Check if value exists in Postgres array](https://stackoverflow.com/questions/11231544/11231965#11231965)

- [Index for finding an element in a JSON array](https://stackoverflow.com/questions/18404055/18405706#18405706)

- [SQLAlchemy: how to filter on PgArray column types?](https://stackoverflow.com/questions/10344468/10344851#10344851)

- [Can IS DISTINCT FROM be combined with ANY or ALL somehow?](https://dba.stackexchange.com/a/60378/3684)

附带说明：

- 当使用integer数组（int4，而不是int2或int8）而且没有NULL值（如您的示例所示）时，考虑使用附加模块intarray，该模块提供了专门的、更快的操作符和索引支持。详情请参阅：

- [How to create an index for elements of an array in PostgreSQL?](https://stackoverflow.com/questions/10867577/10868144#10868144)

- [Compare arrays for equality, ignoring order of elements](https://stackoverflow.com/questions/12870105/12870508#12870508)

至于您的问题中没有得到回答的UNIQUE约束：这是通过对整个数组值（如您所怀疑的那样）使用B树索引来实现的，并且对于查找元素没有任何帮助。详情请参阅：

- [How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?](https://stackoverflow.com/questions/9066972/9067108#9067108)

有用户表示，根据我的经验，除非对integer[]列使用gin__int_ops，否则这些索引几乎不会加速。多年来我一直为此感到沮丧，寻找其他解决方案，直到我发现了这个操作类。它简直是一个奇迹。

对于ANY (array_expression) = constant表达式，GIN索引工作得很好吗？

0
0 Comments

在PostgreSQL中，我们可以对数组进行索引，但是需要使用数组操作符和GIN索引类型。

示例：

CREATE TABLE "Test"("Column1" int[]);
INSERT INTO "Test" VALUES ('{10, 15, 20}');
INSERT INTO "Test" VALUES ('{10, 20, 30}');
CREATE INDEX idx_test on "Test" USING GIN ("Column1");
-- 为了强制使用索引，因为这个测试只有2条记录...
SET enable_seqscan TO off;
EXPLAIN ANALYZE
SELECT * FROM "Test" WHERE "Column1" @> ARRAY[20];

结果：

Bitmap Heap Scan on "Test"  (cost=4.26..8.27 rows=1 width=32) (actual time=0.014..0.015 rows=2 loops=1)
  Recheck Cond: ("Column1" > '{20}'::integer[])
  ->  Bitmap Index Scan on idx_test  (cost=0.00..4.26 rows=1 width=0) (actual time=0.009..0.009 rows=2 loops=1)
        Index Cond: ("Column1" > '{20}'::integer[])
Total runtime: 0.062 ms

需要注意的是，在许多情况下，需要使用`gin__int_ops`选项。如果不使用这个选项，似乎无法使用`&&`和`@>`操作符建立索引。

如OP所猜测的，在这种情况下，实际上并没有对单个数组值进行索引，而是对整个数组进行索引。因此，虽然这可以帮助查询（见解释计划），但这意味着您不能（轻松地）对单个数组值创建唯一约束。但是，如果您使用的是整数数组，可以使用contrib模块“intarray”对单个数组值进行索引，在许多情况下可以提高性能。（我记得对于文本值也在进行一些相关的工作，但是欢迎贡献者帮助完善。）

请在代码示例中不要使用大写字母作为PostgreSQL标识符，这会让不熟悉引用/大小写折叠规则的人感到困惑，特别是对于初次接触PostgreSQL的人来说。

从我的经验来看，这些索引几乎没有提升速度，除非对`integer[]`列使用`gin__int_ops`。在我发现这个操作符类之前，我花了很多年的时间感到沮丧并寻找其他解决方案。它几乎是一个奇迹般的解决方法。

那么，这是否意味着我不应该为字符串数组创建索引？我只应该为整数数组创建索引吗？

只有在安装了"intarray"扩展时，才需要使用操作符类"gin__int_ops"，否则索引会默认工作。我在这里做了更详细的说明：stackoverflow.com/questions/63996454/…

你确定吗？"广义倒排索引"的定义恰好是它可以索引复合值（与btree不同，后者索引整个值）。

0