dimanche 13 mai 2018

2 similar tables with an indexed category_id - 2 different queries generated by Postgres

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