I'm using: Rails 3.2.13 and PostgreSQL 9.4.7
I have 2 product tables, AProducts
and BProducts.
Both tables have a category_id
integer which is an indexed column, and both tables have several million records.
When I query AProducts
by category_id
, the response is quick and uses the index, but when I query BProducts
the response is very very slow, and it uses a sequential scan instead of using the index. I'm trying to figure out why this is happening for BProducts
, given that the table and model is nearly idential to AProducts
. Here are the results from EXPLAIN:
irb(main):2290:0> BProduct.where(:category_id => 700).explain
BProduct Load (219696.7ms) SELECT "b_products".* FROM "b_products" WHERE "b_products"."category_id" = 700
EXPLAIN (1.3ms) EXPLAIN SELECT "b_products".* FROM "b_products" WHERE "b_products"."category_id" = 700
=> "EXPLAIN for: SELECT \"b_products\".* FROM \"b_products\" WHERE \"b_products\".\"category_id\" = 700\n QUERY PLAN\n-----------------------------------------------------------------------------\n Seq Scan on b_products (cost=0.00..1791663.25 rows=926 width=2036)\n Filter: (category_id = 700)\n(2 rows)\n"
The slow query above takes 219696.7ms, and doesn't use the index. The query below for AProduct is quick and DOES use the index.
irb(main):2289:0> AProduct.where(:category_id => 700).explain
AProduct Load (51.0ms) SELECT "a_products".* FROM "a_products" WHERE "a_products"."category_id" = 700
EXPLAIN (1.2ms) EXPLAIN SELECT "a_products".* FROM "a_products" WHERE "a_products"."category_id" = 700
=> "EXPLAIN for: SELECT \"a_products\".* FROM \"a_products\" WHERE \"a_products\".\"category_id\" = 700\n QUERY PLAN\n--------------------------------------------------------------------------------------------------------------------------------\n Index Scan using index_a_products_on_category_id on a_products (cost=0.56..5448.33 rows=1359 width=883)\n Index Cond: (category_id = 700)\n(2 rows)\n"
What I'm trying to figure out, is how can I make my BProduct lookups use the index and fetch the records with the same speed as AProduct, and why isn't that the default behavior I'm seeing?
Aucun commentaire:
Enregistrer un commentaire