join-vs-subquery.rb

require "sequel"
require "benchmark"

system "createdb testing"
DB = Sequel.postgres("testing")

at_exit do
  DB.disconnect
  system "dropdb testing"
end

DB.create_table :managers do
  primary_key :id
  String :name
end

DB.create_table :job_listings do
  primary_key :id
  foreign_key :manager_id, :managers
  String :title
end

class JobListing < Sequel::Model
  many_to_one :manager
end

class Manager < Sequel::Model
  one_to_many :job_listings
end

Manager.multi_insert Array.new(100_000, { name: "Foo" })
JobListing.multi_insert Array.new(100_000) { |i| { manager_id: i + 1 } }
JobListing.multi_insert Array.new(5_000, { title: "Foo" })

puts Benchmark.realtime { JobListing.exclude(manager: Manager.dataset).all }
puts Benchmark.realtime { JobListing.association_left_join(:manager).where(Sequel[:manager][:id] => nil).all }

# 0.08924499992281199
# 0.12003500014543533