gistfile1.txt

comparsports_development=# explain SELECT "products".* FROM "products" INNER JOIN "merchant_products" ON "merchant_products"."product_id" = "products"."id" INNER JOIN "merchant_products_option_values" ON "merchant_products_option_values"."merchant_product_id" = "merchant_products"."id" INNER JOIN "option_values" ON "option_values"."id" = "merchant_products_option_values"."option_value_id" WHERE "products"."active" = 't' AND "products"."brand_id" = 1943 AND (price_min >= 0) ORDER BY products.merchant_product_active_count desc LIMIT 20 OFFSET 0;
 Limit  (cost=34508.48..34508.53 rows=20 width=510)
   ->  Sort  (cost=34508.48..34522.45 rows=5586 width=510)
         Sort Key: products.merchant_product_active_count
         ->  Hash Join  (cost=16341.25..34359.84 rows=5586 width=510)
               Hash Cond: (merchant_products_option_values.option_value_id = option_values.id)
               ->  Hash Join  (cost=14902.40..31919.25 rows=5586 width=514)
                     Hash Cond: (merchant_products_option_values.merchant_product_id = merchant_products.id)
                     ->  Seq Scan on merchant_products_option_values  (cost=0.00..13461.63 rows=933163 width=8)
                     ->  Hash  (cost=14883.39..14883.39 rows=1521 width=514)
                           ->  Nested Loop  (cost=54.90..14883.39 rows=1521 width=514)
                                 ->  Bitmap Heap Scan on products  (cost=54.48..4609.99 rows=1372 width=510)
                                       Recheck Cond: (brand_id = 1943)
                                       Filter: (active AND (price_min >= 0::numeric))
                                       ->  Bitmap Index Scan on index_products_on_brand_id_and_active  (cost=0.00..54.14 rows=1372 width=0)
                                             Index Cond: ((brand_id = 1943) AND (active = true))
                                 ->  Index Scan using index_merchant_products_on_variant_id_and_merchant_id on merchant_products  (cost=0.42..7.48 rows=1 width=8)
                                       Index Cond: (product_id = products.id)
               ->  Hash  (cost=789.49..789.49 rows=39549 width=4)
                     ->  Seq Scan on option_values  (cost=0.00..789.49 rows=39549 width=4)

Time: 4,714 ms