sql - 如何使用 gin 索引查询 postgres 数组？

Question

我有一个带有 gin 索引的 postgres 数组列:

CREATE TABLE things (
    id integer NOT NULL,
    tags character varying(255)[]
);

CREATE INDEX index_things_on_tags ON things USING gin (tags);

有几种方法可以使用各种数组运算符检查列中是否存在元素。以下是我见过的:

1. select * from things where 'blue' = ANY (tags)
2. select * from things where tags <@ '{"blue"}'
3. select * from things where '{"blue","yellow"}' && tags;

在 postgres 9.3 中:

• 第一个会使用 gin 索引吗？
• 我很确定第二个会使用索引。但是，它与第一个不同。它不允许我检查蓝色是否是标签之一，它要求我指定确切的数组。有没有办法使 2 中的语法风格达到 1 所达到的效果？
• 在第三种情况下，我想要任何包含蓝色或黄色中的任何一种的行。这个查询会使用 gin 索引吗？如果没有，我如何使用索引执行此查询？

Answer 1

为什么不测试看看呢？

regress=> SET enable_seqscan  = off;
SET

regress=> explain select * from things where 'blue' = ANY (tags);
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Seq Scan on things  (cost=10000000000.00..10000000037.67 rows=6 width=36)
   Filter: ('blue'::text = ANY ((tags)::text[]))
(2 rows)

regress=> explain select * from things where tags <@ '{"blue"}';
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Bitmap Heap Scan on things  (cost=12.05..21.52 rows=6 width=36)
   Recheck Cond: (tags <@ '{blue}'::character varying[])
   ->  Bitmap Index Scan on index_things_on_tags  (cost=0.00..12.05 rows=6 width=0)
         Index Cond: (tags <@ '{blue}'::character varying[])
(4 rows)

regress=> explain select * from things where '{"blue","yellow"}' && tags;
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Bitmap Heap Scan on things  (cost=12.10..22.78 rows=12 width=36)
   Recheck Cond: ('{blue,yellow}'::character varying[] && tags)
   ->  Bitmap Index Scan on index_things_on_tags  (cost=0.00..12.09 rows=12 width=0)
         Index Cond: ('{blue,yellow}'::character varying[] && tags)
(4 rows)

所以 Pg 正在使用 && 的索引和 <@查询，但不适用于 = ANY (...) .

我确定可以教 Pg 转换 x = ANY (y)进入ARRAY[x] @> y , 但目前没有。

2 所做的正是您所说的。测试“blue”是否是标签之一。这不是平等测试，而是成员资格测试。