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