在RubyOnRails中使用内部联接为表创建postgres索引-CreatepostgresindexfortablewithinnerjoininRubyOnRails

I have an app based on RubyOnRails 4.0. I have two models: Stores and Products. There are about 1.5 million products in the system making it quite slow if I do not use indices properly.

我有一个基于RubyOnRails 4.0的应用程序。我有两种型号：商店和产品。如果我没有正确使用指数，系统中大约有150万个产品使它变得非常慢。

Some basic info

一些基本信息

• Store has_many Products
• 商店has_many产品
• Store.affiliate_type_id is used where 1=Affiliated 2=Not affiliated
• 使用Store.affiliate_type_id，其中1 =附属2 =不附属
• Products have attributes like "category_connection_id" (integer) and "is_available" (boolean)
• 产品具有“category_connection_id”（整数）和“is_available”（布尔值）等属性

In FeededProduct model:

在FeededProduct模型中：

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

This query takes about 500ms which basically interrupts the website:

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

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

Corresponding postgresql:

对应的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)

Update. Postgresql EXPLAIN:

                                           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)

Question: How can I create an index that will take the inner join into consideration and make this faster?

问题：如何创建一个将内部联接考虑在内并使其更快的索引？

1 个解决方案