title: ruby on rails benchmark vs sql benchmark. Which should you use?
body: <p>I have 2 pairs of sql query. 1 from postgresql for testing, and 1 from ruby on rails</p>
  
  <p>Here is with Left join with inner left join to get the sap_temporary.name and sap_default.name</p>
  
  <pre><code>select 
  su.id AS user_id, 
  su.email,
  su.doctor,
  sca.id AS copr_accnt_id,
  sca.company_id,
  sca.company_name,
  sap_default.variant_price_set_id AS sap_default_id,
  sap_default.name AS sap_default_name,
  sap_temporary.variant_price_set_id AS sap_temporary_id,
  sap_temporary.name AS sap_temporary_name
  from spree_corporate_accounts sca
  INNER JOIN "spree_memberships" sm
    ON "sm"."corporate_account_id" = "sca"."id"
    AND "sm"."deleted_at" IS NULL
  INNER JOIN "spree_users" su
    ON "su"."id" = "sm"."user_id"
    AND "su"."deleted_at" IS NULL
  LEFT JOIN (
      select
      sap.id,
      sap.accountable_id, 
      sap.accountable_type,
      sap.variant_price_set_id,
      svps.name
      from spree_account_prices sap
      left join spree_variant_price_sets svps on sap.variant_price_set_id = svps.id
      where status = 'default'
  ) sap_default on sap_default.accountable_id = sca.id and sap_default.accountable_type = 'Spree::CorporateAccount'
  LEFT JOIN (
      select
      sap.id,
      sap.accountable_id, 
      sap.accountable_type,
      sap.variant_price_set_id,
      svps.name
      from spree_account_prices sap
      left join spree_variant_price_sets svps on sap.variant_price_set_id = svps.id
      where status = 'temporary'
  ) sap_temporary on sap_temporary.accountable_id = sca.id and sap_temporary.accountable_type = 'Spree::CorporateAccount';
  
  </code></pre>
  
  <p>Result from postgres USING EXPLAIN YAML FORMAT</p>
  
  <pre><code>- Plan: 
      Node Type: "Nested Loop"
      Parallel Aware: false
      Join Type: "Left"
      Startup Cost: 1.32
      Total Cost: 12.12
      Plan Rows: 1
      Plan Width: 187
      Join Filter: "(sap_1.variant_price_set_id = svps_1.id)"
      Plans: 
        - Node Type: "Nested Loop"
          Parent Relationship: "Outer"
          Parallel Aware: false
          Join Type: "Left"
          Startup Cost: 1.32
          Total Cost: 11.05
          Plan Rows: 1
          Plan Width: 155
          Join Filter: "(sap_1.accountable_id = sca.id)"
          Plans: 
            - Node Type: "Nested Loop"
              Parent Relationship: "Outer"
              Parallel Aware: false
              Join Type: "Left"
              Startup Cost: 1.32
              Total Cost: 9.10
              Plan Rows: 1
              Plan Width: 151
              Join Filter: "(sap.variant_price_set_id = svps.id)"
              Plans: 
                - Node Type: "Nested Loop"
                  Parent Relationship: "Outer"
                  Parallel Aware: false
                  Join Type: "Left"
                  Startup Cost: 1.32
                  Total Cost: 8.03
                  Plan Rows: 1
                  Plan Width: 119
                  Join Filter: "(sap.accountable_id = sca.id)"
                  Plans: 
                    - Node Type: "Nested Loop"
                      Parent Relationship: "Outer"
                      Parallel Aware: false
                      Join Type: "Inner"
                      Startup Cost: 1.32
                      Total Cost: 6.08
                      Plan Rows: 1
                      Plan Width: 115
                      Join Filter: "(sm.corporate_account_id = sca.id)"
                      Plans: 
                        - Node Type: "Hash Join"
                          Parent Relationship: "Outer"
                          Parallel Aware: false
                          Join Type: "Inner"
                          Startup Cost: 1.32
                          Total Cost: 4.99
                          Plan Rows: 1
                          Plan Width: 51
                          Hash Cond: "(su.id = sm.user_id)"
                          Plans: 
                            - Node Type: "Seq Scan"
                              Parent Relationship: "Outer"
                              Parallel Aware: false
                              Relation Name: "spree_users"
                              Alias: "su"
                              Startup Cost: 0.00
                              Total Cost: 3.56
                              Plan Rows: 27
                              Plan Width: 47
                              Filter: "(deleted_at IS NULL)"
                            - Node Type: "Hash"
                              Parent Relationship: "Inner"
                              Parallel Aware: false
                              Startup Cost: 1.31
                              Total Cost: 1.31
                              Plan Rows: 1
                              Plan Width: 8
                              Plans: 
                                - Node Type: "Seq Scan"
                                  Parent Relationship: "Outer"
                                  Parallel Aware: false
                                  Relation Name: "spree_memberships"
                                  Alias: "sm"
                                  Startup Cost: 0.00
                                  Total Cost: 1.31
                                  Plan Rows: 1
                                  Plan Width: 8
                                  Filter: "(deleted_at IS NULL)"
                        - Node Type: "Seq Scan"
                          Parent Relationship: "Inner"
                          Parallel Aware: false
                          Relation Name: "spree_corporate_accounts"
                          Alias: "sca"
                          Startup Cost: 0.00
                          Total Cost: 1.04
                          Plan Rows: 4
                          Plan Width: 68
                    - Node Type: "Seq Scan"
                      Parent Relationship: "Inner"
                      Parallel Aware: false
                      Relation Name: "spree_account_prices"
                      Alias: "sap"
                      Startup Cost: 0.00
                      Total Cost: 1.90
                      Plan Rows: 4
                      Plan Width: 8
                      Filter: "(((status)::text = 'default'::text) AND ((accountable_type)::text = 'Spree::CorporateAccount'::text))"
                - Node Type: "Seq Scan"
                  Parent Relationship: "Inner"
                  Parallel Aware: false
                  Relation Name: "spree_variant_price_sets"
                  Alias: "svps"
                  Startup Cost: 0.00
                  Total Cost: 1.03
                  Plan Rows: 3
                  Plan Width: 36
            - Node Type: "Seq Scan"
              Parent Relationship: "Inner"
              Parallel Aware: false
              Relation Name: "spree_account_prices"
              Alias: "sap_1"
              Startup Cost: 0.00
              Total Cost: 1.90
              Plan Rows: 4
              Plan Width: 8
              Filter: "(((status)::text = 'temporary'::text) AND ((accountable_type)::text = 'Spree::CorporateAccount'::text))"
        - Node Type: "Seq Scan"
          Parent Relationship: "Inner"
          Parallel Aware: false
          Relation Name: "spree_variant_price_sets"
          Alias: "svps_1"
          Startup Cost: 0.00
          Total Cost: 1.03
          Plan Rows: 3
          Plan Width: 36        
  </code></pre>
  
  <p>Here is the Ruby on Rails query </p>
  
  <pre><code>@search = Spree::CorporateAccount.joins(memberships: [:user])
                                           .joins("LEFT JOIN (SELECT 
                                                                sap.accountable_id, 
                                                                sap.accountable_type, 
                                                                sap.variant_price_set_id,
                                                                svps.name
                                                              FROM 
                                                                spree_account_prices sap
                                                                LEFT JOIN spree_variant_price_sets svps on sap.variant_price_set_id = svps.id
                                                                where status = 'default' 
                                                              ) sap_default on sap_default.accountable_id = spree_corporate_accounts.id and sap_default.accountable_type = 'Spree::CorporateAccount' ")
                                           .joins("LEFT JOIN (SELECT 
                                                                sap.accountable_id,
                                                                sap.accountable_type,
                                                                sap.variant_price_set_id,
                                                                svps.name
                                                              FROM 
                                                                spree_account_prices sap
                                                                LEFT JOIN spree_variant_price_sets svps on sap.variant_price_set_id = svps.id
                                                                where status = 'temporary' 
                                                              ) sap_temporary on sap_temporary.accountable_id = spree_corporate_accounts.id and sap_temporary.accountable_type = 'Spree::CorporateAccount' ")
                                           .joins('LEFT JOIN spree_addresses ON spree_addresses.id = spree_corporate_accounts.bill_address_id')
                                           .joins('LEFT JOIN spree_states ON spree_states.id = spree_addresses.state_id')
                                           .joins('LEFT JOIN spree_countries ON spree_countries.id = spree_addresses.country_id')
                                           .where('spree_memberships.deleted_at IS NULL')
                                           .order('spree_corporate_accounts.company_name')
                                           .order('spree_memberships.admin DESC')
                                           .ransack(params[:q])
  </code></pre>
  
  <p>here is the benchmark/result for joins</p>
  
  <pre><code>0.010000    0.000000    0.010000    (  0.011334)
  0.030000    0.000000    0.030000    (  0.030845)
  0.010000    0.000000    0.010000    (  0.013424)
  0.010000    0.000000    0.010000    (  0.012594)
  0.010000    0.000000    0.010000    (  0.012120)
  </code></pre>
  
  <p>the pluck for getting the data</p>
  
  <pre><code>        @corporate_user_list = @search.result.pluck('spree_corporate_accounts.id',
                                                      'spree_corporate_accounts.company_name',
                                                      'spree_memberships.admin',
                                                      'spree_users.email',
                                                      'spree_users.doctor AS NAME',
                                                      'coalesce(spree_corporate_accounts.account_key, spree_users.account_key) as effective_account_key',
                                                      "CASE spree_corporate_accounts.billing_type when 1 THEN 'Postbill' WHEN 2 THEN 'Creditcard' ELSE 'Individual'END",
                                                      "sap_default.name",
                                                      "sap_temporary.name",
                                                      'spree_addresses.address1',
                                                      'spree_addresses.address2',
                                                      'spree_addresses.city',
                                                      'spree_addresses.zipcode',
                                                      'spree_states.name AS state',
                                                      'spree_countries.name AS country',
                                                      'spree_addresses.phone',
                                                      "spree_users.created_at")
  </code></pre>
  
  <p>benchmark result from pluck</p>
  
  <pre><code>0.010000    0.000000    0.010000    (  0.020869)
  0.020000    0.000000    0.020000    (  0.022321)
  0.020000    0.000000    0.020000    (  0.021910)
  0.010000    0.000000    0.010000    (  0.019196)
  0.010000    0.000000    0.010000    (  0.014794)
  </code></pre>
  
  <p>-------------------++++++++++++++++++++++++++++++++++++++++++++++++++----------------------</p>
  
  <p>Here is the second query Postgres and ruby on rails with result</p>
  
  <p>postgres query with inner select</p>
  
  <pre><code>SELECT 
  su.id AS user_id, 
  su.email,
  su.doctor,
  sca.id AS copr_accnt_id,
  sca.company_id,
  sca.company_name,
  sap_default.variant_price_set_id AS sap_default_id,
  sap_temporary.variant_price_set_id AS sap_temporary_id,
  (SELECT name FROM spree_variant_price_sets where id = sap_default.variant_price_set_id ) AS default, 
  (SELECT name FROM spree_variant_price_sets where id = sap_temporary.variant_price_set_id) As temporary
  from spree_corporate_accounts sca
  INNER JOIN "spree_memberships" sm
    ON "sm"."corporate_account_id" = "sca"."id"
    AND "sm"."deleted_at" IS NULL
  INNER JOIN "spree_users" su
    ON "su"."id" = "sm"."user_id"
    AND "su"."deleted_at" IS NULL
  -- where sca.id in (5)
  LEFT JOIN (
      select
      sap.id,
      sap.accountable_id, 
      sap.accountable_type,
      sap.variant_price_set_id
      from spree_account_prices sap
      where status = 'default'
  ) sap_default on sap_default.accountable_id = sca.id and sap_default.accountable_type = 'Spree::CorporateAccount'
  LEFT JOIN (
      select
      sap.id,
      sap.accountable_id, 
      sap.accountable_type,
      sap.variant_price_set_id
      from spree_account_prices sap
      where status = 'temporary'
  ) sap_temporary on sap_temporary.accountable_id = sca.id and sap_temporary.accountable_type = 'Spree::CorporateAccount';
  </code></pre>
  
  <p>result benchmark postgresql format YAML</p>
  
  <pre><code>- Plan: 
      Node Type: "Nested Loop"
      Parallel Aware: false
      Join Type: "Left"
      Startup Cost: 1.32
      Total Cost: 12.06
      Plan Rows: 1
      Plan Width: 187
      Join Filter: "(sap_1.accountable_id = sca.id)"
      Plans: 
        - Node Type: "Nested Loop"
          Parent Relationship: "Outer"
          Parallel Aware: false
          Join Type: "Left"
          Startup Cost: 1.32
          Total Cost: 8.03
          Plan Rows: 1
          Plan Width: 119
          Join Filter: "(sap.accountable_id = sca.id)"
          Plans: 
            - Node Type: "Nested Loop"
              Parent Relationship: "Outer"
              Parallel Aware: false
              Join Type: "Inner"
              Startup Cost: 1.32
              Total Cost: 6.08
              Plan Rows: 1
              Plan Width: 115
              Join Filter: "(sm.corporate_account_id = sca.id)"
              Plans: 
                - Node Type: "Hash Join"
                  Parent Relationship: "Outer"
                  Parallel Aware: false
                  Join Type: "Inner"
                  Startup Cost: 1.32
                  Total Cost: 4.99
                  Plan Rows: 1
                  Plan Width: 51
                  Hash Cond: "(su.id = sm.user_id)"
                  Plans: 
                    - Node Type: "Seq Scan"
                      Parent Relationship: "Outer"
                      Parallel Aware: false
                      Relation Name: "spree_users"
                      Alias: "su"
                      Startup Cost: 0.00
                      Total Cost: 3.56
                      Plan Rows: 27
                      Plan Width: 47
                      Filter: "(deleted_at IS NULL)"
                    - Node Type: "Hash"
                      Parent Relationship: "Inner"
                      Parallel Aware: false
                      Startup Cost: 1.31
                      Total Cost: 1.31
                      Plan Rows: 1
                      Plan Width: 8
                      Plans: 
                        - Node Type: "Seq Scan"
                          Parent Relationship: "Outer"
                          Parallel Aware: false
                          Relation Name: "spree_memberships"
                          Alias: "sm"
                          Startup Cost: 0.00
                          Total Cost: 1.31
                          Plan Rows: 1
                          Plan Width: 8
                          Filter: "(deleted_at IS NULL)"
                - Node Type: "Seq Scan"
                  Parent Relationship: "Inner"
                  Parallel Aware: false
                  Relation Name: "spree_corporate_accounts"
                  Alias: "sca"
                  Startup Cost: 0.00
                  Total Cost: 1.04
                  Plan Rows: 4
                  Plan Width: 68
            - Node Type: "Seq Scan"
              Parent Relationship: "Inner"
              Parallel Aware: false
              Relation Name: "spree_account_prices"
              Alias: "sap"
              Startup Cost: 0.00
              Total Cost: 1.90
              Plan Rows: 4
              Plan Width: 8
              Filter: "(((status)::text = 'default'::text) AND ((accountable_type)::text = 'Spree::CorporateAccount'::text))"
        - Node Type: "Seq Scan"
          Parent Relationship: "Inner"
          Parallel Aware: false
          Relation Name: "spree_account_prices"
          Alias: "sap_1"
          Startup Cost: 0.00
          Total Cost: 1.90
          Plan Rows: 4
          Plan Width: 8
          Filter: "(((status)::text = 'temporary'::text) AND ((accountable_type)::text = 'Spree::CorporateAccount'::text))"
        - Node Type: "Seq Scan"
          Parent Relationship: "SubPlan"
          Subplan Name: "SubPlan 1"
          Parallel Aware: false
          Relation Name: "spree_variant_price_sets"
          Alias: "spree_variant_price_sets"
          Startup Cost: 0.00
          Total Cost: 1.04
          Plan Rows: 1
          Plan Width: 32
          Filter: "(id = sap.variant_price_set_id)"
        - Node Type: "Seq Scan"
          Parent Relationship: "SubPlan"
          Subplan Name: "SubPlan 2"
          Parallel Aware: false
          Relation Name: "spree_variant_price_sets"
          Alias: "spree_variant_price_sets_1"
          Startup Cost: 0.00
          Total Cost: 1.04
          Plan Rows: 1
          Plan Width: 32
          Filter: "(id = sap_1.variant_price_set_id)"        
  </code></pre>
  
  <p>heres the ruby on rails with result from benchmark</p>
  
  <pre><code>@search = Spree::CorporateAccount.joins(memberships: [:user])
                                           .joins("LEFT JOIN ( SELECT accountable_id, accountable_type, variant_price_set_id from spree_account_prices where status = 'default' ) sap_default on sap_default.accountable_id = spree_corporate_accounts.id and sap_default.accountable_type = 'Spree::CorporateAccount' ")
                                           .joins("LEFT JOIN ( SELECT accountable_id, accountable_type, variant_price_set_id from spree_account_prices where status = 'temporary' ) sap_temporary on sap_temporary.accountable_id = spree_corporate_accounts.id and sap_temporary.accountable_type = 'Spree::CorporateAccount' ")
                                           .joins('LEFT JOIN spree_addresses ON spree_addresses.id = spree_corporate_accounts.bill_address_id')
                                           .joins('LEFT JOIN spree_states ON spree_states.id = spree_addresses.state_id')
                                           .joins('LEFT JOIN spree_countries ON spree_countries.id = spree_addresses.country_id')
                                           .where('spree_memberships.deleted_at IS NULL')
                                           .order('spree_corporate_accounts.company_name')
                                           .order('spree_memberships.admin DESC')
                                           .ransack(params[:q])
          }
  </code></pre>
  
  <p>result:</p>
  
  <pre><code>0.010000    0.000000    0.010000    (  0.011175)
  0.030000    0.000000    0.030000    (  0.030159)
  0.010000    0.000000    0.010000    (  0.012327)
  0.020000    0.000000    0.020000    (  0.011852)
  0.010000    0.000000    0.010000    (  0.011519)
  </code></pre>
  
  <p>pluck with select benchmark result</p>
  
  <pre><code>@corporate_user_list = @search.result.pluck('spree_corporate_accounts.id',
                                                      'spree_corporate_accounts.company_name',
                                                      'spree_memberships.admin',
                                                      'spree_users.email',
                                                      'spree_users.doctor AS NAME',
                                                      'coalesce(spree_corporate_accounts.account_key, spree_users.account_key) as effective_account_key',
                                                      "CASE spree_corporate_accounts.billing_type when 1 THEN 'Postbill' WHEN 2 THEN 'Creditcard' ELSE 'Individual'END",
                                                      "(SELECT name FROM spree_variant_price_sets where id = sap_default.variant_price_set_id )",
                                                      "(SELECT name FROM spree_variant_price_sets where id = sap_temporary.variant_price_set_id )",
                                                      'spree_addresses.address1',
                                                      'spree_addresses.address2',
                                                      'spree_addresses.city',
                                                      'spree_addresses.zipcode',
                                                      'spree_states.name AS state',
                                                      'spree_countries.name AS country',
                                                      'spree_addresses.phone',
                                                      "spree_users.created_at")
  </code></pre>
  
  <p>result:</p>
  
  <pre><code>0.040000    0.040000    0.040000    ( 53.477710)
  0.020000    0.010000    0.030000    ( 32.826345)
  0.030000    0.000000    0.030000    ( 34.568018)
  0.030000    0.000000    0.030000    ( 23.509827)
  0.030000    0.010000    0.040000    ( 45.076011)
  </code></pre>
  
  <p>so my question is which one should I use? the 1st postgres query with the inner join is slower than the postgres query with inner select. but when they are used in ruby on rails. they have different result. the inner join and pluck is much faster than the join and pluck with inner select by a significant amount. but this a raw sql wrap on ruby methods though like im confused. can you give me a link how active record initiate raw sql or maybe explain how its inner working? also when you check this on console it gives the same result on postgres, it spit out the same query. The inconsistency bothers me.  </p>