ruby-on-rails - PostgreSQL 复杂索引类型和排序

Question

过去几天我一直在大量阅读有关索引的文章，并且我试图找出正确的方法来为我有很多约束的查询建立索引。我正在使用 postgres_ext gem支持数组数据类型以及 GIN 和 GIST 索引类型。

我有两个问题

.where("a_id IN (?) and b = ? and active = ? and ? != ALL(c) and ? = ANY(d)")
.where("a_id =? and active =? and ? != ALL(c)")

c和d是整型数组

我计划添加的索引:

 add_index :deals, [:a, :b], :where => "active = true"
 add_index :deals [:c, :d], :index_type => :gin, :where => "active = true"

postgres 会在第一个查询中同时使用这两个多列索引吗？

数组数据类型应该始终是“gin”索引类型吗？或者您也可以将它们放入 b 树索引中吗？

最后，第一个索引将用于两个查询中的“a”吗？

附加信息:

我正在使用 PostgreSQL 9.1.3

create_table "table", :force => true do |t|
 t.integer  "a_id"    ##foreign key
 t.string   "title"
 t.text     "description",    :default => ""
 t.boolean  "active",           :default => true
 t.datetime "created_at",      :null => false
 t.datetime "updated_at",    :null => false
 t.integer  "b",
 t.integer  "c", :limit => 8,   :array => true
 t.integer  "d",  :array => true
end

Answer 1

关于数组和 GIN，你可以有一个数组的 b 树索引，但它对像“数组包含元素”这样的操作没有用。为此，您需要 GIN 或 GiST，并且仅支持 GIN 作为所有数组类型的内置索引。

您还可以使用 intarray extension及其用于整数数组的 GiST 索引类型，在写入负载下表现更好，但在读取负载下表现更差。

至于判断Pg是否会同时使用这两个索引，最好的判断方式是使用EXPLAIN ANALYZE看看。通过启用 log_statement 从 PostgreSQL 日志中获取 Rails 执行的语句，或者通过 SQL 登录从 Rails 日志中获取。然后使用 explain analyze 在 psql 中运行它。或者，使用 auto_explain extension在查询运行时捕获有关查询的性能报告。

我有一种感觉，您会发现 Pg 无法在同一个过滤器中组合 GiST 或 GIN 和 b 树索引。组合索引需要位图索引扫描，而 IIRC 仅适用于两个 b 树索引。您可能需要将额外的列添加到 GiST 或 GIN 索引，但这会显着增加索引大小并且可能不值得。

您确实需要使用 explain analyze 来了解它在现实世界中如何处理样本或生产数据。

当使用多列索引时，请记住至少对于 b 树索引，Pg 可以使用 (a,b) 上的索引来过滤 a 上的查询> 或 a 和 b，但不适用于仅在 b 上过滤的查询。索引可以从左到右使用，您不能使用索引来搜索索引右侧的值，除非您还搜索其左侧的所有值。