gist on range investigation.rb

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