mardi 26 mai 2015

How to reduce eager_load query overhead (ActiveRecord)?

I have the following model:

# address.rb

class Address < ActiveRecord::Base
  has_many :orders_for_cleaning,    class_name: 'Order',
                                    foreign_key: :cleaning_address_id
  has_many :orders_for_billing,     class_name: 'Order',
                                    foreign_key: :billing_address_id
  has_many :purchases_for_shipping, class_name: 'Purchase',
                                    foreign_key: :shipping_address_id
  has_many :purchases_for_billing,  class_name: 'Purchase',
                                    foreign_key: :billing_address_id                                    
end

and I want to fetch all addresses of a certain Customer through his orders a purchases using eager_load:

# customer.rb

def addresses
  Address.eager_load(:orders_for_cleaning,
                     :orders_for_billing,
                     :purchases_for_shipping,
                     :purchases_for_billing)
  .where('orders.customer_id = ?
          OR orders_for_billings_addresses.customer_id = ?
          OR purchases.customer_id = ?
          OR purchases_for_billings_addresses.customer_id = ?',
         id, id, id, id)
end

This request generates the following query:

SELECT DISTINCT "addresses"."id" AS t0_r0, 
"addresses"."latitude" AS t0_r1, 
"addresses"."longitude" AS t0_r2, 
"addresses"."house" AS t0_r3, 
"addresses"."street" AS t0_r4, 
"addresses"."city" AS t0_r5, 
"addresses"."zip" AS t0_r6, 
"addresses"."state" AS t0_r7, 
"addresses"."country" AS t0_r8, 
"addresses"."created_at" AS t0_r9, 
"addresses"."updated_at" AS t0_r10, 
"addresses"."street2" AS t0_r11, 
"addresses"."custom_latitude" AS t0_r12, 
"addresses"."custom_longitude" AS t0_r13, 
"addresses"."name" AS t0_r14, 
"addresses"."company" AS t0_r15, 
"addresses"."archived" AS t0_r16, 
"orders"."id" AS t1_r0, 
"orders"."customer_id" AS t1_r1, 
"orders"."cleaning_address_id" AS t1_r2, 
"orders"."billing_address_id" AS t1_r3, 
"orders"."start_at" AS t1_r4, 
"orders"."finish_at" AS t1_r5, 
"orders"."planned_at" AS t1_r6, 
"orders"."started_at" AS t1_r7, 
"orders"."finished_at" AS t1_r8, 
"orders"."payed_price" AS t1_r9, 
"orders"."status" AS t1_r10, 
"orders"."pin" AS t1_r11, 
"orders"."comment" AS t1_r12, 
"orders"."created_at" AS t1_r13, 
"orders"."updated_at" AS t1_r14, 
"orders"."name" AS t1_r15, 
"orders"."company" AS t1_r16, 
"orders"."phone" AS t1_r17, 
"orders"."coupon_code" AS t1_r18, 
"orders"."coupon_discount" AS t1_r19, 
"orders"."location_id" AS t1_r20, 
"orders"."location_discount" AS t1_r21, 
"orders"."job_count" AS t1_r22, 
"orders"."bonus" AS t1_r23, 
"orders"."organization_id" AS t1_r24, 
"orders"."bill_id" AS t1_r25, 
"orders"."cleaning_count_discount" AS t1_r26, 
"orders"."created_by" AS t1_r27, 
"orders"."price_group_id" AS t1_r28, 
"orders"."travel_charge" AS t1_r29, 
"orders"."departed_at" AS t1_r30, 
"orders"."arrived_at" AS t1_r31, 
"orders"."request_feedback" AS t1_r32, 
"orders"."feedback_link" AS t1_r33, 
"orders"."feedback_requested_at" AS t1_r34, 
"orders"."rating" AS t1_r35, 
"orders"."feedback" AS t1_r36, 
"orders"."profit_center_code" AS t1_r37, 
"orders"."feedback_received_at" AS t1_r38, 
"orders"."reorder_bonus" AS t1_r39, 
"orders"."paid_to" AS t1_r40, 
"orders"."cleaner_id" AS t1_r41, 
"orders"."station_id" AS t1_r42, 
"orders"."cleaner_comment" AS t1_r43, 
"orders_for_billings_addresses"."id" AS t2_r0, 
"orders_for_billings_addresses"."customer_id" AS t2_r1, 
"orders_for_billings_addresses"."cleaning_address_id" AS t2_r2, 
"orders_for_billings_addresses"."billing_address_id" AS t2_r3, 
"orders_for_billings_addresses"."start_at" AS t2_r4, 
"orders_for_billings_addresses"."finish_at" AS t2_r5, 
"orders_for_billings_addresses"."planned_at" AS t2_r6, 
"orders_for_billings_addresses"."started_at" AS t2_r7, 
"orders_for_billings_addresses"."finished_at" AS t2_r8, 
"orders_for_billings_addresses"."payed_price" AS t2_r9, 
"orders_for_billings_addresses"."status" AS t2_r10, 
"orders_for_billings_addresses"."pin" AS t2_r11, 
"orders_for_billings_addresses"."comment" AS t2_r12, 
"orders_for_billings_addresses"."created_at" AS t2_r13, 
"orders_for_billings_addresses"."updated_at" AS t2_r14, 
"orders_for_billings_addresses"."name" AS t2_r15, 
"orders_for_billings_addresses"."company" AS t2_r16, 
"orders_for_billings_addresses"."phone" AS t2_r17, 
"orders_for_billings_addresses"."coupon_code" AS t2_r18, 
"orders_for_billings_addresses"."coupon_discount" AS t2_r19, 
"orders_for_billings_addresses"."location_id" AS t2_r20, 
"orders_for_billings_addresses"."location_discount" AS t2_r21, 
"orders_for_billings_addresses"."job_count" AS t2_r22, 
"orders_for_billings_addresses"."bonus" AS t2_r23, 
"orders_for_billings_addresses"."organization_id" AS t2_r24, 
"orders_for_billings_addresses"."bill_id" AS t2_r25, 
"orders_for_billings_addresses"."cleaning_count_discount" AS t2_r26, 
"orders_for_billings_addresses"."created_by" AS t2_r27, 
"orders_for_billings_addresses"."price_group_id" AS t2_r28, 
"orders_for_billings_addresses"."travel_charge" AS t2_r29, 
"orders_for_billings_addresses"."departed_at" AS t2_r30, 
"orders_for_billings_addresses"."arrived_at" AS t2_r31, 
"orders_for_billings_addresses"."request_feedback" AS t2_r32, 
"orders_for_billings_addresses"."feedback_link" AS t2_r33, 
"orders_for_billings_addresses"."feedback_requested_at" AS t2_r34, 
"orders_for_billings_addresses"."rating" AS t2_r35, 
"orders_for_billings_addresses"."feedback" AS t2_r36, 
"orders_for_billings_addresses"."profit_center_code" AS t2_r37, 
"orders_for_billings_addresses"."feedback_received_at" AS t2_r38, 
"orders_for_billings_addresses"."reorder_bonus" AS t2_r39, 
"orders_for_billings_addresses"."paid_to" AS t2_r40, 
"orders_for_billings_addresses"."cleaner_id" AS t2_r41, 
"orders_for_billings_addresses"."station_id" AS t2_r42, 
"orders_for_billings_addresses"."cleaner_comment" AS t2_r43, 
"purchases"."id" AS t3_r0, 
"purchases"."product_price_group_id" AS t3_r1, 
"purchases"."customer_id" AS t3_r2, 
"purchases"."shipping_address_id" AS t3_r3, 
"purchases"."billing_address_id" AS t3_r4, 
"purchases"."created_by" AS t3_r5, 
"purchases"."token" AS t3_r6, 
"purchases"."shipping" AS t3_r7, 
"purchases"."coupon_code" AS t3_r8, 
"purchases"."coupon_discount" AS t3_r9, 
"purchases"."comment" AS t3_r10, 
"purchases"."status" AS t3_r11, 
"purchases"."purchased_at" AS t3_r12, 
"purchases"."created_at" AS t3_r13, 
"purchases"."updated_at" AS t3_r14, 
"purchases"."purchase_invoice_id" AS t3_r15, 
"purchases"."payment" AS t3_r16, 
"purchases"."pickup" AS t3_r17, 
"purchases"."tracking_number" AS t3_r18, 
"purchases"."paper_invoice_sent_at" AS t3_r19, 
"purchases_for_billings_addresses"."id" AS t4_r0, 
"purchases_for_billings_addresses"."product_price_group_id" AS t4_r1, 
"purchases_for_billings_addresses"."customer_id" AS t4_r2, 
"purchases_for_billings_addresses"."shipping_address_id" AS t4_r3, 
"purchases_for_billings_addresses"."billing_address_id" AS t4_r4, 
"purchases_for_billings_addresses"."created_by" AS t4_r5, 
"purchases_for_billings_addresses"."token" AS t4_r6, 
"purchases_for_billings_addresses"."shipping" AS t4_r7, 
"purchases_for_billings_addresses"."coupon_code" AS t4_r8, 
"purchases_for_billings_addresses"."coupon_discount" AS t4_r9, 
"purchases_for_billings_addresses"."comment" AS t4_r10, 
"purchases_for_billings_addresses"."status" AS t4_r11, 
"purchases_for_billings_addresses"."purchased_at" AS t4_r12, 
"purchases_for_billings_addresses"."created_at" AS t4_r13, 
"purchases_for_billings_addresses"."updated_at" AS t4_r14, 
"purchases_for_billings_addresses"."purchase_invoice_id" AS t4_r15, 
"purchases_for_billings_addresses"."payment" AS t4_r16, 
"purchases_for_billings_addresses"."pickup" AS t4_r17, 
"purchases_for_billings_addresses"."tracking_number" AS t4_r18, 
"purchases_for_billings_addresses"."paper_invoice_sent_at" AS t4_r19 
FROM "addresses" 
LEFT OUTER JOIN "orders" 
ON "orders"."cleaning_address_id" = "addresses"."id" 
LEFT OUTER JOIN "orders" "orders_for_billings_addresses" 
ON "orders_for_billings_addresses"."billing_address_id" = "addresses"."id" 
LEFT OUTER JOIN "purchases" 
ON "purchases"."shipping_address_id" = "addresses"."id" 
LEFT OUTER JOIN "purchases" "purchases_for_billings_addresses" 
ON "purchases_for_billings_addresses"."billing_address_id" = "addresses"."id" 
WHERE (orders.customer_id = 3282 OR orders_for_billings_addresses.customer_id = 3282 
  OR purchases.customer_id = 3282 OR purchases_for_billings_addresses.customer_id = 3282)

this query takes about 100ms on my dataset.

But I don't need to load all join model columns just their foreign keys. If I execute stripped SQL query:

SELECT  addresses.*,
        orders.cleaning_address_id,
        orders_for_billings_addresses.billing_address_id,
        purchases.shipping_address_id,
        purchases_for_billings_addresses.billing_address_id
FROM addresses
LEFT OUTER JOIN orders
  ON orders.cleaning_address_id = addresses.id
LEFT OUTER JOIN orders orders_for_billings_addresses
  ON orders_for_billings_addresses.billing_address_id = addresses.id
LEFT OUTER JOIN purchases
  ON purchases.shipping_address_id = addresses.id
LEFT OUTER JOIN purchases purchases_for_billings_addresses
  ON purchases_for_billings_addresses.billing_address_id = addresses.id
WHERE  (orders.customer_id = 3282
        OR orders_for_billings_addresses.customer_id = 3282
        OR purchases.customer_id = 3282
        OR purchases_for_billings_addresses.customer_id = 3282)

It returns the same results in only 50ms.

The question: is there a way to enforce ActiveRecord to select only necessary fields with eager_load? Adding select('addresses.*, <fk_fields...>) doesn't help.

Aucun commentaire:

Enregistrer un commentaire