extend_expense_approvers.md

Query methods

def account_approvers(account)
  User.
    where(id: AccountMembership.
              where('is_approver OR is_admin').
              where(account_id: account.id, suspended: false).
              select(:user_id)).
    or(User.where(id: BkMembership.
                      where(account_id: account.parent_account_id, client_account_id: account.id).
                      select(:user_id)))
end

Explain:

Seq Scan on users  (cost=266.26..74814.19 rows=822746 width=632)
   Filter: ((hashed SubPlan 1) OR (hashed SubPlan 2))
   SubPlan 1
     ->  Index Scan using index_account_memberships_on_account_id on account_memberships  (cost=0.43..12.01 rows=2 width=4)
           Index Cond: (account_id = 409541)
           Filter: ((is_approver OR is_admin) AND (NOT suspended))
   SubPlan 2
     ->  Index Scan using index_bk_membership_on_account_id on bk_memberships  (cost=0.43..254.24 rows=1 width=4)
           Index Cond: (account_id = 241)
           Filter: (client_account_id = 409541)

def account_approvers_sql(account)
  User.where(<<-SQL, account_id: account.id, parent_account_id: account.parent_account_id)
    users.id IN (
      (
        SELECT account_memberships.user_id
        FROM account_memberships
        WHERE account_id = :account_id AND (is_approver OR is_admin)

        UNION ALL

        SELECT bk_memberships.user_id
        FROM bk_memberships
        WHERE account_id = :parent_account_id AND client_account_id = :account_id
      )

      EXCEPT

      SELECT account_memberships.user_id
      FROM account_memberships
      WHERE account_id = :account_id AND suspended
    )
  SQL
end

Explain:

Nested Loop  (cost=266.72..291.66 rows=3 width=628)
   ->  HashAggregate  (cost=266.29..266.32 rows=3 width=4)
         Group Key: account_memberships.user_id
         ->  Append  (cost=0.43..266.29 rows=3 width=4)
               ->  Index Scan using index_account_memberships_on_account_id on account_memberships  (cost=0.43..12.01 rows=2 width=4)
                     Index Cond: (account_id = 409541)
                     Filter: ((is_approver OR is_admin) AND (NOT suspended))
               ->  Index Scan using index_bk_membership_on_account_id on bk_memberships  (cost=0.43..254.24 rows=1 width=4)
                     Index Cond: (account_id = 241)
                     Filter: (client_account_id = 409541)
   ->  Index Scan using users_pkey on users  (cost=0.43..8.45 rows=1 width=628)
         Index Cond: (id = account_memberships.user_id)

Benchmarks

def measure(seed: rand(1..100_000))
  clients = Account.where.not(parent_account_id: nil).order('name').offset(seed).limit(50); nil

  Benchmark.bm do |x|
    x.report('union query') do
      clients.each { |c| account_approvers_sql(c).to_a }
    end

    x.report('nested query') do
      clients.each { |c| account_approvers(c).to_a }
    end
  end
end

measure(seed: 100)
# =>
#                user     system      total        real
# union query  0.044000   0.004000   0.048000 ( 13.502643)
# nested query 0.116000   0.008000   0.124000 ( 30.368886)

NOTE

The two queries are not exactly equivalent, but the slowest one "do less stuff" so I think that still proves the perfomence difference.