pg_result_lars2.rb

# frozen_string_literal: true

require 'bundler/inline'

gemfile do
  source 'https://rubygems.org'
  gem "pg", "~> 1.1"
  gem 'activesupport', path: '../activesupport'
  gem 'activerecord', path: '../activerecord'
  gem 'activemodel', path: '../activemodel'
#   gem 'activesupport', '5.2.0'
#   gem 'activerecord', '5.2.0'
#   gem 'activemodel', '5.2.0'
  gem 'benchmark-ips'
end

require 'active_record'
require 'benchmark/ips'


ActiveRecord::Base.establish_connection(
  :adapter => "postgresql",
  :database => "test_db"
)

pg = ActiveRecord::Base.connection.raw_connection

pg.async_exec <<SQL
drop table if exists topics
SQL

pg.async_exec <<SQL
CREATE TABLE topics (
    id integer NOT NULL,
    title character varying NOT NULL,
    last_posted_at timestamp without time zone,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL,
    views integer DEFAULT 0 NOT NULL,
    posts_count integer DEFAULT 0 NOT NULL,
    user_id integer,
    last_post_user_id integer NOT NULL,
    reply_count integer DEFAULT 0 NOT NULL,
    featured_user1_id integer,
    featured_user2_id integer,
    featured_user3_id integer,
    avg_time integer,
    deleted_at timestamp without time zone,
    highest_post_number integer DEFAULT 0 NOT NULL,
    image_url character varying,
    like_count integer DEFAULT 0 NOT NULL,
    incoming_link_count integer DEFAULT 0 NOT NULL,
    category_id integer,
    visible boolean DEFAULT true NOT NULL,
    moderator_posts_count integer DEFAULT 0 NOT NULL,
    closed boolean DEFAULT false NOT NULL,
    archived boolean DEFAULT false NOT NULL,
    bumped_at timestamp without time zone NOT NULL,
    has_summary boolean DEFAULT false NOT NULL,
    vote_count integer DEFAULT 0 NOT NULL,
    archetype character varying DEFAULT 'regular'::character varying NOT NULL,
    featured_user4_id integer,
    notify_moderators_count integer DEFAULT 0 NOT NULL,
    spam_count integer DEFAULT 0 NOT NULL,
    pinned_at timestamp without time zone,
    score double precision,
    percent_rank double precision DEFAULT 1.0 NOT NULL,
    subtype character varying,
    slug character varying,
    deleted_by_id integer,
    participant_count integer DEFAULT 1,
    word_count integer,
    excerpt character varying(1000),
    pinned_globally boolean DEFAULT false NOT NULL,
    pinned_until timestamp without time zone,
    fancy_title character varying(400),
    highest_staff_post_number integer DEFAULT 0 NOT NULL,
    featured_link character varying
)
SQL

class Topic < ActiveRecord::Base
end

Topic.transaction do
  topic = {
  }
  Topic.columns.each do |c|
    topic[c.name.to_sym] = case c.type
                           when :integer then 1
                           when :datetime then Time.now
                           when :boolean then false
                           else "HELLO WORLD" * 2
                           end
  end

  2000.times do |id|
    topic[:id] = id
    Topic.create!(topic)
  end
end

def ten_topics_select
  r = []
  Topic.limit(10).select(:id, :title).each do |t|
    r << t.id
    r << t.title
  end
  r
end

def top_1000_wasteful
  a = []
  Topic.limit(1000).each do |t|
    a << t.id
  end
  a
end

def pluck(n)
  Topic.limit(n).pluck(:id, :title)
end


Benchmark.ips do |x|
  x.report("top 10 id / title PG") do |i|
    while i > 0
      ten_topics_select
      i -= 1
    end
  end
  x.report("top 1000 id wasteful") do |i|
    while i > 0
      top_1000_wasteful
      i -= 1
    end
  end
  x.report("pluck 1000 mode") do |i|
    while i > 0
      pluck(1000)
      i -= 1
    end
  end
end

# rails branch master:
#
# Warming up --------------------------------------
# top 10 id / title PG   236.000  i/100ms
# top 1000 id wasteful     3.000  i/100ms
#      pluck 1000 mode    38.000  i/100ms
# Calculating -------------------------------------
# top 10 id / title PG      2.430k (± 1.7%) i/s -     12.272k in   5.051112s
# top 1000 id wasteful     36.095  (± 5.5%) i/s -    180.000  in   5.010574s
#      pluck 1000 mode    381.441  (± 1.3%) i/s -      1.938k in   5.081686s
#
# rails branch hyperrecord-v2:
#
# Warming up --------------------------------------
# top 10 id / title PG   242.000  i/100ms
# top 1000 id wasteful     6.000  i/100ms
#      pluck 1000 mode    39.000  i/100ms
# Calculating -------------------------------------
# top 10 id / title PG      2.849k (± 1.0%) i/s -     14.278k in   5.012458s
# top 1000 id wasteful     61.599  (± 6.5%) i/s -    312.000  in   5.081960s
#      pluck 1000 mode    401.916  (± 1.2%) i/s -      2.028k in   5.046694s