Skip to content

Instantly share code, notes, and snippets.

@daniloisr
Created December 7, 2022 17:40
Show Gist options
  • Save daniloisr/9503bb3fca85c644a01f635a9148e56e to your computer and use it in GitHub Desktop.
Save daniloisr/9503bb3fca85c644a01f635a9148e56e to your computer and use it in GitHub Desktop.
ROWS = 1_000_000
ACCOUNTS = 20
REBUILD = true
require 'bundler/inline'
gemfile do
source 'https://rubygems.org'
gem 'activerecord', '~> 4.2'
gem 'pg'
gem 'benchmark-ips'
end
require 'active_record'
require 'logger'
pg_opts = {adapter: "postgresql", username: 'postgres', password: 'postgres', database: "appt_time_range_bench"}
begin
ActiveRecord::Base.establish_connection(pg_opts.except(:database))
ActiveRecord::Base.connection.create_database(pg_opts[:database])
puts "Database #{pg_opts[:database]} was created."
rescue => e
puts e
puts "Database #{pg_opts[:database]} already exists.\n"
end
ActiveRecord::Base.establish_connection(pg_opts)
ActiveRecord::Base.logger = Logger.new(STDOUT)
begin
ActiveRecord::Schema.define do
create_table :appts, force: REBUILD do |t|
t.integer :account_id
t.datetime :start_at
t.datetime :until_at
t.tstzrange :time_range
t.tstzrange :time_range2
end
end
rescue PG::DuplicateTable, ActiveRecord::StatementInvalid
nil
end
con = ActiveRecord::Base.connection
con.execute('CREATE EXTENSION IF NOT EXISTS btree_gist;')
count = con.select_values('select count(*) from appts').first
if count.to_i == 0
puts "inserting #{ROWS}..."
con.execute(<<~SQL)
INSERT
INTO appts(account_id, start_at, until_at, time_range, time_range2)
SELECT acc
, t.start
, t.start + t.duration
, tstzrange(t.start, t.start + t.duration)
, tstzrange(t.start, t.start + t.duration)
FROM (
SELECT (date '2020-01-01' + (d % 700) + interval '4h' + interval '15 min' * trunc(16 * random())) AT TIME ZONE 'UTC' as start
, (interval '15 min' * trunc(6 * random())) as duration
, acc
FROM generate_series(1, #{ROWS / ACCOUNTS}) d
JOIN generate_series(1, #{ACCOUNTS}) acc ON TRUE
) t;
SQL
count = con.select_values('select count(*) from appts').first
puts "Inserted #{count} records on #{ACCOUNTS} accounts"
puts "creating indexes..."
con.execute('CREATE INDEX IF NOT EXISTS start_until ON appts USING BTREE (start_at, until_at, account_id);')
con.execute('CREATE INDEX IF NOT EXISTS time_range_gist ON appts USING GIST (time_range, account_id);')
con.execute('CREATE INDEX IF NOT EXISTS time_range_spgist ON appts USING SPGIST (time_range2);')
puts "done"
else
puts "Database already seeded with #{count} records"
end
query_range = Time.parse('2020-02-02 10:00:00')..Time.parse('2020-02-03 10:00:00')
# to simulate closed search on start/until fields
query_range2 = Time.parse('2020-02-01 10:00:00')..Time.parse('2020-02-04 10:00:00')
ActiveRecord::Base.logger = nil
Benchmark.ips do |x|
x.config(time: 15, warmup: 4)
x.report("start,until closed") do
con.execute(<<~SQL)
select count(*)
from appts
where account_id = 1
and start_at > '#{query_range2.begin}' and start_at < '#{query_range2.end}'
and until_at > '#{query_range2.begin}' and until_at < '#{query_range2.end}'
SQL
end
x.report("start,until open") do
con.execute(<<~SQL)
select count(*)
from appts
where account_id = 1
and start_at < '#{query_range.end}'
and until_at > '#{query_range.begin}'
SQL
end
x.report("time_range spgist") do
con.execute(<<~SQL)
select count(*)
from appts
where account_id = 1
and time_range2 && '[#{query_range.begin}, #{query_range.end}]'
SQL
end
x.report("time_range gist") do
con.execute(<<~SQL)
select count(*)
from appts
where account_id = 1
and time_range && '[#{query_range.begin}, #{query_range.end}]'
SQL
end
x.compare!
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment