为什么我的PostgreSQL数组索引没有被使用（Rails 4）？

本文介绍了为什么我的PostgreSQL数组索引没有被使用（Rails 4）？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个PostgreSQL字符串数组作为表中的列。我使用GIN方法创建了一个索引。但是任何查询都不会使用索引（相反，他们使用过滤器对整个表进行顺序扫描）。我缺少什么？

I've got a PostgreSQL array of strings as a column in a table. I created an index using the GIN method. But ANY queries won't use the index (instead, they're doing a sequential scan of the whole table with a filter). What am I missing?

这是我的迁移：

class CreateDocuments < ActiveRecord::Migration def up create_table :documents do |t| t.string :title t.string :tags, array: true, default: [] t.timestamps end add_index :documents, :tags, using: 'gin' (1..100000).each do |i| tags = [] tags << 'even' if (i % 2) == 0 tags << 'odd' if (i % 2) == 1 tags << 'divisible by 3' if (i % 3) == 0 tags << 'divisible by 4' if (i % 4) == 0 tags << 'divisible by 5' if (i % 5) == 0 Document.create( title: i, tags: tags ) end end def down drop_table :documents end end

这是我的查询，包含结果行数。

Here's my query, with the resulting number of rows.

Document.where("'divisible by 5' = ANY (tags)").explain Document Load (249.8ms) SELECT "documents".* FROM "documents" WHERE ('divisible by 5' = ANY (tags)) D, [2014-03-07T17:09:49.689709 #41937] DEBUG -- : Document Load (249.8ms) SELECT "documents".* FROM "documents" WHERE ('divisible by 5' = ANY (tags)) => EXPLAIN for: SELECT "documents".* FROM "documents" WHERE ('divisible by 5' = ANY (tags)) QUERY PLAN ----------------------------------------------------------------- Seq Scan on documents (cost=0.00..3500.00 rows=20057 width=69) Filter: ('divisible by 5'::text = ANY ((tags)::text[])) (2 rows) Document.where("'divisible by 5' = ANY (tags)").length Document Load (258.0ms) SELECT "documents".* FROM "documents" WHERE ('divisible by 5' = ANY (tags)) D, [2014-03-07T17:09:55.536517 #41937] DEBUG -- : Document Load (258.0ms) SELECT "documents".* FROM "documents" WHERE ('divisible by 5' = ANY (tags)) => 20000

推荐答案

要使用GIN索引，请使用 < @ （包含在）运算符而不是 ANY 构造。

To work with a GIN index use the <@ ("is contained by") operator instead of the ANY construct.

手册在此声明默认GIN索引目前仅支持这些运算符（附加功能随附扩展）：

The manual states here that default GIN indexes currently only support these operators (additional functionality is shipped with extensions):

<@ @> = &&

请尝试此查询：

Document.where("'{divisible by 5}' <@ tags").explain

请注意，左侧需要也是数组符号，即使它是单个元素。运算符< @ 适用于数组。因此'{可被5整除}'。

Note that the left hand side needs to be in array notation, too, even if it's a single element. The operator <@ works for arrays. Hence '{divisible by 5}'.