我有一个PostgreSQL字符串数组,作为表中的一列。我使用GIN方法创建了一个索引。但是,任何查询都不会使用索引(而是使用过滤器对整个表进行顺序扫描)。我想念什么?
这是我的迁移:
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
这是我的查询,其中包含行数。
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索引,请使用 <@(“包含于”)运算符,而不要使用ANYConstruct。
<@
ANY
手册在此处指出,默认GIN索引当前仅支持这些运算符(扩展附带了附加功能):
<@ @> = &&
因此,请尝试以下查询:
Document.where("'{divisible by 5}' <@ tags").explain
请注意array notation,即使是单个元素,左侧也必须位于。该运算符<@适用于 数组 。因此'{divisible by 5}'。
array notation
'{divisible by 5}'