pg_result_lars.rb

# frozen_string_literal: true
# tiny rerunner (I use this on local to rerun script)
# require_relative '../rerunner/rerunner.rb'

require 'bundler/inline'

gemfile do
  source 'https://rubygems.org'
  gem 'pg', path: '../ruby-pg.git'
  gem 'activesupport', path: '../rails/activesupport'
  gem 'activerecord', path: '../rails/activerecord'
  gem 'activemodel', path: '../rails/activemodel'
#   gem 'activesupport', '5.2.0'
#   gem 'activerecord', '5.2.0'
#   gem 'activemodel', '5.2.0'
  gem 'memory_profiler'
  gem 'benchmark-ips'
  #gem 'helix_runtime'
  #gem 'rails_fast_attributes', path: '../../rails_fast_attributes'
  gem 'byebug'
end

require 'active_record'
require 'memory_profiler'
require 'benchmark/ips'
#require 'rails_fast_attributes'


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

def fast_mode(v)
  return if $fast_mode == v
  if ActiveRecord::ConnectionAdapters::PostgreSQL::DatabaseStatements.respond_to?(:switch_exec_query)
    if !v
      ActiveRecord::ConnectionAdapters::PostgreSQL::DatabaseStatements.switch_exec_query :immediate
      $fast_mode = false
    else
      ActiveRecord::ConnectionAdapters::PostgreSQL::DatabaseStatements.switch_exec_query :deferred
      $fast_mode = true
    end
  end
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

#
# MemoryProfiler.report do
#   ten_topics_select
# end.pretty_print
#
# exit

Benchmark.ips do |x|
  [true, false].each do |mode|
    x.report("top 10 id / title PG: fast mode: #{mode}") do |i|
      fast_mode(mode)
      while i > 0
        ten_topics_select
        i -= 1
      end
    end
  end

  x.compare!
end

Benchmark.ips do |x|
  [true, false].each do |mode|
    x.report("top 1000 id wasteful: fast mode: #{mode} ") do |i|
      fast_mode(mode)
      while i > 0
        top_1000_wasteful
        i -= 1
      end
    end
  end

  x.compare!
end

Benchmark.ips do |x|
  [true, false, true].each do |mode|
    x.report("pluck 1000 mode: #{mode} ") do |i|
      fast_mode(mode)
      while i > 0
        pluck(1000)
        i -= 1
      end
    end
  end

  x.compare!
end

# With ActiveRecord-5.2.0 (no fast mode switch):
#
# lars@isa:~/comcard/ruby-pg.git$  git:(𝘮 1 ⇄ 4 lazytuple) 26A ruby pg_result_lars.rb
# Calculating -------------------------------------
# top 10 id / title PG:
#                           2.038k (± 3.1%) i/s -     10.250k in   5.033685s
# top 10 id / title PG:
#                           1.992k (± 7.6%) i/s -     10.000k in   5.051506s
#
# Comparison:
# top 10 id / title PG:     2038.3 i/s
# top 10 id / title PG:     1991.5 i/s - same-ish: difference falls within error
#
# Calculating -------------------------------------
# top 1000 id wasteful:
#                          32.925  (± 9.1%) i/s -    164.000  in   5.029910s
# top 1000 id wasteful:
#                          34.699  (± 8.6%) i/s -    174.000  in   5.044240s
#
# Comparison:
# top 1000 id wasteful:       34.7 i/s
# top 1000 id wasteful:       32.9 i/s - same-ish: difference falls within error
#
# Calculating -------------------------------------
# pluck 1000
#                         410.939  (± 8.3%) i/s -      2.072k in   5.075610s
# pluck 1000
#                         388.984  (± 4.1%) i/s -      1.976k in   5.087424s
# pluck 1000
#                         381.540  (± 2.6%) i/s -      1.924k in   5.045817s
#
# Comparison:
# pluck 1000:      389.0 i/s
# pluck 1000:      381.5 i/s - same-ish: difference falls within error
#


# With ActiveRecord from larskanis/rails branch hyperrecord-v1 commit 5d8e949c0a:
# - Some small improvements without PG specific Result.
# - 1.7x for wastful query with PG specific Result compared to AR-5.2.0 above.
#
# lars@isa:~/comcard/ruby-pg.git$  git:(𝘮 1 ⇄ 4 lazytuple) 26A ruby pg_result_lars.rb
# Calculating -------------------------------------
# top 10 id / title PG: fast mode: true
#                           2.235k (± 1.9%) i/s -     11.340k in   5.075093s
# top 10 id / title PG: fast mode: false
#                           2.107k (± 3.2%) i/s -     10.659k in   5.065300s
#
# Comparison:
# top 10 id / title PG: fast mode: true:     2235.4 i/s
# top 10 id / title PG: fast mode: false:     2106.6 i/s - 1.06x  slower
#
# Calculating -------------------------------------
# top 1000 id wasteful: fast mode: true
#                          56.497  (± 3.5%) i/s -    285.000  in   5.049722s
# top 1000 id wasteful: fast mode: false
#                          37.008  (± 2.7%) i/s -    186.000  in   5.031846s
#
# Comparison:
# top 1000 id wasteful: fast mode: true :       56.5 i/s
# top 1000 id wasteful: fast mode: false :       37.0 i/s - 1.53x  slower
#
# Calculating -------------------------------------
# pluck 1000 mode: true
#                         453.046  (± 5.3%) i/s -      2.303k in   5.099488s
# pluck 1000 mode: false
#                         448.301  (± 5.1%) i/s -      2.250k in   5.033459s
# pluck 1000 mode: true
#                         458.696  (± 2.2%) i/s -      2.295k in   5.005712s
#
# Comparison:
# pluck 1000 mode: true :      458.7 i/s
# pluck 1000 mode: false :      448.3 i/s - same-ish: difference falls within error