对数组列进行索引以允许选择包含值的行的最有效方法

Question

假设我有一个这样的表：

create table mytable (
mycol text[]
)

并且我想要选择mycol包含"hello"的所有行。我可以想出两种方法：

SELECT * FROM mytable WHERE 'hello'=any(mycol);
--or
SELECT * FROM mytable WHERE mycol && '{hello}';

我知道对于第二个选项，我可以使用GIN索引(允许数组选项(，并且我很确定对于第一个选项我会使用BTREE(或者HASH？(。

所以我的问题是：如果我只需要检查单个项的成员身份，那么对于一个有数百万行的表，哪种方法的索引最有效？

Answer 1

第二个，带有GIN索引。

第一个不能以有效的方式使用btree或哈希索引。它可以使用btree索引，但只能作为表的精简副本。

您可能会将其与相反的情况混淆，即列位于ANY之前(并且是标量(，文字位于ANY内部。这个可以使用btree。

SELECT * FROM mytable_scalar WHERE mycol =any('{hello,goodbye}');

看看某件事的效率有多高的一个好方法通常只是用假数据来尝试，但大小不太现实：

insert into mytable select ARRAY[md5(random()::text),md5(random()::text)] from generate_series(1,1500000);