Efficient select and distinct on a large association

user_1236336 0
Viewed 233

I have three models: Catalog, Product and Value. The Value table has a characteristic_id column, and I'd like to get the list of different characteristic_id on a set of values.

The relationships are:

  • a catalog has many products
  • a product has many values

Here is the query I came up with:

Value.joins(:product).select(:characteristic_id).distinct.where(products: {catalog_id: catalog.id}).pluck(:characteristic_id)
=> [441, 2582, 3133]

which returns the right result, but it is extremely slow on a large catalog with a million products (about 50 seconds). I can't find a more efficient way to do this.

Here is an EXPLAIN of the query: 

=> EXPLAIN for: SELECT DISTINCT "values"."characteristic_id" FROM "values" INNER JOIN "products" ON "products"."id" = "values"."product_id" WHERE "products"."catalog_id" = $1 [["catalog_id", 1767]]
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=1515106.82..1515109.15 rows=233 width=4)
   Group Key: "values".characteristic_id
   ->  Hash Join  (cost=124703.76..1492245.65 rows=9144469 width=4)
         Hash Cond: ("values".product_id = products.id)
         ->  Seq Scan on "values"  (cost=0.00..1002863.07 rows=34695107 width=8)
         ->  Hash  (cost=114002.20..114002.20 rows=652285 width=4)
               ->  Bitmap Heap Scan on products  (cost=12311.64..114002.20 rows=652285 width=4)
                     Recheck Cond: (catalog_id = 1767)
                     ->  Bitmap Index Scan on index_products_on_catalog_id  (cost=0.00..12148.57 rows=652285 width=0)
                           Index Cond: (catalog_id = 1767)
(10 rows)

Any idea on how to run this query faster?

rails-activerecord activerecord ruby-on-rails
2 Answers

Make sure you have indexes on both foreign keys:

  • index "values"."product_id"
  • index "products"."catalog_id"
user_15363090
  1. Try to add an index on values.characteristic_id.

  2. Oftentimes GROUP BY may be faster than DISTINCT :

    Value.joins(:product).where(products: {catalog_id: catalog.id}).select(:characteristic_id).group(:characteristic_id).pluck(:characteristic_id)

user_40711710
Related