在RubyOnRails中为内部连接的表创建postgres索引

Question

我有一个基于RubyOnRails 4.0的应用程序。我有两个模型:商店和产品。系统中大约有150万种产品，如果我没有正确使用索引，它会变得很慢。

一些基本信息

• Store has_many Products
• 商店。当1=已关联2=未关联
时使用了belongate_type_id。
• 产品具有"category_connection_id"(整数)和"is_available"(布尔)等属性

在feeddedproduct模型中:

scope :affiliated, -> { joins(:store).where("stores.affiliate_type_id = 1") } 

这个查询大约需要500毫秒，基本上会中断网站:

FeededProduct.where(:is_available => true).affiliated.where(:category_connection_id => @feeded_product.category_connection_id)

对应postgresql:

FeededProduct Load (481.4ms)  SELECT "feeded_products".* FROM "feeded_products" INNER JOIN "stores" ON "stores"."id" = "feeded_products"."store_id" WHERE "feeded_products"."is_available" = 't' AND "feeded_products"."category_connection_id" = 345 AND (stores.affiliate_type_id = 1)

<标题>更新。Postgresql解释:

                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Hash Join  (cost=477.63..49176.17 rows=21240 width=1084)
   Hash Cond: (feeded_products.store_id = stores.id)
   ->  Bitmap Heap Scan on feeded_products  (cost=377.17..48983.06 rows=38580 width=1084)
         Recheck Cond: (category_connection_id = 5923)
         Filter: is_available
         ->  Bitmap Index Scan on cc_w_store_index_on_fp  (cost=0.00..375.25 rows=38580 width=0)
               Index Cond: ((category_connection_id = 5923) AND (is_available = true))
   ->  Hash  (cost=98.87..98.87 rows=452 width=4)
         ->  Seq Scan on stores  (cost=0.00..98.87 rows=452 width=4)
               Filter: (affiliate_type_id = 1)
(10 rows)

问题:我如何创建一个索引，将考虑内部连接并使其更快?

Answer 1

这取决于PostgreSQL选择的连接算法。在查询中使用EXPLAIN来查看PostgreSQL是如何处理查询的。

这些是取决于连接算法的答案:

1. 嵌套循环连接

   在这里，您应该为内部关系 (EXPLAIN输出中的底部表)创建一个连接条件上的索引。您可以通过添加出现在WHERE子句和中的列来进一步改进，从而显著提高选择性(即，显著减少在索引扫描期间过滤掉的行数)。
   对于外部关系，如果这些条件过滤掉表中的大多数行，则对WHERE子句中出现的列建立索引将加快查询速度。

2. 散列连接

   在WHERE子句中，条件过滤掉了表中的大多数行，在两个表的这些列上都有索引是有帮助的。

3. 合并连接

   这里你需要在合并条件下的列上建立索引，以允许PostgreSQL使用索引扫描进行排序。此外，您还可以追加出现在WHERE子句中的列。

如果索引被使用，总是使用EXPLAIN进行测试。如果没有，很可能无法使用它们，或者使用它们会使查询比顺序扫描慢，例如，因为它们没有过滤出足够的行。